On 5/26/15 11:18 AM, g wrote:
Hi all
With SA 1.0.4 I can delete rows that i could not delete with SA 0.9.3 .
your code is relying on a bug in 0.9.3 that was "fixed" in 0.9.5
(https://bitbucket.org/zzzeek/sqlalchemy/issue/3060) , but we broke
other things at the same time and ultimately it didn't get "fixed"
correctly until 0.9.7 (https://bitbucket.org/zzzeek/sqlalchemy/issue/3099).
The default behavior of relationship when an object is deleted that is
referred to via another one with a foreign key is to set it to NULL. If
you'd like the other object to be deleted, you need to use delete
cascade. This is documented in the following places:
http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#configuring-delete-delete-orphan-cascade
http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html#delete
The attached file also illustrates this. Work with the attached test
case and you'll see it works the same on 0.9.7 vs. 1.0.x.
MODEL.
Base = declarative_base()
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('node.id'))
data = Column(String(50))
children = relationship("Node",
backref=backref('parent', remote_side=[id],
),lazy="dynamic"
)
e = create_engine('postgresql+psycopg2://*****/test')
Base.metadata.create_all(e)
session = Session(e)
QUERY AND DATA ARE:
query = session.query(
Node.id,
Node.parent_id,
Node.data)
results = query.all()
print results
[(1, None, u'parent'), (2, 1, u'child')]
NOW I TRY TO DELETE THE PARENT:
parent = session.query(Node).get(1) print parent
session.delete(parent) session.commit() results = query.all() print
results
RESULT with SA 0.9.3
sqlalchemy-0.9.3-py2.7.egg\sqlalchemy\engine\default.pyc in
do_execute(self, cursor, statement, parameters, context) 423 424 def
do_execute(self, cursor, statement, parameters, context=None):-->
425cursor.execute(statement, parameters)426 427 def
do_execute_no_params(self, cursor, statement,
context=None):IntegrityError: (IntegrityError) update or delete on
table "node" violates foreign key constraint "node_parent_id_fkey" on
table "node" DETAIL: Key (id)=(1) is still referenced from table
"node". 'DELETE FROM node WHERE node.id = %(id)s' {'id': 1}
RESULT with SA 1.0.4:
====================
<__main__.Node object at 0x056BC5B0>
[(2, None, u'child')]
The parent is deleted and node.parent_id is set to null.
What should I change in the relationship or model to have in SA 1.0.4
the same behavior as in SA 0.9.3 ?
Regards
G
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
CASCADE_DELETE = False
#CASCADE_DELETE = True
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('node.id'))
data = Column(String(50))
children = relationship(
"Node",
backref=backref('parent', remote_side=[id]),
lazy="dynamic",
cascade="all, delete-orphan" if CASCADE_DELETE else None
)
e = create_engine(
'postgresql+psycopg2://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e)
session.execute(
Node.__table__.insert(),
params=[
{"id": 1, "parent_id": None, "data": "parent"},
{"id": 2, "parent_id": 1, "data": "child"},
]
)
query = session.query(
Node.id,
Node.parent_id,
Node.data).order_by(Node.id)
results = query.all()
assert results == [(1, None, u'parent'), (2, 1, u'child')]
parent = session.query(Node).get(1)
session.delete(parent)
session.commit()
if CASCADE_DELETE:
assert query.all() == []
else:
assert query.all() == [(2, None, u'child')]
