fastAPI数据库的链接

fastAPI数据库的链接

ORM操作数据库

# session
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(
			"数据库类型+数据库类型依赖://数据库用户名:数据库密码@IP:端口/所使用数据库名"
			) # 创建数据引擎

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)  # 创建数据库的本地会话

Base = declarative_base()  # 创建数据库的实例用于构建模型类

# Dependency
def get_db():
    db = SessionLocal()
    try:
        yield db  # 使用yield实现orm的异步操作
    finally:
        db.close()

# model
from sqlalchemy import Column, Integer, String, Boolean, DateTime

class User(Base):
    """
    用户表
    """
    __tablename__ = "model_users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(20), unique=True, index=True, nullable=False)
    password = Column(String(100), nullable=False)
    is_staff = Column(Boolean, default=0)
    email = Column(String(40), unique=True, index=True, nullable=False)
    is_delete = Column(Boolean, default=0)
    create_time = Column(DateTime)
    update_time = Column(DateTime)

    def __repr__(self):
        return f"user_{self.username}"

# schemas
from pydantic import BaseModel
import typing as t

class UserBase(BaseModel):
	username: str
	email: str
	create_time: datetime = datetime.now()
    update_time: datetime = datetime.now()
    is_staff: t.Optional[bool] = False
    is_delete: t.Optional[bool] = False

class UserOut(UserBase):
    pass

class UserCreate(UserBase):
    password: str

    class Config:
        orm_mode = True

class UserEdit(UserBase):
    password: t.Optional[str] = None
    update_time: datetime = datetime.now()

    class Config:
        orm_mode = True

class User(UserBase):
    id: int

    class Config:
        orm_mode = True

# crud
def get_users(db: Session, skip: int, limit: int) -> t.List[user_schemas.User]:
    result = db.query(user_models.User).offset(skip).limit(limit).all()
    return result

# main
from fastapi import FastAPI

app = FastAPI()

@app.get("/users", response_model=t.List[user_schemas.User])
async def get_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = user_crud.get_users(db, skip, limit)
    return users

if __name__ == '__main__':
	import uvicorn
    uvicorn.run("main:app", host="0.0.0.0", port=8000, reload=True)

databases操作数据库

# session.py
import databases

# 创建数据库的链接
databases = databases.Database("数据库类型://数据库用户名:数据库密码@IP:端口/所使用数据库名")


# crud.py
from session import databases

def find_exist_user_by_username(username: str):
    query = "select * from `new_model`.`model_users` where `username`=:username and is_delete=0;"
    return databases.fetch_one(query, values={"username": username})


# main.py
from fastapi import FastAPI

app = FastAPI()


@app.on_event("startup")  
async def startup():
    await databases.connect()  # 连接数据库


@app.on_event("shutdown")
async def shutdown():
    await databases.disconnect()  # 关闭数据库


@app.get("/users", response_model=t.List[user_schemas.User])
async def get_users(username: str):
    users = find_exist_user_by_username(username)
    return users


if __name__ == '__main__':
	import uvicorn
    uvicorn.run("main:app", host="0.0.0.0", port=8000, reload=True)