FastAPI+SQLAlchemy數據庫連接

配置數據庫連接

# db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmakerDATABASE_URL = "mysql+pymysql://root:7997@localhost/fastapidemo"engine = create_engine(DATABASE_URL,pool_size=10,            # 設置連接池大小max_overflow=20,         # 設置連接池最大溢出連接數量pool_timeout=30,         # 設置連接超時時間pool_recycle=1800,       # 設置連接的回收時間
)SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

創建表模型

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import DeclarativeBase, relationshipclass Base(DeclarativeBase):__abstract__ = True  # 標記為抽象基類，防止直接創建表@classmethoddef get_all(cls, session):return session.query(cls).all()book_press = Table('book_press', Base.metadata,Column('book_id', Integer, ForeignKey('book.id'), primary_key=True),Column('press_id', Integer, ForeignKey('press.id'), primary_key=True))class Book(Base):__tablename__ = 'book'id = Column(Integer, primary_key=True, autoincrement=True)title = Column(String(15), nullable=False, unique=True)author_id = Column(Integer, ForeignKey('author.id'))author = relationship('Author', backref='books', cascade='all,delete')press = relationship('Press', backref='books', secondary=book_press)class Author(Base):__tablename__ = 'author'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(15), nullable=False, unique=True)age = Column(Integer, nullable=False)class Press(Base):__tablename__ = 'press'id = Column(Integer, primary_key=True, autoincrement=True)name = Column(String(15), nullable=False, unique=True)if __name__ == '__main__':from db import engineBase.metadata.create_all(engine)

創建alembic遷移文件

安裝+初始化

pip install alembic

alembic init alembic

編輯env.py

# env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context# 導入你的 Base 和模型
from module import Base, Book, Author, Pressconfig = context.config# 默認None 替換成你的Base
target_metadata = Base.metadata
# 其他配置...

編輯alembic.ini

# alembic.ini
sqlalchemy.url = mysql+pymysql://root:7997@localhost/fastapidemo

遷移數據庫

alembic revision --autogenerate
alembic upgrade head

視圖函數查詢

# main.py
from fastapi import FastAPI, Request, Depends
from pydantic import BaseModel
from sqlalchemy.orm import Sessionfrom db import SessionLocal
from orm import Authorapp = FastAPI()
session = SessionLocal()def get_db():db = SessionLocal()try:yield dbfinally:db.close()class Item(BaseModel):pass@app.get("/api/")
async def root(db: Session = Depends(get_db)):# 直接用session查詢res = db.query(Author).all()# 調用模型類的方法查詢res2 = Author.get_all(db)print(res[0].name)print(res2[0].name)return {f"作者名：{res[0].name},{res2[0].name}"}