python_sql_cheat_sheet/sqlmodel_db.py at master · mluksch/python_sql_cheat_sheet · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
import typing

import sqlmodel


# Declare SQLModel-classes for each Table:
# - Each field requires a type.
# - Some field can get assigned default values:
# * sqlmodel.Field(...) with metadata: primary_key, foreign_key, index,
#   sa_column_kwargs={"unique": True} for unique constraints: https://github.com/tiangolo/sqlmodel/issues/82
# * sqlmodel.Relationship with metadata: back_populates="property-in-other-table"
class Shop(sqlmodel.SQLModel, table=True):
    id: typing.Optional[int] = sqlmodel.Field(primary_key=True)
    name: str
    purchases: typing.Optional[typing.List["Purchase"]] = sqlmodel.Relationship(
        back_populates="shop",
        sa_relationship_kwargs={
            # Cascade: orphan-delete
            # if purchases-field gets deleted in Product,
            # then purchases-entity will get deleted in Purchase-Table as well
            "cascade": 'delete-orphan',
        }
    )


# many-to-many association table between Shop & Customer:
class Purchase(sqlmodel.SQLModel, table=True):
    id: typing.Optional[int] = sqlmodel.Field(primary_key=True)
    shop_id: int = sqlmodel.Field(foreign_key="shop.id")
    customer_id: int = sqlmodel.Field(foreign_key="customer.id")
    product_id: int = sqlmodel.Field(foreign_key="product.id")
    shop: Shop = sqlmodel.Relationship(back_populates="purchases")
    customer: "Customer" = sqlmodel.Relationship(
        back_populates="purchases")
    product: "Product" = sqlmodel.Relationship(
        back_populates="purchases")


class Product(sqlmodel.SQLModel, table=True):
    id: typing.Optional[int] = sqlmodel.Field(primary_key=True)
    title: str
    price: float
    purchases: typing.Optional[typing.List[Purchase]] = sqlmodel.Relationship(
        back_populates="product",
        sa_relationship_kwargs={
            # Cascade: orphan-delete
            # if purchases-field gets deleted in Product,
            # then purchases-entity will get deleted in Purchase-Table as well
            "cascade": 'delete-orphan',
        }
    )


class Customer(sqlmodel.SQLModel, table=True):
    id: typing.Optional[int] = sqlmodel.Field(primary_key=True)
    first_name: str
    last_name: str
    age: typing.Optional[int]
    purchases: typing.Optional[typing.List[Purchase]] = sqlmodel.Relationship(
        back_populates="customer",
        sa_relationship_kwargs={
            # Cascade: orphan-delete
            # if purchases-field gets deleted in Customer,
            # then purchases-entity will get deleted in Purchase-Table as well
            "cascade": 'delete-orphan',
        }
    )