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.com>wrote:
>
> 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.
