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

SQLAlchemy 1.1文档

映射类型

现代SQLAlchemy具有两种不同的映射器配置样式。“古典”风格是SQLAlchemy的原始映射API,而“声明式”则是建立在“古典”之上的更丰富,更简洁的系统。Both styles may be used interchangeably, as the end result of each is exactly the same - a user-defined class mapped by the mapper() function onto a selectable unit, typically a Table.

声明性映射

The Declarative Mapping is the typical way that mappings are constructed in modern SQLAlchemy. 通过使用Declarative系统,可以立即定义用户定义的类的组件以及类映射到的Table元数据:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

以上是四列的基本单表映射。额外的属性,如与其他映射类的关系,也在类定义中内联声明:

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    addresses = relationship("Address", backref="user", order_by="Address.id")

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    user_id = Column(ForeignKey('user.id'))
    email_address = Column(String)

声明性映射系统在Object Relational Tutorial中介绍。有关此系统如何工作的更多详细信息,请参阅Declarative

古典映射

A Classical Mapping refers to the configuration of a mapped class using the mapper() function, without using the Declarative system. 这是SQLAlchemy的原始类映射API,它仍然是ORM提供的基本映射系统。

在“古典”形式中,表格元数据是通过Table构造单独创建的,然后通过mapper()函数与User

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper

metadata = MetaData()

user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12))
        )

class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

mapper(User, user)

有关映射属性的信息,例如与其他类的关系,通过properties字典提供。The example below illustrates a second Table object, mapped to a class called Address, then linked to User via relationship():

address = Table('address', metadata,
            Column('id', Integer, primary_key=True),
            Column('user_id', Integer, ForeignKey('user.id')),
            Column('email_address', String(50))
            )

mapper(User, user, properties={
    'addresses' : relationship(Address, backref='user', order_by=address.c.id)
})

mapper(Address, address)

在使用经典映射时,必须直接提供类,而不需要Declarative提供的“字符串查找”系统的好处。SQL表达式通常以Table对象的形式指定,即Address关系的address.c.id,而不是Address.id,因为Address可能尚未链接到表元数据,我们也不能在这里指定一个字符串。

Some examples in the documentation still use the classical approach, but note that the classical as well as Declarative approaches are fully interchangeable. 两个系统最终都会创建相同的配置,由Table,用户定义的类组成,它们与mapper()链接在一起。When we talk about “the behavior of mapper()”, this includes when using the Declarative system as well - it’s still used, just behind the scenes.

对映射,对象的运行时反省

无论使用何种方法,Mapper对象都可以使用Runtime Inspection API系统从任何映射类中获得。使用inspect()函数,可以从映射类获取Mapper

>>> from sqlalchemy import inspect
>>> insp = inspect(User)

详细信息包括Mapper.columns

>>> insp.columns
<sqlalchemy.util._collections.OrderedProperties object at 0x102f407f8>

这是一个可以以列表格式或个人名称查看的名称空间:

>>> list(insp.columns)
[Column('id', Integer(), table=<user>, primary_key=True, nullable=False), Column('name', String(length=50), table=<user>), Column('fullname', String(length=50), table=<user>), Column('password', String(length=12), table=<user>)]
>>> insp.columns.name
Column('name', String(length=50), table=<user>)

其他名称空间包括Mapper.all_orm_descriptors,其中包括所有映射的属性以及混合,关联代理:

>>> insp.all_orm_descriptors
<sqlalchemy.util._collections.ImmutableProperties object at 0x1040e2c68>
>>> insp.all_orm_descriptors.keys()
['fullname', 'password', 'name', 'id']

以及Mapper.column_attrs

>>> list(insp.column_attrs)
[<ColumnProperty at 0x10403fde0; id>, <ColumnProperty at 0x10403fce8; name>, <ColumnProperty at 0x1040e9050; fullname>, <ColumnProperty at 0x1040e9148; password>]
>>> insp.column_attrs.name
<ColumnProperty at 0x10403fce8; name>
>>> insp.column_attrs.name.expression
Column('name', String(length=50), table=<user>)