Re: [sqlalchemy] Re: InsertFromSelect on SQL server
the attached patch is against r113a7ed95335. Test program is attached,
relevant output using PyODBC + FreeTDS:
CREATE TABLE t2 (
id INTEGER NOT NULL IDENTITY(1,1),
data VARCHAR(50) NULL,
PRIMARY KEY (id)
)
2011-08-19 10:34:42,142 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,143 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,145 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t1 (
id INTEGER NOT NULL IDENTITY(1,1),
data VARCHAR(50) NULL,
PRIMARY KEY (id)
)
2011-08-19 10:34:42,145 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,170 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,173 INFO sqlalchemy.engine.base.Engine INSERT INTO t1
(data) VALUES (?)
2011-08-19 10:34:42,173 INFO sqlalchemy.engine.base.Engine (('d0',), ('d1',),
('d2',), ('d3',), ('d4',), ('d5',), ('d6',), ('d7',), ('d8',), ('d9',),
('d10',), ('d11',), ('d12',), ('d13',), ('d14',), ('d15',), ('d16',), ('d17',),
('d18',), ('d19',), ('d20',), ('d21',), ('d22',), ('d23',), ('d24',), ('d25',),
('d26',), ('d27',), ('d28',), ('d29',), ('d30',), ('d31',), ('d32',), ('d33',),
('d34',), ('d35',), ('d36',), ('d37',), ('d38',), ('d39',), ('d40',), ('d41',),
('d42',), ('d43',), ('d44',), ('d45',), ('d46',), ('d47',), ('d48',), ('d49',),
('d50',), ('d51',), ('d52',), ('d53',), ('d54',), ('d55',), ('d56',), ('d57',),
('d58',), ('d59',), ('d60',), ('d61',), ('d62',), ('d63',), ('d64',), ('d65',),
('d66',), ('d67',), ('d68',), ('d69',), ('d70',), ('d71',), ('d72',), ('d73',),
('d74',), ('d75',), ('d76',), ('d77',), ('d78',), ('d79',), ('d80',), ('d81',),
('d82',), ('d83',), ('d84',), ('d85',), ('d86',), ('d87',), ('d88',), ('d89',),
('d90',), ('d91',), ('d92',), ('d93',), ('d94',), ('d95',), ('d96',), ('d97',),
('d98',), ('d99',))
2011-08-19 10:34:42,247 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,249 INFO sqlalchemy.engine.base.Engine INSERT INTO t2
(t2.id, t2.data) SELECT t1.id, t1.data
FROM t1
2011-08-19 10:34:42,249 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,252 INFO sqlalchemy.engine.base.Engine COMMIT
On Aug 19, 2011, at 4:54 AM, Massi wrote:
> Mike, I tried to apply the new patch but it was rejected (working on
> windows 7 and using patch from GnuWin32); it seems that the target
> lines in you patch are misaligned with those in my SA source files. I
> tried to make the changes by hand, but I could not get it working. I
> still get the 'postfetch' error.
>
> On 18 Ago, 19:34, Michael Bayer wrote:
>> On Aug 18, 2011, at 1:19 PM, Massi wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> 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...'
>>
>> here's another patch that should default all that stuff appropriately:
>>
>> diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
>> --- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011
>> -0400
>> +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 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:
>> diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
>> --- a/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:03:30 2011 -0400
>> +++ b/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:31:16 2011 -0400
>> @@ -198,6 +198,10 @@
>> # driver/DB enforces this
Re: [sqlalchemy] Re: InsertFromSelect on SQL server
On Aug 18, 2011, at 1:19 PM, Massi wrote:
> 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...'
here's another patch that should default all that stuff appropriately:
diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 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:
diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:31:16 2011 -0400
@@ -198,6 +198,10 @@
# driver/DB enforces this
ansi_bind_rules = False
+postfetch = ()
+prefetch = ()
+returning = ()
+
def __init__(self, dialect, statement, column_keys=None,
inline=False, **kwargs):
"""Construct a new ``DefaultCompiler`` object.
diff -r 113a7ed95335 lib/sqlalchemy/sql/expression.py
--- a/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:31:16 2011 -0400
@@ -4792,6 +4792,8 @@
Executable._execution_options.union({'autocommit': True})
kwargs = util.immutabledict()
+_returning = None
+
def _process_colparams(self, parameters):
if isinstance(parameters, (list, tuple)):
pp = {}
--
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.
Re: [sqlalchemy] Re: InsertFromSelect on SQL server
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 noattribute '_returning') 'INSERT INTO...'
>
> On 18 Ago, 18:34, Michael Bayer 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
