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 — Eddwin Paz
– Eddwin Paz, Commented Nov 6, 2018 at 17:54
@eddwinpaz That has nothing to do with Inserts. It goes over how to add foreign keys to a association table. — Johnathan
– Johnathan, Commented Nov 6, 2018 at 17:57
Does this answer your question? Flask sqlalchemy many-to-many insert data — ggorlen
– ggorlen, Commented Apr 5, 2023 at 17:40

benvc · Accepted Answer · 2018-11-06 18:59:39Z

15

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

edited Nov 6, 2018 at 18:59

answered Nov 6, 2018 at 18:15

benvc

15.3k4 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pynchia yesterday

what about adding new members to a team which has members already? team.members.add(charlie) ? I mean, does the assignment team.members = [charlie] add charlie on top of the existing members or replaces all the members?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related