How to insert many-to-many relationship data using SQLAlchemy?

Question

What I am trying to figure out is a simple example on how to insert data into a many-to-many relationship. I am trying to make it where a user can be a part of many teams, while also a team would have many users.

I read the SQLAlchemy documentation but they don't show how to actually insert data into a many to many relationship. When I review SO similar questions, they don't go over inserting multiple elements - which is what I need. They mainly show how to create one through an association table or make changes.

class Teams(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    members = relationship('User', secondary=teams_association, back_populates='teams')

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    teams = relationship('Teams', secondary=teams_association, back_populates='members')

teams_association = Table(
    'teams_association',
    Base.metadata,
    Column('captain_id', Integer, ForeignKey('user.id')),
    Column('teams_id', Integer, ForeignKey('teams.id'))
    )

Inserting many-to-many data

session.add_all([
Teams(
    name='wildcats',
    members=??      # <-- Members [1,3,4] How would I do a multiple insert here?
),
Teams(
    name='horseman',
    members=??
),
Teams(
    name='jokers',
    members=??
) ])

session.commit()

session.add_all([
User(
    name='sam',
    teams=?     # [1,3] ex. A Member of Teams 1(wildcats) & 3(jokers)
    ),
User(
    name='ashley',
    teams=?
),
User(
    name='jackson',
    teams=?
),
User(
    name='ethan',,
    teams=?
),
User(
    name='miles',
    teams=?
) ])

Possible duplicate of [How to build many-to-many relations using SQLAlchemy: a good example](https://stackoverflow.com/questions/5756559/how-to-build-many-to-many-relations-using-sqlalchemy-a-good-example) — Eddwin Paz, Nov 06 '18 at 17:54
@eddwinpaz That has nothing to do with Inserts. It goes over how to add foreign keys to a association table. — Johnathan, Nov 06 '18 at 17:57
Does this answer your question? [Flask sqlalchemy many-to-many insert data](https://stackoverflow.com/questions/25668092/flask-sqlalchemy-many-to-many-insert-data) — ggorlen, Apr 05 '23 at 17:40

benvc · Answer 1 · 2018-11-06T18:59:39.207

Following are a few examples of inserting records in your many-to-many association table using the SQLAlchemy ORM that may be helpful to you (shown step by step to hopefully make it a bit easier to follow what needs to happen in each of the different scenarios).

If you are trying to create records in your association table that associate users "sam" and "ashley" with the "wildcats" team, you could do something like the following (assuming you have already created User records for "sam" and "ashley" and a Teams record for "wildcats"):

members = session.query(User).filter(User.name.in_(['sam', 'ashley'])).all()
wildcats = session.query(Teams).filter(Teams.name == 'wildcats').first()
wildcats.members = [member for member in members]
session.commit()

Or if you are trying to create a new "wildcats" Teams record with associated users "sam" and "ashley", then you can modify the above to first create the Teams record. For example:

wildcats = Teams()
wildcats.name = 'wildcats'
members = session.query(User).filter(User.name.in_(['sam', 'ashley'])).all()
wildcats.members = [member for member in members]
session.add(wildcats)
session.commit()

Or if you are trying to create multiple new User records and associate them with multiple Teams records, then you could do something like the following:

sam = User()
sam.name = 'sam'
sam_teams = session.query(Teams).filter(Teams.name.in_(['wildcats', 'jokers'])).all()
sam.teams = [team for team in sam_teams]
ashley = User()
ashley.name = 'ashley'
ashley_teams = session.query(Teams).filter(Teams.name.in_(['wildcats', 'horseman'])).all()
ashley.teams = [team for team in ashley_teams]
session.add_all([sam, ashley])
session.commit()

How to insert many-to-many relationship data using SQLAlchemy?

1 Answers1