SQLAlchemy IntegrityError
Question: I'm having a problem using SQLAlchemy with PySide(PyQt). I'm trying to pop-up
a `QtGui.QDialog`, but when I do this SQLAlchemy throws an exception:

    
    
    Traceback (most recent call last):
      File "C:\Python27\lib\site-packages\preo\preodb\dbviewandmodel.py", line 32, in rowCount
        return len(self.rows())    
      File "C:\Python27\lib\site-packages\preo\preodb\dbviewandmodel.py", line 30, in rows
        return self.tableobj.query.all()
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\query.py", line 1579, in all
    return list(self)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\query.py", line 1688, in __iter__
        self.session._autoflush()
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\session.py", line 862, in _autoflush
        self.flush()
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\session.py", line 1388, in flush
        self._flush(objects)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\session.py", line 1469, in _flush
        flush_context.execute()
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\unitofwork.py", line 302, in execute
        rec.execute(self)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\unitofwork.py", line 446, in execute
        uow
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\orm\mapper.py", line 1878, in _save_obj
        execute(statement, params)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\base.py", line 1191, in execute
        params)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\base.py", line 1271, in _execute_clauseelement
        return self.__execute_context(context)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\base.py", line 1302, in __execute_context
        context.parameters[0], context=context)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\base.py", line 1401, in _cursor_execute
        context)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\base.py", line 1394, in _cursor_execute
        context)
      File "C:\Python27\lib\site-packages\sqlalchemy-0.6.6-py2.7.egg\sqlalchemy\engine\default.py", line 299, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', "[23000] [Microsoft][ODBC
    SQL Server Driver][SQL Server]Violation of UNIQUE KEY
    constraint 'UQ__users__F3DBC5720DAF0CB0'. Cannot insert duplicate key in
    object 'dbo.users'. (2627) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server
    Driver][SQL Server]The statement has been terminated. (3621)") u'INSERT INTO users
    (username, fullname, email, passwordmd5) OUTPUT inserted.id VALUES (?, ?, ?, ?)'
    (None, None, None, None)
    

This is particularly troubling because I have no code, anywhere, that even
attempts to insert records into SQL; I am only ever attempting to query data
from the database. In fact, my DB model is read-only with respect to what
PySide/PyQt are doing (i.e., I'm using a `QtGui.QTableView` model/view and
there is no `insertRows` function in that model).

I have no idea what's going on or how to solve it - again, I have no code to
modify SQL records at all, but still SQLAlchemy attempts to be inserting blank
records into one of my SQL tables. All I can see, in the background, is the
`QTableView` data model is querying the database A LOT. It just seems that
when I popup this `QDialog` (which does have some code in it to query some
table column) this error is thrown. Oddly, this isn't consistent, sometime the
popup appears first before the exception, sometimes the popup appears after
the exception. Under normal circumstances, the `QTableView` data model works
great, just not when I popup this dialog (and ironically, the popup isn't
using any `QTableView` at all, just standard widgets like QLineEdit,
QTextEdit, etc.)

If it helps, I'm using Python 2.7 with SQLAlchemy 0.6.6 (also with Elixir
0.7.1), and PySide 1.0.0 (and PyQt4 4.8.3). I'm on Windows 7 using SQL 2008 R2
(Express). And yes, I've tried rebooting the PC, but the problem still occurs
after a reboot. I'm reluctant to post more code because I have a lot of it in
this particular project and I can't nail down the problem anything specific.

I'm hoping someone might know of oddities in SQLAlchemy and/or PyQt that might
be related to this. I'm also hoping I can continue using SQLAlchemy as I have
a large data model built; I'm reluctant, at this point, to abandon this and
use PyQt's SQL features.


Answer: I've managed to make this problem go away, but it's still not really clear to
me why SQLAlchemy was trying to insert rows in my database - that really
bothers me, but it's not happening anymore.

At any rate, what was, I think, happening, was related to my SQLAlchemy data
model and the way I was accessing it, here is a snippet of that model:

    
    
    from elixir import *
    
    metadata.bind = 'mssql+pyodbc://username:password/dbname'
    metadata.bind.echo = False
    
    class Users(Entity):
        using_options(tablename = 'users')
        username = Field(String(50), unique=True)
        fullname = Field(String(255))
        email = Field(String(255))
        passwordmd5 = Field(String(32))
        def __repr__(self):
            return "<Users ({})({})({})>".format(self.username, self.fullname, self.email)
        def prettyname(self):
            return {'username':'User Name', 'fullname':'Full Name', 'email':'Email Address', 'passwordmd5':'$hidden$'}
    

In my code I needed a way of getting 'pretty' label names for a GUI without
having to hard code this in a GUI (I've been trying to create a dynamic way of
building GUI forms). So, I added the 'prettyname' method to my data model to
give me some application specific metadata in that data model. All I'm doing
is returning a dictionary of items.

I had a secondary problem in that sometimes I needed to get this data from the
class instance for Users and sometimes for a query result for Users (for
example, Users.get_by(id=1)). As it turned out, retrieving this data had to be
done in two ways. In the class instances I had to get the value this way:

    
    
    prettyname = Users().prettyname()['username']
    

But when I was using query results it was:

    
    
    prettyname = queryresult.prettyname()['username']
    

SQLAlchemy seems to have a real problem when I was using the former method
(the class instance method) - as this was being used everytime I was seeing
the crash. When I was using the latter instance I was never seeing a crash.
Still, I needed access to that metadata in the class instance.

The fix, or should I say what turned out to fix this came from another
Stackoverflow article (thank you everyone at Stackoverflow - I'd be nothing
without you). I changed the structure of the dbmodel:

    
    
    class Users(Entity):
        using_options(tablename = 'users')
        username = Field(String(50), unique=True, info={'prettyname':'User Name'})
        fullname = Field(String(255), info={'prettyname':'Full Name'})
        email = Field(String(255), info={'prettyname':'Email Address'})
        passwordmd5 = Field(String(32), info={'hidden':True})
        def __repr__(self):
            return "<Users ({})({})({})>".format(self.username, self.fullname, self.email)
    

This allows me to use a common method of introspection to get the dictionary
data in the info argument, regardless if I'm looking at a class instance, or a
query result. In this case I use the '.table' method of either the class or
query result, then get the column that I need (.c), then use the .info method
of that column to return the dictionary.

Whatever the case, now SQLAlchemy no longer tries to arbitrarily insert rows
in the database anymore.

