Errors in SQLAlchemy's transactions do not return proper traceback (Python 2.7)

Question

In my current model User, I have a field "name" which cannot be null.

I try creating a user object, and adding it to the DBSession provided by Pyramid and submitting it with transaction, like so.

with transaction.manager:
    u = models.User()
    models.DBSession.add(u)

For those who don't use Pyramid, DBSession is:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

Now, in my transaction above, I do have a validation issue - I need to assign a name to User, but I didn't. However, instead of getting an error telling me "You need to assign your user a name!", I get this:

<ipython-input-5-47d9c0e393f7> in <module>()
      2     u = models.User()
----> 3     models.DBSession.add(u)
      4 

/home/user/Projects/env/local/lib/python2.7/site-packages/transaction-1.4.1-py2.7.egg/transaction/_manager.pyc in __exit__(self, t, v, tb)
    118     def __exit__(self, t, v, tb):
    119         if v is None:
--> 120             self.commit()
    121         else:
    122             self.abort()

/home/user/Projects/env/local/lib/python2.7/site-packages/transaction-1.4.1-py2.7.egg/transaction/_manager.pyc in commit(self)
    109         """ See ITransactionManager.
    110         """
--> 111         return self.get().commit()
    112 
    113     def abort(self):

/home/user/Projects/env/local/lib/python2.7/site-packages/transaction-1.4.1-py2.7.egg/transaction/_transaction.py in commit(self)
    276             tb = None
    277             try:
--> 278                 t, v, tb = self._saveAndGetCommitishError()
    279                 self._callAfterCommitHooks(status=False)
    280                 reraise(t, v, tb)

/home/user/Projects/env/local/lib/python2.7/site-packages/transaction-1.4.1-py2.7.egg/transaction/_transaction.py in _saveAndGetCommitishError(self)
    300             import pdb
    301             pdb.set_trace()
--> 302             traceback.print_stack(sys._getframe(1), None, ft)
    303             # Append the stack entries from here down to the exception.
    304             traceback.print_tb(tb, None, ft)

/usr/lib/python2.7/traceback.py in print_stack(f, limit, file)
    267         except ZeroDivisionError:
    268             f = sys.exc_info()[2].tb_frame.f_back
--> 269     print_list(extract_stack(f, limit), file)
    270 
    271 def format_stack(f=None, limit=None):

/usr/lib/python2.7/traceback.py in print_list(extracted_list, file)
     23                '  File "%s", line %d, in %s' % (filename,lineno,name))
     24         if line:
---> 25             _print(file, '    %s' % line.strip())
     26 
     27 def format_list(extracted_list):

/usr/lib/python2.7/traceback.py in _print(file, str, terminator)
     11 
     12 def _print(file, str='', terminator='\n'):
---> 13     file.write(str+terminator)
     14 
     15 

TypeError: 'unicode' does not have the buffer interface

I've found the issue at hand is that, somewhere, there's a python version 2 vs 3 incompatibility, shown here TypeError: 'str' does not support the buffer interface. I know SQLAlchemy supports python 3+, and so that's where the issue may be coming from.

Note that if I do my transaction correctly, no errors are thrown.

Is there any way of getting around this issue without having to overwrite code in traceback.py?

Answer 1

The error you're seeing is not (at least directly) caused by SQLAlchemy, but instead is caused by the combination of SQLAlchemy, IPython, and the way you are trying to use transaction. It will go away if you follow the recommended usage of these tools.

Disclaimer: The below is not the recommended way to use the scoped session and ZopeTransactionExtension in Pyramids, but I wanted to stick as closely to your provided code as possible.

Put this in a file and run it from a virtualenv with SQLAlchemy installed and you will see the correct error message from SQLAlchemy:

from sqlalchemy import types
from sqlalchemy import create_engine
from sqlalchemy.schema import Column
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    name = Column(types.String, primary_key=True)


def pretend_view(request):
    """Pretend view in a Pyramid application using pyramid_tm"""
    import transaction
    user = User()
    with transaction.manager:
        DBSession.add(user)
    return user

if __name__ == '__main__':
    engine = create_engine('sqlite://')
    global DBSession
    DBSession = scoped_session(
        sessionmaker(extension=ZopeTransactionExtension()))
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all()
    #import IPython; IPython.embed()
    pretend_view("dummy request")

Generates this exception: sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: user.name u'INSERT INTO user DEFAULT VALUES' ()

If you fire up IPython instead and run the pretend_view, you will receive the unicode error you mentioned.

Proper use of pyramid_tm

Now, if you want to see the correct error message in IPython, use the session "correctly"!

There's typically no reason to use transaction explicitly in your code; pyramid_tm will commit the transaction when the view returns automatically (assuming no exceptions are raised). This would be the proper way to run the view, and will generate the correct exception even from within IPython:

def pretend_view(request):
    """Pretend view in a Pyramid application using pyramid_tm"""
    session = DBSession()  # You're using a sessionmaker, so you should make a session!
    user = User()
    session.add(user)
    session.flush()
    return user

if you really want to commit the transaction from within the view:

def pretend_view(request):
    """Pretend view in a Pyramid application using pyramid_tm"""
    session = DBSession()
    user = User()
    session.add(user)
    session.flush()
    import transaction
    transaction.commit()
    return user

Other Resources

SQLAlchemy-Pyramid cookbook: http://docs.pylonsproject.org/projects/pyramid/en/latest/tutorials/wiki2/index.html#bfg-sql-wiki-tutorial

pyramid_tm documentation: http://pyramid-tm.readthedocs.org/en/latest/