customerで1 schema = 1 modelの実装ができました。
customersと同様に実装できました。
schema単位のモジュール化も問題ありませんでしたし、こちらの方が凝集度が高く、拡張も容易な気がします。同じような設計思想で開発されているところもありました。
注文は明細がOne to Manyの関係になりますので、SQLAlchemyのドキュメントを読みながら実装していきます。
from datetime import date
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from src.database import Base
class OrderHeader(Base):
__tablename__ = "order_header"
orderId: Mapped[int] = mapped_column(
name="order_id", primary_key=True)
customerId: Mapped[int] = mapped_column(name="customer_id")
orderDate: Mapped[date] = mapped_column(name="order_date")
details: Mapped[list["OrderDetail"]] = relationship(
cascade="all, delete-orphan", lazy="joined")
def __repr__(self) -> str:
return f"OrderHeader(orderId={self.orderId!r}, " \
f"customerId={self.customerId!r}, orderDate={self.orderDate!r})"
class OrderDetail(Base):
__tablename__ = "order_detail"
orderId: Mapped[int] = mapped_column(
ForeignKey("order_header.order_id"),
name="order_id", primary_key=True)
rowNumber: Mapped[int] = mapped_column(
name="row_number", primary_key=True)
productId: Mapped[int] = mapped_column(name="product_id")
quantity: Mapped[int]
pricePerUnit: Mapped[int] = mapped_column(name="price_per_unit")
def __repr__(self) -> str:
return f"OrderDetail(orderId={self.orderId!r}, " \
f"rowNumber={self.rowNumber!r}, productId={self.productId!r})" \
f"quantity={self.quantity!r}, pricePerUnit={self.pricePerUnit!r})"schemaは、自然な延長で実装しましたが、参照関係のため、Detailが上になってます。
from datetime import date
from pydantic import BaseModel
class OrderDetail(BaseModel):
orderId: int | None
rowNumber: int
productId: int
quantity: int
pricePerUnit: int
class Config:
orm_mode = True
class OrderHeader(BaseModel):
orderId: int
customerId: int
orderDate: date
details: list[OrderDetail] = []
class Config:
orm_mode = Trueserviceも自然な延長でできましたが、deleteやupdateの前処理のselectが無駄というか勿体ないというか気になるのは、ギリギリのコンピュータリソースで実装してきたロートルだからなんでしょうかね。
from fastapi import HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select
from . import models, schemas
async def get_order(db: AsyncSession, orderId: int) -> models.OrderHeader:
result = await (db.execute(select(models.OrderHeader).filter(
models.OrderHeader.orderId == orderId)))
order = result.first()
if order is None:
raise HTTPException(status_code=404, detail="Customer not found")
return order[0]
async def create_order(db: AsyncSession, order: schemas.OrderHeader):
db_details = []
for detail in order.details:
db_details.append(models.OrderDetail(
orderId=order.orderId,
rowNumber=detail.rowNumber,
productId=detail.productId,
quantity=detail.quantity,
pricePerUnit=detail.pricePerUnit,
))
db_order = models.OrderHeader(
orderId=order.orderId,
customerId=order.customerId,
orderDate=order.orderDate,
details=db_details)
db.add(db_order)
await db.commit()
await db.refresh(db_order)
return db_order
async def update_order(
db: AsyncSession, orderId: int, order: schemas.OrderHeader
):
original = await get_order(db, orderId)
original.customerId = order.customerId
original.orderDate = order.orderDate
original.details.clear()
for detail in order.details:
original.details.append(models.OrderDetail(
orderId=orderId,
rowNumber=detail.rowNumber,
productId=detail.productId,
quantity=detail.quantity,
pricePerUnit=detail.pricePerUnit,
))
db.add(original)
await db.commit()
await db.refresh(original)
return original
async def delete_order(db: AsyncSession, orderId: int):
original = await get_order(db, orderId)
await db.delete(original)
await db.commit()ちなみにupdate_orderのログはこんな感じです。
2023-04-16 03:02:46,661 INFO sqlalchemy.engine.Engine ROLLBACK
2023-04-16 03:02:52,277 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-04-16 03:02:52,278 INFO sqlalchemy.engine.Engine SELECT order_header.order_id, order_header.customer_id, order_header.order_date, order_detail_1.order_id AS order_id_1, order_detail_1.row_number, order_detail_1.product_id, order_detail_1.quantity, order_detail_1.price_per_unit
FROM order_header LEFT OUTER JOIN order_detail AS order_detail_1 ON order_header.order_id = order_detail_1.order_id
WHERE order_header.order_id = $1::INTEGER
2023-04-16 03:02:52,278 INFO sqlalchemy.engine.Engine [cached since 5.624s ago] (1,)
2023-04-16 03:02:52,286 INFO sqlalchemy.engine.Engine UPDATE order_header SET customer_id=$1::INTEGER WHERE order_header.order_id = $2::INTEGER
2023-04-16 03:02:52,286 INFO sqlalchemy.engine.Engine [generated in 0.00047s] (2, 1)
2023-04-16 03:02:52,291 INFO sqlalchemy.engine.Engine UPDATE order_detail SET product_id=$1::INTEGER, quantity=$2::INTEGER, price_per_unit=$3::INTEGER WHERE order_detail.order_id = $4::INTEGER AND order_detail.row_number = $5::INTEGER
2023-04-16 03:02:52,291 INFO sqlalchemy.engine.Engine [generated in 0.00014s] (2, 3, 2000, 1, 1)
2023-04-16 03:02:52,296 INFO sqlalchemy.engine.Engine COMMIT
2023-04-16 03:02:52,300 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-04-16 03:02:52,300 INFO sqlalchemy.engine.Engine SELECT order_header.order_id, order_header.customer_id, order_header.order_date, order_detail_1.order_id AS order_id_1, order_detail_1.row_number, order_detail_1.product_id, order_detail_1.quantity, order_detail_1.price_per_unit
FROM order_header LEFT OUTER JOIN order_detail AS order_detail_1 ON order_header.order_id = order_detail_1.order_id
WHERE order_header.order_id = $1::INTEGER
2023-04-16 03:02:52,300 INFO sqlalchemy.engine.Engine [cached since 22.93s ago] (1,)
INFO: 127.0.0.1:50652 - "PUT /orders/1 HTTP/1.1" 200 OKということで、NestJSのTypeORMよりは、FastAPIのSQLAlchemyの方が使いやすかったような気がします。
今回のソースコードは、以下のリポジトリで公開しています。
/assets/images/4779417/original/73f16fb0-2d18-4c81-9dd5-156a5b895630?1584499108)
/assets/images/4779417/original/73f16fb0-2d18-4c81-9dd5-156a5b895630?1584499108)
/assets/images/4779417/original/73f16fb0-2d18-4c81-9dd5-156a5b895630?1584499108){kind=avatar}
