Python's SQLAlchemy doesn't clean out the secondary (many-to-many) table?

Question

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?

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 [] .

Nilesh · Accepted Answer

When I tried your code with sqlite its not giving error but when, I tried with MySQL database i got error

2012-03-29 10:43:15,330 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2012-03-29 10:43:15,331 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,332 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2012-03-29 10:43:15,332 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,333 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2012-03-29 10:43:15,333 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,334 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2012-03-29 10:43:15,334 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,337 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2012-03-29 10:43:15,338 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,339 INFO sqlalchemy.engine.base.Engine DESCRIBE `user_tasks`
2012-03-29 10:43:15,339 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,355 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2012-03-29 10:43:15,355 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,356 INFO sqlalchemy.engine.base.Engine DESCRIBE `tasks`
2012-03-29 10:43:15,356 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,357 INFO sqlalchemy.engine.base.Engine 
DROP TABLE user_tasks
2012-03-29 10:43:15,357 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,439 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,440 INFO sqlalchemy.engine.base.Engine 
DROP TABLE users
2012-03-29 10:43:15,440 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,573 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,573 INFO sqlalchemy.engine.base.Engine 
DROP TABLE tasks
2012-03-29 10:43:15,573 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,623 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,624 INFO sqlalchemy.engine.base.Engine DESCRIBE `tasks`
2012-03-29 10:43:15,624 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,632 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-03-29 10:43:15,633 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2012-03-29 10:43:15,633 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,634 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-03-29 10:43:15,634 INFO sqlalchemy.engine.base.Engine DESCRIBE `user_tasks`
2012-03-29 10:43:15,634 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,635 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-03-29 10:43:15,635 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tasks (
    task_id INTEGER NOT NULL AUTO_INCREMENT, 
    description TEXT, 
    PRIMARY KEY (task_id)
)


2012-03-29 10:43:15,635 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,732 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,733 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
    user_id INTEGER NOT NULL AUTO_INCREMENT, 
    email VARCHAR(20), 
    PRIMARY KEY (user_id), 
    UNIQUE (email)
)


2012-03-29 10:43:15,733 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,841 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,842 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user_tasks (
    user_id INTEGER, 
    task_id INTEGER, 
    FOREIGN KEY(user_id) REFERENCES users (user_id), 
    FOREIGN KEY(task_id) REFERENCES tasks (task_id)
)


2012-03-29 10:43:15,842 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:15,959 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:15,964 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-03-29 10:43:15,965 INFO sqlalchemy.engine.base.Engine INSERT INTO users (email) VALUES (%s)
2012-03-29 10:43:15,965 INFO sqlalchemy.engine.base.Engine ('user',)
2012-03-29 10:43:15,966 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:16,010 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-03-29 10:43:16,010 INFO sqlalchemy.engine.base.Engine SELECT users.user_id AS users_user_id, users.email AS users_email 
FROM users
2012-03-29 10:43:16,011 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:16,013 INFO sqlalchemy.engine.base.Engine INSERT INTO tasks (description) VALUES (%s)
2012-03-29 10:43:16,014 INFO sqlalchemy.engine.base.Engine ('user_one task',)
2012-03-29 10:43:16,015 INFO sqlalchemy.engine.base.Engine INSERT INTO user_tasks (user_id, task_id) VALUES (%s, %s)
2012-03-29 10:43:16,016 INFO sqlalchemy.engine.base.Engine (1L, 1L)
2012-03-29 10:43:16,016 INFO sqlalchemy.engine.base.Engine COMMIT
2012-03-29 10:43:16,085 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-03-29 10:43:16,086 INFO sqlalchemy.engine.base.Engine SELECT tasks.task_id AS tasks_task_id, tasks.description AS tasks_description 
FROM tasks
2012-03-29 10:43:16,086 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:16,087 INFO sqlalchemy.engine.base.Engine SELECT user_tasks.user_id AS user_tasks_user_id, user_tasks.task_id AS user_tasks_task_id 
FROM user_tasks
2012-03-29 10:43:16,088 INFO sqlalchemy.engine.base.Engine ()
2012-03-29 10:43:16,089 INFO sqlalchemy.engine.base.Engine SELECT user_tasks.user_id AS user_tasks_user_id, user_tasks.task_id AS user_tasks_task_id 
FROM user_tasks
2012-03-29 10:43:16,089 INFO sqlalchemy.engine.base.Engine ()
[(1L, 1L)]
2012-03-29 10:43:16,091 INFO sqlalchemy.engine.base.Engine DELETE FROM tasks
2012-03-29 10:43:16,091 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "/tmp/test2.py", line 46, in <module>
    s.query(Task).delete()
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2283, in delete
    result = session.execute(delete_stmt, params=self._params)
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 762, in execute
    clause, params or {})
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1399, in execute
    params)
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_context
    context)
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1633, in _execute_context
    context)
  File "/home/npatel/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 325, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test1`.`user_tasks`, CONSTRAINT `user_tasks_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`task_id`))') 'DELETE FROM tasks' ()

So after that I came to know that sqlite not maintain foreign key constraint. Now I change your code and check the output.

from sqlalchemy import create_engine, Column, Integer, Text, Table, ForeignKey, String
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', String(length=20), 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('mysql://test:test@localhost/test', echo=True)
    Model.metadata.drop_all(engine)
    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)]
    the_user.tasks = []
    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 [] .

zzzeek · Answer

See delete(synchronize_session='evaluate'):

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.

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's SQLAlchemy doesn't clean out the secondary (many-to-many) table?

Tags:

python

many-to-many

sqlalchemy

Buttons840

2 Answers

Nilesh

zzzeek

Recent Activity

Donate For Us

Python's SQLAlchemy doesn't clean out the secondary (many-to-many) table?

Tags:

python

many-to-many

sqlalchemy

Buttons840

2 Answers

Nilesh

zzzeek

Related questions

Recent Activity

Donate For Us