tags:

views:

107

answers:

2

Working with cursors in mysql-python I used to call "BEGIN;", "COMMIT;", and "ROLLBACK;" explicitly as follows:

try:
    cursor.execute("BEGIN;")
    # some statements
    cursor.execute("COMMIT;")
except:
    cursor.execute("ROLLBACK;")

then, I found out that the underlying connection object has the corresponding methods:

try:
    cursor.connection.begin()
    # some statements
    cursor.connection.commit()
except:
    cursor.connection.rollback()

Inspecting the DB-API PEP I found out that it does not mention the begin() method for the connection object, even for the extensions.

Mysql-python, by the way, throws the DeprecationWarning, when you use the method. sqlite3.connection, for example, does not have the method at all.

And the question is why there is no such method in the PEP? Is the statement somehow optional, is it enough to invoke commit() instead?

+2  A: 

look a this previously asked question. Generally the "protocol" to use with transactions is:

cursor = conn.cursor()
try:
    cursor.execute(...)
except DatabaseError:
    cursor.rollback()
else:
    cursor.commit()
finally:
    cursor.close()

Starting from python 2.6 sqlite Connection objects can be used as context managers that automatically commit or rollback transactions.

mg
Thank you for the useful link to the neighbour question. This is not the answer for my question but it sheds some light on the possible reasons.
newtover
@newtower: You are right, i was in doubt to answer or just post a comment but doing so i could post that little snip.
mg
By the way, though wrapping the connection with a context manager is usefull, I prefer to reuse the connection in adjacent queries, but cursor classes do not implement the contextmanager interface, even in python 2.6.
newtover
A: 

Decided to answer myself:

A thread about DB API 2.0 transactions in python-list and the following excerpt from the noticeable book SQL The Complete Reference make me think that DB API implements SQL1 standard behaviour:

The first version of the SQL standard (SQL1) defined an implicit transaction mode, based on the transaction support in the early releases of DB2. In implicit mode, only the COMMIT and ROLLBACK statements are supported. A SQL transaction automatically begins with the first SQL statement executed by a user or a program and ends when a COMMIT or ROLLBACK is executed. The end of one transaction implicitly starts a new one.

Explicit transaction mode (the SQL2 and SQL:1999) seems to be handy when the RDBSM supports autocommit mode and the current connection is in that mode, but DB API just does not reflect it.

newtover