mike bayer <mike...@zzzcomputing.com> added the comment:

> Under your proposal, the first line would be changed to say 
> "autocommit=True", even though not all the code below is in autocommit mode 
> (according to the SQLite engine's definition). What's more, I could insert 
> this line of code between statements 3 and 6:

  >  print("Autocommit mode?", conn.autocommit)

As Marc-Andre indicated, this is in fact how "autocommit" behaves on other 
drivers, including:

    psycopg2 - uses either connection.autocommit, or 
extensions.ISOLATION_LEVEL_AUTOCOMMIT
    pymysql - uses conn.autocommit(True|False)
    mysqldb - uses conn.autocommit(True|False)
    cx_Oracle - uses conn.autocommit
    pyodbc - uses conn.autocommit

With all of the above drivers, one can emit "BEGIN" and "COMMIT" using
connection.execute(), and within the scope of that BEGIN/COMMIT pair, the 
database "engine" itself is in a transaction.  The "driver" however remains in 
"autocommit" mode.   This mode specifically means the driver is not getting
involved in starting and stopping transactions. 

As Marc mentions, we're not supposed to be emitting "BEGIN" and "COMMIT" on 
the driver, but none of them get in the way of us doing so, and additionally
most databases support additional options for the "BEGIN/START TRANSACTION" 
phase
that aren't available in the DBAPIs so sometimes we don't have much choice at 
least for the "BEGIN" command.

Here's an example using psycopg2, where the timestamp now() will freeze
when we're in a transaction started using manual "BEGIN"/ "COMMIT", while 
.autocommit stays True, and otherwise match statement execution time if we're 
not:

    >>> import psycopg2
    >>> conn = psycopg2.connect(user="scott", password="tiger", 
host="localhost", database="test")
    >>> conn.autocommit = True
    >>> cursor = conn.cursor()
    >>> cursor.execute("SELECT 1")
    >>> cursor.execute("select now() = statement_timestamp()")
    >>> cursor.fetchall()
    [(True,)]
    >>> cursor.execute("BEGIN")
    >>> cursor.execute("select now() = statement_timestamp();")
    >>> cursor.fetchall()
    [(False,)]  # we're in a transaction
    >>> conn.autocommit   # still in driver-level autocommit
    True
    >>> cursor.execute("COMMIT")
    >>> cursor.execute("select now() = statement_timestamp();")
    >>> cursor.fetchall()
    [(True,)]

For SQLAlchemy we already support pysqlite's "isolation_level=None" to 
implement "autocommit" so this issue does not affect us much, but the meaning 
of the term "autocommit" at the driver level shouldn't be controversial at this 
point as there's a lot of precedent.   "connection.autocommit" does not refer 
to the current transactional state of the database, just the current preference 
set upon the driver itself.

----------

_______________________________________
Python tracker <rep...@bugs.python.org>
<https://bugs.python.org/issue39457>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

Reply via email to