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, [email protected] 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/771739c6-e842-4ff2-8364-be20599bbea9n%40googlegroups.com.
