"""
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
