SQLAlchemy中的"混动"属性

编程/技术 2019-03-17 @ 09:05:23 浏览数: 262 净访问: 206 By: skyrover

本博客采用创作共用版权协议, 要求署名、非商业用途和保持一致. 转载本博客文章必须也遵循署名-非商业用途-保持一致的创作共用协议


Hybrid Attributes

Define attributes on ORM-mapped classes that have “hybrid” behavior.

一般都会在SQLAlchemy定义的类中,使用属性(property)来定制自己想要的列相关的操作,比如在设置值的时候进行预处理,读取的时候进行处理之类的操作。但是今天遇到了一个报错

File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 1786, in _resolved_values
    values.extend(desc._bulk_update_tuples(v))
AttributeError: 'property' object has no attribute '_bulk_update_tuples'

debug了下发现代码中有块地方使用了Query.update()操作,但是普通的属性描述符是不支持这样的操作的,于是查了下文档,发现有hybrid-property的存在,姑且中文叫做混动属性吧(起这个名字其实来源于丰田的混动系统),后面就详细看了看,做个总结如下:

混动属性

很好用的一个特性,We can define higher level functions on mapped classes that produce SQL expressions at the class level, and Python expression evaluation at the instance level. 可以在映射的类中定义高层函数,用于在类级别产生SQL表达式,在实例级别上产生Python求值表达式。

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)

在实例上调用hybrid_property就像是一个属性(property),直接调用就可以自动求值,在类上调用,就会将类作为参数传递进函数进行求值,返回的将用SQLAlchemy表达式机制的一个新的SQL表达式

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

>>> 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方法比如filter_by()一般使用getattr()来定位属性,所以也可以在混动属性上使用

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

至于hybrid_method也是一样的道理:

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

定义不同于属性行为的表达式行为

主要是在于filter()等方法上对定义hybrid_property上的操作,在SQLAlchemy和Python上的函数是有差异的,比如下面

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

执行如下:

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

定义设置器

基本上和属性的设置器一样

class Interval(object):
    # ...

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

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

允许bulk update

可以在使用Query.update()时候定义一个自定义的UPDATE处理器,允许在更新的SET子句中使用混动属性。

一般的,当和Query.update()一起使用混动属性的时候,SQL表达式会被作为SET的目标列。如果我们的Interval类有一个混动属性start_point关联到Interval.start,那么就可以直接用下面的来代替: session.query(Interval).update({Interval.start_point: 10})

然而,如果使用复合混动属性比如Interval.length,那么这个混动就代表不止一列。我们可以设置一个处理器来适配传递给Query.update()的值,这个处理器类似与我们的setter,它就是hybrid_property.update_expression()装饰器

class Interval(object):
    # ...

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

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

    @length.update_expression
    def length(cls, value):
        return [
            (cls.end, cls.start + value)
        ]

如果我们在一个更新语句中使用Interval.length:

session.query(Interval).update({Interval.length: 25}, synchronize_session='fetch')

其实就是UPDATE interval SET end=start + :value

与关系一起工作

依赖join关系的混动

基本上和上面所说的用法类似,只不过在关系上会更需要定义单独的SQL表达式(类似上面的@radius.expression

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

可以这样查询:

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

关联子查询关系混动

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

实际查询:

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

后面高级用法参考:Hybrid Attributes


点赞走一波😏


评论

提交评论