python – 多对多关联表上的SQLAlchemy关系
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python – 多对多关联表上的SQLAlchemy关系,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4628字,纯文字阅读大概需要7分钟。
内容图文
![python – 多对多关联表上的SQLAlchemy关系](/upload/InfoBanner/zyjiaocheng/908/8286170028c04a32b4f66046b289db4a.jpg)
我正在尝试与另一个多对多关系建立关系,代码如下所示:
from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
supervision_association_table = Table('supervision', Base.metadata,
Column('supervisor_id', Integer, ForeignKey('supervisor.id'), primary_key=True),
Column('client_id', Integer, ForeignKey('client.id'), primary_key=True)
)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
class Supervisor(User):
__tablename__ = 'supervisor'
__mapper_args__ = {'polymorphic_identity': 'supervisor'}
id = Column(Integer, ForeignKey('user.id'), primary_key = True)
schedules = relationship("Schedule", backref='supervisor')
class Client(User):
__tablename__ = 'client'
__mapper_args__ = {'polymorphic_identity': 'client'}
id = Column(Integer, ForeignKey('user.id'), primary_key = True)
supervisor = relationship("Supervisor", secondary=supervision_association_table,
backref='clients')
schedules = relationship("Schedule", backref="client")
class Schedule(Base):
__tablename__ = 'schedule'
__table_args__ = (
ForeignKeyConstraint(['client_id', 'supervisor_id'], ['supervision.client_id', 'supervision.supervisor_id']),
)
id = Column(Integer, primary_key=True)
client_id = Column(Integer, nullable=False)
supervisor_id = Column(Integer, nullable=False)
engine = create_engine('sqlite:///temp.db')
db_session = scoped_session(sessionmaker(bind=engine))
Base.metadata.create_all(bind=engine)
我想要做的是将时间表与特定的客户 – 主管关系联系起来,尽管我还没有找到如何做到这一点.通过SQLAlchemy文档,我发现了一些提示,导致Schedule-Table上的ForeignKeyConstraint.
如何指定关联以使此关联有效?
解决方法:
您需要映射supervision_association_table,以便您可以创建与之关系.
我可能会在这里讨论一些事情,但似乎因为你在这里有多对多,你真的不能有Client.schedules – 如果我说Client.schedules.append(some_schedule),那么“监督”中的哪一行是它指向?因此,下面的示例为那些加入每个SupervisorAssociation的Schedule集合的访问者提供了只读“汇总”访问器. association_proxy扩展用于在方便时隐藏SupervisionAssociation对象的详细信息.
from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from itertools import chain
Base = declarative_base()
class SupervisionAssociation(Base):
__tablename__ = 'supervision'
supervisor_id = Column(Integer, ForeignKey('supervisor.id'), primary_key=True)
client_id = Column(Integer, ForeignKey('client.id'), primary_key=True)
supervisor = relationship("Supervisor", backref="client_associations")
client = relationship("Client", backref="supervisor_associations")
schedules = relationship("Schedule")
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
class Supervisor(User):
__tablename__ = 'supervisor'
__mapper_args__ = {'polymorphic_identity': 'supervisor'}
id = Column(Integer, ForeignKey('user.id'), primary_key = True)
clients = association_proxy("client_associations", "client",
creator=lambda c: SupervisionAssociation(client=c))
@property
def schedules(self):
return list(chain(*[c.schedules for c in self.client_associations]))
class Client(User):
__tablename__ = 'client'
__mapper_args__ = {'polymorphic_identity': 'client'}
id = Column(Integer, ForeignKey('user.id'), primary_key = True)
supervisors = association_proxy("supervisor_associations", "supervisor",
creator=lambda s: SupervisionAssociation(supervisor=s))
@property
def schedules(self):
return list(chain(*[s.schedules for s in self.supervisor_associations]))
class Schedule(Base):
__tablename__ = 'schedule'
__table_args__ = (
ForeignKeyConstraint(['client_id', 'supervisor_id'],
['supervision.client_id', 'supervision.supervisor_id']),
)
id = Column(Integer, primary_key=True)
client_id = Column(Integer, nullable=False)
supervisor_id = Column(Integer, nullable=False)
client = association_proxy("supervisor_association", "client")
engine = create_engine('sqlite:///temp.db', echo=True)
db_session = scoped_session(sessionmaker(bind=engine))
Base.metadata.create_all(bind=engine)
c1, c2 = Client(), Client()
sp1, sp2 = Supervisor(), Supervisor()
sch1, sch2, sch3 = Schedule(), Schedule(), Schedule()
sp1.clients = [c1]
c2.supervisors = [sp2]
c2.supervisor_associations[0].schedules = [sch1, sch2]
c1.supervisor_associations[0].schedules = [sch3]
db_session.add_all([c1, c2, sp1, sp2, ])
db_session.commit()
print c1.schedules
print sp2.schedules
内容总结
以上是互联网集市为您收集整理的python – 多对多关联表上的SQLAlchemy关系全部内容,希望文章能够帮你解决python – 多对多关联表上的SQLAlchemy关系所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。