Populating a SQLAlchemy many-to-many relationship using ID's instead of objects

Question

The situation:

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() 

...where prince, olivia and brittany are all <Person> instances, and my_party is a <SexyParty> instance.

My question:

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 

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.

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.

Answer 1

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()