FastAPI（44）- 操作关系型数据库（上）

.
└── sql_app
    ├── __init__.py
    ├── curd.py
    ├── database.py
    ├── main.py
    ├── models.py
    └── schemas.py

# 1、导入 sqlalchemy 部分的包
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 2、声明 database url
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"
# 3、创建 sqlalchemy 引擎
engine = create_engine(
    url=SQLALCHEMY_DATABASE_URL,
    connect_args={"check_same_thread": False}
)
# 4、创建一个 database 会话
session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# 5、返回一个 ORM Model
Base = declarative_base()

# sqlite-pysqlite 库
sqlite+pysqlite:///file_path
# mysql-mysqldb 库
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
# mysql-pymysql 库
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
# mysql-mysqlconnector 库
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
# oracle-cx_Oracle 库
oracle+cx_oracle://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]
# postgresql-pypostgresql 库
postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...]
# SQL Server-PyODBC 库
mssql+pyodbc://<username>:<password>@<dsnname>

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database import Base
class User(Base):
    # 1、表名
    __tablename__ = "users"
    # 2、类属性，每一个都代表数据表中的一列
    # Column 就是列的意思
    # Integer、String、Boolean 就是数据表中，列的类型
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)
    items = relationship("Item", back_populates="owner")
class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", back_populates="items")

from typing import List, Optional
from pydantic import BaseModel
# Item 的基类,表示创建和查询 Item 时共有的属性
class ItemBase(BaseModel):
    title: str
    description: Optional[str] = None
# 创建 Item 时的 Model
class ItemCreate(ItemBase):
    pass
# 查询 Item 时的 Model
class Item(ItemBase):
    id: int
    owner_id: int
    # 向 Pydantic 提供配置
    class Config:
        #  orm_mode 会告诉 Pydantic 模型读取数据，即使它不是字典，而是 ORM 模型（或任何其他具有属性的任意对象）
        orm_mode = True
class UserBase(BaseModel):
    email: str
class UserCreate(UserBase):
    password: str
class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []
    class Config:
        orm_mode = True