# 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