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