Skip to content

Relationships

Every foreign key gets a relationship attribute: follow it like a normal attribute, and SQLAlchemy loads the row for you. The examples use the declarative flavor; the names are the same in dataclasses and sqlmodels.

Set up some data

import datetime

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

from omopmodel import OMOP_5_4_declarative as omop

engine = create_engine("sqlite:///relationships.db")
omop.metadata.create_all(engine)

with Session(engine) as session:
    care_site = omop.CareSite(care_site_id=1, care_site_name="St. Local")
    session.add_all(
        [
            omop.Concept(
                concept_id=8507,
                concept_name="MALE",
                domain_id="Gender",
                vocabulary_id="Gender",
                concept_class_id="Gender",
                concept_code="M",
                valid_start_date=datetime.date(1970, 1, 1),
                valid_end_date=datetime.date(2099, 12, 31),
            ),
            omop.Person(
                person_id=1,
                gender_concept_id=8507,
                year_of_birth=1985,
                race_concept_id=0,
                ethnicity_concept_id=0,
                care_site=care_site,
            ),
            omop.Person(
                person_id=2,
                gender_concept_id=8507,
                year_of_birth=1990,
                race_concept_id=0,
                ethnicity_concept_id=0,
                care_site=care_site,
            ),
        ]
    )
    session.commit()

Usually the concept table comes from an Athena export, see Load vocabularies.

Follow them

with Session(engine) as session:
    person = session.get(omop.Person, 1)
    print(person.care_site.care_site_name)  # St. Local
    print(person.gender_concept.concept_name)  # MALE

    care_site = session.get(omop.CareSite, 1)
    print([p.person_id for p in care_site.persons])  # [1, 2]

person.care_site is one object (or None), care_site.persons is a list.

Use relationships while the session is open. After it closes, attributes that weren't loaded yet raise DetachedInstanceError. To load them up front, use select(omop.CareSite).options(selectinload(omop.CareSite.persons)).

How they are named

Foreign key Attribute Example
care_site_id the column name without _id person.care_site
gender_concept_id the same person.gender_concept
concept_id_1, concept_id_2 concept_1, concept_2 concept_relationship.concept_1
relationship_id relationship_ (trailing underscore) concept_relationship.relationship_
the other side of a foreign key the class name in plural, snake case care_site.persons, person.visit_occurrences, location.care_sites
death.person_id (one-to-one) person.death (one object or None) and death.person
foreign keys to the same table parent_visit_detail, preceding_visit_detail, preceding_visit_occurrence, episode_parent no list on the other side

To list them all for a class:

from sqlalchemy import inspect

print(inspect(omop.Person).relationships.keys())
# ['care_site', 'ethnicity_concept', ..., 'measurements', 'notes', ...]

Concepts

Almost every table points at concept, often with several columns. So Concept has no lists back to them (no concept.persons). It knows its own domain, vocabulary and concept_class, plus one list: concept_classes, the concept classes that use it as their concept_class_concept_id (see the generator policy). To go from a concept to the rows that use it, query:

from sqlalchemy import func, select

with Session(engine) as session:
    # A concept by its id
    male = session.get(omop.Concept, 8507)
    print(male.concept_name)

    # Persons with their gender name, via the relationship
    query = select(omop.Person.person_id, omop.Concept.concept_name).join(
        omop.Person.gender_concept
    )
    print(session.execute(query).all())

    # Persons per gender, via the column
    query = (
        select(omop.Concept.concept_name, func.count())
        .join(omop.Person, omop.Person.gender_concept_id == omop.Concept.concept_id)
        .group_by(omop.Concept.concept_name)
    )
    print(session.execute(query).all())
MALE
[(1, 'MALE'), (2, 'MALE')]
[('MALE', 2)]

Next: Validation