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/b0619e40-c5d5-4590-a61e-7d22aa6ad993n%40googlegroups.com.
