利用 python - fastapi完成数据库操作

项目目录如图所示 本篇直接给出代码 不做解释

__init__.py文件为空即可 作为一个可识别为包的程序作用

db_manager.py 文件

import sqlite3

def initialize_database():
    conn = sqlite3.connect("database.db")
    cursor = conn.cursor()

    # 创建用户表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(30) UNIQUE,
        password VARCHAR(50)
    )
    """)

    # 创建IP地址数据表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ip_addresses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ip VARCHAR(50),
        description VARCHAR(100)
    )
    """)

    conn.commit()
    conn.close()

def get_db_connection():
    conn = sqlite3.connect("database.db")
    return conn

data_router.py 文件

from fastapi import APIRouter, HTTPException, status
from pydantic import BaseModel
import sqlite3
from database.db_manager import get_db_connection

router = APIRouter()

class DataRequest(BaseModel):
    ip: str
    description: str

@router.post("/database/add")
async def add_data(data_request: DataRequest):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO ip_addresses (ip, description) VALUES (?, ?)", (data_request.ip, data_request.description))
    conn.commit()
    conn.close()
    return {"message": "Data added successfully"}

@router.post("/database/del")
async def delete_data(id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM ip_addresses WHERE id=?", (id,))
    if cursor.rowcount == 0:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Data not found")

    conn.commit()
    conn.close()
    return {"message": "Data deleted successfully"}

@router.post("/database/modify")
async def modify_data(id: int, data_request: DataRequest):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("UPDATE ip_addresses SET ip=?, description=? WHERE id=?", (data_request.ip, data_request.description, id))
    if cursor.rowcount == 0:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Data not found")

    conn.commit()
    conn.close()
    return {"message": "Data modified successfully"}

@router.get("/database/find")
async def find_data(id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM ip_addresses WHERE id=?", (id,))
    data = cursor.fetchone()
    if not data:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Data not found")

    conn.close()
    return {"id": data[0], "ip": data[1], "description": data[2]}

user_router.py 文件

from fastapi import APIRouter, HTTPException, status
from pydantic import BaseModel
import sqlite3
import hashlib
from database.db_manager import get_db_connection

router = APIRouter() # 创建一个路由器

def encrypt_password(password: str) -> str:
    return hashlib.md5(password.encode()).hexdigest()

class RegisterRequest(BaseModel):
    username: str
    password: str

class LoginRequest(BaseModel):
    username: str
    password: str

@router.post("/register")
async def register_user(register_request: RegisterRequest):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username=?", (register_request.username,))
    existing_user = cursor.fetchone()
    if existing_user:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Username already registered")

    encrypted_password = encrypt_password(register_request.password)
    cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", (register_request.username, encrypted_password))
    conn.commit()
    conn.close()

    return {"message": "User registered successfully"}

@router.post("/login") # 使用POST方法
async def login_user(login_request: LoginRequest):
    conn = get_db_connection()
    cursor = conn.cursor()
    encrypted_password = encrypt_password(login_request.password)
    cursor.execute("SELECT * FROM users WHERE username=? AND password=?", (login_request.username, encrypted_password))
    user = cursor.fetchone()
    if not user:
        raise HTTPException(status_code=status.HTTP_401_UNAUTHORIZED, detail="Invalid credentials")

    conn.close()
    return {"message": "Login successful"}

最后是main.py 文件

from fastapi import FastAPI
import uvicorn
from routers import user_router, data_router
from database.db_manager import initialize_database

app = FastAPI()

# 初始化数据库
initialize_database()


# 根路径处理程序
@app.get("/")
async def root():
    return {"message": "Welcome to my 0. FastAPI application"}


# 挂载用户管理路由
app.include_router( 
    user_router.router, prefix="/users", tags=["users"]
)  # prefix 表示路由的前缀，tags 表示路由的分组

# 挂载数据库操作路由
app.include_router(data_router.router, prefix="/database", tags=["database"])

if __name__ == "__main__":
    uvicorn.run(app, host="127.0.0.1", port=8000, reload=True)

    # 运行命令：uvicorn main:app --reload

启动程序：运行命令 uvicorn main:app --reload

不做过多解释 有需要了解请联系我即可。