Here a MCWE :
from sqlalchemy import Table, Column, Integer, String, ForeignKey,
create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False)
name = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'person',
'polymorphic_on': type
}
class Manager(Person):
__tablename__ = 'manager'
id = Column(ForeignKey('person.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity': 'manager',
'polymorphic_load': 'selectin'
}
class EngineerBase(Person):
__tablename__ = 'engineer'
id = Column(ForeignKey('person.id'), primary_key=True)
engineer_name = Column(String(30))
class EngineerType1(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t1',
'polymorphic_load': 'selectin'
}
class EngineerType2(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t2',
'polymorphic_load': 'selectin'
}
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
engine.echo = True
session = Session(engine)
eng1 = EngineerType1()
eng2 = EngineerType2()
session.add_all([eng1, eng2])
session.commit()
session.query(Person).all()
produces the following three queries :
2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine SELECT person.id
AS person_id, person.type AS person_type, person.name AS person_name
FROM person
2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine ()
2017-07-13 00:59:07,248 INFO sqlalchemy.engine.base.Engine SELECT engineer.id
AS engineer_id, person.id AS person_id, person.type AS person_type
FROM person JOIN engineer ON person.id = engineer.id
WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id
2017-07-13 00:59:07,249 INFO sqlalchemy.engine.base.Engine (2, 'engineer_t2'
)
2017-07-13 00:59:07,253 INFO sqlalchemy.engine.base.Engine SELECT engineer.id
AS engineer_id, person.id AS person_id, person.type AS person_type
FROM person JOIN engineer ON person.id = engineer.id
WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id
2017-07-13 00:59:07,254 INFO sqlalchemy.engine.base.Engine (1, 'engineer_t1'
)
The last two queries can be grouped together.
Le jeudi 13 juillet 2017 01:45:46 UTC+3, yoch....@gmail.com a écrit :
>
> I have a mixed configuration with both joined and single table subclasses
> in a two-levels inheritance (like that
> <https://groups.google.com/forum/#!topic/sqlalchemy/AE8-myRUZp0>), so
> selectin seems to be the right choice for me.
>
> Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit :
>>
>> On Wed, Jul 12, 2017 at 4:54 PM, <yoch....@gmail.com> wrote:
>> > I noticed that {'polymorphic_load': 'selectin'} on single table
>> inheritance
>> > can make several SQL queries unnecessarily.
>>
>> well "selectin" loading would be inappropriate for single table
>> inheritance because you are telling it to emit additional queries for
>> additional subclasses, when in reality you'd like the original query
>> just to include all columns. For this reason the traditional
>> "with_polymorphic" approach is more appropriate for eager loading of
>> single table subclasses.
>>
>>
>> >
>> >
>> > Le mercredi 12 juillet 2017 22:02:04 UTC+3, yoch....@gmail.com a écrit
>> :
>> >>
>> >> Very awaited version for me (because the selectin) !
>> >>
>> >> I tested in my code both the eagerload and the polymorphic usages, and
>> >> everything works perfectly.
>> >>
>> >> Thank you Mike
>> >>
>> >> Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit :
>> >>>
>> >>> SQLAlchemy release 1.2.0b1 is now available.
>> >>>
>> >>> This is the first beta for the 1.2 series of SQLAlchemy. As is always
>> >>> the case for beta releases, the release is available on Pypi, but
>> only
>> >>> installable when specifying the --pre flag with pip.
>> >>>
>> >>> Key highlights of version 1.2 include:
>> >>>
>> >>> * Connection pool pre-ping - The connection pool now includes an
>> >>> optional "pre ping" feature that will test the "liveness" of a pooled
>> >>> connection for every connection checkout, transparently recycling the
>> >>> DBAPI connection if the database is disconnected. This feature
>> >>> eliminates the need for the "pool recycle" flag as well as the issue
>> >>> of errors raised when a pooled connection is used after a database
>> >>> restart.
>> >>>
>> >>> * New eagerloading features - an all-new eager loader called
>> >>> "selectin" is added. This loader is similar to "subquery" eager
>> >>> loading, but foregoes embedding a complex subquery in favor of using
>> a
>> >>> simple IN expression which uses the primary key values of the
>> >>> just-loaded objects to locate related rows. This style of loading
>> will
>> >>> perform dramatically better than "subquery" eager loading in many
>> >>> cases.
>> >>>
>> >>> * New polymorphic loading options - A new "selectin" loader is also
>> >>> implemented for polymorphic inheritance hierarchies, which will load
>> >>> the extra table rows for subclasses in a result set all at once,
>> >>> without the need to use JOIN or "with_polymorphic". New declarative
>> >>> options are added to allow per-subclass polymorphic loading
>> >>> configuration as well.
>> >>>
>> >>> * The IN operator now emits a simple expression for empty IN - The
>> >>> long standing behavior of "empty in" resolving to a non-performant
>> >>> expression with a warning has been removed as default behavior; for
>> an
>> >>> IN with no elements, a simple 1 != 1 expression is now used to
>> >>> evaluate to "false".
>> >>>
>> >>> * INSERT..ON DUPLICATE KEY UPDATE support in MySQL - complementing
>> the
>> >>> support for PostgreSQL "INSERT..ON CONFLICT" in 1.1, the MySQL
>> dialect
>> >>> now supports the ON DUPLICATE KEY phrase. Simple database-agnostic
>> >>> "merge" routines can now be composed against these two backends.
>> >>>
>> >>> * COMMENT Support - thanks to a generous contribution, version 1.2
>> can
>> >>> emit all necessary DDL as well as fully reflect SQL comments for
>> >>> tables and columns against the MySQL, PostgreSQL, and Oracle
>> backends.
>> >>>
>> >>> * SQL expression caching within ORM loaders - the "lazyload" feature
>> >>> as well as the new "selectin" loading now use SQL expression caching
>> >>> using the "baked query" extension when emitting queries, removing a
>> >>> significant bulk of Python overhead from these very common
>> operations.
>> >>>
>> >>> Users should carefully review the "What's New in SQLAlchemy 1.2?
>> >>> document" [1] as well as the Changelog [2] to note which behaviors
>> and
>> >>> issues are affected. We'd like to thank the many contributors who
>> >>> helped with this release.
>> >>>
>> >>> SQLAlchemy 1.2.0b1 is available on the Download Page [3].
>> >>>
>> >>> [1] http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html
>> >>> [2] http://www.sqlalchemy.org/changelog/CHANGES_1_2_0b1
>> >>> [3] http://www.sqlalchemy.org/download.html
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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.
