Thank you Mike for this clear explanation !
Le jeudi 4 mai 2017 16:41:37 UTC+3, Mike Bayer a écrit :
>
>
> >
> > In my real use case, we have to update both parent and child columns, so
> > I want to use the Children class.
>
> so SQLite won't support that (nor will Postgresql or most other DBs).
> Not possible with standard SQL. Only MySQL's goofy syntax supports
> UPDATE where values are modified in multiple tables at once and you need
> to ensure your query uses filter() to provide the right ON clause.
>
> note this is *different* from the case where you want to UPDATE values
> that are only in *one* of the tables, but you still need both tables to
> find the row. In that case, Postgresql also supports having multiple
> tables referred to in an UPDATE. But still not SQLite. For SQLite and
> other databases, the table that's not the target of the update needs to
> be in a correlated subquery.
>
> I've updated your test with the final query against the two tables to
> work on MySQL:
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy.orm import relationship, Session
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Person(Base):
> __tablename__ = 'person'
> id = Column(Integer, primary_key=True)
> name = Column(String(50))
> type = Column(String(50))
> __mapper_args__ = {
> 'polymorphic_identity':'person',
> 'polymorphic_on':type
> }
>
> class Engineer(Person):
> __tablename__ = 'engineer'
> id = Column(Integer, ForeignKey('person.id'), primary_key=True)
> status = Column(String(30))
> __mapper_args__ = {
> 'polymorphic_identity':'engineer',
> }
>
>
> engine = create_engine("mysql://scott:tiger@localhost/test", echo=True)
>
> Base.metadata.drop_all(engine)
> Base.metadata.create_all(engine)
>
>
> if __name__ == '__main__':
> session = Session(engine)
> engineer = Engineer(name='me', status='working')
>
> # populates
> session.add(engineer)
> session.commit()
>
> session.query(Engineer).filter(Engineer.id == Person.id).\
> filter(Engineer.status == 'working').\
> update({'name': 'bar', 'status': 'done'})
>
>
>
> The query at the end comes out as:
>
> UPDATE engineer, person SET person.name=%s, engineer.status=%s WHERE
> engineer.id = person.id AND engineer.status = %s
>
> ('bar', 'done', 'working')
>
>
> that's totally a MySQL thing. (also I'd love to see another SQL
> expression language / ORM anywhere that supports MySQL's multi-table
> UPDATE :) )
>
>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
