Sqlalchemy：根据属性将类映射到不同表格

Question

我正在尝试往一个已经存在的数据库里写数据，这个数据库里有多个表，表的结构如下：

total_usage_<application>:
    id
    version
    date

其中，<application>会遍历一些字符串，比如“appl1”、“appl2”等等。现在我想用SQLAlchemy创建一个单一的类，像这样：

class DBEntry:
    id = ''
    application = ''
    version = ''
    date = ''

这样，DBEntry类的一个实例foo就可以映射到表"total_usage_" + foo.application。我该怎么做呢？

Answer 1

好的，请看下面的例子，这个例子是完整的，可能会给你展示一种实现方法。它假设你在启动程序时已经知道了 app_name，同时也假设表名遵循某种命名规则，当然你可以根据自己的需要进行调整，或者通过在每个映射类中重写 __tablename__ 来完全手动配置。
但主要的想法是，这个配置被封装在一个函数里（这也可以通过模块导入和预定义常量来实现）。

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, scoped_session, sessionmaker

def camel_to_under(name):
    import re
    s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
    return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()

def configure_database(app_name):
    """ @return: dictionary with all the classes mapped to proper tables for
    specific application.  """
    from sqlalchemy.ext.declarative import declared_attr
    from sqlalchemy.ext.declarative import declarative_base

    class Base(object):
        # docs: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#augmenting-the-base
        @declared_attr
        def __tablename__(cls):
            return camel_to_under(cls.__name__) + "_" + app_name

        def __repr__(self):
            attrs = class_mapper(self.__class__).column_attrs # only columns
            # attrs = class_mapper(self.__class__).attrs # show also relationships
            return u"{}({})".format(self.__class__.__name__,
                ', '.join('%s=%r' % (k.key, getattr(self, k.key))
                    for k in sorted(attrs)
                )
            )

    Base = declarative_base(cls=Base)

    class Class1(Base):
        id = Column(Integer, primary_key=True)
        name = Column(String)

    class Class1Child(Base):
        id = Column(Integer, primary_key=True)
        parent_id = Column(Integer, ForeignKey(Class1.id))
        name = Column(String)
        # relationships
        parent = relationship(Class1, backref="children")

    # return locals()
    return {
        "app_name": app_name,
        "Base": Base,
        "Class1": Class1,
        "Class1Child": Class1Child,
        }

def _test():
    """ Little test for the app.  """
    engine = create_engine(u'sqlite:///:memory:', echo=True)
    session = scoped_session(sessionmaker(bind=engine))

    app_name = "app1"
    x = configure_database(app_name)

    # assign real names
    app_name = x["app_name"]
    Base = x["Base"]
    Class1 = x["Class1"]
    Class1Child = x["Class1Child"]

    # create DB model (not required in production)
    Base.metadata.create_all(engine)

    # test data
    cc = Class1Child(name="child-11")
    c1 = Class1(name="some instance", children=[cc])
    session.add(c1)
    session.commit()

_test()