Add 'native' autocommit support for mysql driverspr/157

Sometimes it might be useful to have 'native' autocommit support
provided by a DB API driver rather than rely on SQLAlchemy autocommit
emulation.

4 files changed, 57 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 0d2c36189..c45f8c3ee 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2387,6 +2387,14 @@ class MySQLDialect(default.DefaultDialect):
 
     def set_isolation_level(self, connection, level):
         level = level.replace('_', ' ')
+
+        # adjust for ConnectionFairy being present
+        if hasattr(connection, 'connection'):
+            connection = connection.connection
+
+        self._set_isolation_level(connection, level)
+
+    def _set_isolation_level(self, connection, level):
         if level not in self._isolation_lookup:
             raise exc.ArgumentError(
                 "Invalid value '%s' for isolation_level. "
diff --git a/lib/sqlalchemy/dialects/mysql/mysqlconnector.py b/lib/sqlalchemy/dialects/mysql/mysqlconnector.py
index 417e1ad6f..4dc803d2c 100644
--- a/lib/sqlalchemy/dialects/mysql/mysqlconnector.py
+++ b/lib/sqlalchemy/dialects/mysql/mysqlconnector.py
@@ -167,4 +167,17 @@ class MySQLDialect_mysqlconnector(MySQLDialect):
     def _compat_fetchone(self, rp, charset=None):
         return rp.fetchone()
 
+    _isolation_lookup = set(['SERIALIZABLE', 'READ UNCOMMITTED',
+                             'READ COMMITTED', 'REPEATABLE READ',
+                             'AUTOCOMMIT'])
+
+    def _set_isolation_level(self, connection, level):
+        if level == 'AUTOCOMMIT':
+            connection.autocommit = True
+        else:
+            connection.autocommit = False
+            super(MySQLDialect_mysqlconnector, self)._set_isolation_level(
+                connection, level)
+
+
 dialect = MySQLDialect_mysqlconnector
diff --git a/lib/sqlalchemy/dialects/mysql/mysqldb.py b/lib/sqlalchemy/dialects/mysql/mysqldb.py
index 929317467..67d2f3d1d 100644
--- a/lib/sqlalchemy/dialects/mysql/mysqldb.py
+++ b/lib/sqlalchemy/dialects/mysql/mysqldb.py
@@ -203,5 +203,17 @@ class MySQLDialect_mysqldb(MySQLDialect):
         else:
             return cset_name()
 
+    _isolation_lookup = set(['SERIALIZABLE', 'READ UNCOMMITTED',
+                             'READ COMMITTED', 'REPEATABLE READ',
+                             'AUTOCOMMIT'])
+
+    def _set_isolation_level(self, connection, level):
+        if level == 'AUTOCOMMIT':
+            connection.autocommit(True)
+        else:
+            connection.autocommit(False)
+            super(MySQLDialect_mysqldb, self)._set_isolation_level(connection,
+                                                                   level)
+
 
 dialect = MySQLDialect_mysqldb
diff --git a/test/dialect/mysql/test_dialect.py b/test/dialect/mysql/test_dialect.py
index e6bad71a4..03f4c494a 100644
--- a/test/dialect/mysql/test_dialect.py
+++ b/test/dialect/mysql/test_dialect.py
@@ -148,3 +148,27 @@ class ExecutionTest(fixtures.TestBase):
     def test_sysdate(self):
         d = testing.db.scalar(func.sysdate())
         assert isinstance(d, datetime.datetime)
+
+    @testing.only_on(['mysql+mysqldb',
+                      'mysql+mysqlconnector',
+                      'mysql+pymysql',
+                      'mysql+cymysql'])
+    def test_autocommit_isolation_level(self):
+        c = testing.db.connect().execution_options(
+            isolation_level='AUTOCOMMIT'
+        )
+        assert c.execute('SELECT @@autocommit;').scalar()
+
+    def test_isolation_level(self):
+        values = {
+            # sqlalchemy -> mysql
+            'READ UNCOMMITTED': 'READ-UNCOMMITTED',
+            'READ COMMITTED': 'READ-COMMITTED',
+            'REPEATABLE READ': 'REPEATABLE-READ',
+            'SERIALIZABLE': 'SERIALIZABLE'
+        }
+        for sa_value, mysql_value in values.items():
+            c = testing.db.connect().execution_options(
+                isolation_level=sa_value
+            )
+            assert c.execute('SELECT @@tx_isolation;').scalar() == mysql_value