Trying to add a record to tables with many-to-many relationship using POST - FastAPI + SQLalchemy

Question

I have some pydantic and SQLalchemy models

game_users = Table('game_users', Base.metadata,
                     Column('game_id', ForeignKey('games.id'), primary_key=True),
                     Column('user_id', ForeignKey('users.id'), primary_key=True)
                     )


class Game(Base):
    __tablename__ = 'games'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    users = relationship("User", secondary="game_users", back_populates='games')


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    age = Column(Integer)
    email = Column(String, nullable=False, unique=True)
    games = relationship("Game", secondary="game_users", back_populates='users')


class UserBase(BaseModel):
    id: int
    name: str
    age: int = Query(ge=0, le=100)
    email: str

    class Config:
        orm_mode = True


class GameBase(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True


class UsersOut(UserBase):
    games: List[GameBase]


class GamesOut(GameBase):
    users: List[UserBase]

And I need to add entries by POST method, but I don't know how to do it exactly. I tried something like this:

@app.post('/connect/{uid}/{gid}')
def connect_to_game(uid: int, gid: int, db: Session = Depends(get_db)):
    game = db.query(Game).filter(Game.id == gid).first()
    user = db.query(User).filter(User.id == uid).first()

    user_games = user(games=[game.id])
    game_users = game(users=[user.id])

    db.add_all([user_games, game_users])
    db.commit()

    return f'{game.name} successfully connected to {user.name}'

But it, certainly, doesn't work. I tried to find information in pydantic and FastAPI documentations, but I couldn't. So I'll be really appreciated for any help or ideas.

Answer 1

I define models as:

game_users = Table('game_users', DBBase.metadata,
                   Column('game_id', ForeignKey('games.id'), primary_key=True),
                   Column('user_id', ForeignKey('users.id'), primary_key=True)
                   )


class Game(DBBase):
    __tablename__ = 'games'
    id = Column(Integer, primary_key=True)
    name = Column(String(10), nullable=False)
    users = relationship("User", secondary=game_users, backref='games')


class User(DBBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(10),  nullable=False)

simplize back_populates by backref.

then assosicate objects:

>>> g1 = Game(name='g1')
>>> g2 = Game(name='g2')
>>> u1 = User(name='u1')
>>> u2 = User(name='u2')
>>> g1.users.extend([u1,u2])
>>> g2.users.extend([u1,u2])
>>> g1.users
[<models.User object at 0x105166b50>, <models.User object at 0x10519cfa0>]
>>> u1.games
[<models.Game object at 0x10512a280>, <models.Game object at 0x105166130>]
>>> s.add_all([g1,g2,u1,u2])  # s is db session
>>> s.commit()

So you just need to use the same type of adding relationships as above in your post method:

@app.post('/connect/{uid}/{gid}')
def connect_to_game(uid: int, gid: int, db: Session = Depends(get_db)):

    game = db.query(Game).filter(Game.id == gid).first()
    user = db.query(User).filter(User.id == uid).first()

    game.users.append(user)
    # no new instance, no need `add_all`
    # db.add_all([user_games, game_users]) 
    db.commit()

    return f'{game.name} successfully connected to {user.name}'

Reference sqlalchemy many to many