Hi, A colleague helped me to write the relationship() part, the working code with relationships is now :
class UserOrgRole(Base): __tablename__ = "user_org_role" user_id = Column(ForeignKey("user.id", ondelete="CASCADE"), primary_key=True ) org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True) role_id = Column(ForeignKey("role.id", ondelete="CASCADE"), primary_key=True ) user = relationship("User", back_populates="orgsroles") org = relationship("Org", back_populates="usersroles") role = relationship("Role", back_populates="usersorgs") class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) username = Column(Text, index=True, nullable=False) fullname = Column(Text, nullable=False) account_type = Column(Text, nullable=False) orgsroles = relationship("UserOrgRole", back_populates="user") class Org(Base): __tablename__ = "org" id = Column(Integer, primary_key=True) name = Column(Text, index=True, nullable=False) slug = Column(Text, index=True, nullable=False) created_at = Column(DateTime) usersroles = relationship("UserOrgRole", back_populates="org") class Role(Base): __tablename__ = "role" id = Column(Integer, primary_key=True) name = Column(Text, index=True, nullable=False) usersorgs = relationship("UserOrgRole", back_populates="role") Have a nice week Françoise Le vendredi 7 janvier 2022 à 16:16:58 UTC+1, fco...@gmail.com a écrit : > Hi all, > > I would like to create some association of, at least, 3 tables User, Org > (organisation) and Role : a User is given a Role on an Organisation. > > So I began writing the following snippet but I am stucked as I do not know > how I should write relationship() for User to be able to refer Orgs and > Roles, Org to refer Users and Roles ... > > I would like to avoid tricky code, do you have some advice on how I should > do ? > > # > https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object > from datetime import datetime > from sqlalchemy import create_engine > from sqlalchemy import Column, ForeignKey, Integer, Text, DateTime > from sqlalchemy.orm import declarative_base, relationship > from sqlalchemy.orm.session import Session > > Base = declarative_base() > > class UserOrgRole(Base): > __tablename__ = "user_org_role" > > user_id = Column( > ForeignKey("user.id", ondelete="CASCADE"), primary_key=True > ) > org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True > ) > # Does it make sense ? > role_id = Column( > ForeignKey("role.id", ondelete="CASCADE"), primary_key=True > ) > > # I do not know what the relationship could back populate, org or role ??? > user = relationship("User") > org = relationship("Org") > role = relationship("Role") > > > class User(Base): > __tablename__ = "user" > > id = Column(Integer, primary_key=True) > username = Column(Text, index=True, nullable=False) > fullname = Column(Text, nullable=False) > account_type = Column(Text, nullable=False) > > def __repr__(self): > return ( > f"<User (username={self.username}, fullname={self.fullname}, " > f"account_type={self.account_type})>" > ) > > > class Org(Base): > __tablename__ = "org" > > id = Column(Integer, primary_key=True) > name = Column(Text, index=True, nullable=False) > slug = Column(Text, index=True, nullable=False) > created_at = Column(DateTime) > > def __repr__(self): > return ( > f"<Org (name={self.name}, slug={self.slug}, " > f"created_at={self.created_at})>" > ) > > > class Role(Base): > __tablename__ = "role" > > id = Column(Integer, primary_key=True) > name = Column(Text, index=True, nullable=False) > > def __repr__(self): > return f"<Role (name={self.name})>" > > > if __name__ == "__main__": > engine = create_engine( > "sqlite:///association_object_ternary.db", echo=False > ) > > Base.metadata.drop_all(engine) > Base.metadata.create_all(engine) > > with Session(engine) as session: > # create parent, append a child via association > u1 = User( > username="jlondon", > fullname="Jack London", > account_type="member", > ) > o1 = Org(name="o1", slug="o1", created_at=datetime.utcnow()) > owner = Role(name="owner") > > uor1 = UserOrgRole() > uor1.user = u1 > uor1.org = o1 > uor1.role = owner > > with session.begin(): > session.add(u1) > session.add(o1) > session.add(owner) > > session.add(uor1) > > Thanks for your help and for this nice library which has a great > documentation. > Françoise > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/771739c6-e842-4ff2-8364-be20599bbea9n%40googlegroups.com.