读后笔记 -- FastAPI 构建Python微服务 Chapter10：解决数值、符号和图形问题
10.2 设置项目
1. 项目结构
### Project Structure ├── static/ # piccolo asgi new 生成的项目结构 │ └── favicon.ico │ └── main.css ├── survey/ # piccolo app new survey 命令生成的应用程序 │ |── api # 接口层 │ │ └── __init__.py │ │ └── respondent.py │ |── piccolo_migrations # 迁移文件 │ │ └── __init__.py │ │ └── survey_xxxxx.py # piccolo migrations new survey -- auto 生成的迁移文件 │ |── repository # 存储层 │ │ └── __init__.py │ │ └── respondent.py │ |── __init__.py │ |── models.py │ |── piccolo_app.py │ |── tables.py # 数据模型类 │── __init__.py └── app.py # piccolo asgi new 生成的项目结构 └── conftest.py # piccolo asgi new 生成的项目结构 └── main.py # piccolo asgi new 生成的项目结构 └── piccolo_conf.py # piccolo asgi new 生成的项目结构 └── piccolo_conf_test.py # piccolo asgi new 生成的项目结构 └── README.md # piccolo asgi new 生成的项目结构 └── requirements.txt # piccolo asgi new 生成的项目结构
2. 利用 Piccolo ORM + PostgreSQL 数据库 生成项目
2.1 安装相关第三方包
pip install piccolo
pip install piccolo-admin
2.2 具体操作：
1. 在新建的项目文件夹下创建一个项目，将会生成项目的一些相关文件，在提示时选择 fastapi [1] -> uvicorn[0] piccolo asgi new 2. 创建项目应用程序，删除默认的 home piccolo app new survey 3. 创建项目应用程序，然后删除项目中默认的 home piccolo app new survey 4. 生成迁移文件，文件存储在 survey\piccolo_migrations\ piccolo migrations new survey --auto 5. 根据模型类自动创建数据库的所有表，前提是配置好数据库项目信息 piccolo migrations forward survey
2.3 相关代码
from fastapi import APIRouter from fastapi.responses import JSONResponse from survey.models import RespondentReq from survey.repository.respondent import RespondentRepository router = APIRouter() @router.post("/respondent/add") async def add_respondent(req: RespondentReq): """ 执行前，确保表 respondent 相关联的表 education, location, occupation 都存在外键对应的数据 :param req: :return: """ respondent_repo = req.dict(exclude_unset=True) repo = RespondentRepository(); result = await repo.insert_respondent(respondent_repo) if result is True: return req else: return JSONResponse(content={"message": "insert respondent problem encountered"}, status_code=500) @router.patch("/respondent/update") async def update_respondent(id: int, req: RespondentReq): respondent_repo = req.dict(exclude_unset=True) repo = RespondentRepository() result = await repo.update_respondent(id, respondent_repo) if result is True: return req else: return JSONResponse(content={"message": "update respondent problem encountered"}, status_code=500) @router.delete("/respondent/delete/{id}") async def delete_respondent(id: int): repo = RespondentRepository() result = await repo.delete_respondent(id) if result is True: return JSONResponse(content={"message": "delete respondent record successfully"}, status_code=201) else: return JSONResponse(content={"message": "delete respondent problem encountered"}, status_code=500) @router.get("/respondent/list") async def list_all_respondent(): repo = RespondentRepository() return await repo.get_all_respondent() @router.get("/respondent/get/{id}") async def get_respondent(id: int): repo = RespondentRepository() return await repo.get_respondent(id)
from typing import Dict, Any from survey.tables import Respondent class RespondentRepository: async def insert_respondent(self, details: Dict[str, Any]) -> bool: try: respondent = Respondent(**details) await respondent.save() except Exception as e: print(e) return False return True async def update_respondent(self, id: int, details: Dict[str, Any]) -> bool: try: respondent = await Respondent.objects().get(Respondent.id == id) for key, value in details.items(): setattr(respondent, key, value) await respondent.save() except: return False return True async def delete_respondent(self, id: int) -> bool: try: respondent = await Respondent.objects().get(Respondent.id == id) await respondent.remove() except Exception as e: print(e) return False return True async def get_all_respondent(self): result = await Respondent.select().order_by(Respondent.id) return result async def get_respondent(self, id: int): return await Respondent.objects().get(Respondent.id == id) async def list_gender(self, gender: str): respondents = await Respondent.select().where(Respondent.gender == gender) return respondents
from piccolo_api.crud.serializers import create_pydantic_model from survey.tables import Respondent RespondentReq = create_pydantic_model(Respondent)
import os from piccolo.conf.apps import AppConfig from survey.tables import Answers, Education, Question, Profile, Login, Location, Occupation, Respondent, Choices CURRENT_DIRECTORY = os.path.dirname(os.path.abspath(__file__)) APP_CONFIG = AppConfig( app_name="survey", migrations_folder_path=os.path.join( CURRENT_DIRECTORY, "piccolo_migrations" ), table_classes=[Answers, Education, Question, Choices, Profile, Login, Location, Occupation, Respondent], migration_dependencies=[], commands=[], )
from piccolo.columns import ForeignKey, Integer, Varchar, Text, Date, Boolean, Float from piccolo.table import Table class Occupation(Table): name = Varchar() class Location(Table): city = Varchar() state = Varchar() country = Varchar() class Login(Table): username = Varchar(unique=True) password = Varchar() class Education(Table): name = Varchar() class Profile(Table): fname = Varchar() lname = Varchar() age = Integer() position = Varchar() login_id = ForeignKey(Login, unique=True) official_id = Integer() date_employed = Date() class Respondent(Table): fname = Varchar() lname = Varchar() age = Integer() birthday = Date() gender = Varchar(length=1) occupation_id = ForeignKey(Occupation) occupation_years = Integer() salary_estimate = Float() company = Varchar() address = Varchar() location_id = ForeignKey(Location) education_id = ForeignKey(Education) school = Varchar() marital = Boolean() count_kids = Integer() class Question(Table): statement = Text() type = Integer() class Choices(Table): question_id = ForeignKey(Question) choice = Varchar() class Answers(Table): respondent_id = ForeignKey(Respondent) question_id = ForeignKey(Question) answer_choice = Integer() answer_text = Text()
from fastapi import FastAPI from piccolo_admin.endpoints import create_admin from starlette.routing import Mount from starlette.staticfiles import StaticFiles from survey.piccolo_app import APP_CONFIG from survey.api import respondent app = FastAPI( routes=[ Mount( "/admin/", create_admin( tables=APP_CONFIG.table_classes, # Required when running under HTTPS: # allowed_hosts=['my_site.com'] ), ), Mount("/static/", StaticFiles(directory="static")), ], ) app.include_router(respondent.router, prefix="/ch10")
if __name__ == "__main__": import uvicorn uvicorn.run("app:app", port=8001, reload=True)
from piccolo.engine.postgres import PostgresEngine from piccolo.conf.apps import AppRegistry DB = PostgresEngine( config={ "database": "pccs", "user": "postgres", "password": "postgres", "host": "localhost", "port": 5432, } ) APP_REGISTRY = AppRegistry( apps=["survey.piccolo_app", "piccolo_admin.piccolo_app"] )
10.3 实现符号计算
符号相关计算（符号表达式，求解线性表达式/非线性表达式，不等式）
from fastapi import APIRouter from fastapi.responses import JSONResponse from sympy import symbols, sympify, parse_expr, Poly, Eq, solve, Ge router = APIRouter() @router.get("/sym/inequality") async def solve_univar_inequality(eqn: str, sol: int): """ 求解线性和非线性不等式，案例： eqn = "x**2" sol = 4 :param eqn: :param sol: :return: """ x = symbols("x") # 将字符串形式的不等式解析为 SymPy 表达式，并创建不等式 expr1 = Ge(parse_expr(eqn, locals()), sol) # 求解不等式 sol = solve([expr1], [x]) return str(sol) @router.get("/sym/nonlinear") async def solve_nonlinear_bivar_eqns(eqn1: str, sol1: int, eqn2: str, sol2: int): """ 求解非线性表达式，案例： eqn1 = "x**2 + y**2" sol1 = 25 eqn2 = "x**2 - y" sol2 = 7 :param eqn1: 字符串形式的第一个方程 :param sol1: 第一个方程的解 :param eqn2: 字符串形式的第二个方程 :param sol2: 第二个方程的解 :return: """ # 将 x 和 y 变量定义为 symbols 对象，接受逗号分隔的变量名字符串 x, y = symbols("x, y") # 将字符串形式的方程解析为 SymPy 表达式 expr1 = parse_expr(eqn1, locals()) expr2 = parse_expr(eqn2, locals()) # 检查表达式是否为线性的 if not Poly(expr1, x, y).is_linear and not Poly(expr2, x, y).is_linear: # 创建方程 eq1 = Eq(expr1, sol1) eq2 = Eq(expr2, sol2) # 求解方程组 sol = solve([eq1, eq2], [x, y]) # 返回解 return str(sol) else: return None @router.get("/sym/linear") async def solve_linear_bivar_eqns(eqn1: str, sol1: int, eqn2: str, sol2: int): """ 求解线性表达式，案例： eqn1 = "2*x + 3*y" sol1 = 8 eqn2 = "x - y" sol2 = 1 :param eqn1: 字符串形式的第一个方程 :param sol1: 第一个方程的解 :param eqn2: 字符串形式的第二个方程 :param sol2: 第二个方程的解 :return: """ # 将 x 和 y 变量定义为 symbols 对象，接受逗号分隔的变量名字符串 x, y = symbols("x, y") # 将字符串形式的方程解析为 SymPy 表达式 expr1 = parse_expr(eqn1, locals()) expr2 = parse_expr(eqn2, locals()) # 检查表达式是否为线性的 if Poly(expr1, x, y).is_linear and Poly(expr2, x, y).is_linear: # 创建方程 eq1 = Eq(expr1, sol1) eq2 = Eq(expr2, sol2) # 求解方程组 sol = solve([eq1, eq2], [x, y]) # 返回解 return str(sol) else: return None @router.post("/sym/equation") async def substitute_bivar_eqn(eqn: str, xval: int, yval: int): """ 创建符号表达式 :param eqn: x**2 + y**2 :param xval: 3 :param yval: 4 :return: 25 """ try: # 将 x 和 y 变量定义为 symbols 对象，接受逗号分隔的变量名字符串 x, y = symbols("x, y") # 使用 sympify 将字符串形式的方程转换为 SymPy 表达式 expr = sympify(eqn) # 使用 subs 方法将 x 和 y 替换为 xval 和 yval return str(expr.subs({x: xval, y: yval})) except: return JSONResponse(content={"message": "invalid equations"}, status_code=500)
10.4/5/6 创建数组和 DataFrame、统计分析、生成csv/xlsx 报告
1. 使用 numpy 创建数组
import numpy as np import ujson from fastapi import APIRouter from survey.models import weights from survey.repository.answers import AnswerRepository from survey.repository.location import LocationRepository router = APIRouter() @router.get("/answer/respondent") async def get_respondent_answers(qid: int): """numpy，创建数组""" repo_loc = LocationRepository() repo_answers = AnswerRepository() locations = await repo_loc.get_all_location() data = [] for loc in locations: loc_q = await repo_answers.get_answers_per_q(loc["id"], qid) if loc_q: loc_data = [weights[qid - 1][str(item["answer_choice"])] for item in loc_q] data.append(loc_data) arr = np.array(data) return ujson.loads(ujson.dumps(arr.tolist()))
2. 使用 pandas 对象的 to_json() 返回 JSON 对象
import itertools import numpy as np import pandas as pd import ujson from fastapi import APIRouter from survey.models import weights from survey.repository.answers import AnswerRepository from survey.repository.location import LocationRepository router = APIRouter() @router.get("/answer/all") async def get_all_answers(): """使用 pandas 对象的 to_json() 返回 JSON 对象""" repo_loc = LocationRepository() repo_answers = AnswerRepository() locations = await repo_loc.get_all_location() temp = [] data = [] for loc in locations: for qid in range(1, 13): loc_q = await repo_answers.get_answers_per_q(loc["id"], qid) if loc_q: loc_data = [weights[qid - 1][str(item["answer_choice"])] for item in loc_q] temp.append(loc_data) temp = list(itertools.chain(*temp)) if temp: data.append(temp) temp = list() arr = np.array(data) return ujson.loads(pd.DataFrame(arr).to_json(orient="split"))
3. 使用 scipy 执行统计分析
import itertools import json import numpy as np from fastapi import APIRouter from scipy import stats from survey.models import weights from survey.repository.answers import AnswerRepository from survey.repository.location import LocationRepository router = APIRouter() def ConvertPythonInt(o): if isinstance(o, np.int32): return int(o) raise TypeError @router.get("/answer/stats") async def get_respondent_answers_stats(qid: int): """使用 scipy 进行统计分析""" repo_loc = LocationRepository() repo_answers = AnswerRepository() locations = await repo_loc.get_all_location() data = [] for loc in locations: # 获取每个位置对于指定问题的回答 loc_q = await repo_answers.get_answers_per_q(loc["id"], qid) if loc_q: # 将回答转换为权重值 loc_data = [weights[qid - 1][str(item["answer_choice"])] for item in loc_q] data.append(loc_data) # 使用 scipy 的 describe 方法进行统计分析 result = stats.describe(list(itertools.chain(*data))) # 将结果转换为 JSON 格式并返回 return json.dumps(result._asdict(), default=ConvertPythonInt)
4. 生成 csv / xlsx 报告
from io import StringIO, BytesIO import pandas as pd import xlsxwriter from fastapi import APIRouter from fastapi.responses import StreamingResponse from survey.repository.respondent import RespondentRepository router = APIRouter() @router.get("/respondents/xlsx", response_description="xlsx") async def create_respondent_report_xlsx(): """生成 xlsx 报告""" repo = RespondentRepository() result = await repo.get_all_respondent() output = BytesIO() workbook = xlsxwriter.Workbook(output) worksheet = workbook.add_worksheet() worksheet.write(0, 0, 'ID') worksheet.write(0, 1, 'First Name') worksheet.write(0, 2, 'Last Name') worksheet.write(0, 3, 'Age') worksheet.write(0, 4, 'Gender') worksheet.write(0, 5, 'Married?') row = 1 for respondent in result: worksheet.write(row, 0, respondent["id"]) worksheet.write(row, 1, respondent["fname"]) worksheet.write(row, 2, respondent["lname"]) worksheet.write(row, 3, respondent["age"]) worksheet.write(row, 4, respondent["gender"]) worksheet.write(row, 5, respondent["marital"]) row += 1 workbook.close() output.seek(0) headers = {"Content-Disposition": 'attachment; filename="list_respondents.xlsx"'} return StreamingResponse(output, headers=headers) @router.get("/respondents/csv", response_description="csv") async def create_respondent_report_csv(): """生成 csv 报告""" repo = RespondentRepository() result = await repo.get_all_respondent() ids = [item["id"] for item in result] fnames = [f'{item["fname"]}' for item in result] lnames = [f'{item["lname"]}' for item in result] ages = [item["age"] for item in result] genders = [f'{item["gender"]}' for item in result] maritals = [f'{item["marital"]}' for item in result] dict = {'Id': ids, 'First Name': fnames, 'Last Name': lnames, 'Age': ages, 'Gender': genders, 'Married?': maritals} df = pd.DataFrame(dict) # 使用 StringIO 在内存中创建一个文件对象 outFileAsStr outFileAsStr = StringIO() df.to_csv(outFileAsStr, index=False) return StreamingResponse(iter([outFileAsStr.getvalue()]), media_type="text/csv", headers={"Content-Disposition": "attachment;filename=list_respondents.csv", "Access-Control-Expose-Headers": "Content-Disposition"})
注意：
