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来进行数据处理，在实际开发中，可以使用分层模式进行开发。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
