Re: Adding Connection.autocommit as standard extension to DB-API 2.0 (PEP 249)
Marc-Andre Lemburg <[email protected]> Tue, 1 Nov 2022 14:37:29 +0100
| Newsgroups | gmane.comp.python.db |
|---|---|
| Organization | eGenix.com Software GmbH; http://www.egenix.com/ |
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --===============8003032287282774334== Content-Type: multipart/alternative; boundary="------------rVcl1AXsxMd90i3S4z6eSV7y" Content-Language: en-US This is a multi-part message in MIME format. --------------rVcl1AXsxMd90i3S4z6eSV7y Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit I've thought some more about this over the last couple of days. Since we're adding a standard extension to the DB-API 2.0 based on existing use in the wild, we should really stick to what database module authors have implemented and not propose new semantics or add anything which is not yet widely accepted to the 2.0 version. Instead, I believe we should document what many modules already implement, which is the read/write .autocommit attribute. As for semantics, we should probably go with option 3 and only mention that many modules will actually already implement option 2. Regarding the problem with having attribute write access result in possible I/O, I think we should at the same time deprecate the write nature of the .autocommit attribute and announce that it'll be replaced with a new method, e.g. .setautocommit() (the DB-API has traditionally not added underscores to the names, except for the optional TPC API group), for DB-API 3.0. Related to this, I'd also suggest adding a new keyword parameter to the connection constructor (autocommit), which defaults to False and can be used to create autocommit connections right from the start. For the next version of the DB-API we should then also consider async methods and functions and try to make sure that all important API parts can be used in an async manner, in particular, making sure that API parts which can result in I/O are always defined in form of methods or functions (I believe that most are already, except for the .autocommit attribute which many modules implement). More generally speaking, I think Python is missing async support for properties. Perhaps this will be added at some point, so that we can write e.g. await connection.autocommit = True OTOH, I'm not really a fan of complex properties and even less so, ones which can result in I/O. I'll put together a new proposal for the new standard extension, addressing the above. On 01.11.2022 14:03, Mike Bayer wrote: > > > On Sun, Oct 30, 2022, at 5:38 PM, Erlend Egeberg Aasland wrote: >> >> > On 28 Oct 2022, at 18:14, Marc-Andre Lemburg <[email protected]> wrote: >> > >> > On 28.10.2022 16:51, Mike Bayer wrote: >> >> On Fri, Oct 28, 2022, at 10:20 AM, Marc-Andre Lemburg wrote: >> >>> On 28.10.2022 15:58, Marc-Andre Lemburg wrote: >> >>> > If there is a pending transaction, though, there are three >> approaches >> >>> > we could take: >> >>> > >> >>> > 1. The database module raises an exception, to force an explicit >> >>> > .commit() or .rollback() by the programmer. >> >>> > >> >>> > 2. The module automatically commits the pending transaction, >> >>> > since that's what autocommit is all about. >> >>> >> >>> Just checked: Option 2 is what ODBC mandates... >> >>> >> https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function >> <https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function> >> >>> (search for SQL_ATTR_AUTOCOMMIT) >> >> That's a C API, which has different programming conventions than >> what Python has, and it's also referring towards a function that's >> being called, so while that's a datapoint to note, I dont think by >> itself it really indicates how a Python API should organize itself >> one way or the other. >> > >> > I just wanted to note that the semantics of what to do when switching >> > from False to True are already defined in one of the major database >> > APIs standards, so it's good practice to follow such a standard. >> >> +1 > > I checked JDBC and it also follows this convention: > https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean) > > > *however*, in both cases of ODBC and JDBC, these are function calls. > If DBAPI had a connection method define called `set_autocommit()`, the > implicit COMMIT would be fine. In Python, running an attribute set > operation should not incur IO at the point at which the attribute is > being assigned. So if we are following existing standards from > different programming languages, the .autocommit attribute should be > changed to be a method `set_autocommit()`. > > >> >> My preference would be 2. or 3. >> >> > 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. >> > >> > 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. >> >> I would be ok with that. > > this is the only variant of the proposal that would be a breaking > change for SQLAlchemy, since it removes existing functionality that > most DBAPIs have now and change a use case that is now possible to be > one that is impossible. The reality would be that some would comply, > and others (most others) would not, because it is extremely difficult > (and mostly unnecessary) to remove functionality from an API. It's > also inconsistent with the previous notion of following other existing > database standards as both ODBC and JDBC feature means of affecting > the autocommit behavior on an already opened connection. > > > _______________________________________________ > DB-SIG maillist [email protected] > https://mail.python.org/mailman/listinfo/db-sig -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Experts (#1, Nov 01 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/ --------------rVcl1AXsxMd90i3S4z6eSV7y Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body> <p>I've thought some more about this over the last couple of days.</p> <p>Since we're adding a standard extension to the DB-API 2.0 based on existing use in the wild, we should really stick to what database module authors have implemented and not propose new semantics or add anything which is not yet widely accepted to the 2.0 version.</p> <p>Instead, I believe we should document what many modules already implement, which is the read/write .autocommit attribute. As for semantics, we should probably go with option 3 and only mention that many modules will actually already implement option 2.</p> <p>Regarding the problem with having attribute write access result in possible I/O, I think we should at the same time deprecate the write nature of the .autocommit attribute and announce that it'll be replaced with a new method, e.g. .setautocommit() (the DB-API has traditionally not added underscores to the names, except for the optional TPC API group), for DB-API 3.0.</p> <p>Related to this, I'd also suggest adding a new keyword parameter to the connection constructor (autocommit), which defaults to False and can be used to create autocommit connections right from the start.<br> </p> <p>For the next version of the DB-API we should then also consider async methods and functions and try to make sure that all important API parts can be used in an async manner, in particular, making sure that API parts which can result in I/O are always defined in form of methods or functions (I believe that most are already, except for the .autocommit attribute which many modules implement).<br> </p> <p>More generally speaking, I think Python is missing async support for properties. Perhaps this will be added at some point, so that we can write e.g.</p> <p>await connection.autocommit = True</p> <p>OTOH, I'm not really a fan of complex properties and even less so, ones which can result in I/O.</p> <p>I'll put together a new proposal for the new standard extension, addressing the above.<br> </p> <p><br> </p> <div class="moz-cite-prefix">On 01.11.2022 14:03, Mike Bayer wrote:<br> </div> <blockquote type="cite" cite="mid:[email protected]"> <title></title> <div><br> </div> <div><br> </div> <div>On Sun, Oct 30, 2022, at 5:38 PM, Erlend Egeberg Aasland wrote:<br> </div> <blockquote type="cite" id="qt"> <div><br> </div> <div>> On 28 Oct 2022, at 18:14, Marc-Andre Lemburg <<a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>> wrote:<br> </div> <div>> <br> </div> <div>> On 28.10.2022 16:51, Mike Bayer wrote:<br> </div> <div>>> On Fri, Oct 28, 2022, at 10:20 AM, Marc-Andre Lemburg wrote:<br> </div> <div>>>> On 28.10.2022 15:58, Marc-Andre Lemburg wrote:<br> </div> <div>>>> > If there is a pending transaction, though, there are three approaches<br> </div> <div>>>> > we could take:<br> </div> <div>>>> ><br> </div> <div>>>> > 1. The database module raises an exception, to force an explicit<br> </div> <div>>>> > .commit() or .rollback() by the programmer.<br> </div> <div>>>> ><br> </div> <div>>>> > 2. The module automatically commits the pending transaction,<br> </div> <div>>>> > since that's what autocommit is all about.<br> </div> <div>>>> <br> </div> <div>>>> Just checked: Option 2 is what ODBC mandates...<br> </div> <div>>>> <a href="https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function" moz-do-not-send="true" class="moz-txt-link-freetext">https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function</a> <<a href="https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function" moz-do-not-send="true" class="moz-txt-link-freetext">https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function</a>><br> </div> <div>>>> (search for SQL_ATTR_AUTOCOMMIT)<br> </div> <div>>> That's a C API, which has different programming conventions than what Python has, and it's also referring towards a function that's being called, so while that's a datapoint to note, I dont think by itself it really indicates how a Python API should organize itself one way or the other. <br> </div> <div>> <br> </div> <div>> I just wanted to note that the semantics of what to do when switching<br> </div> <div>> from False to True are already defined in one of the major database<br> </div> <div>> APIs standards, so it's good practice to follow such a standard.<br> </div> <div><br> </div> <div>+1<br> </div> </blockquote> <div><br> </div> <div>I checked JDBC and it also follows this convention: <a href="https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)" moz-do-not-send="true" class="moz-txt-link-freetext">https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)</a> <br> </div> <div><br> </div> <div>*however*, in both cases of ODBC and JDBC, these are function calls. If DBAPI had a connection method define called `set_autocommit()`, the implicit COMMIT would be fine. In Python, running an attribute set operation should not incur IO at the point at which the attribute is being assigned. So if we are following existing standards from different programming languages, the .autocommit attribute should be changed to be a method `set_autocommit()`.<br> </div> <div><br> </div> <div><br> </div> <blockquote type="cite" id="qt"> <div><br> </div> <div>My preference would be 2. or 3.<br> </div> <div><br> </div> <div>> Overall, I believe that important settings such as autocommit<br> </div> <div>> should only be set in the connection constructor, since the<br> </div> <div>> semantics change dramatically between autocommit and manual<br> </div> <div>> commit.<br> </div> <div>> <br> </div> <div>> In such a world, we'd only have an autocommit keyword argument<br> </div> <div>> in the Connection constructor and a read-only attribute on the<br> </div> <div>> object to query this after creation.<br> </div> <div><br> </div> <div>I would be ok with that.<br> </div> </blockquote> <div><br> </div> <div>this is the only variant of the proposal that would be a breaking change for SQLAlchemy, since it removes existing functionality that most DBAPIs have now and change a use case that is now possible to be one that is impossible. The reality would be that some would comply, and others (most others) would not, because it is extremely difficult (and mostly unnecessary) to remove functionality from an API. It's also inconsistent with the previous notion of following other existing database standards as both ODBC and JDBC feature means of affecting the autocommit behavior on an already opened connection. <br> </div> <div><br> </div> <br> <fieldset class="moz-mime-attachment-header"></fieldset> <pre class="moz-quote-pre" wrap="">_______________________________________________ DB-SIG maillist - <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a> <a class="moz-txt-link-freetext" href="https://mail.python.org/mailman/listinfo/db-sig">https://mail.python.org/mailman/listinfo/db-sig</a> </pre> </blockquote> <pre class="moz-signature" cols="72">-- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Experts (#1, Nov 01 2022) >>> Python Projects, Coaching and Support ... <a class="moz-txt-link-freetext" href="https://www.egenix.com/">https://www.egenix.com/</a> >>> Python Product Development ... <a class="moz-txt-link-freetext" href="https://consulting.egenix.com/">https://consulting.egenix.com/</a> ________________________________________________________________________ ::: 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 <a class="moz-txt-link-freetext" href="https://www.egenix.com/company/contact/">https://www.egenix.com/company/contact/</a> <a class="moz-txt-link-freetext" href="https://www.malemburg.com/">https://www.malemburg.com/</a> </pre> </body> </html> --------------rVcl1AXsxMd90i3S4z6eSV7y-- --===============8003032287282774334== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ DB-SIG maillist - [email protected] https://mail.python.org/mailman/listinfo/db-sig --===============8003032287282774334==--