From 9ebbf8614a24fbc430365f6e76c9fd04616992fa Mon Sep 17 00:00:00 2001
From: Gord Thompson <gord@gordthompson.com>
Date: Sat, 22 Feb 2020 06:44:05 -0700
Subject: [PATCH] Implement autocommit isolation level for pysqlite

Fixes: #5164
Change-Id: I190b9de552dfed9f2a33babf82e42465ef09c82a
(cherry picked from commit 64e8303debd8064d7d9c01c3300cca5f54c02db1)
---
 .gitignore                                 |  1 +
 doc/build/changelog/unreleased_13/5164.rst |  5 +++++
 lib/sqlalchemy/dialects/sqlite/base.py     | 13 +++++++++++--
 lib/sqlalchemy/dialects/sqlite/pysqlite.py | 20 ++++++++++++++++++++
 test/requirements.py                       |  1 +
 5 files changed, 38 insertions(+), 2 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_13/5164.rst

diff --git a/.gitignore b/.gitignore
index 8ac332355f..6d6f6788d4 100644
--- a/.gitignore
+++ b/.gitignore
@@ -30,3 +30,4 @@ test/test_schema.db
 *test_schema.db
 .idea
 /Pipfile*
+/querytest.db
diff --git a/doc/build/changelog/unreleased_13/5164.rst b/doc/build/changelog/unreleased_13/5164.rst
new file mode 100644
index 0000000000..5883ecb1dd
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5164.rst
@@ -0,0 +1,5 @@
+.. change::
+    :tags: sqlite, usecase
+    :tickets: 5164
+
+    Implemented AUTOCOMMIT isolation level for SQLite when using pysqlite.
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 4c630b7d27..dda2161e2f 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -168,8 +168,8 @@ work when using the pysqlite driver.
 
 .. _sqlite_isolation_level:
 
-Transaction Isolation Level
-----------------------------
+Transaction Isolation Level / Autocommit
+----------------------------------------
 
 SQLite supports "transaction isolation" in a non-standard way, along two
 axes.  One is that of the
@@ -185,6 +185,15 @@ and ``"READ UNCOMMITTED"`` corresponding to a value of 0 and 1, respectively.
 SQLite defaults to ``SERIALIZABLE``, however its behavior is impacted by
 the pysqlite driver's default behavior.
 
+When using the pysqlite driver, the ``"AUTOCOMMIT"`` isolation level is also
+available, which will alter the pysqlite connection using the ``.isolation_level``
+attribute on the DBAPI connection and set it to None for the duration
+of the setting.
+
+.. versionadded:: 1.3.16 added support for SQLite AUTOCOMMIT isolation level
+   when using the pysqlite / sqlite3 SQLite driver.
+
+
 The other axis along which SQLite's transactional locking is impacted is
 via the nature of the ``BEGIN`` statement used.   The three varieties
 are "deferred", "immediate", and "exclusive", as described at
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 4485631ce5..fa702efe80 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -324,6 +324,12 @@ ourselves. This is achieved using two event listeners::
         # emit our own BEGIN
         conn.execute("BEGIN")
 
+.. warning:: When using the above recipe, it is advised to not use the
+   :paramref:`.execution_options.isolation_level` setting on
+   :class:`.Connection` and :func:`.create_engine` with the SQLite driver,
+   as this function necessarily will also alter the ".isolation_level" setting.
+
+
 Above, we intercept a new pysqlite connection and disable any transactional
 integration.   Then, at the point at which SQLAlchemy knows that transaction
 scope is to begin, we emit ``"BEGIN"`` ourselves.
@@ -437,6 +443,20 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
     def _get_server_version_info(self, connection):
         return self.dbapi.sqlite_version_info
 
+    def set_isolation_level(self, connection, level):
+        if hasattr(connection, "connection"):
+            dbapi_connection = connection.connection
+        else:
+            dbapi_connection = connection
+
+        if level == "AUTOCOMMIT":
+            dbapi_connection.isolation_level = None
+        else:
+            dbapi_connection.isolation_level = ""
+            return super(SQLiteDialect_pysqlite, self).set_isolation_level(
+                connection, level
+            )
+
     def create_connect_args(self, url):
         if url.username or url.password or url.host or url.port:
             raise exc.ArgumentError(
diff --git a/test/requirements.py b/test/requirements.py
index a1d8c1956d..7ab2b305fc 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -331,6 +331,7 @@ class DefaultRequirements(SuiteRequirements):
 
         if against(config, "sqlite"):
             default = "SERIALIZABLE"
+            levels.add("AUTOCOMMIT")
         elif against(config, "postgresql"):
             default = "READ COMMITTED"
             levels.add("AUTOCOMMIT")
-- 
2.47.2