Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

OK, thank a lot !

Le jeudi 13 juillet 2017 06:01:45 UTC+3, Mike Bayer a écrit :
>
> this is how that would have to be mapped, hypothetically: 
>
> class EngineerBase(Person): 
> __tablename__ = 'engineer' 
>
> id = Column(ForeignKey('person.id'), primary_key=True) 
> engineer_name = Column(String(30)) 
>
> __mapper_args__ = { 
> 'polymorphic_load': 'selectin' 
> } 
>
>
> class EngineerType1(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t1', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> class EngineerType2(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t2', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> doesn't work of course since the polymorphic selectin load is a new 
> feature trying to work its way into a very intricate workflow.  I 
> threw up 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4026/mixing-selectin-load-on-top-of-inline
>  
> to look into it.  not sure how easy / hard it would be. 
>
> On Wed, Jul 12, 2017 at 7:09 PM,   
> wrote: 
> > 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), 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,   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. 
> >>> 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Mike Bayer]

this is how that would have to be mapped, hypothetically:

class EngineerBase(Person):
__tablename__ = 'engineer'

id = Column(ForeignKey('person.id'), primary_key=True)
engineer_name = Column(String(30))

__mapper_args__ = {
'polymorphic_load': 'selectin'
}


class EngineerType1(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t1',
'polymorphic_load': 'inline'
}


class EngineerType2(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t2',
'polymorphic_load': 'inline'
}


doesn't work of course since the polymorphic selectin load is a new
feature trying to work its way into a very intricate workflow.  I
threw up 
https://bitbucket.org/zzzeek/sqlalchemy/issues/4026/mixing-selectin-load-on-top-of-inline
to look into it.  not sure how easy / hard it would be.

On Wed, Jul 12, 2017 at 7:09 PM,   wrote:
> 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), 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,   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 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

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 
> ), 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,   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. 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

I have a mixed configuration with both joined and single table subclasses 
in a two-levels inheritance (like that 
), 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,   
> 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 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Mike Bayer]

On Wed, Jul 12, 2017 at 4:54 PM,   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+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.

-- 
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  

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

I noticed that {'polymorphic_load': 'selectin'} on single table inheritance 
can make several SQL queries unnecessarily.

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+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.

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

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+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.

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Jonathan Vanasco]

Great!  Thank you!.  

Good to know the behavior hasn't changed.   We have a lot of "read-only" 
routes or database-free routes which rely on the "lazy" 
transaction/connection behavior for performance.  I was worried the new 
flag could create db connectivity per-request when the session is created. 
 that would jam up the production boxes.

-- 
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.

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Mike Bayer]

the Session doesn't pull the connection from the engine until you do a
query or call session.connection(), session.execute(), etc. This is
because the Session supports multiple engines simultaenously and it
doesn't know which one to use until it's asked to query something.

On Tue, Jul 11, 2017 at 11:33 AM, Jonathan Vanasco
 wrote:
>
>
> On Tuesday, July 11, 2017 at 10:27:15 AM UTC-4, Mike Bayer wrote:
>>
>> So the note for this is at:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#pessimistic-disconnection-detection-added-to-the-connection-pool
>>
>> then in the main docs, the new flag replaces the previous "pessimistic
>> disconnect" recipe:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic
>>
>> in terms of a Session, the Session in non-autocommit mode, bound to an
>> Engine and not a specific Connection, does a connection pool checkout
>> per transaction, or in autocommit mode does checkout-per-statement, or
>> in autocommit mode when you use begin()/commit() again checkout per
>> transaction.
>
>
> Sorry, my question isn't coming across right... in non-autocommit mode,
> assuming the following example...
>
> # start the session
> s = Session()
>
> # waste some time...
> time.sleep(10)
>
> # get Foo.1
> s.query(Foo).get(1)
>
> # done
> s.rollback()
>
> does the pool checkout logic happen on `s = Session()` or `s.query(...` ?
>
> --
> 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.

-- 
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.

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Jonathan Vanasco]

On Tuesday, July 11, 2017 at 10:27:15 AM UTC-4, Mike Bayer wrote:
>
> So the note for this is at: 
>
>
> http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#pessimistic-disconnection-detection-added-to-the-connection-pool
>  
>
> then in the main docs, the new flag replaces the previous "pessimistic 
> disconnect" recipe: 
>
>
> http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic
>  
>
> in terms of a Session, the Session in non-autocommit mode, bound to an 
> Engine and not a specific Connection, does a connection pool checkout 
> per transaction, or in autocommit mode does checkout-per-statement, or 
> in autocommit mode when you use begin()/commit() again checkout per 
> transaction. 
>

Sorry, my question isn't coming across right... in non-autocommit mode, 
assuming the following example... 

# start the session
s = Session()

# waste some time...
time.sleep(10)

# get Foo.1
s.query(Foo).get(1)

# done
s.rollback()

does the pool checkout logic happen on `s = Session()` or `s.query(...` ?

-- 
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.

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Mike Bayer]

On Mon, Jul 10, 2017 at 5:58 PM, Jonathan Vanasco  wrote:
>
>
> On Monday, July 10, 2017 at 9:44:03 AM UTC-4, Mike Bayer wrote:
>>
>> * 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.
>
>
>
> Can you please detail where this happens?  Would it be on a new Session() or
> on the first time the session is actively used (e.g. SQL is emitted)

So the note for this is at:

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#pessimistic-disconnection-detection-added-to-the-connection-pool

then in the main docs, the new flag replaces the previous "pessimistic
disconnect" recipe:

http://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic

in terms of a Session, the Session in non-autocommit mode, bound to an
Engine and not a specific Connection, does a connection pool checkout
per transaction, or in autocommit mode does checkout-per-statement, or
in autocommit mode when you use begin()/commit() again checkout per
transaction.



>
> --
> 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.

-- 
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.

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[Jonathan Vanasco]

On Monday, July 10, 2017 at 9:44:03 AM UTC-4, Mike Bayer wrote:
>
> * 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.
>


Can you please detail where this happens?  Would it be on a new Session() 
or on the first time the session is actively used (e.g. SQL is emitted)

-- 
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.