On 05/04/2017 09:29 AM, yoch.me...@gmail.com wrote:
Le jeudi 4 mai 2017 16:07:22 UTC+3, Mike Bayer a écrit :
On 05/04/2017 08:41 AM, yoch....@gmail.com <javascript:> wrote:
> Hi,
>
> I'm facing to a strange behavior with bulk update on inherited
class.
>
> Is this a bug ?
it's not.
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query%20update#sqlalchemy.orm.query.Query.update
<http://www.google.com/url?q=http%3A%2F%2Fdocs.sqlalchemy.org%2Fen%2Flatest%2Form%2Fquery.html%3Fhighlight%3Dquery%2520update%23sqlalchemy.orm.query.Query.update&sa=D&sntz=1&usg=AFQjCNFAehd93-iysTyDIJTBu0RctrHV6w>
**Warning**
The Query.update() method is a “bulk” operation, which bypasses ORM
unit-of-work automation in favor of greater performance. **Please read
all caveats and warnings below.**
... below ....
* The method supports multiple table updates, as detailed in Multiple
Table Updates, and this behavior does extend to support updates of
joined-inheritance and other multiple table mappings. However, the join
condition of an inheritance mapper is not automatically rendered. Care
must be taken in any multiple-table update to explicitly include the
joining condition between those tables, even in mappings where this is
normally automatic. E.g. if a class Engineer subclasses Employee, an
UPDATE of the Engineer local table using criteria against the Employee
local table might look like:
session.query(Engineer).\
filter(Engineer.id == Employee.id).\
filter(Employee.name == 'dilbert').\
update({"engineer_type": "programmer"})
Thank you for this response.
In your case, your query is only targeting columns in the base "person"
table. So this is not really a multiple table update and instead of
asking it for query(Engineer) you should be asking for
query(Person).filter(Person.type == 'engineer').
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 :) )
>
> Best regards,
> yoch
>
> --
> 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
<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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
