Sqlalchemy Lookup Tables

Hi I have a table in 3NF form ftype_table = Table( 'FTYPE', Column('ftypeid', Integer, primary_key=True), Column('typename', String(50)), base.metadata, schema=

Solution 1:

the UniqueObject recipe is the standard answer here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject . The idea is to override the creation of File using either __metaclass__.call() or File.__new__() to return the already-existing object, from the DB or from cache (the initial DB lookup, if the object isn't already present, is obviously unavoidable unless something constructed around MySQL's REPLACE is used).

edit: since I've been working on the usage recipes, I've rewritten the unique object recipe to be more portable and updated for 0.5/0.6.

Solution 2:

Just create a cache of FileType objects, so that the database lookup occurs only the first time you use a given file type:

class FileTypeCache(dict):
    def __missing__(self, key):
        obj = self[key] = Session.query(FileType).filter_by(typename=key).one()
        return obj

filetype_cache = FileTypeCache()

file=File()
file.size=10
file.filetype= filetype_cache['PPT']

should work, modulo typos.

Solution 3:

Since declarative_base and zzzeek code does not work with sqlalchemy 0.4, I used the following cache so that new objects also stay unique if they are not present in the db

classFileTypeCache(dict):
    def__missing__(self, key):
        try:
          obj = self[key] = Session.query(FileType).filter_by(typename=key).one()
          return obj
        except InvalidRequestError:
          return obj=self[key]= FileType(key)
          return obj

override eq of FileType

classFileType(object):
    def__init__(self, typename)
       self.typename=typename
    def__eq__(self):
        ifisinstance(other, FileType):
            return self.typename == other.typename
        else:
            returnFalse