Good news! I was able to reproduce the exception after modifying your test
script. Attached is my info and the script.
Below is also repeated at the beginning of the two files.
NOTE: You will need to edit connect_to_mssql()
I was able to get the following error using this script.
--------mssql_implicit_ret.py--------------
ERROR: test_ins (__main__.TestInsWTrigger)
----------------------------------------------------------------------
Traceback (most recent call last):
File "mssql_implicit_ret.py", line 56, in test_ins
r = con.execute(self.t1.insert(), descr='there')
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1450, in execute
params)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1583, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1709, in _execute_context
context.post_exec()
File
"/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py",
line 199, in post_exec
self._lastrowid = int(row[0])
TypeError: int() argument must be a string or a number, not 'NoneType'
------------my_info.txt---------------------
Note, I had to update to sqlalchemy version 0.7.6 to run your unit test
because engine did not have the begin method in 0.7.3. Below is my
information prior to that single update. Nothing else needed to be updated.
There are actually two potential bugs I'm seeing
1.) File
"/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py",
line 199, in post_exec
self._lastrowid = int(row[0])
2.) use_scope_identity=False appears to do nothing.
Thanks,
Derek
On Monday, April 2, 2012 5:37:35 PM UTC-5, Michael Bayer wrote:
>
> 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.
>
>
--
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/-/h9aqQCA-8WwJ.
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.
Note, I had to update to sqlalchemy version 0.7.6 to run your unit test because
engine did not have the begin method in 0.7.3. Below is my information prior
to that single update. Nothing else needed to be updated.
There are actually two potential bugs I'm seeing
1.) File
"/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py",
line 199, in post_exec
self._lastrowid = int(row[0])
2.) use_scope_identity=False appears to do nothing.
---------INFO---------
Here is the query I'm seeing.
INFO:sqlalchemy.engine.base.Engine:INSERT INTO t1 (v1, v2, v3, v4) VALUES (?,
?, ?, ?); select scope_identity()
The query is identical even while passing use_scope_identity=False to
create_engine.
Python 2.7.2 (default, Oct 28 2011, 14:19:36)
[GCC 4.6.1] on linux3
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.7.3'
>>>
$pip freeze
...
pyodbc==2.1.11
...
My connection string contains the following.
DRIVER={FreeTDS}
TDS_Version=8.0
/etc/odbcinst.ini has the following.
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout = 60
CPReuse = 60
I'm using packages provided for Ubuntu 11.10 information below from commandline.
apt-cache showpkg unixodbc unixodbc-dev tdsodbc
Package: unixodbc
Versions:
2.2.14p2-2ubuntu1
(/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages)
(/var/lib/dpkg/status)
Description Language: en
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_i18n_Translation-en
MD5: 9159fbc3e0e2f0a80f88f9105f827a83
Description Language:
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages
MD5: 9159fbc3e0e2f0a80f88f9105f827a83
Reverse Depends:
unixodbc:i386,unixodbc
bibus,unixodbc
slapd,unixodbc 2.2.11
php5-odbc,unixodbc 2.2.11
libreoffice-base,unixodbc
libreoffice,unixodbc
libqt4-sql-odbc,unixodbc 2.2.11
tdsodbc:i386,unixodbc 2.1.1-2
odbcinst1debian2:i386,unixodbc 2.1.1-2
odbcinst:i386,unixodbc 2.1.1-2
libodbcinstq1c2:i386,unixodbc 2.2.4-1
libodbcinstq1c2:i386,unixodbc 2.2.4-1
unixodbc-bin,unixodbc 2.2.11
tclodbc,unixodbc 2.2.11
swi-prolog-odbc,unixodbc 2.2.11
ruby-odbc,unixodbc
r-cran-rodbc,unixodbc 2.2.11
python-pyodbc-dbg,unixodbc 2.2.11
python-pyodbc,unixodbc 2.2.11
proftpd-mod-odbc,unixodbc 2.2.11
perdition-odbc,unixodbc 2.2.11
odbc-postgresql,unixodbc
libsqliteodbc,unixodbc
libsimpledb2,unixodbc 2.2.11-1
libpt2.10.2,unixodbc 2.2.11
libpocoodbc9-dbg,unixodbc 2.2.11
libpocoodbc9,unixodbc 2.2.11
libmyodbc,unixodbc
libgsmsd7,unixodbc 2.2.11
libgnadeodbc2,unixodbc 2.2.11
libgnadeodbc1-dev,unixodbc 2.2.11-16
libghc-haskelldb-hdbc-odbc-dev,unixodbc 2.2.11
libgdal1-1.7.0,unixodbc 2.2.11
libdbd-odbc-perl,unixodbc 2.2.11
libdballe4,unixodbc 2.2.11
libccscript3-1.1-0,unixodbc 2.2.11-1
libaprutil1-dbd-odbc,unixodbc 2.2.11
libapache2-mod-shib2,unixodbc 2.2.11
gretl,unixodbc 2.2.11
grass,unixodbc 2.2.11
globus-rls-server,unixodbc 2.2.11
gambas2-gb-db-odbc,unixodbc 2.2.11
freetds-bin,unixodbc 2.2.11
cl-sql-tests,unixodbc
bibus,unixodbc
asterisk-voicemail-odbcstorage,unixodbc 2.2.11
asterisk-modules,unixodbc 2.2.11
unixodbc-dev,unixodbc 2.2.14p2-2ubuntu1
tdsodbc,unixodbc 2.1.1-2
tdsodbc,unixodbc
slapd,unixodbc 2.2.11
php5-odbc,unixodbc 2.2.11
odbcinst1debian2,unixodbc 2.1.1-2
odbcinst,unixodbc 2.1.1-2
libreoffice-base,unixodbc
libreoffice,unixodbc
libqt4-sql-odbc,unixodbc 2.2.11
libodbcinstq1c2,unixodbc 2.2.4-1
libodbcinstq1c2,unixodbc 2.2.4-1
libodbcinstq1c2,unixodbc 2.2.11
erlang-odbc,unixodbc 2.2.11
Dependencies:
2.2.14p2-2ubuntu1 - libc6 (2 2.7) libltdl7 (2 2.2.6b) libreadline6 (2 6.0)
odbcinst1debian2 (2 2.2.11-3) libmyodbc (0 (null)) odbc-postgresql (0 (null))
tdsodbc (0 (null)) unixodbc-bin (0 (null)) unixodbc-bin (3 2.2.4-1)
unixodbc-bin:i386 (3 2.2.4-1) unixodbc:i386 (0 (null))
Provides:
2.2.14p2-2ubuntu1 -
Reverse Provides:
Package: unixodbc-dev
Versions:
2.2.14p2-2ubuntu1
(/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages)
(/var/lib/dpkg/status)
Description Language: en
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_i18n_Translation-en
MD5: 810ff6350f7d3725bcea408feaf73610
Description Language:
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages
MD5: 810ff6350f7d3725bcea408feaf73610
Reverse Depends:
unixodbc-dev:i386,unixodbc-dev
libqt4-dev,unixodbc-dev
libpt-dev,unixodbc-dev
libocamlodbc-ocaml-dev,unixodbc-dev
libghc-hsql-odbc-dev,unixodbc-dev
libghc-hdbc-odbc-dev,unixodbc-dev 2.2.11
libgdal1-dev,unixodbc-dev
cl-sql-odbc,unixodbc-dev
libqt4-dev,unixodbc-dev
Dependencies:
2.2.14p2-2ubuntu1 - unixodbc (5 2.2.14p2-2ubuntu1) odbcinst1debian2 (5
2.2.14p2-2ubuntu1) libodbcinstq1c2 (5 2.2.14p2-2ubuntu1) libltdl3-dev (0
(null)) libqt3-mt-dev (0 (null)) libiodbc2-dev (0 (null)) libiodbc2-dev:i386 (0
(null)) remembrance-agent (3 2.11-4) remembrance-agent:i386 (3 2.11-4)
unixodbc-dev:i386 (0 (null))
Provides:
2.2.14p2-2ubuntu1 -
Reverse Provides:
Package: tdsodbc
Versions:
0.82-7
(/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages)
(/var/lib/dpkg/status)
Description Language: en
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_i18n_Translation-en
MD5: 0fecf9617c4dc2509f73a437bea0f895
Description Language:
File:
/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_oneiric_main_binary-amd64_Packages
MD5: 0fecf9617c4dc2509f73a437bea0f895
Reverse Depends:
tdsodbc:i386,tdsodbc
libreoffice-base,tdsodbc
unixodbc,tdsodbc
libreoffice-base,tdsodbc
Dependencies:
0.82-7 - libc6 (2 2.8) odbcinst1debian2 (2 2.2.11-3) debconf (18 0.5)
debconf-2.0 (0 (null)) freetds-common (0 (null)) libiodbc2 (16 (null)) unixodbc
(0 (null)) freetds0 (0 (null)) freetds0:i386 (0 (null)) libct0 (0 (null))
libct0:i386 (0 (null)) unixodbc (3 2.1.1-2) unixodbc:i386 (3 2.1.1-2) freetds0
(0 (null)) freetds0:i386 (0 (null)) libct0 (0 (null)) libct0:i386 (0 (null))
libct1 (0 (null)) libct1:i386 (0 (null)) tdsodbc:i386 (0 (null))
Provides:
0.82-7 -
Reverse Provides:
"""
NOTE: You will need to edit connect_to_mssql()
I was able to get the following error using this script.
ERROR: test_ins (__main__.TestInsWTrigger)
----------------------------------------------------------------------
Traceback (most recent call last):
File "mssql_implicit_ret.py", line 56, in test_ins
r = con.execute(self.t1.insert(), descr='there')
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1450, in execute
params)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1709, in _execute_context
context.post_exec()
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", line 199, in post_exec
self._lastrowid = int(row[0])
TypeError: int() argument must be a string or a number, not 'NoneType'"""
from sqlalchemy import create_engine, MetaData, Table, Column, \
Sequence, Integer,String,DateTime, event, DDL
import unittest
import logging
import pyodbc
logging.basicConfig(filename='alc_test.log', level=logging.DEBUG)
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
def connect_to_mssql():
return pyodbc.connect('DRIVER={FreeTDS};SERVER=your_server;DATABASE=your_database;UID=your_uid;PWD=your_pw;TDS_Version=8.0')
class TestInsWTrigger(unittest.TestCase):
def setUp(self):
self.engine = create_engine("mssql+pyodbc://", echo=True, creator=connect_to_mssql)
self.meta = meta = MetaData()
self.t1 = Table('t1', meta,
Column('id', Integer, Sequence('fred', 100, 1),
primary_key=True),
Column('descr', String(200)),
Column('date', DateTime()),
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 instead of insert as
BEGIN
SET IDENTITY_INSERT t1 ON
INSERT INTO t1(id, descr, date)
SELECT
ISNULL((SELECT MAX(id) FROM t1) + 1, 1) AS id,
descr,
GETDATE() AS date
FROM inserted
SET IDENTITY_INSERT t1 OFF
END""")
)
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])
logging.info('>>>>>>>>>' + str(con.execute(self.t2.select()).fetchall()))
#self.assert_(
# con.execute(self.t2.select()).fetchall() ==
# [(200, 'hello'), (201, 'there')]
#)
logging.info('>>>>>>>>>' + str(con.execute(self.t1.select()).fetchall()))
#self.assert_(
# con.execute(self.t1.select()).fetchall() ==
# [(100, 'there')]
#)
if __name__ == '__main__':
unittest.main()
