Implement AUTOCOMMIT for pyodbc, pymssql

In prep for CI coverage for SQL Server, allow AUTOCOMMIT
isolation level to work

Change-Id: I850b977e75f53385986f2c181be4e4412dd3b3f4

6 files changed, 40 insertions, 1 deletions

diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst
index 14e61a04e..36b2819fa 100644
--- a/doc/build/changelog/migration_12.rst
+++ b/doc/build/changelog/migration_12.rst
@@ -1406,3 +1406,10 @@ as the "owner".
     :ref:`multipart_schema_names`
 
 :ticket:`2626`
+
+AUTOCOMMIT isolation level support
+----------------------------------
+
+Both the PyODBC and pymssql dialects now support the "AUTOCOMMIT" isolation
+level as set by :meth:`.Connection.execution_options` which will establish
+the correct flags on the DBAPI connection object.
diff --git a/doc/build/changelog/unreleased_12/sql_server_isolation.rst b/doc/build/changelog/unreleased_12/sql_server_isolation.rst
new file mode 100644
index 000000000..47f483183
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/sql_server_isolation.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: feature, mssql
+
+    Added support for "AUTOCOMMIT" isolation level, as established
+    via :meth:`.Connection.execution_options`, to the
+    PyODBC and pymssql dialects.   This isolation level sets the
+    appropriate DBAPI-specific flags on the underlying
+    connection object.
\ No newline at end of file
diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index ee8445dae..65fe37212 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -194,3 +194,17 @@ class PyODBCConnector(Connector):
             except ValueError:
                 version.append(n)
         return tuple(version)
+
+    def set_isolation_level(self, connection, level):
+        # adjust for ConnectionFairy being present
+        # allows attribute set e.g. "connection.autocommit = True"
+        # to work properly
+        if hasattr(connection, 'connection'):
+            connection = connection.connection
+
+        if level == 'AUTOCOMMIT':
+            connection.autocommit = True
+        else:
+            connection.autocommit = False
+            super(PyODBCConnector, self).set_isolation_level(connection,
+                                                             level)
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 00c43bba6..05381d671 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -250,6 +250,7 @@ To set using per-connection execution options::
 
 Valid values for ``isolation_level`` include:
 
+* ``AUTOCOMMIT`` - pyodbc / pymssql-specific
 * ``READ COMMITTED``
 * ``READ UNCOMMITTED``
 * ``REPEATABLE READ``
@@ -259,6 +260,7 @@ Valid values for ``isolation_level`` include:
 .. versionadded:: 1.1 support for isolation level setting on Microsoft
    SQL Server.
 
+.. versionadded:: 1.2 added AUTOCOMMIT isolation level setting
 
 Nullability
 -----------
diff --git a/lib/sqlalchemy/dialects/mssql/pymssql.py b/lib/sqlalchemy/dialects/mssql/pymssql.py
index 57ca8ab3d..cd800471d 100644
--- a/lib/sqlalchemy/dialects/mssql/pymssql.py
+++ b/lib/sqlalchemy/dialects/mssql/pymssql.py
@@ -94,4 +94,12 @@ class MSDialect_pymssql(MSDialect):
         else:
             return False
 
+    def set_isolation_level(self, connection, level):
+        if level == 'AUTOCOMMIT':
+            connection.autocommit(True)
+        else:
+            connection.autocommit(False)
+            super(MSDialect_pymssql, self).set_isolation_level(connection,
+                                                                level)
+
 dialect = MSDialect_pymssql
diff --git a/test/requirements.py b/test/requirements.py
index 4fcf541e3..4f01eac9b 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -268,7 +268,7 @@ class DefaultRequirements(SuiteRequirements):
     def autocommit(self):
         """target dialect supports 'AUTOCOMMIT' as an isolation_level"""
         return only_on(
-            ('postgresql', 'mysql'),
+            ('postgresql', 'mysql', 'mssql+pyodbc', 'mssql+pymssql'),
             "dialect does not support AUTOCOMMIT isolation mode")
 
     @property