From 4cb74452fe551c3d4f0dd305bee1e69dbdccd99a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 5 Apr 2012 14:31:28 -0400 Subject: [PATCH] - [bug] Repaired the use_scope_identity create_engine() flag when using the pyodbc dialect. Previously this flag would be ignored if set to False. When set to False, you'll get "SELECT @@identity" after each INSERT to get at the last inserted ID, for those tables which have "implicit_returning" set to False. --- CHANGES | 9 +++++++++ lib/sqlalchemy/dialects/mssql/base.py | 25 +++++++++++++++---------- lib/sqlalchemy/dialects/mssql/pyodbc.py | 3 ++- test/dialect/test_mssql.py | 24 +++++++++++++++++++++++- 4 files changed, 49 insertions(+), 12 deletions(-) diff --git a/CHANGES b/CHANGES index 88ddad8f8f..c53822455c 100644 --- a/CHANGES +++ b/CHANGES @@ -42,6 +42,15 @@ CHANGES passes Python unicode literals to PyODBC or not. + - [bug] Repaired the use_scope_identity + create_engine() flag when using the pyodbc + dialect. Previously this flag would be + ignored if set to False. When set to False, + you'll get "SELECT @@identity" after each + INSERT to get at the last inserted ID, + for those tables which have "implicit_returning" + set to False. + - postgresql - [feature] Added new for_update/with_lockmode() options for Postgresql: for_update="read"/ diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index e587215863..78da18711a 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -146,7 +146,7 @@ Enabling Snapshot Isolation Not necessarily specific to SQLAlchemy, SQL Server has a default transaction isolation mode that locks entire tables, and causes even mildly concurrent -applications to have long held locks and frequent deadlocks. +applications to have long held locks and frequent deadlocks. Enabling snapshot isolation for the database as a whole is recommended for modern levels of concurrency support. This is accomplished via the following ALTER DATABASE commands executed at the SQL prompt:: @@ -168,7 +168,7 @@ deprecated and will be removed in 0.8 - the ``s.in_()``/``~s.in_()`` operators should be used when IN/NOT IN are desired. For the time being, the existing behavior prevents a comparison -between scalar select and another value that actually wants to use ``=``. +between scalar select and another value that actually wants to use ``=``. To remove this behavior in a forwards-compatible way, apply this compilation rule by placing the following code at the module import level:: @@ -176,7 +176,7 @@ level:: from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import _BinaryExpression from sqlalchemy.sql.compiler import SQLCompiler - + @compiles(_BinaryExpression, 'mssql') def override_legacy_binary(element, compiler, **kw): return SQLCompiler.visit_binary(compiler, element, **kw) @@ -689,18 +689,22 @@ class MSExecutionContext(default.DefaultExecutionContext): not self.executemany if self._enable_identity_insert: - self.cursor.execute("SET IDENTITY_INSERT %s ON" % - self.dialect.identifier_preparer.format_table(tbl)) + self.root_connection._cursor_execute(self.cursor, + "SET IDENTITY_INSERT %s ON" % + self.dialect.identifier_preparer.format_table(tbl), + ()) def post_exec(self): """Disable IDENTITY_INSERT if enabled.""" + conn = self.root_connection if self._select_lastrowid: if self.dialect.use_scope_identity: - self.cursor.execute( - "SELECT scope_identity() AS lastrowid", ()) + conn._cursor_execute(self.cursor, + "SELECT scope_identity() AS lastrowid", ()) else: - self.cursor.execute("SELECT @@identity AS lastrowid", ()) + conn._cursor_execute(self.cursor, + "SELECT @@identity AS lastrowid", ()) # fetchall() ensures the cursor is consumed without closing it row = self.cursor.fetchall()[0] self._lastrowid = int(row[0]) @@ -710,10 +714,11 @@ class MSExecutionContext(default.DefaultExecutionContext): self._result_proxy = base.FullyBufferedResultProxy(self) if self._enable_identity_insert: - self.cursor.execute( + conn._cursor_execute(self.cursor, "SET IDENTITY_INSERT %s OFF" % self.dialect.identifier_preparer. - format_table(self.compiled.statement.table) + format_table(self.compiled.statement.table), + () ) def get_lastrowid(self): diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 434cfd43c8..a7cb42aac2 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -242,7 +242,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect): def __init__(self, description_encoding='latin-1', **params): super(MSDialect_pyodbc, self).__init__(**params) self.description_encoding = description_encoding - self.use_scope_identity = self.dbapi and \ + self.use_scope_identity = self.use_scope_identity and \ + self.dbapi and \ hasattr(self.dbapi.Cursor, 'nextset') self._need_decimal_fix = self.dbapi and \ self._dbapi_version() < (2, 1, 8) diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py index dddc6333d3..2b35ff57fb 100644 --- a/test/dialect/test_mssql.py +++ b/test/dialect/test_mssql.py @@ -750,7 +750,8 @@ class QueryUnicodeTest(fixtures.TestBase): finally: meta.drop_all() -class QueryTest(fixtures.TestBase): +from test.lib.assertsql import ExactSQL +class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase): __only_on__ = 'mssql' def test_fetchid_trigger(self): @@ -818,6 +819,27 @@ class QueryTest(fixtures.TestBase): con.execute("""drop trigger paj""") meta.drop_all() + @testing.provide_metadata + def test_disable_scope_identity(self): + engine = engines.testing_engine(options={"use_scope_identity":False}) + metadata = self.metadata + metadata.bind = engine + t1 = Table('t1', metadata, + Column('id', Integer, primary_key=True), + implicit_returning=False + ) + metadata.create_all() + + self.assert_sql_execution( + testing.db, + lambda: engine.execute(t1.insert()), + ExactSQL("INSERT INTO t1 DEFAULT VALUES"), + # we dont have an event for + # "SELECT @@IDENTITY" part here. + # this will be in 0.8 with #2459 + ) + assert not engine.dialect.use_scope_identity + def test_insertid_schema(self): meta = MetaData(testing.db) con = testing.db.connect() -- 2.47.2