Also, attached is a test script based on our unit tests which illustrates the feature working as expected - can you run this on a test database on your end, and if it passes, try to modify the trigger/table def so that it reproduces your output ? the test creates/drops two tables and a trigger. thanks.
On Apr 2, 2012, at 6:14 PM, Michael Bayer wrote:
> What SQL are you seeing, do you see "; select scope_identity()" at the end of
> the INSERT statement ? that's how that works. Also software versions ?
> (SQLA, freetds, pyodbc).
>
> On Apr 2, 2012, at 5:27 PM, Derek Litz wrote:
>
>> Hello,
>>
>> First time using sqlalchemy against a table with triggers and it doesn't
>> seem usable. Can anyone confirm or deny my situation as a bug, or enlighten
>> me to my user error?
>>
>> First I ran into this issue:
>>
>> ERROR:root:Database Error
>> Traceback (most recent call last):
>> File my_code...
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 645, in commit
>> self.transaction.commit()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 313, in commit
>> self._prepare_impl()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 297, in _prepare_impl
>> self.session.flush()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 1547, in flush
>> self._flush(objects)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 1616, in _flush
>> flush_context.execute()
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
>> 328, in execute
>> rec.execute(self)
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
>> 472, in execute
>> uow
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
>> line 2220, in _save_obj
>> execute(statement, params)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1399, in execute
>> params)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1532, in _execute_clauseelement
>> compiled_sql, distilled_params
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1640, in _execute_context
>> context)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1633, in _execute_context
>> context)
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line
>> 330, in do_execute
>> cursor.execute(statement, parameters)
>> ProgrammingError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL
>> Server]The target table 'a_table_name' of the DML statement cannot have any
>> enabled triggers if the statement contains an OUTPUT clause without INTO
>> clause. (334) (SQLPrepare)") 'INSERT INTO a_table_name (column_1, column_2,
>> column_3, column_4) OUTPUT inserted.the_id VALUES (?, ?, ?, ?)' ('123000',
>> 2, None, None)
>>
>> I managed to track this down to
>> http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers
>>
>> So, I did that only to find myself with this error, which seems like a bug,
>> rather then a limitation.
>>
>> ERROR:root:Database Error
>> Traceback (most recent call last):
>> File my_code...
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 645, in commit
>> self.transaction.commit()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 313, in commit
>> self._prepare_impl()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 297, in _prepare_impl
>> self.session.flush()
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 1547, in flush
>> self._flush(objects)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> line 1616, in _flush
>> flush_context.execute()
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
>> 328, in execute
>> rec.execute(self)
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line
>> 472, in execute
>> uow
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
>> line 2220, in _save_obj
>> execute(statement, params)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1399, in execute
>> params)
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1532, in _execute_clauseelement
>> compiled_sql, distilled_params
>> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>> line 1652, in _execute_context
>> context.post_exec()
>> File
>> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py",
>> line 195, in post_exec
>> self._lastrowid = int(row[0])
>> TypeError: int() argument must be a string or a number, not 'NoneType'
>>
>> I couldn't figure out any solution to this problem. I did try setting
>> use_scope_identity to False, which not only seemed like a bad idea, but
>> yielded absolutely no effect on the error (I passed this as a keyword
>> argument to create_engine). Any ideas?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msg/sqlalchemy/-/c7eYQzrxbkMJ.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
On Apr 2, 2012, at 6:14 PM, Michael Bayer wrote:
|
from sqlalchemy import create_engine, MetaData, Table, Column, \
Sequence, Integer,String, event, DDL
import unittest
class TestInsWTrigger(unittest.TestCase):
def setUp(self):
self.engine = create_engine("mssql+pyodbc://scott:tiger@ms_2005/", echo=True)
self.meta = meta = MetaData()
self.t1 = Table('t1', meta,
Column('id', Integer, Sequence('fred', 100, 1),
primary_key=True),
Column('descr', String(200)),
implicit_returning = False
)
self.t2 = Table('t2', meta,
Column('id', Integer, Sequence('fred', 200, 1),
primary_key=True),
Column('descr', String(200)))
event.listen(meta, "after_create",
DDL("create trigger paj on t1 for insert as "
"insert into t2 (descr) select descr from inserted")
)
event.listen(meta, "before_drop",
DDL("drop trigger paj")
)
meta.create_all(self.engine)
def tearDown(self):
self.meta.drop_all(self.engine)
def test_ins(self):
with self.engine.begin() as con:
r = con.execute(self.t2.insert(), descr='hello')
self.assert_(r.inserted_primary_key == [200])
r = con.execute(self.t1.insert(), descr='there')
self.assert_(r.inserted_primary_key == [100])
self.assert_(
con.execute(self.t2.select()).fetchall() ==
[(200, 'hello'), (201, 'there')]
)
self.assert_(
con.execute(self.t1.select()).fetchall() ==
[(100, 'there')]
)
if __name__ == '__main__':
unittest.main()
