Wednesday, 25 October 2023

Form a "chain" of relationship through SQLAlchemy

Say I have a database where I want to model a many-to-many relationship between users and the websites they have access to.

I have the tables users and websites, and I create a third users_have_sites table with two foreign keys to modelize this many-to-many relationship.

With SQLAlchemy, this looks something like this:

from sqlalchemy.ext.declarative import DeclarativeBase
from sqlalchemy import Column, Integer, Foreignkey

Base = DeclarativeBase()

class User(Base):
     __table_name__ = "users"
     id = Column(Integer, primary_key=True)
     link = relationship("UserHasWebsite", back_populates="user")
     ... # Other columns
     
class Website(Base):
    ___table_name__ = "websites"
    id = Column(Integer, primary_key=True)
    link = relationship("UserHasWebsite", back_populates="website")
    ...

class UserHasWebsite(Base):
    __table_name__ = "users_have_websites"
    id = Column(Integer, primary_key=True)
    user = relationship("User", back_populates="link" )
    website = relationship("Website", back_populates="link")

I can get a list of Website instances linked to an user by calling [link.website for website in user.link], but I was wondering if there was the possibility to pass this "chained" relationship in the definition of the class, so that I could directly call an attribute user.websites



from Form a "chain" of relationship through SQLAlchemy

No comments:

Post a Comment