[DB-SIG] Adding Connection.autocommit as standard extension to DB-API 2.0 (PEP 249)

Mike Bayer mike_mp at zzzcomputing.com
Fri Oct 28 13:09:46 EDT 2022



On Fri, Oct 28, 2022, at 12:48 PM, Marc-Andre Lemburg wrote:
> On 28.10.2022 18:36, Mike Bayer wrote:
> > 
> > 
> > On Fri, Oct 28, 2022, at 12:14 PM, Marc-Andre Lemburg wrote:
> >>
> >> Overall, I believe that important settings such as autocommit
> >> should only be set in the connection constructor, since the
> >> semantics change dramatically between autocommit and manual
> >> commit.
> > 
> > oh, that change would be much more intrusive.   This defeats the 
> > usability of connection pools, and being able to reset session state on 
> > a connection so that it may be returned to a pool for re-use is a normal 
> > thing.  Within the MS ODBC realm Ive just learned of the 
> > sp_reset_connection stored procedure, which seems to be sparsely 
> > documented, but is mentioned as essential for pooling here:
> > https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver16#tracking-access-to-a-database <https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver16#tracking-access-to-a-database>
> 
> In my application designs, I typically have two or three pools: one for
> autoconnect connections, one for manual commit ones and sometimes
> one for read-only connections.
> 
> In many respects, those types of connections are too different
> to safely keep them in the same pool. The optimization settings
> also often deep, so it would be too costly to reset them for
> placing them into a generic pool.
> 
> But those are my 2 cents.

Agree I would do it the same way and we document it here: https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#setting-isolation-for-a-sessionmaker-engine-wide

however, being able to set autocommit mid-connectivity I think is still important for use cases such as some database migrations, where the connection needs to be set into autocommit to perform some activities that can't run transactionally, but then to allow other operations to proceed within transactions.   It's true this can use two connections as well but IMO I dont think the DBAPI should be getting *that* opinionated.

again it doesnt matter since DBAPIs already have .autocommit attributes on them :)




> 
> >> In such a world, we'd only have an autocommit keyword argument
> >> in the Connection constructor and a read-only attribute on the
> >> object to query this after creation.
> >>
> >> Such a change could be implemented for a DB-API 3.0. For 2.0,
> >> the ship has sailed already.
> >>
> >> So how about going with the above compromise (*) ?
> -- 
> Marc-Andre Lemburg
> eGenix.com
> 
> Professional Python Services directly from the Experts (#1, Oct 28 2022)
> >>> Python Projects, Coaching and Support ...    https://www.egenix.com/
> >>> Python Product Development ...        https://consulting.egenix.com/
> ________________________________________________________________________
> 
> ::: We implement business ideas - efficiently in both time and costs :::
> 
>     eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
>      D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
>             Registered at Amtsgericht Duesseldorf: HRB 46611
>                 https://www.egenix.com/company/contact/
>                       https://www.malemburg.com/
> 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.python.org/pipermail/db-sig/attachments/20221028/74c5d235/attachment-0001.html>


More information about the DB-SIG mailing list