Python DB API - commit() v. execute("commit transaction")?

[Skip Montanaro]

Assuming the underlying database supports transactions, is there any
difference between calling the commit() method on the connection and
calling the execute method on the cursor with the "commit transaction"
statement? It seems a bit asymmetric to me to start a transaction with

cur.execute("begin transaction")

but end it with

conn.commit()

Thx,

Skip

[Jon Ribbens]

Yes, I found this most peculiar.

[Ian Kelly]

On Tue, May 30, 2017 at 7:29 AM, Skip Montanaro

There's no difference I'm aware of in the implementations I've used,
but having a consistent API does allow for constructions such as:

try:
do_stuff(conn)
except:
conn.rollback()
finally:
conn.commit()

without having to worry about variations in syntax between databases.

[Chris Angelico]

I prefer:

with conn, conn.cursor() as cur:
do_stuff(cur)

Not sure how many connectors that works with, but it's the best way to
use psycopg2.

ChrisA

[Joseph L. Casale]

-----Original Message-----
From: Python-list [mailto:python-list-
bounces+jcasale=activene...@python.org] On Behalf Of Ian Kelly
Sent: Tuesday, May 30, 2017 8:12 AM
To: Python <pytho...@python.org>
Subject: Re: Python DB API - commit() v. execute("commit transaction")?

> There's no difference I'm aware of in the implementations I've used,
> but having a consistent API does allow for constructions such as:
>
> try:
> do_stuff(conn)
> except:
> conn.rollback()
> finally:
> conn.commit()

So you always commit even after an exception?

[Jon Ribbens]

On 2017-05-30, Joseph L. Casale <jca...@activenetwerx.com> wrote:
>> There's no difference I'm aware of in the implementations I've used,
>> but having a consistent API does allow for constructions such as:
>>
>> try:
>> do_stuff(conn)
>> except:
>> conn.rollback()
>> finally:
>> conn.commit()
>
> So you always commit even after an exception?

Well, as you can see, it rollbacks then commits, so the commit does nothing.

[Ian Kelly]

On Tue, May 30, 2017 at 8:21 AM, Chris Angelico <ros...@gmail.com> wrote:

> On Wed, May 31, 2017 at 12:12 AM, Ian Kelly <ian.g...@gmail.com> wrote:
>> There's no difference I'm aware of in the implementations I've used,
>> but having a consistent API does allow for constructions such as:
>>
>> try:
>> do_stuff(conn)
>> except:
>> conn.rollback()
>> finally:
>> conn.commit()
>>

>> without having to worry about variations in syntax between databases.
>
> I prefer:
>
> with conn, conn.cursor() as cur:
> do_stuff(cur)

As do I, but it's not as clear what that does and the use as context
managers is also not explicitly supported by PEP 249.

[Ian Kelly]

On Tue, May 30, 2017 at 8:19 AM, Joseph L. Casale
<jca...@activenetwerx.com> wrote:
> -----Original Message-----
> From: Python-list [mailto:python-list-
> bounces+jcasale=activene...@python.org] On Behalf Of Ian Kelly
> Sent: Tuesday, May 30, 2017 8:12 AM
> To: Python <pytho...@python.org>
> Subject: Re: Python DB API - commit() v. execute("commit transaction")?
>

>> There's no difference I'm aware of in the implementations I've used,
>> but having a consistent API does allow for constructions such as:
>>
>> try:
>> do_stuff(conn)
>> except:
>> conn.rollback()
>> finally:
>> conn.commit()
>

> So you always commit even after an exception?

Doh, that finally should have been else. Though to be fair, committing
immediately after rolling back should be harmless if there are no
other threads using the same connection.

[Jon Ribbens]

On 2017-05-30, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> On Tue, 30 May 2017 13:42:14 -0000 (UTC), Jon Ribbens
><jon+u...@unequivocal.eu> declaimed the following:

> https://stackoverflow.com/questions/2546926/why-connection-in-pythons-db-api-does-not-have-begin-operation
>
> may offer some reasoning... (along with its linked discussion
> https://groups.google.com/forum/#!topic/comp.lang.python/gNC_O6zUdAA )

I can't make head nor tail of what they are trying to say there.
Mind you, it doesn't help that the DB-API concept of cursors seems
to bear almost no resemblance to the SQL concept of cursors.

> The main argument I have for not using
> cur.execute("begin transaction")
> is that the DB-API module concept of being in/out of a transaction might
> get out of synchronization.

What would you suggest doing instead?

[Chris Angelico]

On Wed, May 31, 2017 at 1:09 AM, Ian Kelly <ian.g...@gmail.com> wrote:
> On Tue, May 30, 2017 at 8:21 AM, Chris Angelico <ros...@gmail.com> wrote:
>> On Wed, May 31, 2017 at 12:12 AM, Ian Kelly <ian.g...@gmail.com> wrote:

>>> There's no difference I'm aware of in the implementations I've used,
>>> but having a consistent API does allow for constructions such as:
>>>
>>> try:
>>> do_stuff(conn)
>>> except:
>>> conn.rollback()
>>> finally:
>>> conn.commit()
>>>

>>> without having to worry about variations in syntax between databases.
>>
>> I prefer:
>>
>> with conn, conn.cursor() as cur:
>> do_stuff(cur)
>
> As do I, but it's not as clear what that does and the use as context
> managers is also not explicitly supported by PEP 249.

Yes, it's not very clear that "with conn, conn.cursor() as cur" means
"with the database". What I'd really like would be "with conn:", but
that's only half the story. But it does make very good sense to use a
context manager to bracket all use of the database.

ChrisA

[MRAB]

It also swallows any exception from do_stuff.

[Chris Angelico]

On Wed, May 31, 2017 at 1:59 AM, MRAB <pyt...@mrabarnett.plus.com> wrote:
> On 2017-05-30 16:03, Jon Ribbens wrote:
>>

> It also swallows any exception from do_stuff.

It's just missing the "raise" at the bottom of the except block. For
production code, I would assume that that would be present.

But this is why you really need to be using a context manager. If the
connector doesn't itself support them, wrap up your transaction
handling into your own context manager and use that. It's too easy to
forget something.

ChrisA

[Jon Ribbens]

On 2017-05-30, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:

> On Tue, 30 May 2017 15:12:55 -0000 (UTC), Jon Ribbens
><jon+u...@unequivocal.eu> declaimed the following:

>>I can't make head nor tail of what they are trying to say there.
>>Mind you, it doesn't help that the DB-API concept of cursors seems
>>to bear almost no resemblance to the SQL concept of cursors.
>

> The main concept is that (set autocommit to False, for those DB
> adapters that have autocommit) a transaction is implicitly begun on the
> first SQL statement that accesses the data (though I think I've read that
> SQLite doesn't start a transaction until one submits an SQL statement that
> modifies data).
>
> And this behavior apparently matches version 1 of the SQL "standard".
> Version 2 added explicit "begin" -- I suspect Python DB-API adapters to a
> ver2 database are suppose to issue the "begin" whenever the adapter
> believes there is no open transaction.

Indeed. As I say, I think perhaps the source of the confusion is that
DB-API cursors make no sense at all, rather than the problem being
transactions per se.

It's almost as if DB-API was written by someone who had never seen
a database. 'execute' being a method of the cursor rather than the
connection is a logical impossibility - cursors are something that
are returned by SQL execution, not the other way around.

If DB-API had Connection.execute, then Connection.commit wouldn't
be strange. Cursor.execute is bizarre, and it being paired with
Connection.commit is just icing on the bizarre cake.

[Ian Kelly]

On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
> Indeed. As I say, I think perhaps the source of the confusion is that
> DB-API cursors make no sense at all, rather than the problem being
> transactions per se.
>
> It's almost as if DB-API was written by someone who had never seen
> a database. 'execute' being a method of the cursor rather than the
> connection is a logical impossibility - cursors are something that
> are returned by SQL execution, not the other way around.

A cursor is just a control structure for traversing over a result set.
Whether you call execute and it creates and returns a cursor, or you
create the cursor first and then bind it to a result set by calling
execute (like the DB-API) seems no more meaningful to me than asking
which should be created first: the model or the view. Personally I
prefer the DB-API approach because it makes it easier to create cursor
subclasses and potentially allows for reuse of buffers.

As to the idea earlier in the thread that "the DB-API concept of

cursors seems to bear almost no resemblance to the SQL concept of

cursors", I disagree there as well. Again, the concept of a cursor is
just a control structure. The only real difference between a DB-API
cursor and a SQL cursor (as used e.g. in PL/SQL) is that one is native
to the database process while the other lives in the client.

> If DB-API had Connection.execute, then Connection.commit wouldn't
> be strange. Cursor.execute is bizarre, and it being paired with
> Connection.commit is just icing on the bizarre cake.

Cursor.execute is fine since DB-API cursors are bound after
construction. The alternative would be to have Connection.execute
return a handle to the result set and then calling something like
Cursor.bind to attach it.

Cursor.commit on the other hand would never make sense because you
can't commit a single cursor. You only can commit the current
transactional state of the connection as a whole. Maybe that's another
reason for having a Connection.commit method -- it lets you commit
without going to the trouble of creating a cursor in the first place
(but then, why not have Connection.insert or Connection.update for the
same purpose?)

[Jon Ribbens]

On 2017-05-30, Ian Kelly <ian.g...@gmail.com> wrote:
> On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
> A cursor is just a control structure for traversing over a result set.

Exactly - so it makes no sense at all to have one when there is no
result set. It makes even less sense to require one in order to
execute queries that don't even have result sets (e.g. INSERT).

> As to the idea earlier in the thread that "the DB-API concept of
> cursors seems to bear almost no resemblance to the SQL concept of
> cursors", I disagree there as well.

If you explained to someone who was an SQL expert but knew nothing
about Python that you wrote a Python program that did the following:

(a) Connected to an SQL database
(b) Created a cursor on the connection (with no query)
(c) Executed an INSERT on the cursor

Do you think they would recognise that as being similar to their
understanding of SQL cursors?

> Again, the concept of a cursor is just a control structure. The only
> real difference between a DB-API cursor and a SQL cursor (as used
> e.g. in PL/SQL) is that one is native to the database process while
> the other lives in the client.

That's completely untrue. An SQL cursor is a result set and a position
in that result set. A DB-API "cursor" is a database connection (even
though there is something else in DB-API that is also called a
database connection.) You don't need an SQL cursor unless you're doing
something fancy and complicated. You need a DB-API cursor before you
can do anything at all.

No other language's database API works this way - they all work by
executing queries on the connection (I checked Go, Java, PHP, Ruby,
Perl).

> Cursor.commit on the other hand would never make sense because you
> can't commit a single cursor.

Indeed - that's my point. The problem isn't that it's
Connection.commit, the problem is that it's Cursor.execute.

> You only can commit the current transactional state of the
> connection as a whole. Maybe that's another reason for having a
> Connection.commit method -- it lets you commit without going to the
> trouble of creating a cursor in the first place

Er, there can't *be* anything to commit unless you've already created
a "cursor".

[Ian Kelly]

On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
> On 2017-05-30, Ian Kelly <ian.g...@gmail.com> wrote:
>> On Tue, May 30, 2017 at 1:27 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>> A cursor is just a control structure for traversing over a result set.
>
> Exactly - so it makes no sense at all to have one when there is no
> result set. It makes even less sense to require one in order to
> execute queries that don't even have result sets (e.g. INSERT).

What if you want to set cursor options before executing the query,
e.g. Cursor.setoutputsize? It doesn't make sense as a Connection
method, but it needs to be called before execute. Then you need to
access the cursor before it has a result set.

>> As to the idea earlier in the thread that "the DB-API concept of
>> cursors seems to bear almost no resemblance to the SQL concept of
>> cursors", I disagree there as well.
>
> If you explained to someone who was an SQL expert but knew nothing
> about Python that you wrote a Python program that did the following:
>
> (a) Connected to an SQL database
> (b) Created a cursor on the connection (with no query)
> (c) Executed an INSERT on the cursor
>
> Do you think they would recognise that as being similar to their
> understanding of SQL cursors?

I'll grant that it's weird that cursors are also used for DML and DDL.
I think though that a SQL expert would understand what is meant when
PEP-249 says "These objects represent a database cursor, which is used
to manage the context of a fetch operation."

>> Again, the concept of a cursor is just a control structure. The only
>> real difference between a DB-API cursor and a SQL cursor (as used
>> e.g. in PL/SQL) is that one is native to the database process while
>> the other lives in the client.
>
> That's completely untrue. An SQL cursor is a result set and a position
> in that result set.

Per Wikipedia: "In computer science, a database cursor is a control
structure that enables traversal over the records in a database.
Cursors facilitate subsequent processing in conjunction with the
traversal, such as retrieval, addition and removal of database
records. The database cursor characteristic of traversal makes cursors
akin to the programming language concept of iterator."

Yes, SQL cursors are tied to specific result sets. But what you gain
above and beyond the result set by using a cursor is row-by-row
traversal.

> A DB-API "cursor" is a database connection

Baloney. Creating a cursor does not spawn a new connection to the
database. Cursors created from a connection share that connection.

> No other language's database API works this way - they all work by
> executing queries on the connection (I checked Go, Java, PHP, Ruby,
> Perl).

So Python is unique in this regard. What of it?

>> You only can commit the current transactional state of the
>> connection as a whole. Maybe that's another reason for having a
>> Connection.commit method -- it lets you commit without going to the
>> trouble of creating a cursor in the first place
>
> Er, there can't *be* anything to commit unless you've already created
> a "cursor".

Er, you cut off the part of my comment that laments exactly this situation.

But it may be the case that the code doing the commit is only loosely
coupled to the code that modified something and you don't have that
cursor hanging around still, in which case the method saves you the
need to create a *second* cursor in order to issue a "commit"
statement.

[Chris Angelico]

On Wed, May 31, 2017 at 11:06 AM, Ian Kelly <ian.g...@gmail.com> wrote:
> What if you want to set cursor options before executing the query,
> e.g. Cursor.setoutputsize? It doesn't make sense as a Connection
> method, but it needs to be called before execute. Then you need to
> access the cursor before it has a result set.

If cursors have value as sub-transaction concepts, it must be possible
to create multiple of them, apply different options to them, and then
execute queries on them independently. Is this the case? Actually, it
probably doesn't need to be, as there's not really any options where
you'd care about that (if something emulated cursors by simply picking
the largest buffer size ever selected, you'd probably never see the
difference). So the value of a pre-query cursor is fairly minimal.

In the DB API 3.0, what I would like to see is that the connection
becomes a context manager that gives you a transaction object. With
that transaction, you can perform queries, and you could create
cursors from it if you want them, or just use a single in-built
cursor. But a cursor would relate to a result set, and would have no
meaning outside of it. At the moment, we don't have any object
representation of a transaction, which means that (unless I'm missing
something here) it's fundamentally impossible to have concurrent
transactions on a single connection.

ChrisA

[Jon Ribbens]

On 2017-05-31, Ian Kelly <ian.g...@gmail.com> wrote:
> On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>> A DB-API "cursor" is a database connection
>
> Baloney. Creating a cursor does not spawn a new connection to the
> database. Cursors created from a connection share that connection.

Baloney yourself - I didn't say it was a *new* connection. In DB-API,
a Connection is basically nothing - you can do nothing at all with it.
A Cursor is actually what any other language would call a connection.
If you say that this description is confusing connections and cursors,
then you have understood my point - that's exactly what DB-API is doing.

>> No other language's database API works this way - they all work by
>> executing queries on the connection (I checked Go, Java, PHP, Ruby,
>> Perl).
>
> So Python is unique in this regard. What of it?

So it's a big hint that Python is wrong, if it takes terminology used
by databases and all other languages, and uses that same terminology
to mean something that no other language uses it to mean. Having your
own quirky API for a standard function is generally not a good thing,
especially when the quirky API has no advantages whatsoever.

> But it may be the case that the code doing the commit is only loosely
> coupled to the code that modified something and you don't have that
> cursor hanging around still, in which case the method saves you the
> need to create a *second* cursor in order to issue a "commit"
> statement.

Yes, this is indeed a problem with DB-API - you have to keep *two*
objects around all the time (the connection and the cursor) and pass
them to functions, etc, when in any sensible system as used by all
other languages you would only need to pass the connection.

[Skip Montanaro]

Let me try to gently steer the discussion back to my original
question. I know that both databases I'm using support the following
statements:

begin transaction
commit transaction
rollback transaction

I'm kind of stuck with the database API I have. ("Love the child you
have, not the one you wish you had?") Given that I have the choice to
execute those three statements to bound a transaction, is there any
reason not to use them instead of

(conn or cur).execute("begin transaction")
conn.commit()
conn.rollback()

?

I must say this discussion has been enlightening for me. I'm not a
database guy, so for better or worse, my view of how to interact with
relational databases has always been colored by the Python database
adapters. I will pose one question which relates to the broader
discussion which has ensued. On occasion, I have found myself wanting
to perform subsequent queries while processing the result set from an
initial query. Something like:

conn = ... whatever ...

cur1 = conn.cursor()
cur2 = conn.cursor()

for row in cur1.execute("select something ..."):
tidbit = ... pick something of interest out of the row ...
for new_row in cur2.execute("select something else involving the
tidbit ..."):
... process new_row ...

I know that given a smart database person, a single (probably pretty
complex) select statement can be written which collapses the nested
for loops into a single result set and corresponding single for loop.
Still, can you do the nested cur1.execute/cur2.execute thing if you
are performing selects on the connection?

Skip

[Jon Ribbens]

On 2017-05-31, Skip Montanaro <skip.mo...@gmail.com> wrote:
> I'm kind of stuck with the database API I have. ("Love the child you
> have, not the one you wish you had?") Given that I have the choice to
> execute those three statements to bound a transaction, is there any
> reason not to use them instead of
>
> (conn or cur).execute("begin transaction")
> conn.commit()
> conn.rollback()

I very much doubt it.

> I must say this discussion has been enlightening for me. I'm not a
> database guy, so for better or worse, my view of how to interact with
> relational databases has always been colored by the Python database
> adapters.

Indeed. This is where I was coming from too, and when I moved beyond
that point I got very confused because it turned out that Python had
seriously misled me.

> cur1 = conn.cursor()
> cur2 = conn.cursor()
>
> for row in cur1.execute("select something ..."):
> tidbit = ... pick something of interest out of the row ...
> for new_row in cur2.execute("select something else involving the
> tidbit ..."):
> ... process new_row ...

Well, you can't do that, because you can't iterate over an execute().
You would do:

cur.execute("SELECT ...")
for row1 in cur.fetchall():
cur.execute("SELECT ...")
for row2 in cur.fetchall():
....

and as far as the database is concerned, the query is over and done
with as soon as you call fetchall(), so there is no possible overlap
between the different queries.

[Skip Montanaro]

Well, you can't do that, because you can't iterate over an execute().
You would do:

cur.execute("SELECT ...")
for row1 in cur.fetchall():
cur.execute("SELECT ...")
for row2 in cur.fetchall():
....

Whoops, yeah. I think one of the adapters at my disposal at work (maybe one
layered on top of pyodbc) allows me to do that. I get confused. Never just
type (pseudo) code into an email message...

Skip

[Peter Otten]

You can, if the database is sqlite3.

> You would do:
>
> cur.execute("SELECT ...")
> for row1 in cur.fetchall():
> cur.execute("SELECT ...")
> for row2 in cur.fetchall():
> ....
>
> and as far as the database is concerned, the query is over and done
> with as soon as you call fetchall(), so there is no possible overlap
> between the different queries.

It's easy to simulate an iterable cursor with

iter(cursor.fetchone, None)

and then two cursors instead of one start making sense again.

[Jon Ribbens]

On 2017-05-31, Peter Otten <__pet...@web.de> wrote:

> Jon Ribbens wrote:
>> You would do:
>>
>> cur.execute("SELECT ...")
>> for row1 in cur.fetchall():
>> cur.execute("SELECT ...")
>> for row2 in cur.fetchall():
>> ....
>>
>> and as far as the database is concerned, the query is over and done
>> with as soon as you call fetchall(), so there is no possible overlap
>> between the different queries.
>
> It's easy to simulate an iterable cursor with
>
> iter(cursor.fetchone, None)
>
> and then two cursors instead of one start making sense again.

Indeed. I think this would not work, in general. For example, I think
with MySQLdb it would work if you use a standard Cursor class, as that
downloads the entire result set as soon as execute() is called, but it
would not work with the SSCursor class which downloads results as
needed. Again, this is because DB-API "cursors" are not SQL cursors.

[Pavol Lisy]

On 5/31/17, Peter Otten <__pet...@web.de> wrote:
> Jon Ribbens wrote:
>

> You can, if the database is sqlite3.

cx_Oracle too.

But althoug return from execute is undefined (
https://www.python.org/dev/peps/pep-0249/#id16 ), you could iterate
over cursor ( https://www.python.org/dev/peps/pep-0249/#iter )

>> You would do:
>>
>> cur.execute("SELECT ...")
>> for row1 in cur.fetchall():
>> cur.execute("SELECT ...")
>> for row2 in cur.fetchall():
>> ....
>>
>> and as far as the database is concerned, the query is over and done
>> with as soon as you call fetchall(), so there is no possible overlap
>> between the different queries.
>

> It's easy to simulate an iterable cursor with
>
> iter(cursor.fetchone, None)
>
> and then two cursors instead of one start making sense again.

fetchall could be disaster for memory!

[Peter Otten]

Jon Ribbens wrote:

> On 2017-05-31, Peter Otten <__pet...@web.de> wrote:
>> Jon Ribbens wrote:

>>> You would do:
>>>
>>> cur.execute("SELECT ...")
>>> for row1 in cur.fetchall():
>>> cur.execute("SELECT ...")
>>> for row2 in cur.fetchall():
>>> ....
>>>
>>> and as far as the database is concerned, the query is over and done
>>> with as soon as you call fetchall(), so there is no possible overlap
>>> between the different queries.
>>

>> It's easy to simulate an iterable cursor with
>>
>> iter(cursor.fetchone, None)
>>
>> and then two cursors instead of one start making sense again.
>

> Indeed. I think this would not work, in general. For example, I think
> with MySQLdb it would work if you use a standard Cursor class, as that
> downloads the entire result set as soon as execute() is called, but it
> would not work with the SSCursor class which downloads results as
> needed. Again, this is because DB-API "cursors" are not SQL cursors.

ere's an excerpt of PEP 249:

"""
.cursor()
Return a new Cursor Object using the connection.

If the database does not provide a direct cursor concept, the module will
have to emulate cursors using other means to the extent needed by this
specification. [4]

Cursor Objects

These objects represent a database cursor, which is used to manage the

context of a fetch operation. Cursors created from the same connection are
not isolated, i.e., any changes done to the database by a cursor are
immediately visible by the other cursors. Cursors created from different
connections can or can not be isolated, depending on how the transaction
support is implemented (see also the connection's .rollback() and .commit()
methods).
"""

I don't see how a cursor class that messes up interleaved fetchone() calls
can be compliant with the DB-API.

[DFS]

On 5/31/2017 6:26 AM, Jon Ribbens wrote:


> Yes, this is indeed a problem with DB-API - you have to keep *two*
> objects around all the time (the connection and the cursor) and pass
> them to functions, etc, when in any sensible system as used by all
> other languages you would only need to pass the connection.

You only need to pass the cursor.

=====================================================
import sqlite3

def DMin(mincurs):
mincurs.execute("select min(val) from test;")
return mincurs.fetchone()[0]

def DMax(maxcurs):
maxcurs.execute("select max(val) from test;")
return maxcurs.fetchone()[0]

conn = sqlite3.connect("D:\\test.sqlite")
curs = conn.cursor()

curs.execute("drop table if exists test;")
curs.execute("create table test (val integer);")
for i in range(1,21):
curs.execute("insert into test values(?);",(i,))
conn.commit() #note: commit is optional for this test

print(DMin(curs))
print(DMax(curs))

curs.close()
conn.close()
=====================================================

[Jon Ribbens]

On 2017-05-31, DFS <nos...@dfs.com> wrote:
> On 5/31/2017 6:26 AM, Jon Ribbens wrote:
>> Yes, this is indeed a problem with DB-API - you have to keep *two*
>> objects around all the time (the connection and the cursor) and pass
>> them to functions, etc, when in any sensible system as used by all
>> other languages you would only need to pass the connection.
>
> You only need to pass the cursor.

... unless you want to call .commit() or .rollback().
Which is where we came in.

[Jon Ribbens]

Why not?

Either way, it appears that such classes are standard.

[Jon Ribbens]

On 2017-05-31, Pavol Lisy <pavol...@gmail.com> wrote:
> But althoug return from execute is undefined (
> https://www.python.org/dev/peps/pep-0249/#id16 ), you could iterate
> over cursor ( https://www.python.org/dev/peps/pep-0249/#iter )

... which is also optional.

[Chris Angelico]

Technically you CAN commit from the cursor:

cur.connection.commit()

Proof:

>>> import psycopg2
>>> conn = psycopg2.connect("")
>>> cur = conn.cursor()
>>> cur.connection is conn
True

ChrisA

[Jon Ribbens]

On 2017-05-31, Chris Angelico <ros...@gmail.com> wrote:
> On Thu, Jun 1, 2017 at 12:10 AM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>> ... unless you want to call .commit() or .rollback().
>> Which is where we came in.
>
> Technically you CAN commit from the cursor:
>
> cur.connection.commit()

That's an optional extension.

[Ian Kelly]

On Wed, May 31, 2017 at 8:18 AM, Chris Angelico <ros...@gmail.com> wrote:
> Technically you CAN commit from the cursor:
>
> cur.connection.commit()

Technically cur.connection is an optional extension. ;-)

[Ian Kelly]

It works just fine with every DB-API client I've used, including those
which only fetch results as needed. cx_Oracle, for instance. It's not
Python's fault if the underlying MySQL client interface is shitty.

> --
> https://mail.python.org/mailman/listinfo/python-list

[Ian Kelly]

On Wed, May 31, 2017 at 4:26 AM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
> On 2017-05-31, Ian Kelly <ian.g...@gmail.com> wrote:
>> On Tue, May 30, 2017 at 4:57 PM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>>> A DB-API "cursor" is a database connection
>>
>> Baloney. Creating a cursor does not spawn a new connection to the
>> database. Cursors created from a connection share that connection.
>
> Baloney yourself - I didn't say it was a *new* connection. In DB-API,
> a Connection is basically nothing - you can do nothing at all with it.
> A Cursor is actually what any other language would call a connection.
> If you say that this description is confusing connections and cursors,
> then you have understood my point - that's exactly what DB-API is doing.

I don't understand what you mean by "connection", then. You seem to be
defining it as "something that executes queries". I would instead
define it as "something responsible for managing the low-level state
of communication with the database server". There's no particular
reason why that should include the higher-level concept of managing
query execution. APIs that do this are just conflating concepts in a
different way.

I tend to think of DB-API cursors as being more analogous with DBI
(Perl) or ODBC statement handles. DBI lets you call conn.execute
directly, but more normally you call conn.prepare(query) which does
some allocations and maybe parses the query in advance and gives you a
statement handle, followed by stmt.execute(params) which actually
executes the query. You then call fetchone, etc. from the statement
handle, not the connection. It's not exactly like DB-API because the
cursor doesn't use a prepared statement and might be reused for
different statements, but it has a similar feel to it.

>>> No other language's database API works this way - they all work by
>>> executing queries on the connection (I checked Go, Java, PHP, Ruby,
>>> Perl).
>>
>> So Python is unique in this regard. What of it?
>
> So it's a big hint that Python is wrong, if it takes terminology used
> by databases and all other languages, and uses that same terminology
> to mean something that no other language uses it to mean. Having your
> own quirky API for a standard function is generally not a good thing,
> especially when the quirky API has no advantages whatsoever.

I think we'll just have to agree to disagree about whether Python's
usage of the terms "connection" and "cursor" are correct.

> Yes, this is indeed a problem with DB-API - you have to keep *two*
> objects around all the time (the connection and the cursor) and pass
> them to functions, etc, when in any sensible system as used by all
> other languages you would only need to pass the connection.

Either a function needs the connection and will create its own cursor
if necessary, or a function just works with the result of a query and
only needs the cursor. The only reason I can think of to pass both is
if the function is going to make additional queries based on a result
set, but in that case you would still need to clearly separate the
connection from the input data, one way or another. Regardless, I
can't recall that I've ever found this burdensome.

[Jon Ribbens]

On 2017-05-31, Ian Kelly <ian.g...@gmail.com> wrote:
> On Wed, May 31, 2017 at 4:26 AM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>> Baloney yourself - I didn't say it was a *new* connection. In DB-API,
>> a Connection is basically nothing - you can do nothing at all with it.
>> A Cursor is actually what any other language would call a connection.
>> If you say that this description is confusing connections and cursors,
>> then you have understood my point - that's exactly what DB-API is doing.
>
> I don't understand what you mean by "connection", then. You seem to be
> defining it as "something that executes queries".

That's what everyone else in the world except Python DB-API appears to
define it to mean, yes.

> I would instead define it as "something responsible for managing the
> low-level state of communication with the database server". There's
> no particular reason why that should include the higher-level
> concept of managing query execution. APIs that do this are just
> conflating concepts in a different way.

A "different" way that is much better and doesn't involve redefining
core SQL concepts to mean things that nobody else uses them to mean.

> I tend to think of DB-API cursors as being more analogous with DBI
> (Perl) or ODBC statement handles.

They're not, they're analogous with the DBI connection and DBI
statement handle being merged into one concept, with the DB-API
"connection" having no analogy as it serves no purpose and is not
used by any other database API.

[Chris Angelico]

So technically, you win :)

ChrisA

[Pavol Lisy]

Thanks, I missed that.

BTW these optional extensions were added to PEP 15 years ago (
https://github.com/python/peps/commit/cdb77a562cccd2da35bc1e795cf54a8c1916a08d
).

Aren't they de facto standard now?

[Gregory Ewing]

Chris Angelico wrote:
> In the DB API 3.0, what I would like to see is that the connection
> becomes a context manager that gives you a transaction object. With
> that transaction, you can perform queries, and you could create
> cursors from it if you want them, or just use a single in-built
> cursor. But a cursor would relate to a result set, and would have no
> meaning outside of it.

A while back I wrote a dedicated wrapper around the Firebird
API (I wasn't impressed by the DB API, for many of the reasons
being discussed here), and this is almost exactly how I structured
it. Except I didn't really have cursors as an explicit concept at
all -- executing a query just gave you an object that you iterate
over to get the results. It just seemed the most Pythonic way to
do it.

It helped that the Firebird API itself was structured that
way, so all I really had to do is wrap it in the most
straightforward way.

Is there any serious work being done on a DB API 3.0?
If there is, I'd be interested in helping with the design.

--
Greg

[Jon Ribbens]

On 2017-06-01, Gregory Ewing <greg....@canterbury.ac.nz> wrote:
> Is there any serious work being done on a DB API 3.0?
> If there is, I'd be interested in helping with the design.

There are a bunch of existing APIs in other languages that can easily
be copied ;-) The good news is of course that since the DB-API
'Connection' class has so few existing methods, adding new ones while
maintaining backwards-compatibility should be easy.

[Chris Angelico]

On Thu, Jun 1, 2017 at 8:05 PM, Gregory Ewing
<greg....@canterbury.ac.nz> wrote:
> A while back I wrote a dedicated wrapper around the Firebird
> API (I wasn't impressed by the DB API, for many of the reasons
> being discussed here), and this is almost exactly how I structured
> it. Except I didn't really have cursors as an explicit concept at
> all -- executing a query just gave you an object that you iterate
> over to get the results. It just seemed the most Pythonic way to
> do it.

Executing a query gives you some sort of object. That object either
contains all the results, or has the resource handle (or whatever)
needed to fetch more from the back end. That basically makes it a
cursor, so we're not far different after all :)

ChrisA

[Frank Millman]

"Skip Montanaro" wrote in message
news:CANc-5Uz2ruXRWnAX8pJEVQZt...@mail.gmail.com...

> Assuming the underlying database supports transactions, is there any
difference between calling the commit() method on the connection and
calling the execute method on the cursor with the "commit transaction"
statement? It seems a bit asymmetric to me to start a transaction with

> cur.execute("begin transaction")

> but end it with

> conn.commit()

Yes there is a difference, at least as far as the combination of PostgreSQL
and psycopg2 is concerned. I will use 'PSQL' in the following, to save me
some typing.

A while ago I had to delve into PSQL locking, as I had a problem with locks
not being cleared. I learned that, for a simple SELECT statement, PSQL
checks to see if it is in a transaction. If not, it does not set any locks,
but if it is, it creates a lock which is cleared on the next
COMMIT/ROLLBACK.

By default, psycopg2 uses 'autocommit', which means that even a SELECT is
preceded by a 'BEGIN' statement internally. I never changed the default, so
all of the following assumes that autocommit is on.

I had many SELECT's, but I was not issuing any form of commit, so the locks
built up. I solved my problem by always committing. However in my
experimenting I found something curious.

I had one window open on a python session, where I could execute commands,
and another on a psql session, where I could monitor the 'lock' table.

I found that, if I issued a SELECT, a lock was created, if I called
conn.commit(), the lock was cleared. I could repeat this sequence and the
pattern was consistent.

However, if I issued a SELECT and called cur.execute('commit'), the lock was
cleared, but the next SELECT did *not* create a lock.

I worked out a possible reason for this, which I have not proved it by
examining the source code of psycopg2, but is internally consistent. The
theory goes like this -

psycopg2 is in one of two states - a transaction is active, or it is not
active. If you execute any command, and a transaction is not active, it
starts a transaction first. If you call conn.commit() or conn.rollback(), it
sends the command to the database and resets its state. However, (and this
is the theory,) if you call cur.execute('commit'), it sends the command to
the database, but does not reset its state. So when you execute the next
command, it thinks the transaction is still active, so it does not start a
new transaction. PSQL, on the other hand, knows that the previous
transaction has been committed, so if the next command is a SELECT, it does
not create a lock.

As I said, I cannot prove this, but the theory fits the observed behaviour
perfectly, so I have proceeded on the assumption that it is true. Therefore
I now always run every SQL command or block of commands within a context
manager, which always calls conn.commit() or conn.rollback() on exit, and I
have not had any more problems. I use exactly the same code for sqlite3 and
for Sql Server/pyodbc, and it has not caused any problems there either.

Frank Millman

[Frank Millman]

"Frank Millman" wrote in message news:ogr3ff$sg1$1...@blaine.gmane.org...

> By default, psycopg2 uses 'autocommit', which means that even a SELECT is
preceded by a 'BEGIN' statement internally. I never changed the default, so
all of the following assumes that autocommit is on.

Oops - by default it does *not* use autocommit, so the following assumes
that it is off.

Frank

[Chris Angelico]

On Fri, Jun 2, 2017 at 5:18 PM, Frank Millman <fr...@chagford.com> wrote:
> As I said, I cannot prove this, but the theory fits the observed behaviour
> perfectly, so I have proceeded on the assumption that it is true. Therefore
> I now always run every SQL command or block of commands within a context
> manager, which always calls conn.commit() or conn.rollback() on exit, and I
> have not had any more problems. I use exactly the same code for sqlite3 and
> for Sql Server/pyodbc, and it has not caused any problems there either.

+1.

A bit more info: When you perform read-only queries against a
PostgreSQL database, you still have transactional integrity, just as
you would with mutating transactions. Two SELECT statements in the
same transaction will see a consistent view of the underlying
database. To accomplish this, the database creates low-grade locks, so
it knows which things you're using. (It's not quite that simple, since
Postgres uses MVCC, but broadly speaking it's so.) Thus transactions
are just as important for SELECT statements as they are for INSERT or
UPDATE... or, for that matter, ALTER TABLE (this is a point on which
not all DBMSes agree - transactional DDL is one of the features I love
about Postgres). Always using a context manager is good practice and
great for code clarity. I would be inclined to mandate it in a style
guide, if I were in charge of any good-sized psycopg2-based project.

ChrisA

[Gregory Ewing]

Chris Angelico wrote:
> Executing a query gives you some sort of object. That object either
> contains all the results, or has the resource handle (or whatever)
> needed to fetch more from the back end. That basically makes it a
> cursor, so we're not far different after all :)

The point is that my API doesn't make a big deal out of them.
You don't typically think about them, just as you don't usually
think much about the intermediate iterator created when you
do "for x in some_list".

--
Greg

[Gregory Ewing]

Frank Millman wrote:
> I never changed the
> default, so all of the following assumes that autocommit is on.
>
> I had many SELECT's, but I was not issuing any form of commit, so the
> locks built up. I solved my problem by always committing.

Something is screwy when a feature called "autocommit" results in
you having to issue explicit commits.

--
Greg

[Chris Angelico]

Which is a fully reasonable way to do things. And actually, on further
consideration, I think it's probably the better way; the number of
times you would want to explicitly create a cursor are so few that
they can be handled by some sort of unusual method call, and the basic
usage should look something like this:

with psycopg2.connect(...) as conn:
with conn.trans() as trn:
for row in trn.execute("select ..."):
print(row)

The outer context manager is optional, but not the inner one and the
method call, as I'm not a fan of the unusual usage where "with conn:"
creates a transaction - it's different from the way most context
managers work (managing the resource represented by the object itself,
not some additional resource allocated on __enter__). The iterator
used on trn.execute would be a cursor such as you describe.

ChrisA

[Jon Ribbens]

Indeed, the DB-API spec says that auto-commmit must be initially off.
This led to an extremely irritating situation whereby Python-MySQLdb
changed incompatibly between versions, it used to have auto-commit on
but was changed to bring it in line with the spec - and they didn't
even add any way of achieving the old backwards-compatible behaviour!

(You can call Connection.autocommit() but this has to happen after the
connection has already been established, and results in every new
connection starting with two completely pointless "SET autocommit 0"
"SET autocommit 1" commands.)

[Neil Cerutti]

On 2017-06-02, Frank Millman <fr...@chagford.com> wrote:

> As I said, I cannot prove this, but the theory fits the
> observed behaviour perfectly, so I have proceeded on the
> assumption that it is true. Therefore I now always run every
> SQL command or block of commands within a context manager,
> which always calls conn.commit() or conn.rollback() on exit,
> and I have not had any more problems. I use exactly the same
> code for sqlite3 and for Sql Server/pyodbc, and it has not
> caused any problems there either.

You're probably not expected to interleave transaction control
commands from different levels of abstraction, e.g., only call
'commit' directly if you called 'begin' directly.

--
Neil Cerutti

[Jon Ribbens]

On 2017-06-02, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> Connector/Python (MySQL) [guess it is time for me to finally upgrade to
> Python 3.x -- it was the delay in getting mysqldb ported that held me back]
> does allow for turning on autocommit -- which is documented as issuing an
> implicit commit after each SQL (which I take to mean each .execute() ), and
> would likely cause problems with explicit BEGIN. Also not recommended for
> InnoDB tables, but considered appropriate for MyISAM tables [no transaction
> feature on those].

Bewaare - MyISAM tables have no transactions for DML but they do have
transactions for DDL. Insane but true.

[Neil Cerutti]

On 2017-06-02, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
>

> A bit of a long free-association rambling...
>
> On Fri, 2 Jun 2017 12:07:45 +0000 (UTC), Neil Cerutti
> <ne...@norwich.edu> declaimed the following:

>>You're probably not expected to interleave transaction control
>>commands from different levels of abstraction, e.g., only call
>>'commit' directly if you called 'begin' directly.
>

> .execute("begin")
> is likely not safe either.
>
> If the adapter has been set to "autocommit", it might issue an
> implicit "commit" after processing that execute -- wiping out
> the transaction one has explicitly started...
>
> If not in "autocommit", the adapter may (will) at some point
> issue an implicit "begin" -- resulting in an attempt to nest
> transactions within the one connection.
>
> My conclusion:
> If using a DB-API compliant adapter, explicitly issuing "begin" and
> "commit" via .execute() should be avoided if one expects to be portable
> (change the adapter from one DBMS to another).
> Learn the behavior of the adapter (does any SQL start a transaction, or
> only INSERT/UPDATE/DELETE/REPLACE -- the latter seems to be the
> current SQLite3 documented behavior, exclusive of both editions
> of the "Definitive Guide" which imply that an active
> transaction will be commited upon executing a SELECT [Python
> help file for module states that SELECT does /not/ commit]) so
> you understand when it should be IN or OUT of a transaction
> state. *

Good point!

> * Mixing various SQLite3 documentation (both the engine and Python's
> module) gives a confusing mix:
> The engine (per "Definite Guide") normally runs in autocommit -- and
> appears to only go into non-autocommit when a "begin" is issued.
> The module (per DB-API) runs in non-autocommit -- and issues an
> implicit "begin" on the first of those DML operations mentioned above.
> So... SELECT prior to any of the listed operations is effectively
> auto-commit, as are any DDL operations (with the addition that DDL will
> perform a commit IF the module believes a transaction is open).

You configure the BEGIN operation by setting isolation_level.
Setting it to IMMEDIATE (or EXCLUSIVE) avoids the deferral of
lock acquisition.

> Given the two -- turning on autocommit in the module may result
> in no implicit "begin"; and transaction control is totally up
> to the user .execute("begin|commit").

Agreed.

> But this behavior may not match up with /other/ adapters, in
> which turning ON autocommit in the adapter could just mean it
> does a sequence of begin/SQL/commit for every .execute(). (per
> documentation, not experience)

sqlite3 behavior in autocommit matches up except when I
explicitly muck things up with an explicit BEGIN.

Conclusion seems to be that sqlite3 has a mode that permits
explicit BEGIN/COMMIT, but you shouldn't do it *except* in that
mode, and it's not portable.

--
Neil Cerutti

[Chris Angelico]

Not insane; not all DBMSes have transactional DDL, and of the major
ones, several have only more recently added it (despite having had
rock-solid transactional DML for decades). It's an excellent feature
but not easy to implement. Personally, I like it enough that I choose
a DBMS based on features like that, but there are plenty of people who
aren't too bothered by it.

That said, though, MySQL is AFAIK the only full-scale DBMS that
doesn't support DDL rollback in 2017. I don't know whether you can
craft a transaction that mixes DDL and DML in all of them, but
certainly in most; and it's a great feature, because you can version
your schema trivially (putting a version number in a metadata table).
I love it. :)

ChrisA

[Skip Montanaro]

On Fri, Jun 2, 2017 at 11:14 AM, Dennis Lee Bieber <wlf...@ix.netcom.com>
wrote:

> My conclusion:
> If using a DB-API compliant adapter, explicitly issuing "begin" and
> "commit" via .execute() should be avoided if one expects to be portable
> (change the adapter from one DBMS to another).
> Learn the behavior of the adapter (does any SQL start a
> transaction, or
> only INSERT/UPDATE/DELETE/REPLACE -- the latter seems to be the current
> SQLite3 documented behavior, exclusive of both editions of the "Definitive
> Guide" which imply that an active transaction will be commited upon
> executing a SELECT [Python help file for module states that SELECT does
> /not/ commit]) so you understand when it should be IN or OUT of a
> transaction state. *
>

I just checked, and the sqlite3 adapter I have access to (Python 2.7.13 in
a Conda env, module version 2.6.0, SQLite3 3.13.0) has no autocommit
attribute at all. I checked at the module, connection and cursor levels.
I'm using pyodbc via another layer added by others at work to connect to
SQL Server. That extra layer explicitly sets autocommit to True on the
underlying Connection object before returning it to the caller.

In my case, my code isn't terribly large. I think it's safer to set
autocommit to False and be explicit in my use of transactions.

Skip

[Jon Ribbens]

On 2017-06-02, Chris Angelico <ros...@gmail.com> wrote:
> On Sat, Jun 3, 2017 at 2:45 AM, Jon Ribbens <jon+u...@unequivocal.eu> wrote:
>> Bewaare - MyISAM tables have no transactions for DML but they do have
>> transactions for DDL. Insane but true.
>
> Not insane; not all DBMSes have transactional DDL, and of the major
> ones, several have only more recently added it (despite having had
> rock-solid transactional DML for decades). It's an excellent feature
> but not easy to implement.

I'm not saying that transactional DDL is insane (it isn't), but MyISAM
tables having transactions *only* for DDL is... surprising. Especially
when it suddenly appeared as a "feature" in between two versions. It
took me quite a while to work out why our database was randomly hanging.

[Neil Cerutti]

On 2017-06-02, Skip Montanaro <skip.mo...@gmail.com> wrote:
> On Fri, Jun 2, 2017 at 11:14 AM, Dennis Lee Bieber
> <wlf...@ix.netcom.com> wrote:
> I just checked, and the sqlite3 adapter I have access to
> (Python 2.7.13 in a Conda env, module version 2.6.0, SQLite3
> 3.13.0) has no autocommit attribute at all. I checked at the
> module, connection and cursor levels.

You get autocommit with sqlite3 by setting isolation_level=None
on the connection object.

https://docs.python.org/2/library/sqlite3.html#sqlite3-controlling-transactions

--
Neil Cerutti

[Skip Montanaro]

On Fri, Jun 2, 2017 at 2:40 PM, Neil Cerutti <ne...@norwich.edu> wrote:

> You get autocommit with sqlite3 by setting isolation_level=None
> on the connection object.
>

Thanks for the pointer. I'd probably never have noticed the correspondence.

Skip

[Chris Angelico]

Wait, you have transactions with MyISAM now? I thought MySQL supported
transactions with InnoDB but not MyISAM, and the reason you didn't get
transactional DDL was that the system catalog tables are mandatorily
MyISAM, even if all your own tables are InnoDB.

ChrisA

[Chris Angelico]

On Sat, Jun 3, 2017 at 7:29 AM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> On Sat, 3 Jun 2017 06:48:28 +1000, Chris Angelico <ros...@gmail.com>
> declaimed the following:

>
>>
>>Wait, you have transactions with MyISAM now? I thought MySQL supported
>>transactions with InnoDB but not MyISAM, and the reason you didn't get
>>transactional DDL was that the system catalog tables are mandatorily
>>MyISAM, even if all your own tables are InnoDB.
>>
>

> Not really transactions -- but locks on the "metadata" tables...
>
> http://www.chriscalender.com/tag/myisam-locks/

Oh. That's just the basic protection of "don't let anyone change the
table while we're using it". It doesn't mean you can roll back an
ALTER TABLE, much less take advantage of full transactional integrity.
In PostgreSQL, you can do something like this (pseudocode):

version = #select schema_version from metadata#
if version < 1:
#create table foo (id serial primary key, bar text not null)#
if version < 2:
#alter table foo add quux integer not null default 10#
if version < 3:
#create table spam (id serial primary key, foo_id int not null
references foo)#

#update metadata set schema_version = 3#
if version > 3:
raise IntegrityError("Cannot backlevel database")
#commit#

Now, even if anything crashes out while you're migrating the database
(either because the power fails, or because of an error in your code,
or anything), you have an absolute guarantee that the version field
and the database will be consistent - that version 2 *always* has both
bar and quux columns, etc. There's no way to have half a schema
migration done, or finish the migration but fail to update the version
marker, or anything. You KNOW that it's safe, even against logic
errors.

That's what transactional DDL gives you.

ChrisA

[Gregory Ewing]

Chris Angelico wrote:
> Always using a context manager is good practice and
> great for code clarity.

Another thing about my Firebird interface was that you were
forced to always use transactions, because the transaction
object was the only thing that had methods for executing
statements.

--
Greg

[Gregory Ewing]

Chris Angelico wrote:
> with psycopg2.connect(...) as conn:
> with conn.trans() as trn:
> for row in trn.execute("select ..."):
> print(row)
>
> The outer context manager is optional, but not the inner one

While I fully support making the use of transactions mandatory,
I wouldn't like to be forced to use them in a with statement.

In the application that I originally built my Firebird interface
for, I had a mechanism where a user could open up a piece of
data for editing, and then choose to save or cancel the edits.
I implemented it by keeping a transaction around for the
duration and then committing it or rolling it back. If a
with statement were required around all transactions, I
wouldn't have been able to do that.

--
Greg

[Chris Angelico]

You wouldn't be FORCED to, but it would be strongly recommended. You
could simply:

trn = conn.trans()

and then use it that way, but then you're responsible for calling
trn.commit() or trn.rollback(). You would also be responsible for the
longevity of your locks; if you hold a transaction waiting for a
human, you potentially keep some things locked for a long time. Which
is probably intentional as regards the primary record being edited,
but you'd also hold locks on anything else you touch too.

BTW, it should be possible to do:

with trn.trans() as subtrn:

on DBMSes that support subtransactions (eg PostgreSQL). For what that's worth.

ChrisA

[Jon Ribbens]

It's not so much as properly transactional as it is "the database
wedges if a transaction is still open even though MyISAM tables
aren't supposed to have transactions in the first place".

The combination of "Python-MYSQLdb suddenly turned off autocommit"
and "MySQL suddenly made transactions relevant to MyISAM" caused me
a certain amount of difficulties a while back.