SQLAlchemy 表达式语言和 SQLite 的删除级联 [英] SQLAlchemy expression language and SQLite's on delete cascade

问题描述

我有两个相关表，分别是users和roles，它们之间的关系是多对多的，所以另一个关联表userroles也存在.

I have two related tables namely users and roles and the relationship between them is many-to-many, so another association table userroles also exists.

userroles 表使用外键跟踪关联的 users 和 roles 行.对于外键，ondelete 参数设置为 "CASCADE" 以在删除任何关联元素时删除关联行.

The userroles table keeps track of the associated users and roles rows using foreign keys. For the foreign keys the ondelete parameter is set to "CASCADE" to get rid of the association row if any of the associated elements are being deleted.

这是我的设置:

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))

但是，当我删除具有 1 唯一 ID 的 Admin 角色时，关联的行不会从 userroles 表中删除.为什么?

However when I delete the Admin role which has the unique id of 1, the associated row is not being deleted from the userroles table. Why?

我在这里遗漏了什么?

推荐答案

好吧，看来您需要为 sqlite 强制执行外键.因此，基于此答案，应该这样做:

Okay it seems like you need to enforce foreign keys for sqlite. So based on this answer one should do:

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()

这篇关于SQLAlchemy 表达式语言和 SQLite 的删除级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！