Re: [sqlalchemy] Deletion of object with relationship(lazy='raise')
I can confirm this is a bug as I've found precedent for this not having to
raise, new issue is
https://github.com/sqlalchemy/sqlalchemy/issues/4997
On Fri, Nov 22, 2019, at 9:24 AM, Mike Bayer wrote:
> that said, I'm looking at this as a potential bug because it should be able
> to leave this attribute alone, not really sure how this should be handled.
>
> On Fri, Nov 22, 2019, at 9:21 AM, Mike Bayer wrote:
>> lazy="raise" on a many to one is problematic because a lot of many-to-one
>> operations involve pulling up the object from the identity map, and that's
>> it. since people are usually only trying to guard against SQL being emitted,
>> use the raise_on_sql option instead:
>>
>> parent = relationship(Parent, lazy='raise_on_sql')
>>
>> thanks for the perfect test case BTW makes this very easy for me
>>
>> On Fri, Nov 22, 2019, at 6:12 AM, Marat Sharafutdinov wrote:
>>> from sqlalchemy import Column, ForeignKey, Integer, create_engine
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import relationship, sessionmaker
>>>
>>> Base = declarative_base()
>>>
>>> class Parent(Base):
>>> __tablename__ = 'parents'
>>> id = Column(Integer, primary_key=True)
>>>
>>> class Child(Base):
>>> __tablename__ = 'children'
>>> id = Column(Integer, primary_key=True)
>>> parent_id = Column(Integer, ForeignKey(Parent.id))
>>> parent = relationship(Parent, lazy='raise', passive_deletes=True)
>>>
>>> engine = create_engine('sqlite:///:memory:', echo=True)
>>> Base.metadata.create_all(engine)
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>>
>>> # Add
>>> parent = Parent()
>>> session.add(parent)
>>> session.flush()
>>> child = Child(parent_id=parent.id)
>>> session.add(child)
>>> session.commit()
>>>
>>> # Delete
>>> child = session.query(Child).first()
>>> session.delete(child)
>>> session.commit()
>>>
>>> Now I'm getting the following warning:
>>> /.venv/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:2021:
>>> SAWarning: On Child.parent, 'passive_deletes' is normally configured on
>>> one-to-many, one-to-one, many-to-many relationships only.
>>>
>>> If I change `parent` relationship as follows:
>>> parent = relationship(Parent, lazy='raise', backref=backref('children',
>>> passive_deletes=True))
>>> then I would get the following exception:
>>> sqlalchemy.exc.InvalidRequestError: 'Child.parent' is not available due to
>>> lazy='raise'
>>>
>>> How should it be made properly?
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy+unsubscr...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/40f63feb-383e-4fee-8db6-21a757887c54%40googlegroups.com
>>>
>>> .
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/79e8c08e-c761-4d48-96d8-4cabf6de36de%40www.fastmail.com
>>
>> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e99fe1a3-0547-4dae-ab61-e1cd26ccd879%40www.fastmail.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit
Re: [sqlalchemy] Deletion of object with relationship(lazy='raise')
that said, I'm looking at this as a potential bug because it should be able to
leave this attribute alone, not really sure how this should be handled.
On Fri, Nov 22, 2019, at 9:21 AM, Mike Bayer wrote:
> lazy="raise" on a many to one is problematic because a lot of many-to-one
> operations involve pulling up the object from the identity map, and that's
> it. since people are usually only trying to guard against SQL being emitted,
> use the raise_on_sql option instead:
>
> parent = relationship(Parent, lazy='raise_on_sql')
>
> thanks for the perfect test case BTW makes this very easy for me
>
> On Fri, Nov 22, 2019, at 6:12 AM, Marat Sharafutdinov wrote:
>> from sqlalchemy import Column, ForeignKey, Integer, create_engine
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import relationship, sessionmaker
>>
>> Base = declarative_base()
>>
>> class Parent(Base):
>> __tablename__ = 'parents'
>> id = Column(Integer, primary_key=True)
>>
>> class Child(Base):
>> __tablename__ = 'children'
>> id = Column(Integer, primary_key=True)
>> parent_id = Column(Integer, ForeignKey(Parent.id))
>> parent = relationship(Parent, lazy='raise', passive_deletes=True)
>>
>> engine = create_engine('sqlite:///:memory:', echo=True)
>> Base.metadata.create_all(engine)
>> Session = sessionmaker(bind=engine)
>> session = Session()
>>
>> # Add
>> parent = Parent()
>> session.add(parent)
>> session.flush()
>> child = Child(parent_id=parent.id)
>> session.add(child)
>> session.commit()
>>
>> # Delete
>> child = session.query(Child).first()
>> session.delete(child)
>> session.commit()
>>
>> Now I'm getting the following warning:
>> /.venv/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:2021:
>> SAWarning: On Child.parent, 'passive_deletes' is normally configured on
>> one-to-many, one-to-one, many-to-many relationships only.
>>
>> If I change `parent` relationship as follows:
>> parent = relationship(Parent, lazy='raise', backref=backref('children',
>> passive_deletes=True))
>> then I would get the following exception:
>> sqlalchemy.exc.InvalidRequestError: 'Child.parent' is not available due to
>> lazy='raise'
>>
>> How should it be made properly?
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/40f63feb-383e-4fee-8db6-21a757887c54%40googlegroups.com
>>
>> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/79e8c08e-c761-4d48-96d8-4cabf6de36de%40www.fastmail.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e99fe1a3-0547-4dae-ab61-e1cd26ccd879%40www.fastmail.com.
Re: [sqlalchemy] Deletion of object with relationship(lazy='raise')
lazy="raise" on a many to one is problematic because a lot of many-to-one
operations involve pulling up the object from the identity map, and that's it.
since people are usually only trying to guard against SQL being emitted, use
the raise_on_sql option instead:
parent = relationship(Parent, lazy='raise_on_sql')
thanks for the perfect test case BTW makes this very easy for me
On Fri, Nov 22, 2019, at 6:12 AM, Marat Sharafutdinov wrote:
> from sqlalchemy import Column, ForeignKey, Integer, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parents'
> id = Column(Integer, primary_key=True)
>
> class Child(Base):
> __tablename__ = 'children'
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey(Parent.id))
> parent = relationship(Parent, lazy='raise', passive_deletes=True)
>
> engine = create_engine('sqlite:///:memory:', echo=True)
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> session = Session()
>
> # Add
> parent = Parent()
> session.add(parent)
> session.flush()
> child = Child(parent_id=parent.id)
> session.add(child)
> session.commit()
>
> # Delete
> child = session.query(Child).first()
> session.delete(child)
> session.commit()
>
> Now I'm getting the following warning:
> /.venv/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:2021:
> SAWarning: On Child.parent, 'passive_deletes' is normally configured on
> one-to-many, one-to-one, many-to-many relationships only.
>
> If I change `parent` relationship as follows:
> parent = relationship(Parent, lazy='raise', backref=backref('children',
> passive_deletes=True))
> then I would get the following exception:
> sqlalchemy.exc.InvalidRequestError: 'Child.parent' is not available due to
> lazy='raise'
>
> How should it be made properly?
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/40f63feb-383e-4fee-8db6-21a757887c54%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/79e8c08e-c761-4d48-96d8-4cabf6de36de%40www.fastmail.com.
Re: [sqlalchemy] Deletion
How are you doing the delete?
This should delete both.
a = sess.query(Peripheral).filter(Peripheral.label=='some label').one()
sess.delete(a)
sess.commit()
This will not work.
a = sess.query(Peripheral).filter(Peripheral.label=='some
label').delete()
I think the explanation here is that in this case we are creating an
explicit SQL delete statement without adding Peripheral instances to the
session and so there is no knowledge that a related Actuator exists. Someone
else might be able to give a better explanation.
--
Mike Conley
On Tue, Oct 18, 2011 at 9:03 AM, fribes fri...@gmail.com wrote:
Hi all,
Despite some doc and web digging, I didn't find how to tell sqa to behave
the way I want :
on deletion on Peripheral, also delete in Actuator.
with the following code, the record in Actuator remains after a deletion,
and a subsequent creation fails with IntegrityError.
class Peripheral(Base):
__tablename__ = 'peripheral'
id = Column(Integer, primary_key=True)
label = Column(String(20), nullable=False)
__mapper_args__ = {'polymorphic_on': peripheral_type,
'polymorphic_identity': 'peripheral'}
class Actuator(Peripheral):
__tablename__ = 'actuator'
__mapper_args__ = {'polymorphic_identity': 'actuator'}
id = Column(None, ForeignKey('peripheral.id'), primary_key=True)
duration = Column(Integer)
Any suggestion ?
Regards,
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
Re: [sqlalchemy] Deletion
On Oct 18, 2011, at 10:03 AM, fribes wrote:
Hi all,
Despite some doc and web digging, I didn't find how to tell sqa to behave the
way I want :
on deletion on Peripheral, also delete in Actuator.
with the following code, the record in Actuator remains after a deletion, and
a subsequent creation fails with IntegrityError.
class Peripheral(Base):
__tablename__ = 'peripheral'
id = Column(Integer, primary_key=True)
label = Column(String(20), nullable=False)
__mapper_args__ = {'polymorphic_on': peripheral_type,
'polymorphic_identity': 'peripheral'}
class Actuator(Peripheral):
__tablename__ = 'actuator'
__mapper_args__ = {'polymorphic_identity': 'actuator'}
id = Column(None, ForeignKey('peripheral.id'), primary_key=True)
duration = Column(Integer)
Any suggestion ?
usually relationship() with cascade=all, delete-orphan is used for this use
case, so that SQLAlchemy can maintain knowledge about the link between
Peripheral and Actuator. The other alternative is to use ON DELETE CASCADE
on the foreign key; this is part of the schema you'd generate in the database.
Relevant docs:
http://www.sqlalchemy.org/docs/orm/tutorial.html#configuring-delete-delete-orphan-cascade
http://www.sqlalchemy.org/docs/core/schema.html#on-update-and-on-delete
http://www.sqlalchemy.org/docs/orm/collections.html#using-passive-deletes
Regards,
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
Re: [sqlalchemy] Deletion order during flush is not correct.
This is an interesting edge case and I can probably ensure that the dependency
between Parent/Child is present in the unit of work even if there is no known
linkage at the Child.parent level for the objects actually present - ticket
#2002 is added for this.
In the meantime, the uow needs to be aware of the linkage between Parent-Child
when flush occurs. Adding a backref children to the parent relationship
will do it, or ensuring that child.parent is accessed before emitting the flush
will do it. The usual way this kind of delete is performed is the delete
cascade is added to the children backref, then the Parent is deleted alone,
the deletes cascading to the Child objects naturally.
But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
minutes or maybe not.
On Dec 15, 2010, at 2:17 PM, Will 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
Re: [sqlalchemy] Deletion order during flush is not correct.
Wow, this has been a problem for me for the past 3 or 4 days and took
a while to get to that example. Defining the backrefs or the
relationships in the opposite direction did the job. I had
intentionally left out some of the backreffed relationships because I
didn't need them for what I was working on, but it definitely is worth
it to get this working.
Thanks a lot.
-Will
On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer mike...@zzzcomputing.com wrote:
This is an interesting edge case and I can probably ensure that the
dependency between Parent/Child is present in the unit of work even if there
is no known linkage at the Child.parent level for the objects actually
present - ticket #2002 is added for this.
In the meantime, the uow needs to be aware of the linkage between
Parent-Child when flush occurs. Adding a backref children to the parent
relationship will do it, or ensuring that child.parent is accessed before
emitting the flush will do it. The usual way this kind of delete is
performed is the delete cascade is added to the children backref, then
the Parent is deleted alone, the deletes cascading to the Child objects
naturally.
But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
minutes or maybe not.
On Dec 15, 2010, at 2:17 PM, Will 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
Re: [sqlalchemy] Deletion order during flush is not correct.
It was in fact a one liner, so you can go back to your original code if you use
the latest 0.6 tip:
http://hg.sqlalchemy.org/sqlalchemy/archive/rel_0_6.tar.gz
thanks for the bug report !
On Dec 15, 2010, at 3:41 PM, Will Weaver wrote:
Wow, this has been a problem for me for the past 3 or 4 days and took
a while to get to that example. Defining the backrefs or the
relationships in the opposite direction did the job. I had
intentionally left out some of the backreffed relationships because I
didn't need them for what I was working on, but it definitely is worth
it to get this working.
Thanks a lot.
-Will
On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
This is an interesting edge case and I can probably ensure that the
dependency between Parent/Child is present in the unit of work even if there
is no known linkage at the Child.parent level for the objects actually
present - ticket #2002 is added for this.
In the meantime, the uow needs to be aware of the linkage between
Parent-Child when flush occurs. Adding a backref children to the parent
relationship will do it, or ensuring that child.parent is accessed before
emitting the flush will do it. The usual way this kind of delete is
performed is the delete cascade is added to the children backref, then
the Parent is deleted alone, the deletes cascading to the Child objects
naturally.
But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
minutes or maybe not.
On Dec 15, 2010, at 2:17 PM, Will 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
