Re: Adding Connection.autocommit as standard extension to DB-API 2.0 (PEP 249)
Marc-Andre Lemburg <[email protected]> Thu, 3 Nov 2022 20:51:57 +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.
--===============3656688722306539776==
Content-Type: multipart/alternative;
boundary="------------v5HWbQOXepG7fCi1emKjj2Di"
Content-Language: en-US
This is a multi-part message in MIME format.
--------------v5HWbQOXepG7fCi1emKjj2Di
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
On 03.11.2022 19:25, Tony Locke wrote:
> Hello all, with pg8000 if autocommit is turned on while a transaction
> is in progress, the autocommit behaviour only takes effect after the
> current transaction has been explicitly committed or rolled back. Of
> the options given, I'd go for option 1, raising an exception. I don't
> like the idea of SQL statements being executed implicitly, which is
> why I'd be against option 2.
That's a fair point, but please remember that no statements are
executed. The transaction control is completely with the driver library
and negotiated with the server (some use implicit statements for this,
but that's just a technical detail). In fact, users should /not/ use
transaction statements on connections managed by drivers. That's why we
have .rollback() and .commit() as separate APIs in the DB-API.
I think we've already settled on option 3, with the added note that many
database modules implement option 2. pg8000 would not be one of those,
but that's not a problem.
Writing to the attribute will be deprecated at the same time as
documenting it, so that we can prepare for DB-API 3.0 using a method
instead. This would then also address your concern.
>
> A radical suggestion for DBAPI-3.0 would be to remove autocommit
> completely. I think this would make the dbapi interface easier for
> people to understand, because there would be no implicit SQL commands,
> which is what I think confuses people. Of course the downside is less
> portability, but I think people accept that if they change their
> database a lot of things will be different, and autocommit is just one
> of them. A higher level library such as SQLAlchemy could still emit
> the correct SQL via the dialect so that users of SQLAlchemy wouldn't
> need to know about autocommit.
That's not going to work out :-) Autocommit is essential for some
database operations, e.g. to avoid locking. Many databases also don't
permit running certain DDL statements inside transactions.
BTW: Django's default is to run in autocommit mode... not a particularly
good choice, but that's how it is:
https://docs.djangoproject.com/en/4.1/topics/db/transactions/#autocommit
Cheers,
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Nov 03 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/
--------------v5HWbQOXepG7fCi1emKjj2Di
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 03.11.2022 19:25, Tony Locke wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAPpF0yO4OjNmJwCDZF2eG_Hym8sXY9=UHZPF8zbaikRof8jfTA@mail.gmail.com">
<div dir="ltr">
<div>Hello all, with pg8000 if autocommit is turned on while a
transaction is in progress, the autocommit behaviour only
takes effect after the current transaction has been explicitly
committed or rolled back. Of the options given, I'd go for
option 1, raising an exception. I don't like the idea of SQL
statements being executed implicitly, which is why I'd be
against option 2.</div>
</div>
</blockquote>
<p>That's a fair point, but please remember that no statements are
executed. The transaction control is completely with the driver
library and negotiated with the server (some use implicit
statements for this, but that's just a technical detail). In fact,
users should <i>not</i> use transaction statements on connections
managed by drivers. That's why we have .rollback() and .commit()
as separate APIs in the DB-API.</p>
<p>I think we've already settled on option 3, with the added note
that many database modules implement option 2. pg8000 would not be
one of those, but that's not a problem.</p>
<p>Writing to the attribute will be deprecated at the same time as
documenting it, so that we can prepare for DB-API 3.0 using a
method instead. This would then also address your concern.<br>
</p>
<blockquote type="cite"
cite="mid:CAPpF0yO4OjNmJwCDZF2eG_Hym8sXY9=UHZPF8zbaikRof8jfTA@mail.gmail.com">
<div dir="ltr">
<div><br>
</div>
<div>A radical suggestion for DBAPI-3.0 would be to remove
autocommit completely. I think this would make the dbapi
interface easier for people to understand, because there would
be no implicit SQL commands, which is what I think confuses
people. Of course the downside is less portability, but I
think people accept that if they change their database a lot
of things will be different, and autocommit is just one of
them. A higher level library such as SQLAlchemy could still
emit the correct SQL via the dialect so that users of
SQLAlchemy wouldn't need to know about autocommit.</div>
</div>
</blockquote>
<p>That's not going to work out :-) Autocommit is essential for some
database operations, e.g. to avoid locking. Many databases also
don't permit running certain DDL statements inside transactions.</p>
<p>BTW: Django's default is to run in autocommit mode... not a
particularly good choice, but that's how it is:
<a class="moz-txt-link-freetext" href="https://docs.djangoproject.com/en/4.1/topics/db/transactions/#autocommit">https://docs.djangoproject.com/en/4.1/topics/db/transactions/#autocommit</a><br>
</p>
<p>Cheers,</p>
<pre class="moz-signature" cols="72">--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Experts (#1, Nov 03 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>
--------------v5HWbQOXepG7fCi1emKjj2Di--
--===============3656688722306539776==
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
--===============3656688722306539776==--