Python SQLAlchemy 淺談 hybrid_property

最近 ORM 用很兇，在翻閱 SQLAlchemy 文件的時候恰巧發現一個有趣的功能 - Hybrid Attributes 。

有多有趣呢？玩玩看就知道。

本文環境 #

• Python 3.6.5
• SQLAlchemy 1.2

Hybrid Attributes #

如果用過 property decorator 的人一定覺得 Hybrid Attributes 跟 property decorator 沒兩樣，不過 Hybrid Attributes 的好處是能夠被用在 query 中，增加程式的可讀性。

例如以下的 User Model:

# -*- coding: utf-8 -*-
import hashlib
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)
    gender_code = Column(Integer, default=0)  # 0: unknown, 1: male, 2: female

    def __init__(self, name, username, password, gender_code):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password.encode('utf-8')).hexdigest()
        self.gender_code = gender_code


engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

如果要 query 所有 gender_code == 0 的 Users ，正常情況下可能會這麼做：

>>> session.query(User).filter(User.gender_code == 0).all()

未來閱讀這段程式，可能還得去查 0 代表什麼。

但如果搭配 Hybrid Attributes 中的 hybrid_property 就可以把 User model 改成：

# -*- coding: utf-8 -*-
import hashlib
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)
    gender_code = Column(Integer, default=0)  # 0: unknown, 1: male, 2: female

    def __init__(self, name, username, password, gender_code):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password.encode('utf-8')).hexdigest()
        self.gender_code = gender_code

    @hybrid_property
    def gender_unknown(self):
        return self.gender_code == 0
        

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

上述範例可以看到多了一段 @hybrid_property decorator ，用法跟 property decorator 很像，不過 query 所有 gender_code == 0 的 Users 的方式可以藉由 hybrid_property 的加成變成：

>>> session.query(User).filter(User.gender_unknown).all()

可讀性瞬間提高很多！

而且也能夠看到 SQLAlchemy 正確以 WHERE user.gender_code = 0 的條件執行：

2018-09-26 21:48:03,184 INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.name AS user_name, user.username AS user_username, user.password AS user_password, user.gender_code AS user_gender_code
FROM user
WHERE user.gender_code = ?
2018-09-26 21:48:03,184 INFO sqlalchemy.engine.base.Engine (0,)

接下來試試更複雜的條件，試試查詢 name = 'John' and gender_code = 1 的資料， 在 User class 新增一個 hybrid_property john_male:

    @hybrid_property
    def john_male(self):
        return self.name == 'john' and self.gender_code == 1

查詢看看是否成功：

>>> session.query(User).filter(User.john_male).all()

結果發現只有以 WHERE name = 'john' ，少了 AND gender_code = 1 的條件：

2018-09-26 21:48:03,184 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-09-26 21:48:03,184 INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.name AS user_name, user.username AS user_username, user.password AS user_password, user.gender_code AS user_gender_code
FROM user
WHERE user.name = ?
2018-09-26 21:48:03,184  INFO sqlalchemy.engine.base.Engine ('john',)

把 john_male 的 expression 列印出來的話，很明顯發現只有正確套用 self.name == 'john'

>>> str(User.john_male.expression)
'"user".name = :name_1'

這是因為 SQLAlchemy 中的 WHERE 語句必須用 and_ or_ 連接詞(conjunctions)連接起來才有效。

所以必須更正成為：

import hashlib
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import and_


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)
    gender_code = Column(Integer, default=0)  # 0: unknown, 1: male, 2: female

    def __init__(self, name, username, password, gender_code):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password.encode('utf-8')).hexdigest()
        self.gender_code = gender_code

    @hybrid_property
    def gender_unknown(self):
        return self.gender_code == 0

    @hybrid_property
    def john_male(self):
        return and_(self.name == 'john',  self.gender_code == 1)


engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

如此一來就會正確了！

以上就是 SQLAlchemy 的 hybrid_property 簡單介紹。

最後，文件除了 hybrid_property ，也有 hybrid_method 可以使用，有興趣的話建議可以進一步閱讀文件 Hybrid Attributes 。

References #

https://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html

python sqlalchemy