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.
