Python的SQLAlchemy不会清除辅助(多对多)表吗? [英] Python's SQLAlchemy doesn't clean out the secondary (many-to-many) table?

问题描述

我在User s和Task s之间有多对多关系.我希望在删除Task或User时清除辅助表"(即有助于多对多关系的表).如何为此配置SQLAlchemy?

I have a many-to-many relationship between Users and Tasks. I want the "secondary table" (meaning, the table that facilitates the many-to-many relation) to be cleaned out when I delete a Task or User. How can I configure SQLAlchemy for this?

这是一些示例python代码，用于演示我遇到的问题.注意:此代码完全独立，仅需sqlalchemy模块.如果复制并粘贴此代码，则应该能够运行它而没有任何副作用，并且自己可以看到相同的行为.脚本的最后一行显示，当我删除相应的任务时，辅助表"中的相关行并未被删除.在本示例中，所有断言都通过了.

Here is some sample python code which demonstrates the problem I'm having. Note: This code is fully self contained, and only requires the sqlalchemy module. If you copy and paste this code, you should be able to run it without any side effects and see the same behavior yourself. The last line of the script shows that the relevant row in the "secondary table" was not removed when I removed the corresponding task. All the assertions pass in this example.

from sqlalchemy import create_engine, Column, Integer, Text, Table, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship

Model = declarative_base()

class User(Model):
    __tablename__ = 'users'
    id = Column('user_id', Integer, primary_key=True)
    email = Column('email', Text, unique=True)

    def __init__(self, email):
        self.email = email

user_tasks = Table('user_tasks', Model.metadata,
    Column('user_id', Integer, ForeignKey('users.user_id')),
    Column('task_id', Integer, ForeignKey('tasks.task_id')))

class Task(Model):
    __tablename__ = 'tasks'
    id = Column('task_id', Integer, primary_key=True)
    description = Column('description', Text)
    assigned_to = relationship('User', secondary=user_tasks, backref='tasks')

    def __init__(self, description):
        self.description = description

if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:')
    Model.metadata.create_all(engine)
    s = Session(engine)
    the_user = User('user')
    s.add(the_user)
    s.commit()
    assert s.query(User).all() == [the_user]
    user_task = Task('user_one task')
    user_task.assigned_to.append(the_user)
    s.add(user_task)
    s.commit()
    assert s.query(Task).all() == [user_task]
    assert s.query(user_tasks).all() == [(1,1)]
    s.query(Task).delete()
    s.commit()
    assert s.query(Task).all() == []
    assert s.query(User).all() == [the_user]
    assert s.query(user_tasks).all() == [(1,1)]  # I was expecting [] .

推荐答案

请参见

该方法不提供Python中的级联关系-假定已为需要它的任何外键引用配置了ON DELETE CASCADE.会话需要过期(在commit()之后自动发生，或调用expire_all())，以便正确表示要删除的依赖对象或删除孤立级联对象的状态.

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE is configured for any foreign key references which require it. The Session needs to be expired (occurs automatically after commit(), or call expire_all()) in order for the state of dependent objects subject to delete or delete-orphan cascade to be correctly represented.

也就是说，SQLAlchemy无法找到您要删除的所有Task对象，并无法从user_tasks中找出要删除的每一行-最好的方法是对外键使用ON DELETE CASCADE(赢得如果未启用外键，则无法使用MySQL MyISAM表或SQLite):

That is, SQLAlchemy isn't able to find all the Task objects you're deleting and figure out each row to be deleted from user_tasks - the best way to do this is to use ON DELETE CASCADE on the foreign keys (won't work with MySQL MyISAM tables or SQLite if foreign keys aren't enabled):

http://docs .sqlalchemy.org/en/latest/core/constraints.html#on-update-and-on-delete

这篇关于Python的SQLAlchemy不会清除辅助(多对多)表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！