Clone wiki

sqlalchemy / UsageRecipes / EntityName

Entity Name

"entity_name" is a feature supported up through SQLAlchemy version 0.4 which allows a class to be fully mapped multiple times to entirely different Table objects. A string entity_name parameter would be passed to most Session methods indicating which mapping should be in use.

The issue with this approach was that SQLAlchemy mappings determine class behavior before instances are associated with a session. For example, class-bound attributes produce SQL expressions, the aliased() construct can make an "alias" of a class based on its mapped table, and mapping configurations produce object attributes of widely varying behavior, including scalars, SQL-bound attributes, and collections of all types.

Based on this, it is clear that the "entity_name" for a particular instance must be decided at the moment the class is used, whether this is to create SQL expressions using it, or to create new instances of that class. The natural progression of this should be clear by now - use distinct classes!

The entity_name feature was adapted from Hibernate, where in the Java world creating a subclass is a very explicit and rigid affair. It turns out that in Python, this is not at all the case. Using type we can easily make anonymous subclasses for each desired mapping:

from sqlalchemy import *
from sqlalchemy.orm import *

metadata = MetaData(create_engine('sqlite://', echo=True))
t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
t2 = Table('t2', metadata, Column('id', Integer, primary_key=True))

def map_class_to_some_table(cls, table, entity_name, **kw):
     newcls = type(entity_name, (cls, ), {})
     mapper(newcls, table, **kw)
     return newcls

class Foo(object):

T1Foo = map_class_to_some_table(Foo, t1, "T1Foo")
T2Foo = map_class_to_some_table(Foo, t2, "T2Foo")

sess = sessionmaker()()

sess.add_all([T1Foo(), T1Foo(), T2Foo(), T1Foo()])

print sess.query(T1Foo).all()
print sess.query(T2Foo).all()

Here's the same kind of idea with declarative, using a more class-based style:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TBase(object):
    """Base class is a 'mixin'.

    Guidelines for declarative mixins is at:

    id = Column(Integer, primary_key=True)
    data = Column(String(50))

    def __repr__(self):
        return "%s(data=%r)" % (

class T1Foo(TBase, Base):
    __tablename__ = 't1'

class T2Foo(TBase, Base):
    __tablename__ = 't2'

    timestamp = Column(DateTime,

engine = create_engine('sqlite://', echo=True)


sess = sessionmaker(engine)()

sess.add_all([T1Foo(data='t1'), T1Foo(data='t2'), T2Foo(data='t3'),

print sess.query(T1Foo).all()
print sess.query(T2Foo).all()