Basic Relationship Patterns

    The setup for each of the following sections is as follows:

    As SQLAlchemy has evolved, different ORM configurational styles have emerged. For examples in this section and others that use annotated mappings with Mapped, the corresponding non-annotated form should use the desired class, or string class name, as the first argument passed to . The example below illustrates the form used in this document, which is a fully Declarative example using PEP 484 annotations, where the construct is also deriving the target class and collection type from the Mapped annotation, which is the most modern form of SQLAlchemy Declarative mapping:

    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[list["Child"]] = relationship(back_populates="parent")
    8. class Child(Base):
    9.     __tablename__ = "child_table"
    11.     id: Mapped[int] = mapped_column(primary_key=True)
    12.     parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"))
    13.     parent: Mapped["Parent"] = relationship(back_populates="children")

    In contrast, using a Declarative mapping without annotations is the more “classic” form of mapping, where requires all parameters passed to it directly, as in the example below:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id = mapped_column(Integer, primary_key=True)
    5.     children = relationship("Child", back_populates="parent")
    8. class Child(Base):
    9.     __tablename__ = "child_table"
    11.     id = mapped_column(Integer, primary_key=True)
    12.     parent_id = mapped_column(ForeignKey("parent_table.id"))
    13.     parent = relationship("Parent", back_populates="children")

    Finally, using Imperative Mapping, which is SQLAlchemy’s original mapping form before Declarative was made (which nonetheless remains preferred by a vocal minority of users), the above configuration looks like:

    1. registry.map_imperatively(
    2.     Parent,
    3.     parent_table,
    4.     properties={"children": relationship("Child", back_populates="parent")},
    5. )
    7. registry.map_imperatively(
    8.     Child,
    9.     child_table,
    10.     properties={"parent": relationship("Parent", back_populates="children")},
    11. )

    Additionally, the default collection style for non-annotated mappings is list. To use a set or other collection without annotations, indicate it using the parameter:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id = mapped_column(Integer, primary_key=True)
    5.     children = relationship("Child", collection_class=set, ...)

    Detail on collection configuration for relationship() is at .

    Additional differences between annotated and non-annotated / imperative styles will be noted as needed.

    One To Many

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

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[list["Child"]] = relationship()
    8. class Child(Base):
    9.     __tablename__ = "child_table"
    11.     id: Mapped[int] = mapped_column(primary_key=True)
    12.     parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"))

    To establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one, specify an additional relationship() and connect the two using the parameter, using the attribute name of each relationship() as the value for on the other:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[list["Child"]] = relationship(back_populates="parent")
    8. class Child(Base):
    9.     __tablename__ = "child_table"
    11.     id: Mapped[int] = mapped_column(primary_key=True)
    12.     parent_id: Mapped[int] = mapped_column(ForeignKey("parent_table.id"))
    13.     parent: Mapped["Parent"] = relationship(back_populates="children")

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

    Using annotated Declarative mappings, the type of collection used for the is derived from the collection type passed to the Mapped container type. The example from the previous section may be written to use a set rather than a list for the Parent.children collection using Mapped[set["Child"]]:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[set["Child"]] = relationship(back_populates="parent")

    When using non-annotated forms including imperative mappings, the Python class to use as a collection may be passed using the parameter.

    See also

    Customizing Collection Access - contains further detail on collection configuration including some techniques to map to dictionaries.

    Configuring Delete Behavior for One to Many

    It is often the case that all Child objects should be deleted when their owning Parent is deleted. To configure this behavior, the delete cascade option described at is used. An additional option is that a Child object can itself be deleted when it is deassociated from its parent. This behavior is described at delete-orphan.

    See also

    Using foreign key ON DELETE cascade with ORM relationships

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

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     child_id: Mapped[int] = mapped_column(ForeignKey("child_table.id"))
    6.     child: Mapped["Child"] = relationship()
    9. class Child(Base):
    10.     __tablename__ = "child_table"
    12.     id: Mapped[int] = mapped_column(primary_key=True)

    The above example shows a many-to-one relationship that assumes non-nullable behavior; the next section, Nullable Many-to-One, illustrates a nullable version.

    Bidirectional behavior is achieved by adding a second and applying the relationship.back_populates parameter in both directions, using the attribute name of each as the value for relationship.back_populates on the other:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     child_id: Mapped[int] = mapped_column(ForeignKey("child_table.id"))
    6.     child: Mapped["Child"] = relationship(back_populates="parents")
    9. class Child(Base):
    10.     __tablename__ = "child_table"
    12.     id: Mapped[int] = mapped_column(primary_key=True)
    13.     parents: Mapped[list["Parent"]] = relationship(back_populates="child")

    Nullable Many-to-One

    In the preceding example, the Parent.child relationship is not typed as allowing None; this follows from the Parent.child_id column itself not being nullable, as it is typed with Mapped[int]. If we wanted Parent.child to be a nullable many-to-one, we can set both Parent.child_id and Parent.child to be Optional[], in which case the configuration would look like:

    1. from typing import Optional
    4. class Parent(Base):
    5.     __tablename__ = "parent_table"
    7.     id: Mapped[int] = mapped_column(primary_key=True)
    8.     child_id: Mapped[Optional[int]] = mapped_column(ForeignKey("child_table.id"))
    9.     child: Mapped[Optional["Child"]] = relationship(back_populates="parents")
    12. class Child(Base):
    13.     __tablename__ = "child_table"
    15.     id: Mapped[int] = mapped_column(primary_key=True)
    16.     parents: Mapped[list["Parent"]] = relationship(back_populates="child")

    Above, the column for Parent.child_id will be created in DDL to allow NULL values. When using mapped_column() with explicit typing declarations, the specification of child_id: Mapped[Optional[int]] is equivalent to setting to True on the Column, whereas child_id: Mapped[int] is equivalent to setting it to False. See for background on this behavior.

    Tip

    If using Python 3.10 or greater, PEP 604 syntax is more convenient to indicate optional types using | None, which when combined with postponed annotation evaluation so that string-quoted types aren’t required, would look like:

    1. from __future__ import annotations
    4. class Parent(Base):
    5.     __tablename__ = "parent_table"
    7.     id: Mapped[int] = mapped_column(primary_key=True)
    8.     child_id: Mapped[int | None] = mapped_column(ForeignKey("child_table.id"))
    9.     child: Mapped[Child | None] = relationship(back_populates="parents")
    12. class Child(Base):
    13.     __tablename__ = "child_table"
    15.     id: Mapped[int] = mapped_column(primary_key=True)
    16.     parents: Mapped[list[Parent]] = relationship(back_populates="child")

    One To One

    One To One is essentially a relationship from a foreign key perspective, but indicates that there will only be one row at any time that refers to a particular parent row.

    When using annotated mappings with Mapped, the “one-to-one” convention is achieved by applying a non-collection type to the annotation on both sides of the relationship, which will imply to the ORM that a collection should not be used on either side, as in the example below:

    Above, when we load a Parent object, the Parent.child attribute will refer to a single Child object rather than a collection. If we replace the value of Parent.child with a new Child object, the ORM’s unit of work process will replace the previous Child row with the new one, setting the previous child.parent_id column to NULL by default unless there are specific cascade behaviors set up.

    Tip

    As mentioned previously, the ORM considers the “one-to-one” pattern as a convention, where it makes the assumption that when it loads the Parent.child attribute on a Parent object, it will get only one row back. If more than one row is returned, the ORM will emit a warning.

    However, the Child.parent side of the above relationship remains as a “many-to-one” relationship and is unchanged, and there is no intrinsic system within the ORM itself that prevents more than one Child object to be created against the same Parent during persistence. Instead, techniques such as may be used in the actual database schema to enforce this arrangement, where a unique constraint on the Child.parent_id column would ensure that only one Child row may refer to a particular Parent row at a time.

    New in version 2.0: The relationship() construct can derive the effective value of the parameter from a given Mapped annotation.

    When using relationship() without the benefit of annotations, the one-to-one pattern can be enabled using the relationship.uselist parameter set to False on what would normally be the “many” side, illustrated in a non-annotated Declarative configuration below:

    1. class Parent(Base):
    2.     __tablename__ = "parent_table"
    4.     id = mapped_column(Integer, primary_key=True)
    5.     child = relationship("Child", uselist=False, back_populates="parent")
    8. class Child(Base):
    9.     __tablename__ = "child_table"
    11.     id = mapped_column(Integer, primary_key=True)
    12.     parent_id = mapped_column(ForeignKey("parent_table.id"))
    13.     parent = relationship("Parent", back_populates="child")
    1. from __future__ import annotations
    3. from sqlalchemy import Column
    4. from sqlalchemy import Table
    5. from sqlalchemy import ForeignKey
    6. from sqlalchemy import Integer
    7. from sqlalchemy.orm import Mapped
    8. from sqlalchemy.orm import mapped_column
    9. from sqlalchemy.orm import DeclarativeBase
    10. from sqlalchemy.orm import relationship
    13. class Base(DeclarativeBase):
    14.     pass
    17. # note for a Core table, we use the sqlalchemy.Column construct,
    18. # not sqlalchemy.orm.mapped_column
    19. association_table = Table(
    20.     "association_table",
    21.     Column("left_id", ForeignKey("left_table.id")),
    23. )
    26. class Parent(Base):
    27.     __tablename__ = "left_table"
    29.     id: Mapped[int] = mapped_column(primary_key=True)
    30.     children: Mapped[list[Child]] = relationship(secondary=association_table)
    33. class Child(Base):
    34.     __tablename__ = "right_table"
    36.     id: Mapped[int] = mapped_column(primary_key=True)

    Tip

    The “association table” above has foreign key constraints established that refer to the two entity tables on either side of the relationship. The data type of each of association.left_id and association.right_id is normally inferred from that of the referenced table and may be omitted. It is also recommended, though not in any way required by SQLAlchemy, that the columns which refer to the two entity tables are established within either a unique constraint or more commonly as the primary key constraint; this ensures that duplicate rows won’t be persisted within the table regardless of issues on the application side:

    1. association_table = Table(
    2.     "association_table",
    3.     Base.metadata,
    4.     Column("left_id", ForeignKey("left_table.id"), primary_key=True),
    5.     Column("right_id", ForeignKey("right_table.id"), primary_key=True),
    6. )

    Setting Bi-Directional Many-to-many

    For a bidirectional relationship, both sides of the relationship contain a collection. Specify using , and for each relationship() specify the common association table:

    1. from __future__ import annotations
    3. from sqlalchemy import Column
    4. from sqlalchemy import Table
    5. from sqlalchemy import ForeignKey
    6. from sqlalchemy import Integer
    7. from sqlalchemy.orm import Mapped
    8. from sqlalchemy.orm import mapped_column
    9. from sqlalchemy.orm import DeclarativeBase
    10. from sqlalchemy.orm import relationship
    13. class Base(DeclarativeBase):
    14.     pass
    17. association_table = Table(
    18.     "association_table",
    19.     Base.metadata,
    20.     Column("left_id", ForeignKey("left_table.id"), primary_key=True),
    21.     Column("right_id", ForeignKey("right_table.id"), primary_key=True),
    22. )
    25. class Parent(Base):
    26.     __tablename__ = "left_table"
    28.     id: Mapped[int] = mapped_column(primary_key=True)
    29.     children: Mapped[list[Child]] = relationship(
    30.         secondary=association_table, back_populates="parents"
    31.     )
    34. class Child(Base):
    35.     __tablename__ = "right_table"
    37.     id: Mapped[int] = mapped_column(primary_key=True)
    38.     parents: Mapped[list[Parent]] = relationship(
    39.         secondary=association_table, back_populates="children"
    40.     )

    Using a late-evaluated form for the “secondary” argument

    The relationship.secondary parameter of also accepts two different “late evaluated” forms, including string table name as well as lambda callable. See the section Using a late-evaluated form for the “secondary” argument of many-to-many for background and examples.

    Configuration of collections for a Many to Many relationship is identical to that of One To Many, as described at . For an annotated mapping using Mapped, the collection can be indicated by the type of collection used within the generic class, such as set:

    1. class Parent(Base):
    2.     __tablename__ = "left_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[set["Child"]] = relationship(secondary=association_table)

    When using non-annotated forms including imperative mappings, as is the case with one-to-many, the Python class to use as a collection may be passed using the relationship.collection_class parameter.

    See also

    - contains further detail on collection configuration including some techniques to map relationship() to dictionaries.

    Deleting Rows from the Many to Many Table

    A behavior which is unique to the relationship.secondary argument to is that the Table which is specified here is automatically subject to 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 a record from the collection will have the effect of the row being deleted on flush:

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

    A question which often arises is how the row in the “secondary” table can be deleted when the child object is handed directly to :

    1. session.delete(somechild)

    There are several possibilities here:

    • If there is a relationship() from Parent to Child, but there is not a reverse-relationship that links a particular Child to each Parent, SQLAlchemy will not have any awareness that when deleting this particular Child object, it needs to maintain the “secondary” table that links it to the 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 in the Child.parents collection to locate all Parent objects, and remove each row from the “secondary” table which establishes this link. Note that this relationship does not need to be bidirectional; SQLAlchemy is strictly looking at every associated with the Child object being deleted.

    • A higher performing option here is to use ON DELETE CASCADE directives with the foreign keys used by the database. Assuming the database supports this feature, the database itself can be made to automatically delete rows in the “secondary” table as referencing rows in “child” are deleted. SQLAlchemy can be instructed to forego actively loading in the Child.parents collection in this case using the relationship.passive_deletes directive on ; see Using foreign key ON DELETE cascade with ORM relationships for more details on this.

    Note again, these behaviors are only relevant to the option used with relationship(). If dealing with association tables that are mapped explicitly and are not present in the option of a relevant relationship(), cascade rules can be used instead to automatically delete entities in reaction to a related entity being deleted - see for information on this feature.

    See also

    Using delete cascade with many-to-many relationships

    Association Object

    The association object pattern is a variant on many-to-many: it’s used when an association table contains additional columns beyond those which are foreign keys to the parent and child (or left and right) tables, columns which are most ideally mapped to their own ORM mapped class. This mapped class is mapped against the that would otherwise be noted as relationship.secondary when using the many-to-many pattern.

    In the association object pattern, the parameter is not used; instead, a class is mapped directly to the association table. Two individual relationship() constructs then link first the parent side to the mapped association class via one to many, and then the mapped association class to the child side via many-to-one, to form a uni-directional association object relationship from parent, to association, to child. For a bi-directional relationship, four constructs are used to link the mapped association class to both parent and child in both directions.

    The example below illustrates a new class Association which maps to the Table named association; this table now includes an additional column called extra_data, which is a string value that is stored along with each association between Parent and Child. By mapping the table to an explicit class, rudimental access from Parent to Child makes explicit use of Association:

    1. from typing import Optional
    3. from sqlalchemy import ForeignKey
    4. from sqlalchemy import Integer
    5. from sqlalchemy.orm import Mapped
    6. from sqlalchemy.orm import mapped_column
    7. from sqlalchemy.orm import DeclarativeBase
    8. from sqlalchemy.orm import relationship
    11. class Base(DeclarativeBase):
    12.     pass
    15. class Association(Base):
    16.     __tablename__ = "association_table"
    17.     left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id"), primary_key=True)
    18.     right_id: Mapped[int] = mapped_column(
    19.         ForeignKey("right_table.id"), primary_key=True
    20.     )
    21.     extra_data: Mapped[Optional[str]]
    22.     child: Mapped["Child"] = relationship()
    25. class Parent(Base):
    26.     __tablename__ = "left_table"
    27.     id: Mapped[int] = mapped_column(primary_key=True)
    28.     children: Mapped[list["Association"]] = relationship()
    31. class Child(Base):
    32.     __tablename__ = "right_table"
    33.     id: Mapped[int] = mapped_column(primary_key=True)

    To illustrate the bi-directional version, we add two more constructs, linked to the existing ones using relationship.back_populates:

    1. from typing import Optional
    3. from sqlalchemy import ForeignKey
    4. from sqlalchemy import Integer
    5. from sqlalchemy.orm import Mapped
    6. from sqlalchemy.orm import mapped_column
    7. from sqlalchemy.orm import DeclarativeBase
    8. from sqlalchemy.orm import relationship
    11. class Base(DeclarativeBase):
    12.     pass
    15. class Association(Base):
    16.     __tablename__ = "association_table"
    17.     left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id"), primary_key=True)
    18.     right_id: Mapped[int] = mapped_column(
    19.         ForeignKey("right_table.id"), primary_key=True
    20.     )
    21.     extra_data: Mapped[Optional[str]]
    22.     child: Mapped["Child"] = relationship(back_populates="parents")
    23.     parent: Mapped["Parent"] = relationship(back_populates="children")
    26. class Parent(Base):
    27.     __tablename__ = "left_table"
    28.     id: Mapped[int] = mapped_column(primary_key=True)
    29.     children: Mapped[list["Association"]] = relationship(back_populates="parent")
    32. class Child(Base):
    33.     __tablename__ = "right_table"
    34.     id: Mapped[int] = mapped_column(primary_key=True)
    35.     parents: Mapped[list["Association"]] = relationship(back_populates="child")

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

    1. # create parent, append a child via association
    2. p = Parent()
    3. a = Association(extra_data="some data")
    4. a.child = Child()
    5. p.children.append(a)
    7. # iterate through child objects via association, including association
    8. # attributes
    9. for assoc in p.children:
    10.     print(assoc.extra_data)
    11.     print(assoc.child)

    To enhance the association object pattern such that direct access to the Association object is optional, SQLAlchemy provides the extension. This extension allows the configuration of attributes which will access two “hops” with a single access, one “hop” to the associated object, and a second to a target attribute.

    See also

    Association Proxy - allows direct “many to many” style access between parent and child for a three-class association object mapping.

    Warning

    Avoid mixing the association object pattern with the pattern directly, as this produces conditions where data may be read and written in an inconsistent fashion without special steps; the association proxy is typically used to provide more succinct access. For more detailed background on the caveats introduced by this combination, see the next section .

    Combining Association Object with Many-to-Many Access Patterns

    As mentioned in the previous section, the association object pattern does not automatically integrate with usage of the many-to-many pattern against the same tables/columns at the same time. From this it follows that read operations may return conflicting data and write operations may also attempt to flush conflicting changes, causing either integrity errors or unexpected inserts or deletes.

    To illustrate, the example below configures a bidirectional many-to-many relationship between Parent and Child via Parent.children and Child.parents. At the same time, an association object relationship is also configured, between Parent.child_associations -> Association.child and Child.parent_associations -> Association.parent:

    1. from typing import Optional
    3. from sqlalchemy import ForeignKey
    4. from sqlalchemy import Integer
    5. from sqlalchemy.orm import Mapped
    6. from sqlalchemy.orm import mapped_column
    7. from sqlalchemy.orm import DeclarativeBase
    8. from sqlalchemy.orm import relationship
    11. class Base(DeclarativeBase):
    12.     pass
    15. class Association(Base):
    16.     __tablename__ = "association_table"
    18.     left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id"), primary_key=True)
    19.     right_id: Mapped[int] = mapped_column(
    20.         ForeignKey("right_table.id"), primary_key=True
    21.     )
    22.     extra_data: Mapped[Optional[str]]
    24.     # association between Assocation -> Child
    25.     child: Mapped["Child"] = relationship(back_populates="parent_associations")
    27.     # association between Assocation -> Parent
    30. class Parent(Base):
    31.     __tablename__ = "left_table"
    33.     id: Mapped[int] = mapped_column(primary_key=True)
    35.     # many-to-many relationship to Child, bypassing the `Association` class
    36.     children: Mapped[list["Child"]] = relationship(
    37.         secondary="association_table", back_populates="parents"
    38.     )
    40.     # association between Parent -> Association -> Child
    41.     child_associations: Mapped[list["Association"]] = relationship(
    42.         back_populates="parent"
    43.     )
    46. class Child(Base):
    47.     __tablename__ = "right_table"
    49.     id: Mapped[int] = mapped_column(primary_key=True)
    51.     # many-to-many relationship to Parent, bypassing the `Association` class
    52.     parents: Mapped[list["Parent"]] = relationship(
    53.         secondary="association_table", back_populates="children"
    54.     )
    56.     # association between Child -> Association -> Parent
    57.     parent_associations: Mapped[list["Association"]] = relationship(
    58.         back_populates="child"
    59.     )

    When using this ORM model to make changes, changes made to Parent.children will not be coordinated with changes made to Parent.child_associations or Child.parent_associations in Python; while all of these relationships will continue to function normally by themselves, changes on one will not show up in another until the is expired, which normally occurs automatically after Session.commit().

    Additionally, if conflicting changes are made, such as adding a new Association object while also appending the same related Child to Parent.children, this will raise integrity errors when the unit of work flush process proceeds, as in the example below:

    Appending Child to Parent.children directly also implies the creation of rows in the association table without indicating any value for the association.extra_data column, which will receive NULL for its value.

    It’s fine to use a mapping like the above if you know what you’re doing; there may be good reason to use many-to-many relationships in the case where use of the “association object” pattern is infrequent, which is that it’s easier to load relationships along a single many-to-many relationship, which can also optimize slightly better how the “secondary” table is used in SQL statements, compared to how two separate relationships to an explicit association class is used. It’s at least a good idea to apply the parameter to the “secondary” relationship to avoid the issue of conflicting changes occurring, as well as preventing NULL being written to the additional association columns, as below:

    1. class Parent(Base):
    2.     __tablename__ = "left_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    6.     # many-to-many relationship to Child, bypassing the `Association` class
    7.     children: Mapped[list["Child"]] = relationship(
    8.         secondary="association_table", back_populates="parents", viewonly=True
    9.     )
    11.     # association between Parent -> Association -> Child
    12.     child_associations: Mapped[list["Association"]] = relationship(
    13.         back_populates="parent"
    14.     )
    17. class Child(Base):
    18.     __tablename__ = "right_table"
    20.     id: Mapped[int] = mapped_column(primary_key=True)
    22.     # many-to-many relationship to Parent, bypassing the `Association` class
    23.     parents: Mapped[list["Parent"]] = relationship(
    24.         secondary="association_table", back_populates="children", viewonly=True
    25.     )
    27.     # association between Child -> Association -> Parent
    28.     parent_associations: Mapped[list["Association"]] = relationship(
    29.         back_populates="child"
    30.     )

    A popular alternative to the above pattern is one where the direct many-to-many Parent.children and Child.parents relationships are replaced with an extension that will transparently proxy through the Association class, while keeping everything consistent from the ORM’s point of view. This extension is known as the Association Proxy.

    See also

    - allows direct “many to many” style access between parent and child for a three-class association object mapping.

    Most of the examples in the preceding sections illustrate mappings where the various constructs refer to their target classes using a string name, rather than the class itself, such as when using Mapped, a forward reference is generated that exists at runtime only as a string:

    1. class Parent(Base):
    2.     # ...
    4.     children: Mapped[list["Child"]] = relationship(back_populates="parent")
    7. class Child(Base):
    8.     # ...
    10.     parent: Mapped["Parent"] = relationship(back_populates="children")

    Similarly, when using non-annotated forms such as non-annotated Declarative or Imperative mappings, a string name is also supported directly by the construct:

    1. registry.map_imperatively(
    2.     Parent,
    3.     parent_table,
    4.     properties={"children": relationship("Child", back_populates="parent")},
    5. )
    7. registry.map_imperatively(
    8.     Child,
    9.     child_table,
    10.     properties={"parent": relationship("Parent", back_populates="children")},
    11. )

    These string names are resolved into classes in the mapper resolution stage, which is an internal process that occurs typically after all mappings have been defined and is normally triggered by the first usage of the mappings themselves. The registry object is the container in which these names are stored and resolved to the mapped classes they refer towards.

    In addition to the main class argument for , other arguments which depend upon the columns present on an as-yet undefined class may also be specified either as Python functions, or more commonly as strings. For most of these arguments except that of the main argument, string inputs are evaluated as Python expressions using Python’s built-in eval() function, as they are intended to receive complete SQL expressions.

    Warning

    As the Python eval() function is used to interpret the late-evaluated string arguments passed to relationship() mapper configuration construct, these arguments should not be repurposed such that they would receive untrusted user input; eval() is not secure against untrusted user input.

    The full namespace available within this evaluation includes all classes mapped for this declarative base, as well as the contents of the sqlalchemy package, including expression functions like and sqlalchemy.sql.functions.func:

    1. class Parent(Base):
    2.     # ...
    4.     children: Mapped[list["Child"]] = relationship(
    5.         order_by="desc(Child.email_address)",
    6.         primaryjoin="Parent.id == Child.parent_id",
    7.     )

    For the case where more than one module contains a class of the same name, string class names can also be specified as module-qualified paths within any of these string expressions:

    1. class Parent(Base):
    2.     # ...
    4.     children: Mapped[list["myapp.mymodel.Child"]] = relationship(
    5.         order_by="desc(myapp.mymodel.Child.email_address)",
    6.         primaryjoin="myapp.mymodel.Parent.id == myapp.mymodel.Child.parent_id",
    7.     )

    In an example like the above, the string passed to Mapped can be disambiguated from a specific class argument by passing the class location string directly to as well. Below illustrates a typing-only import for Child, combined with a runtime specifier for the target class that will search for the correct name within the registry:

    1. import typing
    3. if typing.TYPE_CHECKING:
    4.     from myapp.mymodel import Child
    7. class Parent(Base):
    8.     # ...
    10.     children: Mapped[list["Child"]] = relationship(
    11.         "myapp.mymodel.Child",
    12.         order_by="desc(myapp.mymodel.Child.email_address)",
    13.         primaryjoin="myapp.mymodel.Parent.id == myapp.mymodel.Child.parent_id",
    14.     )

    The qualified path can be any partial path that removes ambiguity between the names. For example, to disambiguate between myapp.model1.Child and myapp.model2.Child, we can specify model1.Child or model2.Child:

    1. class Parent(Base):
    2.     # ...
    4.     children: Mapped[list["Child"]] = relationship(
    5.         "model1.Child",
    6.         order_by="desc(mymodel1.Child.email_address)",
    7.         primaryjoin="Parent.id == model1.Child.parent_id",
    8.     )

    The construct also accepts Python functions or lambdas as input for these arguments. A Python functional approach might look like the following:

    1. import typing
    3. from sqlalchemy import desc
    5. if typing.TYPE_CHECKING:
    6.     from myapplication import Child
    9. def _resolve_child_model():
    10.     from myapplication import Child
    12.     return Child
    15. class Parent(Base):
    16.     # ...
    18.     children: Mapped[list["Child"]] = relationship(
    19.         _resolve_child_model(),
    20.         order_by=lambda: desc(_resolve_child_model().email_address),
    21.         primaryjoin=lambda: Parent.id == _resolve_child_model().parent_id,
    22.     )

    The full list of parameters which accept Python functions/lambdas or strings that will be passed to eval() are:

    Warning

    As stated previously, the above parameters to are evaluated as Python code expressions using eval(). DO NOT PASS UNTRUSTED INPUT TO THESE ARGUMENTS.

    It should also be noted that in a similar way as described at , any MapperProperty construct can be added to a declarative base mapping at any time (noting that annotated forms are not supported in this context). If we wanted to implement this after the Address class were available, we could also apply it afterwards:

    1. # first, module A, where Child has not been created yet,
    2. # we create a Parent class which knows nothing about Child
    5. class Parent(Base):
    6.     ...
    9. # ... later, in Module B, which is imported after module A:
    12. class Child(Base):
    13.     ...
    16. from module_a import Parent
    18. # assign the User.addresses relationship as a class variable.  The
    19. # declarative base class will intercept this and map the relationship.
    20. Parent.children = relationship(Child, primaryjoin=Child.parent_id == Parent.id)

    As is the case for ORM mapped columns, there’s no capability for the Mapped annotation type to take part in this operation; therefore, the related class must be specified directly within the construct, either as the class itself, the string name of the class, or a callable function that returns a reference to the target class.

    Note

    As is the case for ORM mapped columns, assignment of mapped properties to an already mapped class will only function correctly if the “declarative base” class is used, meaning the user-defined subclass of DeclarativeBase or the dynamically generated class returned by or registry.generate_base(). This “base” class includes a Python metaclass which implements a special __setattr__() method that intercepts these operations.

    Runtime assignment of class-mapped attributes to a mapped class will not work if the class is mapped using decorators like or imperative functions like registry.map_imperatively().

    Using a late-evaluated form for the “secondary” argument of many-to-many

    Many-to-many relationships make use of the relationship.secondary parameter, which ordinarily indicates a reference to a typically non-mapped object or other Core selectable object. Late evaluation using either a lambda callable or string name is supported, where string resolution works by evaluation of given Python expression which links identifier names to same-named Table objects that are present in the same collection referred towards by the current registry.

    For the example given at , if we assumed that the association_table Table object would be defined at a point later on in the module than the mapped class itself, we may write the using a lambda as:

    1. class Parent(Base):
    2.     __tablename__ = "left_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)
    5.     children: Mapped[list["Child"]] = relationship(
    6.         "Child", secondary=lambda: association_table
    7.     )

    Or to illustrate locating the same Table object by name, the name of the is used as the argument. From a Python perspective, this is a Python expression evaluated as a variable named “association_table” that is resolved against the table names within the MetaData collection:

    1. class Parent(Base):
    2.     __tablename__ = "left_table"
    4.     id: Mapped[int] = mapped_column(primary_key=True)

    Warning

    When passed as a string, argument is interpreted using Python’s eval() function, even though it’s typically the name of a table. DO NOT PASS UNTRUSTED INPUT TO THIS STRING.