Re: [sqlalchemy] cascade delete in relationship and session.execute(table.update())
I really appreciate your help! It works great without any problem.
I tried session.refresh(storeobject) and I was wondering why it didn't
work, now I learned the difference from your method.
Again, thank you very much, and have a great weekend.
On Tue, Jan 3, 2012 at 10:56 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jan 2, 2012, at 4:06 PM, Wubin wrote:
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message.
This would indicate that PolymorphicClass is mapped to a table as well.
A DELETE or UPDATE statement, in standard SQL, doesn't support more than
one table being affected at the same time (only MySQL has an extended
syntax that supports this but it's not supported by the ORM). There's
also a syntax that supports only one table being updated, but multiple
tables in the FROM clause which on Postgresql is UPDATE..FROM, and
SQLAlchemy now supports that too, but again the ORM doesn't yet have
support for that to be integrated.
So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine.
OK
But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted?
yeah just expire the collection:
session.expire(storeobject, ['name_of_products_collection'])
(2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Right now you can only pass in the base class, I took a look since we do
support UPDATE..FROM for supporting DBs, the controversial part here is
that an UPDATE against the child table which then refers to the base table
would need WHERE criterion to join the two together, which introduces
tricky decisionmaking. But one possibility is to just leave that up to
the user in this case.
I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
possibility.
--
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] cascade delete in relationship and session.execute(table.update())
On Jan 2, 2012, at 4:06 PM, Wubin wrote:
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message.
This would indicate that PolymorphicClass is mapped to a table as well.A
DELETE or UPDATE statement, in standard SQL, doesn't support more than one
table being affected at the same time (only MySQL has an extended syntax that
supports this but it's not supported by the ORM). There's also a syntax that
supports only one table being updated, but multiple tables in the FROM clause
which on Postgresql is UPDATE..FROM, and SQLAlchemy now supports that too,
but again the ORM doesn't yet have support for that to be integrated.
So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine.
OK
But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted?
yeah just expire the collection:
session.expire(storeobject, ['name_of_products_collection'])
(2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Right now you can only pass in the base class, I took a look since we do
support UPDATE..FROM for supporting DBs, the controversial part here is that an
UPDATE against the child table which then refers to the base table would need
WHERE criterion to join the two together, which introduces tricky
decisionmaking. But one possibility is to just leave that up to the user in
this case.
I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
possibility.
--
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.
[sqlalchemy] cascade delete in relationship and session.execute(table.update())
Hi,
Currently I am trying to implement a feature that transfers all
products in a store to another store, and then destroy the old store
which contains no product. The sqlalchemy version is 0.6.8.
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message. So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine. But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted? (2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Thank you very much, and happy new year!
--
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.
