我有两个相关的表,即users
和roles
,它们之间的关系是多对多的,所以另一个关联表也存在userroles
。
userroles
表使用外键跟踪关联的users
和roles
行。对于外键,ondelete
参数设置为"CASCADE"
以在删除任何关联元素时删除关联行。
这是我的设置:
import sqlalchemy as sa
engine = sa.create_engine("sqlite:///:memory:", echo=True)
metadata = sa.MetaData()
userroles = sa.Table(
"userroles",
metadata,
sa.Column("user_id", sa.Integer, sa.ForeignKey("users.id", ondelete="CASCADE")),
sa.Column("role_id", sa.Integer, sa.ForeignKey("roles.id", ondelete="CASCADE")),
)
users = sa.Table(
"users",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String),
)
roles = sa.Table(
"roles",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String),
)
metadata.create_all(engine)
conn = engine.connect()
conn.execute(users.insert().values(name="Joe"))
conn.execute(roles.insert().values(name="Admin"))
conn.execute(roles.insert().values(name="User"))
conn.execute(userroles.insert().values(user_id=1, role_id=1))
但是,当我删除唯一 id 为1
的Admin
角色时,关联的行不会从userroles
表中删除。为什么?
我在这里错过了什么?
好吧,您似乎需要为 sqlite 强制执行外键。所以基于这个答案,人们应该做:
from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection
@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
if isinstance(dbapi_connection, SQLite3Connection):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON;")
cursor.close()