Python, SQLAlchemy and Postgresql: understanding inheritance

Welcome to Stack Overflow: in the future, if you have more than one question; you should provide a separate post for each. Feel free to link them together if it might help provide context.

Table inheritance in postgres is a very different thing and solves a different set of problems from class inheritance in python, and sqlalchemy makes no attempt to combine them.

When you use table inheritance in postgres, you're doing some trickery at the schema level so that more elaborate constraints can be enforced than might be easy to express in other ways; Once you have designed your schema; applications aren't normally aware of the inheritance; If they insert a row; it just magically appears in the parent table (much like a view). This is useful, for instance, for making some kinds of bulk operations more efficient (you can just drop the table for the month of january).

This is a fundamentally different idea from inheritance as seen in OOP (in python or otherwise, with relational persistence or otherwise). In that case, the application is aware that two types are related, and that the subtype is a permissible substitute for the supertype. "A holding is an address, a contact has an address therefore a contact can have a holding."

Which of these, (mostly orthogonal) tools you need depends on the application. You might need neither, you might need both.

1. Sqlalchemy's mechanisms for working with object inheritance is flexible and robust, you should use it in favor of a home built solution if it is compatible with your particular needs (this should be true for almost all applications).

2. The declarative extension is a convenience; It allows you to describe the mapped table, the python class and the mapping between the two in one 'thing' instead of three. It makes your code more "DRY"; It is however only a convenience layered on top of "classic sqlalchemy" and it isn't necessary by any measure.

3. If you find that you need table inheritance that's visible from sqlalchemy; your mapped classes won't be any different from not using those features; tables with inheritance are still normal relations (like tables or views) and can be mapped without knowledge of the inheritance in the python code.

For your #3, you don't necessarily have to declare empty entity classes to use mapper. If your application doesn't need fancy properties, you can just use introspection and metaclasses to model the existing tables without defining them. Here's what I did:

mymetadata = sqlalchemy.MetaData()
myengine = sqlalchemy.create_engine(...)

def named_table(tablename):
  u"return a sqlalchemy.Table object given a SQL table name"
  return sqlalchemy.Table(tablename, mymetadata, autoload=True, autoload_with=myengine)

def new_bound_class(engine, table):
  u"returns a new ORM class (processed by sqlalchemy.orm.mapper) given a sqlalchemy.Table object"
  fieldnames = table.c.__dict__['_data']
  def format_attributes(obj, transform):
    attributes = [u'%s=%s' % (x, transform(x)) for x in fieldnames]
    return u', '.join(attributes)
  class DynamicORMClass(object):
    def __init__(self, **kw):
      u"Keyword arguments may be used to initialize fields/columns"
      for key in kw:
        if key in fieldnames: setattr(self, key, kw[key])
        else: raise KeyError, '%s is not a valid field/column' % (key,)
    def __repr__(self):
      return u'%s(%s)' % (self.__class__.__name__, format_attributes(self, repr))
    def __str__(self):
      return u'%s(%s)' % (str(self.__class__), format_attributes(self, str))
  DynamicORMClass.__doc__ = u"This is a dynamic class created using SQLAlchemy based on table %s" % (table,)
  return sqlalchemy.orm.mapper(DynamicORMClass, table)

def named_orm_class(table):
  u"returns a new ORM class (processed by sqlalchemy.orm.mapper) given a table name or object"
  if not isinstance(table, Table):
    table = named_table(table)
  return new_bound_class(table)

Example of use:

>>> myclass = named_orm_class('mytable')
>>> session = Session()
>>> obj = myclass(name='Fred', age=25, ...)
>>> session.add(obj)
>>> session.commit()
>>> print str(obj)  # will print all column=value pairs

I beefed up my versions of new_bound_class and named_orm_class a little more with decorators, etc. to provide extra capabilities, and you can too. Of course, under the covers, it is declaring an empty entity class. But you don't have to do it, except this one time.

This will tide you over until you decide that you're tired of doing all those joins yourself, and why can't I just have an object attribute that does a lazy select query against related classes whenever I use it. That's when you make the leap to declarative (or Elixir).