CODEWITHSUNDEEP
pythonsqlsqlalchemy
choeger
choeger

Reputation: 3567

Can SQLAlchemy express one-to-many relations via association tables?

I am trying to access an existing DB with SQLAlchemy. (Thus, I cannot easily change the schema or the existing data. I am also not sure why the schema is structured the way it is.)

Consider the following pattern:

association_table = Table(
    "association_table",
    Base.metadata,
    Column("child_id", ForeignKey("child_table.id"), primary_key=True),
    Column("parent_id", ForeignKey("parent_table.id")),
)


class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent", secondary=association_table)


class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)

In this example, note how there is a primary key in the association table. This should model a one-to-many relationship (one parent per child, many children per parent), even though the association table would not be necessary for that purpose.

The documentation does not mention this at all, however. secondary is only ever associated with many-to-many relationships and if I translate this pattern to real-world code, the parent field is indeed created as a collection.

Is there a way to convince SQLAlchemy to read a one-to-many relationship from an association table?

Upvotes: 0

Views: 43

Answers (1)

ljmc
ljmc

Reputation: 5264

You can have a secondary table link two tables in a one to many relationship, you just need to have a one-to-one side (with uselist=False) and a one-to-many side.

However, I would ask you to think why you'd want this added complexity.

Full demo:

from sqlalchemy import Column, ForeignKey, Integer, Table, create_engine
from sqlalchemy.orm import Session, declarative_base, relationship

Base = declarative_base()


association_table = Table(
    "association_table",
    Base.metadata,
    Column("parent_id", ForeignKey("parent_table.id"), primary_key=True),
    Column("child_id", ForeignKey("child_table.id"), primary_key=True),
)


class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        secondary=association_table,
        back_populates="parent",
    )


class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)
    parent = relationship(
        "Parent",
        secondary=association_table,
        back_populates="children",
        uselist=False,  # only one parent
    )


engine = create_engine("sqlite://", echo=True, future=True)

Base.metadata.create_all(engine)

with Session(engine) as session:

    alice = Parent()
    bob = Child()
    charlie = Child()

    alice.children.extend([bob, charlie])

    session.add(alice)
    session.flush()

emits

CREATE TABLE parent_table (
        id INTEGER NOT NULL, 
        PRIMARY KEY (id)
)
CREATE TABLE child_table (
        id INTEGER NOT NULL, 
        PRIMARY KEY (id)
)
CREATE TABLE association_table (
        parent_id INTEGER NOT NULL, 
        child_id INTEGER NOT NULL, 
        PRIMARY KEY (parent_id, child_id), 
        FOREIGN KEY(parent_id) REFERENCES parent_table (id), 
        FOREIGN KEY(child_id) REFERENCES child_table (id)
)
INSERT INTO child_table DEFAULT VALUES
INSERT INTO child_table DEFAULT VALUES
INSERT INTO parent_table DEFAULT VALUES
INSERT INTO association_table (parent_id, child_id) VALUES (1, 1), (1, 2)

Upvotes: 1

Related Questions