Trying to use a UUID for a primary key. Getting stale data on SQLalchemy commit()

Question

Starting SQLalchemy user here. I plan to use UUID's as the primary keys for my tables.

In the tutorial I saw some code for using the native Python UUID type in ORM classes. Eureka! I can use Postgresql's native UUID type for my system database and this TypeDecorator will stringify the UUID's for SQLite on my mobile clients.

http://docs.sqlalchemy.org/en/latest/core/types.html#backend-agnostic-guid-type

Sadness. When using this with an existing SQLite database that has stringified UUID's as the primary key I get stale data errors when I try to commit any changes.

This class crashes with stale data on commit.

class CommodityTypes(Base):
    __tablename__ = 'CommodityTypes'
    uuidKey = Column(GUID, primary_key=True)
    myName = Column(String, unique = True)
    sortKey = Column(Integer, unique = True)

, but this class works:

class NewTypes(Base):
    __tablename__ = 'CommodityTypes'
    uuidKey = Column(String, primary_key=True)
    myName = Column(String, unique = True)
    sortKey = Column(Integer, unique = True)

Queried objects from the CommodityTypes class show the python UUID type for uuidKey. The script queries the object correctly. I can change settings, but I can't commit. The decorated uuidKey doesn't seem to work.

I can go forward just using Strings for the uuidKey columns, but it frustrates me that the code from http://docs.sqlalchemy.org/en/latest/core/types.html#backend-agnostic-guid-type almost works.

Here's sample code with the problem. The string workaround not using the GUID type decorator is commented out.

#system modules
import uuid

#other modules
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.types import TypeDecorator, CHAR
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound

engine = create_engine('sqlite:////home/XXXX/XobfuscatedXXXX/XXXXXXXX.sqlite')

Base = declarative_base()

Session = sessionmaker(bind=engine)

class GUID(TypeDecorator):
    """Platform-independent GUID type.

    Uses Postgresql's UUID type, otherwise uses
    CHAR(32), storing as stringified hex values.

    """
    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value)
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value)
            else:
                # hexstring
                return "%.32x" % value

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

from sqlalchemy import Column, Boolean, DateTime, Date, Float, ForeignKey, Integer, Numeric, String

class CommodityTypes(Base):
    __tablename__ = 'CommodityTypes'
    uuidKey = Column(GUID, primary_key=True)
    myName = Column(String, unique = True)
    sortKey = Column(Integer, unique = True)

#class NewTypes(Base):
#    __tablename__ = 'CommodityTypes'
#    uuidKey = Column(String, primary_key=True)
#    myName = Column(String, unique = True)
#    sortKey = Column(Integer, unique = True)

if __name__=="__main__":
    session = Session()
#    newList = session.query(NewTypes).order_by(NewTypes.sortKey)
#    for instance in newList:
#        print(instance.myName)
#
#    nt = newList[1]
#    print(nt.myName)
#    print(nt.sortKey)
#    nt.sortKey = 11
#    print(nt.sortKey)
#    session.commit()
#    print(nt.sortKey)

    ctList = session.query(CommodityTypes).order_by(CommodityTypes.sortKey)
    for instance in ctList:
        print(instance.myName)
    ct = ctList[1]
    print(ct.myName)
    print(ct.sortKey)
    ct.sortKey = 22
    print(ct.sortKey)
    session.commit()
    print(ct.sortKey)

Oh, forgot to mention software versions:

Python 3.1.3 (r313:86834, Dec 1 2010, 06:15:12)

[GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2