Mike Bayer avatar Mike Bayer committed 8fb64f9

- [feature] Added support for the "isolation_level"
parameter to all MySQL dialects. Thanks
to mu_mind for the patch here. [ticket:2394]
- add documentation examples for mysql, postgresql
- pep8ing

Comments (0)

Files changed (6)

     commit or rollback transaction with errors
     on engine.begin().
 
+- mysql
+  - [feature] Added support for the "isolation_level"
+    parameter to all MySQL dialects.  Thanks
+    to mu_mind for the patch here. [ticket:2394]
+
 - oracle
 
-  - Added missing compilation support for 
+  - [bug] Added missing compilation support for 
     LONG [ticket:2401]
 
 0.7.5 (January 28, 2012)

lib/sqlalchemy/dialects/mysql/base.py

 database itself, especially if database reflection features are
 to be used.
 
+Transaction Isolation Level
+---------------------------
+
+:func:`.create_engine` accepts an ``isolation_level`` 
+parameter which results in the command ``SET SESSION 
+TRANSACTION ISOLATION LEVEL <level>`` being invoked for 
+every new connection. Valid values for this parameter are
+``READ COMMITTED``, ``READ UNCOMMITTED``, 
+``REPEATABLE READ``, and ``SERIALIZABLE``::
+
+    engine = create_engine(
+                    "mysql://scott:tiger@localhost/test", 
+                    isolation_level="READ UNCOMMITTED"
+                )
+
+(new in 0.7.6)
+
 Keys
 ----
 
     _backslash_escapes = True
     _server_ansiquotes = False
 
-    def __init__(self, use_ansiquotes=None, **kwargs):
+    def __init__(self, use_ansiquotes=None, isolation_level=None, **kwargs):
         default.DefaultDialect.__init__(self, **kwargs)
+        self.isolation_level = isolation_level
+
+    def on_connect(self):
+        if self.isolation_level is not None:
+            def connect(conn):
+                self.set_isolation_level(conn, self.isolation_level)
+            return connect
+        else:
+            return None
+
+    _isolation_lookup = set(['SERIALIZABLE', 
+                'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ'])
+
+    def set_isolation_level(self, connection, level):
+        level = level.replace('_', ' ')
+        if level not in self._isolation_lookup:
+            raise exc.ArgumentError(
+                "Invalid value '%s' for isolation_level. "
+                "Valid isolation levels for %s are %s" % 
+                (level, self.name, ", ".join(self._isolation_lookup))
+                )
+        cursor = connection.cursor()
+        cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level)
+        cursor.execute("COMMIT")
+        cursor.close()
+
+    def get_isolation_level(self, connection):
+        cursor = connection.cursor()
+        cursor.execute('SELECT @@tx_isolation')
+        val = cursor.fetchone()[0]
+        cursor.close()
+        return val.upper().replace("-", " ")
 
     def do_commit(self, connection):
         """Execute a COMMIT."""

lib/sqlalchemy/dialects/postgresql/base.py

 :func:`.create_engine` accepts an ``isolation_level`` parameter which results
 in the command ``SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
 <level>`` being invoked for every new connection. Valid values for this
-parameter are ``READ_COMMITTED``, ``READ_UNCOMMITTED``, ``REPEATABLE_READ``,
-and ``SERIALIZABLE``.  Note that the psycopg2 dialect does *not* use this
-technique and uses psycopg2-specific APIs (see that dialect for details).
+parameter are ``READ COMMITTED``, ``READ UNCOMMITTED``, ``REPEATABLE READ``,
+and ``SERIALIZABLE``::
+
+    engine = create_engine(
+                    "postgresql+pg8000://scott:tiger@localhost/test", 
+                    isolation_level="READ UNCOMMITTED"
+                )
+
+When using the psycopg2 dialect, a psycopg2-specific method of setting
+transaction isolation level is used, but the API of ``isolation_level``
+remains the same - see :ref:`psycopg2_isolation`.
+
 
 Remote / Cross-Schema Table Introspection
 -----------------------------------------

lib/sqlalchemy/dialects/postgresql/psycopg2.py

 
 The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
 
+.. _psycopg2_isolation:
+
 Transaction Isolation Level
 ---------------------------
 

test/engine/test_transaction.py

             return 'SERIALIZABLE'
         elif testing.against('postgresql'):
             return 'READ COMMITTED'
+        elif testing.against('mysql'):
+            return "REPEATABLE READ"
         else:
             assert False, "default isolation level not known"
 
             return 'READ UNCOMMITTED'
         elif testing.against('postgresql'):
             return 'SERIALIZABLE'
+        elif testing.against('mysql'):
+            return "SERIALIZABLE"
         else:
             assert False, "non default isolation level not known"
 
     def test_engine_param_stays(self):
 
         eng = testing_engine()
-        isolation_level = eng.dialect.get_isolation_level(eng.connect().connection)
+        isolation_level = eng.dialect.get_isolation_level(
+                                eng.connect().connection)
         level = self._non_default_isolation_level()
 
         ne_(isolation_level, level)
 
         eng = testing_engine(options=dict(isolation_level=level))
         eq_(
-            eng.dialect.get_isolation_level(eng.connect().connection),
+            eng.dialect.get_isolation_level(
+                                eng.connect().connection),
             level
         )
 
 
     def test_default_level(self):
         eng = testing_engine(options=dict())
-        isolation_level = eng.dialect.get_isolation_level(eng.connect().connection)
+        isolation_level = eng.dialect.get_isolation_level(
+                                        eng.connect().connection)
         eq_(isolation_level, self._default_isolation_level())
 
     def test_reset_level(self):
         eng = testing_engine(options=dict())
         conn = eng.connect()
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._default_isolation_level())
+        eq_(
+            eng.dialect.get_isolation_level(conn.connection), 
+            self._default_isolation_level()
+        )
 
-        eng.dialect.set_isolation_level(conn.connection, self._non_default_isolation_level())
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._non_default_isolation_level())
+        eng.dialect.set_isolation_level(
+                conn.connection, self._non_default_isolation_level()
+            )
+        eq_(
+            eng.dialect.get_isolation_level(conn.connection), 
+            self._non_default_isolation_level()
+        )
 
         eng.dialect.reset_isolation_level(conn.connection)
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._default_isolation_level())
+        eq_(
+            eng.dialect.get_isolation_level(conn.connection), 
+            self._default_isolation_level()
+        )
 
         conn.close()
 
     def test_reset_level_with_setting(self):
-        eng = testing_engine(options=dict(isolation_level=self._non_default_isolation_level()))
+        eng = testing_engine(options=dict(
+                            isolation_level=
+                                self._non_default_isolation_level()))
         conn = eng.connect()
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._non_default_isolation_level())
-
-        eng.dialect.set_isolation_level(conn.connection, self._default_isolation_level())
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._default_isolation_level())
-
+        eq_(eng.dialect.get_isolation_level(conn.connection),
+            self._non_default_isolation_level())
+        eng.dialect.set_isolation_level(conn.connection,
+                self._default_isolation_level())
+        eq_(eng.dialect.get_isolation_level(conn.connection),
+            self._default_isolation_level())
         eng.dialect.reset_isolation_level(conn.connection)
-        eq_(eng.dialect.get_isolation_level(conn.connection), self._non_default_isolation_level())
-
+        eq_(eng.dialect.get_isolation_level(conn.connection),
+            self._non_default_isolation_level())
         conn.close()
 
     def test_invalid_level(self):
             exc.ArgumentError, 
                 "Invalid value '%s' for isolation_level. "
                 "Valid isolation levels for %s are %s" % 
-                ("FOO", eng.dialect.name, ", ".join(eng.dialect._isolation_lookup)),
+                ("FOO", eng.dialect.name, 
+                ", ".join(eng.dialect._isolation_lookup)),
             eng.connect)
 
     def test_per_connection(self):
         from sqlalchemy.pool import QueuePool
-        eng = testing_engine(options=dict(poolclass=QueuePool, pool_size=2, max_overflow=0))
+        eng = testing_engine(options=dict(
+                                poolclass=QueuePool, 
+                                pool_size=2, max_overflow=0))
 
         c1 = eng.connect()
-        c1 = c1.execution_options(isolation_level=self._non_default_isolation_level())
-
+        c1 = c1.execution_options(
+                    isolation_level=self._non_default_isolation_level()
+                )
         c2 = eng.connect()
-        eq_(eng.dialect.get_isolation_level(c1.connection), self._non_default_isolation_level())
-        eq_(eng.dialect.get_isolation_level(c2.connection), self._default_isolation_level())
-
+        eq_(
+            eng.dialect.get_isolation_level(c1.connection),
+            self._non_default_isolation_level()
+        )
+        eq_(
+            eng.dialect.get_isolation_level(c2.connection),
+            self._default_isolation_level()
+        )
         c1.close()
         c2.close()
         c3 = eng.connect()
-        eq_(eng.dialect.get_isolation_level(c3.connection), self._default_isolation_level())
-
+        eq_(
+            eng.dialect.get_isolation_level(c3.connection),
+            self._default_isolation_level()
+        )
         c4 = eng.connect()
-        eq_(eng.dialect.get_isolation_level(c4.connection), self._default_isolation_level())
+        eq_(
+            eng.dialect.get_isolation_level(c4.connection),
+            self._default_isolation_level()
+        )
 
         c3.close()
         c4.close()
             r"on Connection.execution_options\(\), or "
             r"per-engine using the isolation_level "
             r"argument to create_engine\(\).",
-            select([1]).execution_options, isolation_level=self._non_default_isolation_level()
+            select([1]).execution_options, 
+                    isolation_level=self._non_default_isolation_level()
         )
 
 
             r"To set engine-wide isolation level, "
             r"use the isolation_level argument to create_engine\(\).",
             create_engine,
-            testing.db.url, execution_options={'isolation_level':self._non_default_isolation_level}
+            testing.db.url, 
+                execution_options={'isolation_level':
+                            self._non_default_isolation_level}
         )

test/lib/requires.py

 def isolation_level(fn):
     return _chain_decorators_on(
         fn,
-        only_on(('postgresql', 'sqlite'), "DBAPI has no isolation level support"),
+        only_on(('postgresql', 'sqlite', 'mysql'), "DBAPI has no isolation level support"),
         fails_on('postgresql+pypostgresql',
                       'pypostgresql bombs on multiple isolation level calls')
     )
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.