SystemError: 'finally' pops bad exception with MS SQL Server

[jrpfinch]

Hi

I am relatively new to sqlalchemy and python generally. I am using
sqlalchemy with pyodbc in TurboGears and am hitting the dreaded
"SystemError: 'finally' pops bad exception" that other users appear to
have encountered, but in a different context.

The model.py is below the body of this message. customer_live is a
SQL Server view and as such has no primary key (so you have to define
it manually - can't just rely on autoload).

It currently appears to have problems propogating error messages -
when you try to order Customer_live by a column name that doesn't
exist (e.g. blabla), you get the following:

[root@msdcustdata view-tester]# tg-admin shell
...detected
Python 2.4.3 (#1, Mar 14 2007, 18:51:08)
[GCC 4.1.1 20070105 (Red Hat 4.1.1-52)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(CustomShell)
>>> q=session.query(Customer_live).order_by('blabla')
>>> for row in q:
... print row
...
Traceback (most recent call last):
File "<console>", line 1, in ?
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/orm/query.py", line 936, in __iter__
return self._execute_and_instances(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/orm/query.py", line 939, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/orm/session.py", line 625, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/engine/base.py", line 844, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/engine/base.py", line 895, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/engine/base.py", line 907, in _execute_compiled
self.__execute_raw(context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/engine/base.py", line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/engine/base.py", line 958, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/databases/mssql.py", line 820, in do_execute
super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement,
parameters, context=context, **kwargs)
File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.6-py2.4.egg/
sqlalchemy/databases/mssql.py", line 499, in do_execute
cursor.execute("SET IDENTITY_INSERT %s OFF" %
self.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception


When you try to order by a column name that does exist, everything
works as expected. Please let me know if there is anything I can do
so that the correct error message is propogated (e.g. column name
"blabla" does not exist). I am willing to help as much as I can
ironing out this problem, so let me know if you want me to run more
tests and I will provide all the output.

Best regards

Jon


import pkg_resources
pkg_resources.require("SQLAlchemy>=0.3.10")
from turbogears.database import metadata, mapper, bind_meta_data
# import some basic SQLAlchemy classes for declaring the data model
# (see http://www.sqlalchemy.org/docs/04/ormtutorial.html)
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.orm import relation
# import some datatypes for table columns from SQLAlchemy
# (see http://www.sqlalchemy.org/docs/04/types.html for more)
from sqlalchemy import String, Unicode, Integer, DateTime
bind_meta_data()

# your data tables
all_customer_info = Table('all_customer_info', metadata,
Column('ext_company_id', Integer,
primary_key=True),
autoload=True)
customer_live = Table('customer_live', metadata,
Column('cust_id', Integer, primary_key=True),
Column('cust_ext_company_id', None,
ForeignKey('all_customer_info.ext_company_id')),
autoload=True)
ems_live = Table('ems_live', metadata,
Column('ems_id', Integer, primary_key=True),
Column('ems_cust_id', None,
ForeignKey('customer_live.cust_id')),
autoload=True)
ne_live = Table('ne_live', metadata,
Column('ne_id', Integer, primary_key=True),
Column('ne_ems_id', None,
ForeignKey('ems_live.ems_id')),
autoload=True)

other_hardware_live = Table('other_hardware_live', metadata,
Column('oh_id', Integer, primary_key=True),
Column('oh_ems_id', None,
ForeignKey('ems_live.ems_id')),
autoload=True)


# your model classes
class All_customer_info(object):
pass

class Customer_live(object):
pass

class Ems_live(object):
pass

class Ne_live(object):
pass
class Other_hardware_live(object):
pass


# set up mappers between your data tables and classes
mapper(All_customer_info, all_customer_info, properties =
dict(customer=relation(Customer_live,uselist=False,backref='customer_info')))
mapper(Customer_live, customer_live, properties =
dict(emss=relation(Ems_live,backref='cust')))
mapper(Ems_live, ems_live, properties =
dict(nes=relation(Ne_live,backref='ems'),ohs=relation(Other_hardware_live,backref='ems')))
mapper(Ne_live, ne_live)
mapper(Other_hardware_live, other_hardware_live)

[Rick Morrison]

There have been other reports of this issue, all specific to pyodbc. It doesn't appear to be an issue with SA, as other MSSQL DBAPI modules don't exhibit the problem.

Please raise the issue on the pyodbc list, I'll work with you if needed to help resolve it.

[jrpfinch]

Yes it appears to be a pyodbc problem, which is a shame because it's
making debugging my project incredibly difficult (and I've had even
more problems with the other MS SQL libraries like pymssql).

The bug report is here:

http://sourceforge.net/tracker/index.php?func=detail&aid=1948657&group_id=162557&atid=824254

I am willing to put in a lot of time to make this work - however I
have no experience in C++ so it's going to be a challenge without some
help.

Here is a reproduction of the problem in pyodbc:

>>> import pyodbc
>>> cnxn = pyodbc.connect("DSN=xxxxxx;UID=xxxxx;PWD=xxxxxx")
>>> cursor=cnxn.cursor()
>>>
>>> cursor.execute("select * from no_such_table")

Traceback (most recent call last):

File "<stdin>", line 1, in ?
<type 'instance'>: ('42S02', "[42S02] [FreeTDS][SQL Server]Invalid
object name 'no_such_table'. (208) (SQLExecDirectW)")
>>>
>>>
>>> try:
... cursor.execute("select * from no_such_table")
... finally:
... print "Oh dear me!"
...
Oh dear me!

Traceback (most recent call last):

File "<stdin>", line 4, in ?

SystemError: 'finally' pops bad exception
>>>

I am going to try contacting the admin for pyodbc and it also looks
like the bug report has some sort of patch you can apply right at the
bottom in errors.diff (which I will try applying and then rebuilding
pyodbc, but like I say I have no C++ experience so won't be able to do
much if that doesn't work).

Any more advice?

[Rick Morrison]

I read through the pyodbc forum quickly, and it looks as though this issue, first reported back in April, cannot be reproduced by the maintainer. Pick up the forum thread entitled 
pyodbc pops bad exception since 2.0.39

and add your simple pyodbc-only testcase - see if you can get a working patch from the maintainer. A patch that fixes your testcase might prompt a new release (last release was back in April).

[jrpfinch]

Done - I really hope we can get this fixed because sqlalchemy and
TurboGears is incredibly difficult to use with MS SQL Server at the
moment. I think I may be able to improve sqlalchemy if I can get the
errors propogating properly

Cheers

Jon

[jrpfinch]

Turns out this error does not occur when you compile pyodbc for
Windows. Maybe this means that there is a bug in FreeTDS (which is
the ODBC driver I am using in Linux). Windows output:

>>> try:
... cursor.execute("SELECT * FROM no_such_table")
... finally:
... print "oh dear"
...
oh dear

Traceback (most recent call last):

File "<stdin>", line 2, in <module>
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL
Server Driver][

SQL Server]Invalid object name 'no_such_table'. (208)
(SQLExecDirectW)")

>>> pyodbc.version
'2.0.58'