所以我有 2 個檔案，一個客戶端和一個 db 類：
import sqlite3
from os.path import isfile
class Database:
def __init__(self, db_name):
self.db_name = db_name
self.check = self.__check_db()
self.conn = sqlite3.connect(self.db_name, check_same_thread=False)
self.cursor = self.conn.cursor()
if not self.check:
self.__create_db()
def query(self, query, args=()):
self.cursor.execute(query, args)
result = self.cursor.fetchall()
self.conn.commit()
return result
def __check_db(self):
return isfile(self.db_name)
def __create_db(self):
self.cursor.executescript("""
PRAGMA FOREIGN_KEYS = ON;
CREATE TABLE utilizadores (id INTEGER PRIMARY KEY, nome TEXT, senha TEXT);
CREATE TABLE artistas (id INTEGER PRIMARY KEY, id_spotify TEXT, nome TEXT);
CREATE TABLE musicas (id INTEGER PRIMARY KEY, id_spotify TEXT, nome TEXT, id_artista INTEGER, FOREIGN KEY(id_artista) REFERENCES artistas(id) ON DELETE CASCADE);
CREATE TABLE avaliacoes (id INTEGER PRIMARY KEY, sigla TEXT, designacao TEXT);
CREATE TABLE playlists (id_user INTEGER, id_musica INTEGER, id_avaliacao INTEGER, PRIMARY KEY (id_user, id_musica), FOREIGN KEY(id_user) REFERENCES utilizadores(id) ON DELETE CASCADE, FOREIGN KEY(id_musica) REFERENCES musicas(id) ON DELETE CASCADE, FOREIGN KEY(id_avaliacao) REFERENCES avaliacoes(id) ON DELETE CASCADE);
INSERT INTO avaliacoes (id, sigla, designacao) VALUES (1, "M", "Medíocre"), (2, "m", "Mau"), (3, "S", "Suficiente"), (4, "B", "Boa"), (5, "MB", "Muito Boa");
""")
和客戶；
from database import Database
db = Database("spotify.db")
db.query("insert into artistas (id_spotify, nome) values (?, ?)", ("1", "The Beatles"))
db.query("insert into musicas (id_spotify, nome, id_artista) values (?, ?, ?)", ("m1", "Hey Jude", 1))
db.query("delete from artistas where id = ?", (1,))
如果我因為外鍵而進行最后一次查詢，它也應該洗掉音樂，但由于某種原因它不會。我不知道這樣做的原因，因為我有 PRAGMA fk = on 并且我在同一個連接上
藝術家 --> 藝術家
音樂 --> 音樂
uj5u.com熱心網友回復：
PRAGMA FOREIGN_KEYS = ON在每個連接的基礎上運行，這意味著您必須為您獲得的每個連接物件設定它（如果您想要它提供的行為，否則不要設定它，因為它可能會降低性能）。
在Database類內部定義：
def set_foreign_keys(self)
self.conn.execute("PRAGMA FOREIGN_KEYS = ON")
并在客戶端使用它：
from database import Database
db = Database("spotify.db")
db.set_foreign_keys()
db.query("insert into artistas (id_spotify, nome) values (?, ?)", ("1", "The Beatles"))
db.query("insert into musicas (id_spotify, nome, id_artista) values (?, ?, ?)", ("m1", "Hey Jude", 1))
db.query("delete from artistas where id = ?", (1,))
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/465369.html