Why doesn't the connection in the Python DB-API have a “start” operation?

Question

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

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

it turned out that the base connection object has the appropriate methods:

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

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

Mysql-python, by the way, throws the DeprecationWarning method when you use this method. sqlite3.connection, for example, has no method at all.

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

Answer 1

I decided to answer myself:

A thread about DB API 2.0 transactions in a python list and the following excerpt from a notable SQL book A complete reference makes me think that the DB API implements the standard SQL1 behavior:

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

Explicit transaction mode (SQL2 and SQL: 1999) seems convenient when RDBSM supports auto-switching mode, and the current connection is in this mode, but the DB API simply does not reflect it.

Answer 2

look at this previously asked question . Typically, a “protocol” for use with transactions:

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

Starting with python 2.6 sqlite Connection objects can be used as context managers that automatically commit or roll back transactions .