[sqlalchemy] Deletion of a row from an association table
I am working on an app using python3 and SqlAlchemy for SQLite3 database
management. I have some tables that have a Many to Many relationship. I've
created an association table to handle this relationship.
Class Machine(Base):
__tablename__ 'machine'
machine_ID = Column(Integer, primary_key=True)
etc...
Class Options(Base):
__tableName__ 'options'
options_ID = Column(Integer, primary_key=True)
etc...
The association table
Machine_Options = table('machine_options', Base.metadata,
Column('machine_FK', Integer, ForeignKey('machine.machine_ID'),
primary_key=True),
Column('options_FK',Integer, ForeignKey('options.options_ID'),
primary_key=True))
All the items for the Machine and Options are inserted independently. When
I want to associate a machine with an option I use an append query which
works very well.
My problem is when I want to break this association between a machine and
an option. I have tried a direct row deletion from the association table
using a FILTER() clause on the machine_FK and the options_FK but SqlAlchemy
gives me an error informing me that 'Machine_Options' table has no field
'machine_FK'. It seems that SqlAlchemy does not map association tables. I
have tried to remove the row from 'Machine_Options' indirectly using joins
with the machine and options table but received another error that I can
not delete or update using joins.
I am looking for the code to only delete a row from the association table
without affecting the original machine or options table.
So far my internet search has been fruitless.
--
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/60cb5a82-ca33-46c9-bf1e-b70deb82b485o%40googlegroups.com.
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.
[sqlalchemy] Deletion of object with relationship(lazy='raise')
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] Deletion
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.
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
[sqlalchemy] Deletion of referenced objects fails
Hi,
I have a problem using SQLAlchemy 0.4 when deleting referenced
objects in a PostgreSQL database and adding new ones within the same
transaction.
Originally, I found the problem with Elixir 0.4.0-pre (svn r216) and
SQLAlchemy 0.3.11 -
http://groups.google.com/group/sqlelixir/browse_thread/thread/b03a96e3ba9e61ea
Probably the problem can only be reproduced using a database which enforces
ForeignKey constraints (Postgresql does).
I don't know much about SQLAlchemy but I tried to create an SQLAlchemy script
which
reproduces the behavior (the original Elixir test case can be found on
http://pastebin.com/f3307e3c0 ). I hope the script exhibits really the same
problem as my Elixir script...
Example snippet (complete script http://pastebin.com/f6057bdbf ):
---
foo = session.query(User).filter_by(name='Foo Bar').one()
session.save(foo)
for address in foo.addresses:
foo.addresses.remove(address)
session.delete(address)
session.delete(foo)
foo = User()
session.save(foo)
foo.id = 1
foo_addr = Address()
session.save(foo_addr)
foo_addr.street = Picadelly Circus
foo.addresses.append(foo_addr)
transaction.commit()
---
This gives me the following traceback (complete output:
http://pastebin.com/f28f0e198 ,
original Elixir traceback http://pastebin.com/f5ae5c7c ):
---
Traceback (most recent call last):
File ./sqlalchemy_foreignkeys.py, line 88, in ?
transaction.commit()
...
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/home/fs/lokal/python-modules/SQLAlchemy-0.4.0-py2.4.egg/sqlalchemy/engine/base.py,
line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) Aktualisieren oder
Löschen in Tabelle »Address« verletzt Fremdschlüssel-Constraint
»users_addresses__Address_Address_id_fkey« von Tabelle
»users_addresses__Address«
DETAIL: Auf Schlüssel (id)=(1) wird noch aus Tabelle
»users_addresses__Address« verwiesen.
'DELETE FROM Address WHERE Address.id = %(id)s' {'id': 1}
---
Sorry for the German exception message, I did not manage to get an English one
despite
switching the system locale to en_US. Here is a rough translation to English:
Update or deletion of table »Address« violates foreign key
constraint »users_addresses__Address_Address_id_fkey« of table
»users_addresses__Address«
DETAIL: Table »users_addresses__Address« still references key (id)=(1).
SQL trace:
---
BEGIN
SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name = %(users_name)s ORDER BY users.id LIMIT 2 OFFSET 0
{'users_name': 'Foo Bar'}
SELECT Address.id AS Address_id, Address.street AS Address_street
FROM Address, users_addresses__Address
WHERE %(param_1)s = users_addresses__Address.user_id AND
users_addresses__Address.Address_id = Address.id
{'param_1': 1}
UPDATE users SET name=%(name)s WHERE users.id = %(users_id)s
{'users_id': 1, 'name': None}
select nextval('Address_id_seq')
None
INSERT INTO Address (id, street) VALUES (%(id)s, %(street)s)
{'street': 'Picadelly Circus', 'id': 2L}
INSERT INTO users_addresses__Address (user_id, Address_id) VALUES
(%(user_id)s, %(Address_id)s)
{'Address_id': 2L, 'user_id': 1}
DELETE FROM Address WHERE Address.id = %(id)s
{'id': 1}
ROLLBACK
---
I think the problem is the order of the SQL deletion statements. The item in
»users_addresses__Address«
must be deleted before deleting the address.
Is this behavior by design? Do I abuse the SQLAlchemy api?
fs
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] deletion behavior question
I would like some objects that are related through many to many tables to delete the many to many entry on deletion, but NOT the endpoint. It seems that cascade=all deletes both, and no arg to cascade leaves left over invalid entries in the manytomany table. Is there a suggested way to deal with this? Should I add a destructor to the classes that wipes out the many to many entry with a sql delete clause? Thanks Iain --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
