Example of creating associations using SQLAlchemy

sqlalchemy-orm-example.py

from typing import List, Optional
from sqlalchemy import String, ForeignKey, create_engine
from sqlalchemy.orm import Mapped, Session, mapped_column, relationship, DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(back_populates="user")
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id = mapped_column(ForeignKey("user_account.id"))
    user: Mapped[User] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

with Session(engine) as session:
    # create tables
    Base.metadata.create_all(engine)

    # create user
    user = User()
    user.name = "Jon"
    user.fullname = "Jon Doe"

    # create address
    address = Address()
    address.email_address = "[email protected]"

    # tie the two objects together
    user.addresses.append(address)

    # save
    #   this save will save both the user and address objects
    #   as well as set the ids correctly
    session.add(user)

    # test and query
    result = session.query(User).filter(User.name == 'Jon').first()
    if result:
        print("--------------------------------------")
        print(result)
        print(f"\tuser id: {result.id}")
        print(result.addresses)
        address = result.addresses[0]
        print(f"\taddress id: {address.id}, user id: {address.user_id}")
        print("--------------------------------------")
    else:
        print("Nothing found")

    session.commit()

You will get an output that looks like this:

--------------------------------------
User(id=1, name='Jon', fullname='Jon Doe')
        user id: 1
[Address(id=1, email_address='[email protected]')]
        address id: 1, user id: 1
--------------------------------------