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

SQLAlchemy 1.1文档

混合属性

在具有“混合”行为的ORM映射类上定义属性。

“混合”意味着属性在类级和实例级定义了不同的行为。

hybrid扩展提供了特殊形式的方法装饰器,大约有50行代码,几乎不依赖SQLAlchemy的其余部分。它理论上可以与任何基于描述符的表达系统一起工作。

考虑映射Interval,表示整数startend值。我们可以在类级别生成SQL表达式的映射类上定义更高级别的函数,在实例级别上定义Python表达式评估。下面,用hybrid_methodhybrid_property装饰的每个函数都可以将self作为类的实例,或者作为类本身:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, aliased
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method

Base = declarative_base()

class Interval(Base):
    __tablename__ = 'interval'

    id = Column(Integer, primary_key=True)
    start = Column(Integer, nullable=False)
    end = Column(Integer, nullable=False)

    def __init__(self, start, end):
        self.start = start
        self.end = end

    @hybrid_property
    def length(self):
        return self.end - self.start

    @hybrid_method
    def contains(self, point):
        return (self.start <= point) & (point <= self.end)

    @hybrid_method
    def intersects(self, other):
        return self.contains(other.start) | self.contains(other.end)

上面的length属性返回了endstart属性之间的差异。对于Interval的实例,使用普通的Python描述符机制在Python中进行相减:

>>> i1 = Interval(5, 10)
>>> i1.length
5

When dealing with the Interval class itself, the hybrid_property descriptor evaluates the function body given the Interval class as the argument, which when evaluated with SQLAlchemy expression mechanics returns a new SQL expression:

>>> print Interval.length
interval."end" - interval.start

>>> print Session().query(Interval).filter(Interval.length > 10)
SELECT interval.id AS interval_id, interval.start AS interval_start,
interval."end" AS interval_end
FROM interval
WHERE interval."end" - interval.start > :param_1

ORM methods such as filter_by() generally use getattr() to locate attributes, so can also be used with hybrid attributes:

>>> print Session().query(Interval).filter_by(length=5)
SELECT interval.id AS interval_id, interval.start AS interval_start,
interval."end" AS interval_end
FROM interval
WHERE interval."end" - interval.start = :param_1

Interval类的例子还说明了用hybrid_method修饰的contains()intersects()两个方法。这个装饰器将相同的想法应用于hybrid_property应用于属性的方法。这些方法返回布尔值,并利用Python |&位运算符来产生等效的实例级和SQL表达式级布尔行为:

>>> i1.contains(6)
True
>>> i1.contains(15)
False
>>> i1.intersects(Interval(7, 18))
True
>>> i1.intersects(Interval(25, 29))
False

>>> print Session().query(Interval).filter(Interval.contains(15))
SELECT interval.id AS interval_id, interval.start AS interval_start,
interval."end" AS interval_end
FROM interval
WHERE interval.start <= :start_1 AND interval."end" > :end_1

>>> ia = aliased(Interval)
>>> print Session().query(Interval, ia).filter(Interval.intersects(ia))
SELECT interval.id AS interval_id, interval.start AS interval_start,
interval."end" AS interval_end, interval_1.id AS interval_1_id,
interval_1.start AS interval_1_start, interval_1."end" AS interval_1_end
FROM interval, interval AS interval_1
WHERE interval.start <= interval_1.start
    AND interval."end" > interval_1.start
    OR interval.start <= interval_1."end"
    AND interval."end" > interval_1."end"

定义与属性行为不同的表达行为

我们使用上面的&|位运算符是幸运的,考虑到我们的函数在两个布尔值上运行以返回一个新的布尔值。在很多情况下,构建Python in-function和SQLAlchemy SQL表达式有很大的区别,应该定义两个独立的Python表达式。为了这个目的,hybrid修饰符定义了hybrid_property.expression()修饰符。作为例子,我们将定义间隔的半径,这需要使用绝对值函数:

from sqlalchemy import func

class Interval(object):
    # ...

    @hybrid_property
    def radius(self):
        return abs(self.length) / 2

    @radius.expression
    def radius(cls):
        return func.abs(cls.length) / 2

在Python函数abs()用于实例级操作,SQL函数ABS()通过func对象用于类级表达式:

>>> i1.radius
2

>>> print Session().query(Interval).filter(Interval.radius > 5)
SELECT interval.id AS interval_id, interval.start AS interval_start,
    interval."end" AS interval_end
FROM interval
WHERE abs(interval."end" - interval.start) / :abs_1 > :param_1

定义Setters

混合属性也可以定义setter方法。如果我们想要length,设置时修改端点值:

class Interval(object):
    # ...

    @hybrid_property
    def length(self):
        return self.end - self.start

    @length.setter
    def length(self, value):
        self.end = self.start + value

现在在set中调用长度(self, value)

>>> i1 = Interval(5, 10)
>>> i1.length
5
>>> i1.length = 12
>>> i1.end
17

处理关系

创建与相关对象(而不是基于列的数据)相结合的混合体时,没有本质区别。对不同表情的需求往往更大。我们将要说明的两个变体是“依赖于连接”的混合体,以及“相关子查询”混合体。

加入 - 从属关系混合

Consider the following declarative mapping which relates a User to a SavingsAccount:

from sqlalchemy import Column, Integer, ForeignKey, Numeric, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class SavingsAccount(Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    balance = Column(Numeric(15, 5))

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

    accounts = relationship("SavingsAccount", backref="owner")

    @hybrid_property
    def balance(self):
        if self.accounts:
            return self.accounts[0].balance
        else:
            return None

    @balance.setter
    def balance(self, value):
        if not self.accounts:
            account = Account(owner=self)
        else:
            account = self.accounts[0]
        account.balance = value

    @balance.expression
    def balance(cls):
        return SavingsAccount.balance

上面的混合属性balance与该用户的帐户列表中的第一个SavingsAccount条目一起使用。Python中的getter / setter方法可以将accounts视为self上可用的Python列表。

但是,在表达式层面上,预计User类将在适当的上下文中使用,以便存在到SavingsAccount的适当连接:

>>> print Session().query(User, User.balance).\
...     join(User.accounts).filter(User.balance > 5000)
SELECT "user".id AS user_id, "user".name AS user_name,
account.balance AS account_balance
FROM "user" JOIN account ON "user".id = account.user_id
WHERE account.balance > :balance_1

但是请注意,虽然实例级访问器需要担心是否存在self.accounts,但是在SQL表达式级别,我们基本上会使用外连接:

>>> from sqlalchemy import or_
>>> print (Session().query(User, User.balance).outerjoin(User.accounts).
...         filter(or_(User.balance < 5000, User.balance == None)))
SELECT "user".id AS user_id, "user".name AS user_name,
account.balance AS account_balance
FROM "user" LEFT OUTER JOIN account ON "user".id = account.user_id
WHERE account.balance <  :balance_1 OR account.balance IS NULL

相关子查询关系混合

当然,我们可以放弃依赖封闭查询的连接使用来支持相关的子查询,这个子查询可以被移植到一个列表达式中。相关的子查询更具可移植性,但在SQL级别上往往执行得更差。使用在Using column_property所示的相同技术,我们可以调整我们的SavingsAccount示例来聚合所有个帐户的余额,并使用相关的子查询列表达式:

from sqlalchemy import Column, Integer, ForeignKey, Numeric, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import select, func

Base = declarative_base()

class SavingsAccount(Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
    balance = Column(Numeric(15, 5))

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

    accounts = relationship("SavingsAccount", backref="owner")

    @hybrid_property
    def balance(self):
        return sum(acc.balance for acc in self.accounts)

    @balance.expression
    def balance(cls):
        return select([func.sum(SavingsAccount.balance)]).\
                where(SavingsAccount.user_id==cls.id).\
                label('total_balance')

The above recipe will give us the balance column which renders a correlated SELECT:

>>> print s.query(User).filter(User.balance > 400)
SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE (SELECT sum(account.balance) AS sum_1
FROM account
WHERE account.user_id = "user".id) > :param_1

构建自定义比较器

混合财产还包括帮助建立定制比较的帮手。一个比较器对象允许自定义每个SQLAlchemy表达式运算符的行为。当创建自定义类型在SQL方面有一些非常特殊的行为时,它们很有用。

下面的示例类允许对名为word_insensitive的属性进行不区分大小写的比较:

from sqlalchemy.ext.hybrid import Comparator, hybrid_property
from sqlalchemy import func, Column, Integer, String
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class CaseInsensitiveComparator(Comparator):
    def __eq__(self, other):
        return func.lower(self.__clause_element__()) == func.lower(other)

class SearchWord(Base):
    __tablename__ = 'searchword'
    id = Column(Integer, primary_key=True)
    word = Column(String(255), nullable=False)

    @hybrid_property
    def word_insensitive(self):
        return self.word.lower()

    @word_insensitive.comparator
    def word_insensitive(cls):
        return CaseInsensitiveComparator(cls.word)

在上面,针对word_insensitive的SQL表达式将把LOWER() SQL函数应用于双方:

>>> print Session().query(SearchWord).filter_by(word_insensitive="Trucks")
SELECT searchword.id AS searchword_id, searchword.word AS searchword_word
FROM searchword
WHERE lower(searchword.word) = lower(:lower_1)

The CaseInsensitiveComparator above implements part of the ColumnOperators interface. 可以将所有比较操作(即eqltgt等)应用于“强制”使用Operators.operate()

class CaseInsensitiveComparator(Comparator):
    def operate(self, op, other):
        return op(func.lower(self.__clause_element__()), func.lower(other))

混合值对象

注意在前面的例子中,如果我们要将SearchWord实例的word_insensitive属性与一个普通的Python字符串进行比较,那么纯Python字符串不会被强制为小写字母 - 我们构建的CaseInsensitiveComparator仅由@word_insensitive.comparator返回,仅适用于SQL方面。

自定义比较器的一个更全面的形式是构造一个混合值对象该技术将目标值或表达式应用于值对象,然后在所有情况下由访问器返回值对象。值对象允许控制值的所有操作,以及如何处理比较值,无论是在SQL表达式还是在Python值方面。用新的CaseInsensitiveWord类替换以前的CaseInsensitiveComparator类:

class CaseInsensitiveWord(Comparator):
    "Hybrid value representing a lower case representation of a word."

    def __init__(self, word):
        if isinstance(word, basestring):
            self.word = word.lower()
        elif isinstance(word, CaseInsensitiveWord):
            self.word = word.word
        else:
            self.word = func.lower(word)

    def operate(self, op, other):
        if not isinstance(other, CaseInsensitiveWord):
            other = CaseInsensitiveWord(other)
        return op(self.word, other.word)

    def __clause_element__(self):
        return self.word

    def __str__(self):
        return self.word

    key = 'word'
    "Label to apply to Query tuple results"

Above, the CaseInsensitiveWord object represents self.word, which may be a SQL function, or may be a Python native. By overriding operate() and __clause_element__() to work in terms of self.word, all comparison operations will work against the “converted” form of word, whether it be SQL side or Python side. 我们的SearchWord类现在可以无条件地从单个混合调用中传递CaseInsensitiveWord对象:

class SearchWord(Base):
    __tablename__ = 'searchword'
    id = Column(Integer, primary_key=True)
    word = Column(String(255), nullable=False)

    @hybrid_property
    def word_insensitive(self):
        return CaseInsensitiveWord(self.word)

现在,word_insensitive属性普遍具有不区分大小写的比较行为,包括SQL表达式与Python表达式(请注意,Python值在这里转换为小写):

>>> print Session().query(SearchWord).filter_by(word_insensitive="Trucks")
SELECT searchword.id AS searchword_id, searchword.word AS searchword_word
FROM searchword
WHERE lower(searchword.word) = :lower_1

SQL表达式与SQL表达式:

>>> sw1 = aliased(SearchWord)
>>> sw2 = aliased(SearchWord)
>>> print Session().query(
...                    sw1.word_insensitive,
...                    sw2.word_insensitive).\
...                        filter(
...                            sw1.word_insensitive > sw2.word_insensitive
...                        )
SELECT lower(searchword_1.word) AS lower_1,
lower(searchword_2.word) AS lower_2
FROM searchword AS searchword_1, searchword AS searchword_2
WHERE lower(searchword_1.word) > lower(searchword_2.word)

仅Python表达式:

>>> ws1 = SearchWord(word="SomeWord")
>>> ws1.word_insensitive == "sOmEwOrD"
True
>>> ws1.word_insensitive == "XOmEwOrX"
False
>>> print ws1.word_insensitive
someword

混合价值模式对于任何可能具有多种表示形式(如时间戳,时间差,度量单位,货币和加密密码)的值都非常有用。

也可以看看

Hybrids and Value Agnostic Types - on the techspot.zzzeek.org blog

Value Agnostic Types, Part II - on the techspot.zzzeek.org blog

建立变形金刚

一个转换器是一个可以接收Query对象并返回一个新对象的对象。The Query object includes a method with_transformation() that returns a new Query transformed by the given function.

我们可以将它和Comparator类结合起来,生成一种类型的配方,既可以设置查询的FROM子句,也可以指定过滤条件。

考虑一个映射的类Node,它使用邻接列表组装成一个层次树模式:

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id =Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    parent = relationship("Node", remote_side=id)

Suppose we wanted to add an accessor grandparent. 这将返回Node.parentparent当我们有一个Node的实例时,这很简单:

from sqlalchemy.ext.hybrid import hybrid_property

class Node(Base):
    # ...

    @hybrid_property
    def grandparent(self):
        return self.parent.parent

对于表达,事情不是很清楚。We’d need to construct a Query where we join() twice along Node.parent to get to the grandparent. 我们可以返回一个转换的可调用对象,我们将和Comparator类结合使用来接收任何Query对象,并返回一个加入到Node.parent属性,并根据给定的标准进行过滤:

from sqlalchemy.ext.hybrid import Comparator

class GrandparentTransformer(Comparator):
    def operate(self, op, other):
        def transform(q):
            cls = self.__clause_element__()
            parent_alias = aliased(cls)
            return q.join(parent_alias, cls.parent).\
                        filter(op(parent_alias.parent, other))
        return transform

Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id =Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    parent = relationship("Node", remote_side=id)

    @hybrid_property
    def grandparent(self):
        return self.parent.parent

    @grandparent.comparator
    def grandparent(cls):
        return GrandparentTransformer(cls)

GrandparentTransformer覆盖Comparator层次结构的核心Operators.operate()方法,以返回一个查询转换可调用,然后运行给定的比较操作在特定的上下文中。Such as, in the example above, the operate method is called, given the Operators.eq callable as well as the right side of the comparison Node(id=5). 然后返回一个函数transform,它将首先转换Query以加入到Node.parent,然后比较parent_alias >在左侧和右侧使用Operators.eq,传入Query.filter

>>> from sqlalchemy.orm import Session
>>> session = Session()
sql>>> session.query(Node).\
...        with_transformation(Node.grandparent==Node(id=5)).\
...        all()

我们可以通过从“过滤器”步骤中分离“连接”步骤来修改模式,使其更加冗长而灵活。The tricky part here is ensuring that successive instances of GrandparentTransformer use the same AliasedClass object against Node. 下面我们使用一个简单的记忆方法,将一个GrandparentTransformer与每个类关联:

class Node(Base):

    # ...

    @grandparent.comparator
    def grandparent(cls):
        # memoize a GrandparentTransformer
        # per class
        if '_gp' not in cls.__dict__:
            cls._gp = GrandparentTransformer(cls)
        return cls._gp

class GrandparentTransformer(Comparator):

    def __init__(self, cls):
        self.parent_alias = aliased(cls)

    @property
    def join(self):
        def go(q):
            return q.join(self.parent_alias, Node.parent)
        return go

    def operate(self, op, other):
        return op(self.parent_alias.parent, other)
sql>>> session.query(Node).\
...            with_transformation(Node.grandparent.join).\
...            filter(Node.grandparent==Node(id=5))

“变压器”模式是一个开始使用一些功能性编程范例的实验模式。虽然只推荐给高级和/或耐心的开发人员,但可能有很多令人惊奇的东西可用。

API参考

class sqlalchemy.ext.hybrid.hybrid_method(func, expr=None)

基础:sqlalchemy.orm.base.InspectionAttrInfo

一个装饰器,允许定义一个Python对象方法,具有实例级别和类级别的行为。

__init__(func, expr=None)

创建一个新的hybrid_method

用法通常是通过装饰者:

from sqlalchemy.ext.hybrid import hybrid_method

class SomeClass(object):
    @hybrid_method
    def value(self, x, y):
        return self._value + x + y

    @value.expression
    def value(self, x, y):
        return func.some_function(self._value, x, y)
表达 T0> ( T1> EXPR T2> ) T3> ¶ T4>

提供一个定义SQL表达式生成方法的修改装饰器。

class sqlalchemy.ext.hybrid.hybrid_property(fget, fset=None, fdel=None, expr=None)

基础:sqlalchemy.orm.base.InspectionAttrInfo

一个装饰器,允许定义一个Python描述符同时具有实例级别和类级别的行为。

__init__(fget, fset=None, fdel=None, expr=None)

创建一个新的hybrid_property

用法通常是通过装饰者:

from sqlalchemy.ext.hybrid import hybrid_property

class SomeClass(object):
    @hybrid_property
    def value(self):
        return self._value

    @value.setter
    def value(self, value):
        self._value = value
比较 T0> ( T1> 比较 T2> ) T3> ¶ T4>

提供一个定义比较器生成方法的修改装饰器。

装饰方法的返回值应该是Comparator的一个实例。

删除器 T0> ( T1> FDEL T2> ) T3> ¶ T4>

提供定义值删除方法的修改装饰器。

表达 T0> ( T1> EXPR T2> ) T3> ¶ T4>

提供一个定义SQL表达式生成方法的修改装饰器。

设定器 T0> ( T1> FSET T2> ) T3> ¶ T4>

提供一个定义值设置器方法的修改装饰器。

class sqlalchemy.ext.hybrid.Comparator(expression)

基础:sqlalchemy.orm.interfaces.PropComparator

一个辅助类,允许轻松构建用于混合使用的自定义PropComparator类。

sqlalchemy.ext.hybrid.HYBRID_METHOD = symbol('HYBRID_METHOD')
sqlalchemy.ext.hybrid.HYBRID_PROPERTY = symbol('HYBRID_PROPERTY')