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
