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

flask-sqlalchemy, many-to-many, python, sql, sqlalchemy

Solution

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

Problem

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.

Original source