FastAPI(五)sqlalchemy操作mysql数据库

心已赠人 2024-03-27 16:31 33阅读 0赞

#### 一、安装依赖 ####

pip install SQLAlchemy==1.4.46

pip install sqlacodegen

pip install pymysql

#### 二、在根目录新建文件夹db ####

![2aa5bdfebde3499aab70c810c6bc0574.png][]

#### 三、在db文件夹中新建session.py文件 ####

import os
    from contextlib import contextmanager
    from typing import Generator
    from urllib import parse
    
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    
    HOST = 'localhost'
    PORT = 3306
    USERNAME = 'root'
    PASSWORD = parse.quote_plus('123456')
    DB = 'authcenter'
    
    DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
    
    engine = create_engine(DB_URI, pool_pre_ping=True)
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    
    # 这里一定要用with上下文去管理session,否则会出现很多诡异的情况！！！切记
    
    # 创建session
    DbSession = sessionmaker(bind=engine)
    db_session = DbSession()
    
    
    @contextmanager
    def session_maker(session=db_session):
        try:
            yield session
            session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()
    
    
    def get_db() -> Generator:
        """
        get SQLAlchemy session to curd
        :return: SQLAlchemy Session
        """
        db = None
        try:
            db = SessionLocal()
            yield db
        finally:
            if db:
                db.close()
    
    
    def get_db_connect() -> Generator:
        """
        get SQLAlchemy connect to exec sql
        :return: SQLAlchemy connect
        """
        conn = None
        try:
            conn = engine.connect()
            yield conn
        finally:
            if conn:
                conn.close()
    
    
    # 逆向工程 自动生成模型文件
    if __name__ == '__main__':
        tables = ["user"]
        os.system(
            f'sqlacodegen --tables {",".join(tables)} {DB_URI} > db_models.py')

**温馨提示：在填写PASSWORD的密码栏的时候使用parse.quote\_plus('123456')将密码包裹起来。这是防止有些时候密码中包含了特殊字符@，这个和驱动中的@冲突。**

![30e503237e004015a374c0bec6b08e14.png][]

#### 四、 执行session.py生成模型文件 ####

![2d60d2da95b748f3a184ebdb00ff9133.png][]

![871b9198fe7840dd9fa95f9d9d84bc2a.png][]

#### 五、CRUD ####

在user文件夹下新建crud文件夹，并在该文件夹下新建user\_crud.py文件内容如下：

from sqlalchemy.orm import Session
    
    from db.db_models import User
    
    
    def getByUsername(db: Session, *, username: str):
        """
        根据用户名查询用户
        :param db: 
        :param username: 
        :return: 
        """
        return db.query(User).filter(User.username == username).first()

![9b1cee91d9254af38a10f385c40d1ada.png][]

#### 六、调用CRUD ####

修改user文件夹下的views.py如下：

from fastapi import APIRouter, Depends
    from sqlalchemy.orm import Session
    
    from apps.user.crud import user_crud
    from apps.user.schemas.user_schemas import Item
    from db.session import get_db
    
    router = APIRouter()
    
    
    @router.post("/login", summary="用户登录", tags=['用户'])
    async def login(item: Item, db: Session = Depends(get_db)):
        username = user_crud.getByUsername(db=db, username=item.username)
        return {'message': '登录成功', 'data': username}

![55d2c132232f4c508eb79695a9d32648.png][]

[2aa5bdfebde3499aab70c810c6bc0574.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6a9d19d1541548d7ac62fe549c1fd867.png
[30e503237e004015a374c0bec6b08e14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ffa1bc06d6f84735a59c57705340d5e6.png
[2d60d2da95b748f3a184ebdb00ff9133.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e18b26bf192441a0b493e114942d4f4d.png
[871b9198fe7840dd9fa95f9d9d84bc2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8e6876eefff747e782b6687bcfb43ede.png
[9b1cee91d9254af38a10f385c40d1ada.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/80c612a705594401b42c6a1e1f27bf40.png
[55d2c132232f4c508eb79695a9d32648.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/135992c036b4411caec26d37edab58bc.png