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 <mike...@zzzcomputing.com> 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.py Thu Aug 18 13:03:30 2011 -0400
>> +++ b/lib/sqlalchemy/sql/compiler.py Thu 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.
>
from sqlalchemy import *
from sqlalchemy.sql.expression import UpdateBase
from sqlalchemy.ext.compiler import compiles
class InsertFromSelect(UpdateBase) :
def __init__(self, table, select) :
self.table = table
self.select = select
@compiles(InsertFromSelect, "mssql")
def visit_insert_from_select(element, compiler, **kw):
compiler._mssql_requires_identity_insert = True
compiler.isinsert = 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
e = create_engine("mssql+pyodbc://scott:tiger@ms_2005/", echo=True)
m = MetaData()
t1 = Table('t1', m,
Column('id', Integer, primary_key=True),
Column('data', String(50))
)
t2 = Table('t2', m,
Column('id', Integer, primary_key=True),
Column('data', String(50))
)
m.drop_all(e)
m.create_all(e)
e.execute(t1.insert(), [{"data":"d%d" % i} for i in xrange(100)])
e.execute(InsertFromSelect(t2, t1.select()))
assert e.execute(t2.select()).fetchall() == e.execute(t1.select()).fetchall()
mssql_user_defined_identity_insert.patch
Description: Binary data
-- 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.
