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

SQLAlchemy 1.1文档

SQL表达式作为映射的属性

映射类上的属性可以链接到可用于查询的SQL表达式。

使用混合

The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called “hybrid attribute”, described in the section Hybrid Attributes. 混合提供了一个既可以在Python级别也可以在SQL表达级别运行的表达式。For example, below we map a class User, containing attributes firstname and lastname, and include a hybrid that will provide for us the fullname, which is the string concatenation of the two:

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))

    @hybrid_property
    def fullname(self):
        return self.firstname + " " + self.lastname

上面,fullname属性在实例和类级别都被解释,所以它可以从一个实例中获得:

some_user = session.query(User).first()
print(some_user.fullname)

以及在查询中可用:

some_user = session.query(User).filter(User.fullname == "John Smith").first()

字符串连接的例子是一个简单的例子,Python表达式可以在实例和类级别双重使用。通常,必须将SQL表达式与Python表达式区分开来,这可以使用hybrid_property.expression()来实现。下面我们举例说明如何使用Python中的if语句和SQL表达式的sql.expression.case()结构在混合内部出现条件:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))

    @hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname

    @fullname.expression
    def fullname(cls):
        return case([
            (cls.firstname != None, cls.firstname + " " + cls.lastname),
        ], else_ = cls.lastname)

使用column_property

可以使用orm.column_property()函数以类似于定期映射的Column的方式映射SQL表达式。使用这种技术,该属性在加载时与所有其他列映射属性一起加载。这在某些情况下比混合使用更有优势,因为值可以与对象的父行同时加载,特别是如果表达式链接到其他表(通常作为相关子查询)来访问通常在已经加载的对象上不可用的数据。

Disadvantages to using orm.column_property() for SQL expressions include that the expression must be compatible with the SELECT statement emitted for the class as a whole, and there are also some configurational quirks which can occur when using orm.column_property() from declarative mixins.

我们的“全名”示例可以使用orm.column_property()来表示,如下所示:

from sqlalchemy.orm import column_property

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    fullname = column_property(firstname + " " + lastname)

相关的子查询也可以使用。下面我们使用select()结构来创建一个SELECT,它将特定的User可用的Address

from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    address_count = column_property(
        select([func.count(Address.id)]).\
            where(Address.user_id==id).\
            correlate_except(Address)
    )

在上面的例子中,我们定义了如下所示的select()结构:

select([func.count(Address.id)]).\
    where(Address.user_id==id).\
    correlate_except(Address)

The meaning of the above statement is, select the count of Address.id rows where the Address.user_id column is equated to id, which in the context of the User class is the Column named id (note that id is also the name of a Python built in function, which is not what we want to use here - if we were outside of the User class definition, we’d use User.id).

The select.correlate_except() directive indicates that each element in the FROM clause of this select() may be omitted from the FROM list (that is, correlated to the enclosing SELECT statement against User) except for the one corresponding to Address. This isn’t strictly necessary, but prevents Address from being inadvertently omitted from the FROM list in the case of a long string of joins between User and Address tables where SELECT statements against Address are nested.

如果导入问题阻止了column_property()与类内联定义,则可以在两者都配置后将其分配给类。在声明式中,这具有调用Mapper.add_property()在事实之后添加其他属性的效果:

User.address_count = column_property(
        select([func.count(Address.id)]).\
            where(Address.user_id==User.id)
    )

对于多对多关系,使用and_()将关联表的字段连接到关系中的两个表,这里用经典的映射来说明:

from sqlalchemy import and_

mapper(Author, authors, properties={
    'book_count': column_property(
                        select([func.count(books.c.id)],
                            and_(
                                book_authors.c.author_id==authors.c.id,
                                book_authors.c.book_id==books.c.id
                            )))
    })

使用普通描述符

在SQL查询比orm.column_property()hybrid_property可以提供更精细的情况下,必须使用常规Python函数作为属性访问,假设表达式只需要在已经加载的实例上可用。该函数用Python自己的@property修饰器修饰,将其标记为只读属性。Within the function, object_session() is used to locate the Session corresponding to the current object, which is then used to emit a query:

from sqlalchemy.orm import object_session
from sqlalchemy import select, func

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))

    @property
    def address_count(self):
        return object_session(self).\
            scalar(
                select([func.count(Address.id)]).\
                    where(Address.user_id==self.id)
            )

简单的描述符方法作为最后的手段是有用的,但是在通常的情况下比混合属性和列属性方法性能低,因为它需要在每次访问时发出一个SQL查询。