版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

混合、自定义实体公共类

使用declarative时的一个常见需求是跨许多类共享某些功能,例如一组公共列,一些公用表选项或其他映射属性。标准的Python成语就是让这些类继承自包含这些共同特征的基础。

当使用declarative时,通过使用自定义的声明式基类以及除主基础之外还继承的“mixin”类,这个习惯用法是允许的。声明包括几个帮助器功能,以便如何声明映射。下面是一些常用的混合成语的例子:

from sqlalchemy.ext.declarative import declared_attr

class MyMixin(object):

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {'mysql_engine': 'InnoDB'}
    __mapper_args__= {'always_refresh': True}

    id =  Column(Integer, primary_key=True)

class MyModel(MyMixin, Base):
    name = Column(String(1000))

Where above, the class MyModel will contain an “id” column as the primary key, a __tablename__ attribute that derives from the name of the class itself, as well as __table_args__ and __mapper_args__ defined by the MyMixin mixin class.

There’s no fixed convention over whether MyMixin precedes Base or not. 正常的Python方法解析规则适用,上面的例子也适用于:

class MyModel(Base, MyMixin):
    name = Column(String(1000))

这是有效的,因为这里Base没有定义MyMixin定义的任何变量,即__tablename____table_args__ id如果Base确实定义了同名的属性,那么放置在继承列表中的类将决定在新定义的类上使用哪个属性。

通过传参来定制化Base基类

除了使用MixIn类这种方法外,上文提到的技术也是完全可以使用到Base基类本身的,实现方法就是通过给declarative_base() 参数传一个 cls 参数

from sqlalchemy.ext.declarative import declared_attr

class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {'mysql_engine': 'InnoDB'}

    id =  Column(Integer, primary_key=True)

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base(cls=Base)

class MyModel(Base):
    name = Column(String(1000))

如上,MyModel以及其他继承自Base的实体类就会拥有从类名(id主键列)派生的表名,“巴拉巴拉”的

在列中混合

在mixin上指定列的最基本的方法是简单的声明:

class TimestampMixin(object):
    created_at = Column(DateTime, default=func.now())

class MyModel(TimestampMixin, Base):
    __tablename__ = 'test'

    id =  Column(Integer, primary_key=True)
    name = Column(String(1000))

Where above, all declarative classes that include TimestampMixin will also have a column created_at that applies a timestamp to all row insertions.

熟悉SQLAlchemy表达式语言的人知道id(object identity)唯一标识了一个对象实例在一张表(schema)中的身份Two Table objects a and b may both have a column called id, but the way these are differentiated is that a.c.id and b.c.id are two distinct Python objects, referencing their parent tables a and b respectively.

在mixin列中,似乎只有一个Column对象被显式创建,但是上面的最终的created_at列必须作为不同Python对象存在类。为了达到这个目的,声明性扩展创建了一个被检测为mixin的类所遇到的每个Column对象的copy

这种复制机制仅限于没有外键的简单列,因为ForeignKey本身包含对在这个级别不能正确重新创建的列的引用。对于具有外键的列以及需要目标显式上下文的各种映射级结构,提供了declared_attr装饰器,以便可以将许多类通用的模式定义为可调用的:

from sqlalchemy.ext.declarative import declared_attr

class ReferenceAddressMixin(object):
    @declared_attr
    def address_id(cls):
        return Column(Integer, ForeignKey('address.id'))

class User(ReferenceAddressMixin, Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

Where above, the address_id class-level callable is executed at the point at which the User class is constructed, and the declarative extension can use the resulting Column object as returned by the method without the need to copy it.

更改为0.6.5版:sqlalchemy.util.classproperty重命名为declared_attr

Columns generated by declared_attr can also be referenced by __mapper_args__ to a limited degree, currently by polymorphic_on and version_id_col; the declarative extension will resolve them at class construction time:

class MyMixin:
    @declared_attr
    def type_(cls):
        return Column(String(50))

    __mapper_args__= {'polymorphic_on':type_}

class MyModel(MyMixin, Base):
    __tablename__='test'
    id =  Column(Integer, primary_key=True)

在关系中混合

relationship()创建的关系通过声明性的mixin类专门使用declared_attr方法提供,消除了复制关系及其可能的列绑定内容时可能出现的任何歧义。下面是一个结合外键列和关系的例子,这样两个类FooBar都可以被配置为通过多对一引用共同的目标类:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship("Target")

class Foo(RefTargetMixin, Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)

class Bar(RefTargetMixin, Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)

class Target(Base):
    __tablename__ = 'target'
    id = Column(Integer, primary_key=True)

使用高级关系参数(例如primaryjoin等)

relationship()定义需要显式的主要联接,order_by等除了最简单的情况外,表达式都应该使用后缀形式表示这些参数,也就是说,使用字符串形式或lambda表达式。这是因为使用@declared_attr配置的相关Column对象不可用于其他@declared_attr属性;而这些方法将工作并返回新的Column对象,这些对象并不是Declarative将使用的Column对象,因为它使用不同的 Column对象。

规范的例子是依赖于另一个混合列的主连接条件:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship(Target,
            primaryjoin=Target.id==cls.target_id   # this is *incorrect*
        )

使用上面的mixin映射一个类,我们会得到一个错误:

sqlalchemy.exc.InvalidRequestError: this ForeignKey's parent column is not
yet associated with a Table.

这是因为我们在target()方法中调用的target_id ColumnColumn该声明实际上是要映射到我们的表。

上面的条件使用lambda来解决:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship(Target,
            primaryjoin=lambda: Target.id==cls.target_id
        )

或者也可以是字符串形式(最终生成一个lambda):

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship("Target",
            primaryjoin="Target.id==%s.target_id" % cls.__name__
        )

在deferred(),column_property()和其他MapperProperty类中混合

Like relationship(), all MapperProperty subclasses such as deferred(), column_property(), etc. 最终涉及对列的引用,因此,当与声明性的mixin一起使用时,必须具有declared_attr要求,以便不需要依赖于复制:

class SomethingMixin(object):

    @declared_attr
    def dprop(cls):
        return deferred(Column(Integer))

class Something(SomethingMixin, Base):
    __tablename__ = "something"

The column_property() or other construct may refer to other columns from the mixin. declared_attr被调用之前,它们被提前复制:

class SomethingMixin(object):
    x = Column(Integer)

    y = Column(Integer)

    @declared_attr
    def x_plus_y(cls):
        return column_property(cls.x + cls.y)

在版本1.0.0中进行了更改:将mixin列复制到最终的映射类,以便declared_attr方法可以访问将要映射的实际列。

在关联代理和其他属性中混合

Mixins可以指定用户定义的属性以及其他扩展单元,如association_proxy()在属性必须专门针对目标子类定制的情况下,declared_attr的使用是必需的。一个例子是构建多个association_proxy()属性,每个属性都指向一个不同类型的子对象。下面是一个association_proxy() / mixin的例子,它为实现类提供了一个字符串值的标量列表:

from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class HasStringCollection(object):
    @declared_attr
    def _strings(cls):
        class StringAttribute(Base):
            __tablename__ = cls.string_table_name
            id = Column(Integer, primary_key=True)
            value = Column(String(50), nullable=False)
            parent_id = Column(Integer,
                            ForeignKey('%s.id' % cls.__tablename__),
                            nullable=False)
            def __init__(self, value):
                self.value = value

        return relationship(StringAttribute)

    @declared_attr
    def strings(cls):
        return association_proxy('_strings', 'value')

class TypeA(HasStringCollection, Base):
    __tablename__ = 'type_a'
    string_table_name = 'type_a_strings'
    id = Column(Integer(), primary_key=True)

class TypeB(HasStringCollection, Base):
    __tablename__ = 'type_b'
    string_table_name = 'type_b_strings'
    id = Column(Integer(), primary_key=True)

上面,HasStringCollection mixin产生了一个relationship(),它引用了一个新生成的名为StringAttribute的类。使用HasStringCollection mixin生成的StringAttribute类具有其自己的Table定义。它还生成一个association_proxy()对象,它将对strings属性的引用代理到每个StringAttributevalue实例。

TypeA or TypeB can be instantiated given the constructor argument strings, a list of strings:

ta = TypeA(strings=['foo', 'bar'])
tb = TypeA(strings=['bat', 'bar'])

该列表将生成StringAttribute对象的集合,这些对象被保存到type_a_stringstype_b_strings表的本地表中:

>>> print(ta._strings)
[<__main__.StringAttribute object at 0x10151cd90>,
    <__main__.StringAttribute object at 0x10151ce10>]

When constructing the association_proxy(), the declared_attr decorator must be used so that a distinct association_proxy() object is created for each of the TypeA and TypeB classes.

New in version 0.8: declared_attr is usable with non-mapped attributes, including user-defined attributes as well as association_proxy().

使用mixins控制表继承

可以使用__tablename__属性来提供一个函数,该函数将确定继承层次结构中用于每个类的表的名称,以及一个类是否具有其自己的不同表。

这是通过将declared_attr指示符与名为__tablename__()的方法结合使用来实现的。对于每个映射的类,声明式将始终为__tablename____mapper_args____table_args__函数调用declared_attr在层次结构中。因此,该功能需要单独接收每个类,并为每个类提供正确的答案。

例如,要创建一个mixin,为每个类提供一个基于类名的简单表名:

from sqlalchemy.ext.declarative import declared_attr

class Tablename:
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

class Person(Tablename, Base):
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = None
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    primary_language = Column(String(50))

或者,我们可以使用has_inherited_table()修改我们的__tablename__函数来为子类返回None这具有这些子类映射到父对象的单个表继承的效果:

from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.declarative import has_inherited_table

class Tablename(object):
    @declared_attr
    def __tablename__(cls):
        if has_inherited_table(cls):
            return None
        return cls.__name__.lower()

class Person(Tablename, Base):
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

在继承场景中混合列

在与declared_attr一起使用时,如何处理__tablename__和其他特殊名称,以及如何混合列和属性(例如关系,列属性等)),该函数仅针对层次结构中的基类进行调用。下面,只有Person类会收到一个名为id的列。在Engineer,映射将失败,这是没有给主键:

class HasId(object):
    @declared_attr
    def id(cls):
        return Column('id', Integer, primary_key=True)

class Person(HasId, Base):
    __tablename__ = 'person'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = 'engineer'
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

在连接表继承中,通常情况下,我们希望在每个子类上明确命名列。但在这种情况下,我们可能希望在每个表上都有一个id列,并通过外键相互引用。We can achieve this as a mixin by using the declared_attr.cascading modifier, which indicates that the function should be invoked for each class in the hierarchy, just like it does for __tablename__:

class HasId(object):
    @declared_attr.cascading
    def id(cls):
        if has_inherited_table(cls):
            return Column('id',
                          Integer,
                          ForeignKey('person.id'), primary_key=True)
        else:
            return Column('id', Integer, primary_key=True)

class Person(HasId, Base):
    __tablename__ = 'person'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

class Engineer(Person):
    __tablename__ = 'engineer'
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

版本1.0.0新增:新增declared_attr.cascading

结合来自多个Mixins的Table / Mapper参数

在声明性mixin指定的__table_args____mapper_args__的情况下,您可能希望将几个mixin的一些参数与您希望在类iteself上定义的参数结合起来。这里可以使用declared_attr装饰器来创建从多个集合中抽取的用户定义的整理例程:

from sqlalchemy.ext.declarative import declared_attr

class MySQLSettings(object):
    __table_args__ = {'mysql_engine':'InnoDB'}

class MyOtherMixin(object):
    __table_args__ = {'info':'foo'}

class MyModel(MySQLSettings, MyOtherMixin, Base):
    __tablename__='my_model'

    @declared_attr
    def __table_args__(cls):
        args = dict()
        args.update(MySQLSettings.__table_args__)
        args.update(MyOtherMixin.__table_args__)
        return args

    id =  Column(Integer, primary_key=True)

用Mixins创建索引

要定义适用于从mixin派生的所有表的命名的可能多列Index,请使用Index的“inline”形式,并将其建立为__table_args__

class MyMixin(object):
    a =  Column(Integer)
    b =  Column(Integer)

    @declared_attr
    def __table_args__(cls):
        return (Index('test_idx_%s' % cls.__tablename__, 'a', 'b'),)

class MyModel(MyMixin, Base):
    __tablename__ = 'atable'
    c =  Column(Integer,primary_key=True)