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的任意命令来操作
- 新建几个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
