使用ID而不是对象填充SQLAlchemy多对多关系 [英] Populating a SQLAlchemy many-to-many relationship using ID's instead of objects
问题描述
情况:
因此,我在SQLAlchemy中使用关联表.
例如,一个人可以参加许多聚会,而一个聚会可以有许多人作为客人:
So, I have a basic many-to-many relationship in SQLAlchemy using an association table.
For example, a person can attend many parties, and a party can have many persons as guests:
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = db.Column(db.String(50))
class SexyParty(Base):
__tablename__ = 'sexy_party'
id = Column(Integer, primary_key=True)
guests = relationship('Person', secondary='guest_association',
lazy='dynamic', backref='parties')
guest_association = Table(
'guest_association',
Column('user_id', Integer(), ForeignKey('person.id')),
Column('sexyparty.id', Integer(), ForeignKey('sexyparty.id'))
)
通常,如果我想添加参加聚会的嘉宾列表,我会执行以下操作:
Normally if I wanted to add a list of guests to a party, I would do something like this:
my_guests = [prince, olivia, brittany, me]
my_party.guests = guests
db.session.commit()
......其中王子,奥利维亚和布列塔尼都是<Person>
实例,而my_party是<SexyParty>
实例.
...where prince, olivia and brittany are all <Person>
instances, and my_party is a <SexyParty>
instance.
我的问题:
我想使用个人ID而不是实例将客人添加到聚会中. 例如:
I'd like to add guests to a party using person ID's rather than instances. For example:
guest_ids = [1, 2, 3, 5]
my_party.guests = guest_ids # <-- This fails, because guest_ids
# are not <Person> instances
我总是可以从数据库中加载实例,但这将导致不必要的DB查询,只是为了设置简单的多对多关系.
I could always load the instances from the databases, but that would entail an unnecessary DB query just to set a simple many-to-many relationships.
如何使用person_id的列表设置.guests
属性?
必须使用一种简单的方法来完成此操作,因为关联表最终还是使用ID来表示多对多关系...
How would I go about setting the .guests
attribute using a list of person_id's?
There has to be a simple way to do this since the association table ultimately represents the many-to-many relationship using ID's anyway...
预先感谢,希望问题清楚.
thanks in advance, hope the question is clear.
推荐答案
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(50))
class SexyParty(Base):
__tablename__ = 'sexy_party'
id = Column(Integer, primary_key=True)
guests = relationship('Person', secondary='guest_association',
lazy='dynamic', backref='parties')
guest_association = Table(
'guest_association', Base.metadata,
Column('user_id', Integer(), ForeignKey('person.id'), primary_key=True),
Column('sexyparty_id', Integer(), ForeignKey('sexy_party.id'), primary_key=True)
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
p1 = Person(id=1, name='p1')
p2 = Person(id=2, name='p2')
p3 = Person(id=3, name='p3')
p4 = Person(id=4, name='p4')
sp1 = SexyParty(id=1)
sess.add_all([sp1, p1, p2, p3, p4])
sess.commit()
# method one. use insert()
sess.execute(guest_association.insert().values([(1, 1), (2, 1)]))
# method two. map, optional association proxy
from sqlalchemy.ext.associationproxy import association_proxy
class GuestAssociation(Base):
__table__ = guest_association
party = relationship("SexyParty", backref="association_recs")
SexyParty.association_ids = association_proxy(
"association_recs", "user_id",
creator=lambda uid: GuestAssociation(user_id=uid))
sp1.association_ids.extend([3, 4])
sess.commit()
这篇关于使用ID而不是对象填充SQLAlchemy多对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!