I got:
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 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
On 18 Ago, 19:08, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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
> > athttp://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.
