I changed the code to (SA 0.7.2 with pyodbc2.1.9):
class InsertFromSelect(Executable, ClauseElement) :
_execution_options =\
Executable._execution_options.union({'autocommit': True})
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, "mssql")
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
tab_name = compiler.process(element.table, asfrom=True)
q = "INSERT INTO %s (%s) %s;" % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
return q
applied the patch and I got the following error:
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1399, in execute
params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1599, in _execute_context
None, None)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py", line 418, in _init_compiled
self._is_explicit_returning = compiled.statement._returning
StatementError: 'InsertFromSelect' object has no attribute
'_returning' (original cause: AttributeError: 'InsertFromSelect'
object has no attribute '_returning') 'INSERT INTO...'
On 18 Ago, 18:34, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Aug 18, 2011, at 12:24 PM, Massi wrote:
>
>
>
>
>
>
>
>
>
> > Hi everyone, I'm trying to implement an InsertFromSelect workaround to
> > handle the identity insert issue of SQL server. This is more or less
> > what I'm doing:
>
> > class InsertFromSelect(Executable, ClauseElement) :
> > def __init__(self, table, select) :
> > self.table = table
> > self.select = select
>
> > @compiler.compiles(InsertFromSelect, "mssql")
> > def visit_insert_from_select(element, compiler, **kw) :
> > tab_name = compiler.process(element.table, asfrom=True)
> > q = "SET IDENTITY_INSERT %s ON;" %(tab_name)
> > q += "INSERT INTO %s (%s) %s;" % (tab_name,
> > ', '.join([compiler.process(c) for c in element.table.c]),
> > compiler.process(element.select))
> > q += "SET IDENTITY_INSERT %s OFF;" %(tab_name)
> > return q
>
> it would be better to use the built in IDENTITY_INSERT facilities of the
> MSSQL dialect itself.
>
> Set the "isinsert" flag on your compiled object, and also set a new flag I
> can add called "_mssql_requires_identity_insert":
>
> @compiler.compiles(InsertFromSelect, "mssql")
> def visit_insert_from_select(element, compiler, **kw) :
> compiler.isinsert = True
> compiler._mssql_requires_identity_insert = True
>
> patch for testing:
>
> diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
> --- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400
> +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011 -0400
> @@ -655,7 +655,9 @@
> seq_column = tbl._autoincrement_column
> insert_has_sequence = seq_column is not None
>
> - if insert_has_sequence:
> + if getattr(self.compiled._mssql_requires_identity_insert, False):
> + self._enable_identity_insert = True
> + elif insert_has_sequence:
> self._enable_identity_insert = \
> seq_column.key in self.compiled_parameters[0]
> else:
>
> if this works for you I can commit it with a short test and it will be in
> 0.7.3.
>
>
>
> > insert_from_select = InsertFromSelect(new_table, old_table))
> > engine.execute(insert_from_select)
>
> easier issue here, your construct also needs "autocommit" enabled if you are
> going to use connectionless execution:
>
> class InsertFromSelect(...):
> _execution_options = \
> Executable._execution_options.union({'autocommit': True})
>
> or better yet just subclass UpdateBase instead of (Executable, ClauseElement).
>
> will add a doc
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
