Basic Relationship Patterns

A quick walkthrough of the basic relational patterns.

The imports used for each of the following sections is as follows:

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()

One To Many

A one to many relationship places a foreign key on the child table referencingthe parent. relationship() is then specified on the parent, as referencinga collection of items represented by the child:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

To establish a bidirectional relationship in one-to-many, where the “reverse”side is a many to one, specify an additional relationship() and connectthe two using the relationship.back_populates parameter:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

Child will get a parent attribute with many-to-one semantics.

Alternatively, the backref option may be usedon a single relationship() instead of usingback_populates:

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

Many To One

Many to one places a foreign key in the parent table referencing the child.relationship() is declared on the parent, where a new scalar-holdingattribute will be created:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Bidirectional behavior is achieved by adding a second relationship()and applying the relationship.back_populates parameterin both directions:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", back_populates="parents")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parents = relationship("Parent", back_populates="child")

Alternatively, the backref parametermay be applied to a single relationship(), such as Parent.child:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref="parents")

One To One

One To One is essentially a bidirectional relationship with a scalarattribute on both sides. To achieve this, the uselist flag indicatesthe placement of a scalar attribute instead of a collection on the “many” sideof the relationship. To convert one-to-many into one-to-one:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child = relationship("Child", uselist=False, back_populates="parent")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="child")

Or for many-to-one:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", back_populates="parent")
 
class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = relationship("Parent", back_populates="child", uselist=False)

As always, the relationship.backref and backref() functionsmay be used in lieu of the relationship.back_populates approach;to specify uselist on a backref, use the backref() function:

from sqlalchemy.orm import backref
 
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'))
    child = relationship("Child", backref=backref("parent", uselist=False))

Many To Many

Many to Many adds an association table between two classes. The associationtable is indicated by the secondary argument torelationship(). Usually, the Table uses the MetaDataobject associated with the declarative base class, so that the ForeignKeydirectives can locate the remote tables with which to link:

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table)
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

For a bidirectional relationship, both sides of the relationship contain acollection. Specify using relationship.back_populates, andfor each relationship() specify the common association table:

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        secondary=association_table,
        back_populates="parents")
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship(
        "Parent",
        secondary=association_table,
        back_populates="children")

When using the backref parameter instead ofrelationship.back_populates, the backref will automatically usethe same secondary argument for the reverse relationship:

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

The secondary argument of relationship() also accepts a callablethat returns the ultimate argument, which is evaluated only when mappers arefirst used. Using this, we can define the association_table at a laterpoint, as long as it’s available to the callable after all module initializationis complete:

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=lambda: association_table,
                    backref="parents")

With the declarative extension in use, the traditional “string name of the table”is accepted as well, matching the name of the table as stored in Base.metadata.tables:

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary="association",
                    backref="parents")

Deleting Rows from the Many to Many Table

A behavior which is unique to the secondary argument to relationship()is that the Table which is specified here is automatically subjectto INSERT and DELETE statements, as objects are added or removed from the collection.There is no need to delete from this table manually. The act of removing arecord from the collection will have the effect of the row being deleted on flush:

# row will be deleted from the "secondary" table
# automatically
myparent.children.remove(somechild)

A question which often arises is how the row in the “secondary” table can be deletedwhen the child object is handed directly to Session.delete():

session.delete(somechild)

There are several possibilities here:

• If there is a relationship() from Parent to Child, but there isnot a reverse-relationship that links a particular Child to each Parent,SQLAlchemy will not have any awareness that when deleting this particularChild object, it needs to maintain the “secondary” table that links it tothe Parent. No delete of the “secondary” table will occur.

• If there is a relationship that links a particular Child to each Parent,suppose it’s called Child.parents, SQLAlchemy by default will load inthe Child.parents collection to locate all Parent objects, and removeeach row from the “secondary” table which establishes this link. Note thatthis relationship does not need to be bidirectional; SQLAlchemy is strictlylooking at every relationship() associated with the Child objectbeing deleted.

• A higher performing option here is to use ON DELETE CASCADE directiveswith the foreign keys used by the database. Assuming the database supportsthis feature, the database itself can be made to automatically delete rows in the“secondary” table as referencing rows in “child” are deleted. SQLAlchemycan be instructed to forego actively loading in the Child.parentscollection in this case using the passive_deletesdirective on relationship(); see Using Passive Deletes for more detailson this.

Note again, these behaviors are only relevant to the secondary optionused with relationship(). If dealing with association tables thatare mapped explicitly and are not present in the secondary optionof a relevant relationship(), cascade rules can be used insteadto automatically delete entities in reaction to a related entity beingdeleted - see Cascades for information on this feature.

Association Object

The association object pattern is a variant on many-to-many: it’s usedwhen your association table contains additional columns beyond thosewhich are foreign keys to the left and right tables. Instead of usingthe secondary argument, you map a new classdirectly to the association table. The left side of the relationshipreferences the association object via one-to-many, and the associationclass references the right side via many-to-one. Below we illustratean association table mapped to the Association class whichincludes a column called extra_data, which is a string value thatis stored along with each association between Parent andChild:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child")
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association")
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

As always, the bidirectional version makes use of relationship.back_populatesor relationship.backref:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

Working with the association pattern in its direct form requires that childobjects are associated with an association instance before being appended tothe parent; similarly, access from parent to child goes through theassociation object:

# create parent, append a child via association
p = Parent()
a = Association(extra_data="some data")
a.child = Child()
p.children.append(a)
 
# iterate through child objects via association, including association
# attributes
for assoc in p.children:
    print(assoc.extra_data)
    print(assoc.child)

To enhance the association object pattern such that directaccess to the Association object is optional, SQLAlchemyprovides the Association Proxy extension. Thisextension allows the configuration of attributes which willaccess two “hops” with a single access, one “hop” to theassociated object, and a second to a target attribute.

Warning

The association object pattern does not coordinate changes with aseparate relationship that maps the association table as “secondary”.

Below, changes made to Parent.children will not be coordinatedwith changes made to Parent.child_associations orChild.parent_associations in Python; while all of these relationships will continueto function normally by themselves, changes on one will not show up in anotheruntil the Session is expired, which normally occurs automaticallyafter Session.commit():

class Association(Base):
    __tablename__ = 'association'
 
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
 
    child = relationship("Child", backref="parent_associations")
    parent = relationship("Parent", backref="child_associations")
 
class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
 
    children = relationship("Child", secondary="association")
 
class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

Additionally, just as changes to one relationship aren’t reflected in theothers automatically, writing the same data to both relationships will causeconflicting INSERT or DELETE statements as well, such as below where weestablish the same relationship between a Parent and Child objecttwice:

p1 = Parent()
c1 = Child()
p1.children.append(c1)
 
# redundant, will cause a duplicate INSERT on Association
p1.parent_associations.append(Association(child=c1))

It’s fine to use a mapping like the above if you know whatyou’re doing, though it may be a good idea to apply the viewonly=True parameterto the “secondary” relationship to avoid the issue of redundant changesbeing logged. However, to get a foolproof pattern that allows a simpletwo-object Parent->Child relationship while still using the associationobject pattern, use the association proxy extensionas documented at Association Proxy.