21.FastAPI关系型数据处理

21.FastAPI关系型数据处理

在FastAPI应用程序开发中，不可避免要使用关系型数据库，FastAPI本身并不要求使用关系型数据库。但在开发中可以根据自己的需求使用任何关系型数据库。这里，我们以PostgreSQL数据库及SQLAlchemy为例来说明在FastAPI应用程序开发中使用数据库的方法。

在本例中，实现一个通讯录的管理和维护，包括通讯录的增删改查操作。该例不使用ORM来实现，以原生SQL +SQLALchemy的方式来实现，SQLALchemy只用于执行SQL语句，不使用其ORM的方式。

21.1设计并创建数据表

create table test.d_address_book(
    address_book_id varchar(64) primary key,  --id
    name varchar(32),  --姓名
    sex varchar(2),  --性别
    mobile varchar(16),  --手机号码
    email varchar(256),  --电子邮箱
    qq varchar(32),  --QQ号
    com_name varchar(128), --单位名称
    post_address varchar(256),  --通讯地址
    remarks varchar(256)  --备注
);

21.2创建pydantic模型

class AddressBook(BaseModel):
    address_book_id: Optional[str] = None
    name: str = Field(...)
    sex: str = Field(...)
    mobile: str = Field(...)
    email: str = Field(...)
    qq: str = Field(...)
    com_name: str = Field(...)
    post_address: str = Field(...)
    remarks: Optional[str] = None

上面的pydantic模型与数据库表test.d_address_book的字段名保持一致。

21.3建立数据库连接

在项目中新建一个database.py文件，用于连接数据库，代码如下：

# coding: utf-8
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
​
# 数据库连接url
SQLALCHEMY_DATABASE_URL = "postgresql://postgres:jane10181010@127.0.0.1:9432/qydb"
​
# 创建数据库连接
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    echo = True
)
​
# 创建会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
​
def getdb():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

上面的代码用于建立数据库连接，使用数据库连接的时候通过getdb()函数获取到数据库会话，然后执行SQL语句。

21.4创建数据

创建数据，即在数据表中插入数据，本例采用执行原生SQL来处理。代码如下：

@app.post(path='/address_book/create')
async def create_address_book(addressbook: AddressBook=Body(...), db: Session=Depends(getdb)):
    res = {'res': True, 'desc': None}
    # 设置主键
    addressbook.address_book_id = str(uuid.uuid1())
    # SQL语句
    sql = '''
    INSERT INTO test.d_address_book
    (address_book_id, name, sex, mobile, email, qq, com_name, post_address, remarks)
    VALUES(:address_book_id, :name, :sex, :mobile, :email, :qq, :com_name, :post_address, :remarks)
    '''
    # 执行SQL语句
    try:
        db.execute(sql, params=addressbook.dict())
        db.commit()
    except Exception as ex:
        db.rollback()
        res['res'] = False
        res['desc'] = str(ex)
        print(ex)
​
    return res

测试请求：

curl -H "Content-Type: application/json" -X POST -d "{\"name\": \"limin\", \"sex\": \"nv\", \"mobile\":\"13904710527\", \"email\":\"liming@163.com\", \"qq\":\"469125782\", \"com_name\":\"Beijing zhong he qing yang ke ji you xian gong si\", \"post_address\":\"Beijing shi hai dian qu chang chun qiao lu xin qi dian jia yuan 12 hao lou 1905\", \"remarks\": null}" http://127.0.0.1:8000/address_book/create
{"res":true,"desc":null}

21.5更新数据

代码如下：

@app.post(path='/address_book/update')
async def update_address_book(addressbook: AddressBook=Body(...), db: Session = Depends(getdb)):
    res = {'res': True, 'desc': None}
    # SQL语句
    sql = '''
    UPDATE test.d_address_book set 
    name=:name, 
    sex=:sex, 
    mobile=:mobile, 
    email=:email, 
    qq=:qq, 
    com_name=:com_name, 
    post_address=:post_address, 
    remarks=:remarks
    where address_book_id=:address_book_id
    '''
    # 执行SQL语句
    try:
        db.execute(sql, params=addressbook.dict())
        db.commit()
    except Exception as ex:
        db.rollback()
        res['res'] = False
        res['desc'] = str(ex)
        print(ex)
​
    return res

测试请求：

curl -H "Content-Type: application/json" -X POST -d "{\"address_book_id\":\"53578e06-8565-11ec-9c53-70c94ec87656\", \"name\": \"hejing\", \"sex\": \"nv\", \"mobile\":\"13904710527\", \"email\":\"liming@163.com\", \"qq\":\"469125782\", \"com_name\":\"Beijing zhong he qing yang ke ji you xian gong si\", \"post_address\":\"Beijing shi hai dian qu chang chun qiao lu xin qi dian jia yuan 12 hao lou 1905\", \"remarks\": null}" http://127.0.0.1:8000/address_book/update
{"res":true,"desc":null}

21.6删除数据

代码如下：

@app.post(path='/address_book/delete')
async def delete_address_book(address_book_id: str=Body(..., embed=True), db: Session = Depends(getdb)):
    res = {'res': True, 'desc': None}
    # SQL语句
    sql = '''
    DELETE from test.d_address_book
    where address_book_id=:address_book_id
    '''
    # 执行SQL语句
    try:
        db.execute(sql, params={'address_book_id': address_book_id})
        db.commit()
    except Exception as ex:
        db.rollback()
        res['res'] = False
        res['desc'] = str(ex)
        print(ex)
​
    return res

需要注意：由于删除数据只提交数据表的主键，所以在参数中使用了Body(..., embed=True)。

测试请求：

curl -H "Content-Type: application/json" -X POST -d "{\"address_book_id\":\"1b43feb4-856a-11ec-a926-70c94ec87656\"}" http://127.0.0.1:8000/address_book/delete
{"res":true,"desc":null}

21.7查询数据

代码如下：

@app.get(path='/address_book/retrieve', response_model=List[AddressBook])
async def retrieve_address_book(db: Session = Depends(getdb)):
    # SQL语句
    sql = '''
    SELECT address_book_id, "name", sex, mobile, email, qq, com_name, post_address, remarks
    FROM test.d_address_book
    '''
    # 执行SQL语句
    rows = []
    try:
        result = db.execute(sql)
        rows = result.fetchall()
    except Exception as ex:
        print(ex)
​
    return rows

上面的代码中，响应模型仍然使用AddressBook，且是AddressBook模型的List，虽然在代码中直接返回了数据集，但通过响应模型的限制可以根据开发需求进行返回数据的限制和管理。

测试请求：

curl http://127.0.0.1:8000/address_book/retrieve
[
    {
        "address_book_id":"19ef0854-8564-11ec-87ac-70c94ec87656",
        "name":"limin",
        "sex":"nv",
        "mobile":"13904710527",
        "email":"liming@163.com",
        "qq":"469125782",
        "com_name":"Beijing zhong he qing yang ke ji you xian gong si",
        "post_address":"Beijing shi hai dian qu chang chun qiao lu xin qi dian jia yuan 12 hao lou 1905",
        "remarks":null
    },{
        "address_book_id":"8d21f814-8564-11ec-b05d-70c94ec87656",
        "name":"limin",
        "sex":"nv",
        "mobile":"13904710527",
        "email":"liming@163.com",
        "qq":"469125782",
        "com_name":"Beijing zhong he qing yang ke ji you xian gong si",
        "post_address":"Beijing shi hai dian qu chang chun qiao lu xin qi dian jia yuan 12 hao lou 1905",
        "remarks":null
    }
]

以上例子没有使用SQLALchemy的ORM方式进行数据处理，同时为了简化开发，在路由方法中直接执行原生SQL来进行数据处理，在实际开发中，可以使用分层模式进行开发。