版本: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

当处理Interval类本身时,hybrid_property描述符将给定Interval类的函数体评估为参数,当使用SQLAlchemy表达式机制返回一个新的SQL表达式:

>>> 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类示例还演示了contains()intersects()两种方法,用hybrid_method修饰。该装饰器将相同的想法应用于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内函数和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

处理关系

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

加入 - 从属关系混合

考虑以下将UserSavingsAccount关联的声明性映射:

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')

上面的配方会给我们提供一个相关的SELECT的balance列:

>>> 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)

上面的CaseInsensitiveComparator实现了ColumnOperators接口的一部分。可以对所有比较操作(即,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. 通过重写operate()__clause_element__()以根据self.word工作,所有比较操作都将针对“转换后” word,无论是SQL端还是Python端。我们的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

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

也可以看看

杂种和价值不可知类型 - 在techspot.zzzeek.org博客上

价值不可知论类型,第二部分 - 在techspot.zzzeek.org博客上

建立变形金刚

一个转换器是一个可以接收Query对象并返回一个新对象的对象。Query对象包含一个方法with_transformation(),该方法返回由给定函数转换的新的Query

我们可以将它和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 __ funcexpr = None t5 >

创建一个新的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表达式生成方法的修改装饰器。

类 T0> sqlalchemy.ext.hybrid。 T1> hybrid_property T2> ( T3> fget T4>, FSET = Nonefdel = Noneexpr = 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 =符号('HYBRID_METHOD')
sqlalchemy.ext.hybrid。 HYBRID_PROPERTY =符号('HYBRID_PROPERTY')