sqlalchemy lookup tables

Question

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='TEMP')
file_table = Table(
    'FILE',
    base.metadata,
    Column('fileid', Integer, primary_key=True),
    Column('datatypeid', Integer, ForeignKey(ftype_table.c.datatypeid)),
    Column('size', Integer),
    schema='TEMP')                                                                

and mappers

class File(object): pass
class FileType(object): pass
mapper(File, file_table, properties={'filetype': relation(FileType)})
mapper(FileType, file_table)

suppose Ftype table contains 1:TXT 2:AVI 3:PPT

what i would like to do is the following if i create a File object like this:

file=File()
file.size=10
file.filetype= FileType('PPT')
Session.save(file)
Session.flush()

is that the File table contains fileid:xxx,size:10, datatypeid:3

Unfortunately an entry gets added to the FileType table and this id gets propagated to the File table.

Is there a smart way to do achieve the above with sqlalchemy witout the need to do a query on the FileType table to see if the entry exist or not

Thanks

Answer 1

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.

Answer 2

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.

Answer 3

That looks great.

Unfortunately i m using db2 and stuck with sqlalchemy 0.4.0 Is there a 0.4.0 version

Answer 4

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

class FileTypeCache(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

class FileType(object):
    def __init__(self, typename)
       self.typename=typename
    def __eq__(self):
        if isinstance(other, FileType):
            return self.typename == other.typename
        else:
            return False