typing, attrs and sqlalchemy

person.py

from typing import Optional
import attr
from sqlalchemy import (
    Table,
    MetaData,
    Column,
    Unicode,
    Integer,
    create_engine,
    ForeignKey,
)
from sqlalchemy.orm import mapper, sessionmaker, relationship


meta = MetaData()


@attr.s
class Job:
    id: Optional[int] = attr.ib(default=None)
    name: str = attr.ib(default="")


@attr.s
class Person:
    id: Optional[int] = attr.ib(default=None)
    name: str = attr.ib(default="")
    job: Job = attr.ib(default=None)


person_table = Table(
    "person",
    meta,
    Column("id", Integer, primary_key=True),
    Column("name", Unicode),
    Column("job_id", Integer, ForeignKey("job.id")),
)
job_table = Table(
    "job", meta, Column("id", Integer, primary_key=True), Column("name", Unicode)
)

engine = create_engine("sqlite:///")
engine.echo = True
Session = sessionmaker(bind=engine)

mapper(Job, job_table)
mapper(Person, person_table, properties={"job": relationship(Job)})
meta.create_all(bind=engine)

j = Job(name="engineer")
p = Person(name="aodag", job=j)

print(p)
session = Session()
session.add(p)
session.flush()
print(p)
print([attr.asdict(p) for p in session.query(Person).all()])
# -> [{'id': 1, 'name': 'aodag', 'job': {'id': 1, 'name': 'engineer'}}]