On Aug 18, 2011, at 1:06 PM, Massi wrote:
> 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:
It's just checking if theres a RETURNING clause added, which your clause does
not yet support. Add _returning = False to your construct for now.
>
> 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.
>
--
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.
