通过SqlAlchemy中的关联对象进行多对多，自指，非对称关系(推特模型) [英] Many-to-many, self-referential, non-symmetrical relationship (twitter model) via Association Object in SqlAlchemy

问题描述

如何最好地在SqlAlchemy中实现多对多，自我参照，非对称关系(例如Twitter)?我想使用一个关联对象(让我们将此类称为跟随")，以便可以拥有与该关系关联的其他属性.

How would one best implement a many-to-many, self-referential, non symmetrical relationship (think Twitter) in SqlAlchemy? I want to use an association object (let's call this class "Follow") so that I can have additional attributes associated with the relationship.

我已经看到了很多使用关联表的示例，但是没有一个像我上面描述的那样.这是我到目前为止的内容:

I've seen plenty of examples which use an association tables, but none like I've describe above. Here's what I have so far:

class UserProfile(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    full_name = Column(Unicode(80))
    gender = Column(Enum(u'M',u'F','D', name='gender'), nullable=False)
    description = Column(Unicode(280))
    followed = relationship(Follow, backref="followers") 

class Follow(Base):
    __tablename__ = 'follow'

    follower_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    followee_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    status = Column(Enum(u'A',u'B', name=u'status'), default=u'A')
    created = Column(DateTime, default=func.now())
    followee = relationship(UserProfile, backref="follower")

有想法吗?

推荐答案

在这里.在这里，这可以通过使用裸链接表制作的多对多的优点来进行改进.

This is already almost answered in here. Here this is improved by having the advantages of a many-to-many made with a bare link table.

我在SQL和SqlAlchemy中都不擅长，但是由于我在较长时间内一直牢记此问题，因此我试图找到一种兼具两个优点的解决方案:具有附加属性的关联对象和 direct 关联，例如裸链接表(该链接表本身不提供对象).受操作者的其他建议的刺激，以下内容对我来说似乎很安静:

I'm not good in SQL and neither in SqlAlchemy but since I had this problem in mind for some longer time, I tried to find a solution that has both advantages: an association object with additional attributes and a direct association like with a bare link table (which doesn't provide an object on its own for the association). Stimulated by additional suggestions of the op the following seems quiet nice to me:

#!/usr/bin/env python3
# coding: utf-8

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') #, echo=True)
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]) # doesn't work with followed_by

  sess.commit()

# uncomment the next line and run script once to create some sample data
# create_sample_data(session)

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构造函数的参数oder似乎不合逻辑，但仅以这种方式起作用(对此进行了解释

It seems indispensible to define two relations for the Association Object. The association_proxy method seems to be not ideally tailored for self-referential relations. The argument oder of the Follow constructor doesn't seem logical to me but works only this way (this is explained here).

在第117页的里克·科普兰-基本Sqlalchemy 中，您可以找到以下内容关于relationship()的secondary参数的说明:

In the book Rick Copeland - Essential Sqlalchemy on page 117 you find the following note regarding the secondary-parameter to relationship():

请注意，如果您使用的是SQLAlchemy的M:N功能 关系，联接表应仅用于联接两者 表一起使用，不存储辅助属性.如果你需要 使用中间联接表存储附加属性 关系，则应改用两个1:N关系.

Note that, if you are using SQLAlchemy’s ability to do M:N relationships, the join table should only be used to join the two tables together, not to store auxilliary properties. If you need to use the intermediate join table to store addi- tional properties of the relation, you should use two 1:N relations instead.

抱歉，这有点冗长，但是我喜欢可以直接复制，粘贴和执行的代码.这适用于Python 3.4和SqlAlchemy 0.9，但也可能适用于其他版本.

Sorry for that this is a little bit verbose but I like code that I can copy, paste, and execute directly. This works with Python 3.4 and SqlAlchemy 0.9 but likely also with other versions.

这篇关于通过SqlAlchemy中的关联对象进行多对多，自指，非对称关系(推特模型)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！