An update. This problem does occur with sqlite it's just that sqlite
doesn't enforce the foreign key so it doesn't throw an exception.
# output that deletes in the proper order
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
()
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT
2010-12-15 14:33:52,199 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
(2,)
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
((1,), (2,))
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT
# output that deletes in the wrong order
2010-12-15 14:33:56,691 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
()
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT
2010-12-15 14:33:56,694 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
(2,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
((1,), (2,))
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT
On Dec 15, 2:17 pm, Will <willman...@gmail.com> wrote:
> """
> Hello,
>
> I've been recently having a problem with sqlalchemy not flushing
> deletes in the proper order. I've created a simple example for the
> problem that has been occuring. I tried to run this using sqlite and
> it doesn't have any problems, it is only with Postgresql.
>
> One thing of note is that if there is only one Child it doesn't seem
> to
> have a problem, only when there are multiple children. Not sure if
> that makes a difference in the SQLAlchemy code.
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> from sqlalchemy.schema import Column, ForeignKey
> from sqlalchemy.types import Integer
>
> #engine = create_engine('sqlite:///')
> engine = create_engine('postgresql://test_runner@/testing_db')
>
> Model = declarative_base()
>
> class Parent(Model):
> __tablename__ = 'parents'
>
> id = Column(Integer, primary_key=True)
>
> class Child(Model):
> __tablename__ = 'children'
>
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey('parents.id'),
> nullable=False)
> parent = relationship('Parent')
>
> def begin():
> """Begin transaction"""
> #global transaction
> #transaction = session.begin()
>
> session.begin()
>
> def commit():
> """Commit transaction"""
> #global transaction
> #transaction.commit()
>
> session.commit()
>
> Model.metadata.create_all(engine)
>
> parent = Parent()
> children = [Child(parent=parent), Child(parent=parent)]
>
> Session = sessionmaker(bind=engine, autocommit=True)
> session = Session()
>
> try:
> session.bind.echo = True
>
> begin()
> session.add_all(children)
> session.add(parent)
> commit()
>
> begin()
> for child in children:
> session.delete(child)
> session.delete(parent)
> commit()
>
> session.bind.echo = False
> finally:
> Model.metadata.drop_all(engine)
>
> """
> From running the script I have two different outputs because it seems
> to run the deletes in a random order so subsequent runs will behave
> differently.
> """
>
> # Example Failed Run
> """
> 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> BEGIN (implicit)
> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {}
> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'parent_id': 1}
> 2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'parent_id': 1}
> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> COMMIT
> 2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> BEGIN (implicit)
> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> SELECT parents.id AS parents_id
> FROM parents
> WHERE parents.id = %(param_1)s
> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'param_1': 1}
> 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> DELETE FROM parents WHERE parents.id = %(id)s
> 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'id': 1}
> 2010-12-15 13:45:05,058 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> ROLLBACK
> 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> select relname from pg_class c join pg_namespace n on
> n.oid=c.relnamespace where n.nspname=current_schema() and
> lower(relname)=%(name)s
> 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'name': u'children'}
> 2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> select relname from pg_class c join pg_namespace n on
> n.oid=c.relnamespace where n.nspname=current_schema() and
> lower(relname)=%(name)s
> 2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'name': u'parents'}
> 2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> DROP TABLE children
> 2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {}
> 2010-12-15 13:45:05,066 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> COMMIT
> 2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> DROP TABLE parents
> 2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {}
> 2010-12-15 13:45:05,068 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> COMMIT
> Traceback (most recent call last):
> File "sharded_session_issue.py", line 64, in <module>
> commit(session)
> File "sharded_session_issue.py", line 47, in commit
> session.commit()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
> 623, in commit
> self.transaction.commit()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
> 385, in commit
> self._prepare_impl()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
> 369, in _prepare_impl
> self.session.flush()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
> 1397, in flush
> self._flush(objects)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
> 1478, in _flush
> flush_context.execute()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/unitofwork.py",
> line 304, in execute
> rec.execute(self)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/unitofwork.py",
> line 471, in execute
> uow
> File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/mapper.py", line
> 2076, in _delete_obj
> c = connection.execute(statement, del_objects)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line
> 1191, in execute
> params)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line
> 1271, in _execute_clauseelement
> return self.__execute_context(context)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line
> 1302, in __execute_context
> context.parameters[0], context=context)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line
> 1401, in _cursor_execute
> context)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line
> 1394, in _cursor_execute
> context)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py",
> line 299, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on
> table "parents" violates foreign key constraint
> "children_parent_id_fkey" on table "children"
> DETAIL: Key (id)=(1) is still referenced from table "children".
> 'DELETE FROM parents WHERE parents.id = %(id)s' {'id': 1}
> """
>
> # Example Successful Run
> """
> 2010-12-15 13:45:40,471 INFO sqlalchemy.engine.base.Engine.0x...95d0
> BEGIN (implicit)
> 2010-12-15 13:45:40,472 INFO sqlalchemy.engine.base.Engine.0x...95d0
> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
> 2010-12-15 13:45:40,472 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {}
> 2010-12-15 13:45:40,473 INFO sqlalchemy.engine.base.Engine.0x...95d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:40,473 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'parent_id': 1}
> 2010-12-15 13:45:40,474 INFO sqlalchemy.engine.base.Engine.0x...95d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:40,474 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'parent_id': 1}
> 2010-12-15 13:45:40,475 INFO sqlalchemy.engine.base.Engine.0x...95d0
> COMMIT
> 2010-12-15 13:45:40,476 INFO sqlalchemy.engine.base.Engine.0x...95d0
> BEGIN (implicit)
> 2010-12-15 13:45:40,477 INFO sqlalchemy.engine.base.Engine.0x...95d0
> SELECT children.id AS children_id, children.parent_id AS
> children_parent_id
> FROM children
> WHERE children.id = %(param_1)s
> 2010-12-15 13:45:40,477 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'param_1': 1}
> 2010-12-15 13:45:40,478 INFO sqlalchemy.engine.base.Engine.0x...95d0
> SELECT children.id AS children_id, children.parent_id AS
> children_parent_id
> FROM children
> WHERE children.id = %(param_1)s
> 2010-12-15 13:45:40,478 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'param_1': 2}
> 2010-12-15 13:45:40,479 INFO sqlalchemy.engine.base.Engine.0x...95d0
> DELETE FROM children WHERE children.id = %(id)s
> 2010-12-15 13:45:40,479 INFO sqlalchemy.engine.base.Engine.0x...95d0
> ({'id': 1}, {'id': 2})
> 2010-12-15 13:45:40,480 INFO sqlalchemy.engine.base.Engine.0x...95d0
> SELECT parents.id AS parents_id
> FROM parents
> WHERE parents.id = %(param_1)s
> 2010-12-15 13:45:40,480 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'param_1': 1}
> 2010-12-15 13:45:40,481 INFO sqlalchemy.engine.base.Engine.0x...95d0
> DELETE FROM parents WHERE parents.id = %(id)s
> 2010-12-15 13:45:40,481 INFO sqlalchemy.engine.base.Engine.0x...95d0
> {'id': 1}
> 2010-12-15 13:45:40,482 INFO sqlalchemy.engine.base.Engine.0x...95d0
> COMMIT
> """
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
