sqlalchemy_declarative_orm_crud_with_relationships_and_session.py

1from typing import List
2from typing import Optional
3from sqlalchemy import ForeignKey
4from sqlalchemy import String
5from sqlalchemy.orm import DeclarativeBase
6from sqlalchemy.orm import Mapped
7from sqlalchemy.orm import mapped_column
8from sqlalchemy.orm import relationship
9from sqlalchemy import create_engine
10from sqlalchemy.orm import Session
11
12class Base(DeclarativeBase):
13    pass
14
15class User(Base):
16    __tablename__ = "user_account"
17
18    id: Mapped[int] = mapped_column(primary_key=True)
19    name: Mapped[str] = mapped_column(String(30))
20    fullname: Mapped[Optional[str]]
21
22    addresses: Mapped[List["Address"]] = relationship(
23        back_populates="user", cascade="all, delete-orphan"
24    )
25
26    def __repr__(self) -> str:
27        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
28
29class Address(Base):
30    __tablename__ = "address"
31
32    id: Mapped[int] = mapped_column(primary_key=True)
33    email_address: Mapped[str]
34    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
35
36    user: Mapped["User"] = relationship(back_populates="addresses")
37
38    def __repr__(self) -> str:
39        return f"Address(id={self.id!r}, email_address={self.email_address!r})"
40
41engine = create_engine("sqlite://", echo=True)
42
43Base.metadata.create_all(engine)
44
45with Session(engine) as session:
46    spongebob = User(
47        name="spongebob",
48        fullname="Spongebob Squarepants",
49        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
50    )
51    sandy = User(
52        name="sandy",
53        fullname="Sandy Cheeks",
54        addresses=[
55            Address(email_address="sandy@sqlalchemy.org"),
56            Address(email_address="sandy@google.com"),
57        ],
58    )
59    patrick = User(name="patrick", fullname="Patrick Star")
60
61    session.add_all([spongebob, sandy, patrick])
62
63    session.commit()