SQLAlchemy 多对多关系 Association Proxy

在定义多对多（Many to Many）模型时，上一篇文章里说，中间表只用能db.Table定义。
这会导致很多Session Model的操作不可用，而且中间表中添加和查询额外字段也很麻烦。
其实，较新的SQLAlchemy版本，已经支持中间表格用模型定义了，这就是：Association Proxy

 

举例：

某个网站系统，其中两张表

• 一张表是用户信息User，每个用户可以有多种标签Keywords
• 另一张表是标签信息Keyword，每类标签，可以标注给多个用户
  那很显然是Many to Many，需要另外定义一张中间表。

如果我们想在中间表上添加额外字段，那就可以定义：Association Objects

模型定义

引入sqlalchemy模块

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
ss = Session()
Base = declarative_base()

定义User Model
注意关联Keyword Model，使用association_proxy

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

    # association proxy of "user_keywords" collection
    # to "keyword" attribute
    keywords = association_proxy('user_keywords', 'keyword') # 中间表和Many2Many表的名字

    def __init__(self, name):
        self.name = name

第二张表：Keyword，注意，其中某个字段，要加跟表名一样的label！比如，keyword字段

class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', String(64)) # 要加跟表名一样的标签

    def __init__(self, keyword):
        self.keyword = keyword

    def __repr__(self):
        return 'Keyword(%s)' % repr(self.keyword)

中间表，用来关联User/Keyword两张表：

• 两个字段，是ForeignKey类型
• user字段，使用backref，方便直接调用：user1.user_keywords
• 你可以添加任意多的额外字段，比如special_key

class UserKeyword(Base):
    __tablename__ = 'user_keyword'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
    special_key = Column(String(50))

    # bidirectional attribute/collection of "user"/"user_keywords"
    user = relationship(User,
                backref=backref("user_keywords",
                                cascade="all, delete-orphan")
            )

    # reference to the "Keyword" object
    keyword = relationship("Keyword")

    def __init__(self, keyword=None, user=None, special_key=None):
        self.user = user
        self.keyword = keyword
        self.special_key = special_key
    def __repr__(self):
        return f'User({self.user_id})-Keyword({self.keyword_id })_self.special_key '

好了，定义完成，生成表格吧

Base.metadata.create_all(engine)   

session操作

UserKeyword这个中间表，可以使用session的任意命令来操作

1. 新建几个User和Keyword

ss.add_all([
    Keyword('shadow'), Keyword('LOR1'), Keyword('WWII'),
    User('Kevin'), User('Olivia'),
])
ss.add_all([
    UserKeyword(user=ss.query(User).get(1), keyword=ss.query(Keyword).get(3), special_key ='abc'),
    UserOb(user=ss.query(User).get(2), keyword=ss.query(Keyword).get(3), special_key ='efg'),
    UserOb(user=ss.query(User).get(2), keyword=ss.query(Keyword).get(2), special_key ='cde'),
])

检查一下是否创建成功：

ss.query(UserKeyword).all()
# [User(1)-Keyword(3)_abc, User(2)-Keyword(3)_efg, User(2)-Keyword(2)_efg]

试一下强大的Session Query!
比如，我们想查询一下，各类Keyword的使用次数：

stmt = ss.query(UserKeyword.keyword_id, func.count('*').label('kw_count')).
    group_by(UserKeyword.keyword_id).subquery()
for kw, count in ss.query(Keyword, stmt.c.kw_count).
     outerjoin(stmt, Keyword.id==stmt.c.keyword_id).order_by(Keyword.id):
     print(kw, 'kw_count:', count)

#Keyword(shadow) kw_count: None
#Keyword(LOR1) kw_count: 1
#Keyword(WWII) kw_count: 2

比如，想根据额外字段special_key是否为abc来查询：

stmt = ss.query(UserKeyword.keyword_id, func.count(UserKeyword.special_key).label('status_count')).
    filter(UserKeyword.special_key=='abc').group_by(UserKeyword.ob_id).subquery()
for kw, count in ss.query(Keyword, stmt.c.status_count).
     outerjoin(stmt, Keyword.id==stmt.c.keyword_id).order_by(Keyword.id):
     print(kw, 'status_count:', count)
print('Filtered:')
for kw, count in ss.query(Keyword, stmt.c.status_count).
     filter(stmt.c.status_count>=1).outerjoin(stmt, Keyword.id==stmt.c.keyword_id).order_by(Keyword.id):
     print(kw, 'status_count:', count)

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

链接：https://www.jianshu.com/p/4867f4d33a3b