这个问题已经在
这里几乎得到了解决。这里通过使用裸链接表实现了多对多关系的优势,进行了改进。
我不擅长SQL和SqlAlchemy,但由于我已经考虑了很长时间这个问题,我尝试找到一个既有关联属性对象又有直接关联的解决方案(就像裸链接表一样,它本身并没有为关联提供对象)。受到提问者额外建议的刺激,以下解决方案对我来说似乎非常好:
import sqlalchemy as sqAl
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
engine = sqAl.create_engine('sqlite:///m2m-w-a2.sqlite')
metadata = sqAl.schema.MetaData(bind=engine)
Base = declarative_base(metadata)
class UserProfile(Base):
__tablename__ = 'user'
id = sqAl.Column(sqAl.Integer, primary_key=True)
full_name = sqAl.Column(sqAl.Unicode(80))
gender = sqAl.Column(sqAl.Enum('M','F','D', name='gender'), default='D', nullable=False)
description = sqAl.Column(sqAl.Unicode(280))
following = association_proxy('followeds', 'followee')
followed_by = association_proxy('followers', 'follower')
def follow(self, user, **kwargs):
Follow(follower=self, followee=user, **kwargs)
def __repr__(self):
return 'UserProfile({})'.format(self.full_name)
class Follow(Base):
__tablename__ = 'follow'
followee_id = sqAl.Column(sqAl.Integer, sqAl.ForeignKey('user.id'), primary_key=True)
follower_id = sqAl.Column(sqAl.Integer, sqAl.ForeignKey('user.id'), primary_key=True)
status = sqAl.Column(sqAl.Enum('A','B', name=u'status'), default=u'A')
created = sqAl.Column(sqAl.DateTime, default=sqAl.func.now())
followee = relationship(UserProfile, foreign_keys=followee_id, backref='followers')
follower = relationship(UserProfile, foreign_keys=follower_id, backref='followeds')
def __init__(self, followee=None, follower=None, **kwargs):
"""necessary for creation by append()ing to the association proxy 'following'"""
self.followee = followee
self.follower = follower
for kw,arg in kwargs.items():
setattr(self, kw, arg)
Base.metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
def create_sample_data(sess):
import random
usernames, fstates, genders = ['User {}'.format(n) for n in range(4)], ('A', 'B'), ('M','F','D')
profs = []
for u in usernames:
user = UserProfile(full_name=u, gender=random.choice(genders))
profs.append(user)
sess.add(user)
for u in [profs[0], profs[3]]:
for fu in profs:
if u != fu:
u.follow(fu, status=random.choice(fstates))
profs[1].following.append(profs[3])
sess.commit()
profs = session.query(UserProfile).all()
print( '{} follows {}: {}'.format(profs[0], profs[3], profs[3] in profs[0].following))
print('{} is followed by {}: {}'.format(profs[0], profs[1], profs[1] in profs[0].followed_by))
for p in profs:
print("User: {0}, following: {1}".format(
p.full_name, ", ".join([f.full_name for f in p.following])))
for f in p.followeds:
print(" " * 25 + "{0} follow.status: '{1}'"
.format(f.followee.full_name, f.status))
print(" followed_by: {1}".format(
p.full_name, ", ".join([f.full_name for f in p.followed_by])))
for f in p.followers:
print(" " * 25 + "{0} follow.status: '{1}'"
.format(f.follower.full_name, f.status))
似乎有必要为关联对象定义两个关系。 association_proxy方法似乎不是为自引用关系量身定制的。对于Follow
构造函数的参数顺序,我觉得不太合理,但只有这种方式才能正常工作(这在这里有解释)。
在Rick Copeland - Essential Sqlalchemy一书的第117页,您会发现有关relationship()
的secondary
参数的以下注释:
请注意,如果您使用SQLAlchemy的M:N关系功能,则联接表应仅用于将两个表连接在一起,而不是用于存储辅助属性。如果您需要使用中间联接表来存储关系的其他属性,则应改为使用两个1:N关系。对于代码过于冗长,我感到抱歉,但我喜欢可以直接复制、粘贴和执行的代码。这适用于Python 3.4和SqlAlchemy 0.9,但可能也适用于其他版本。
user.id -> follow.follower_id
或user.id -> follow.followee_id
赋值的歧义,而不明确定义primaryjoin/secondardjoin。 - TNTcreator
参数到association_proxy
,以便SA
知道如何创建Follow
实例:following = association_proxy('followeds', 'followee', creator=lambda followee: Follow(followee=followee)); followed_by = association_proxy('followers', 'follower', creator=lambda follower: Follow(followee=follower))
- vanfollowed_by = association_proxy('followers', 'follower', creator=lambda follower: Follow(follower=follower))
)也可以工作,但是定义两者使我们不依赖于Follow
构造函数的参数顺序,并且我们可以更改签名,例如def __init__(self, follower=None, followee=None, **kwargs):
这感觉更合适。 - TNTprof1.following.append(prof2)
等同于prof1.followeds.append(Follow(user2))
,在append
方法中,follower
属性被“分配”(文档说参数,但这对我来说似乎不准确)。因此,我的理解是,即使self
在creator
lambda中具有有效(和正确)的值,它也会被append
方法覆盖。 - TNT