Python搭建数据查询接口服务

 1 # -*- coding: UTF-8 -*-
 2 """
 3 @author: cc
 4 @file: service.py
 5 @time: 2021/05/24
 6 """
 7 
 8 import sqlite3
 9 from fastapi import FastAPI
10 import uvicorn
11 import os
12 from fastapi.middleware.cors import CORSMiddleware
13 
14 service_path = os.path.normpath(os.path.dirname(os.path.abspath(__file__)))
15 db = os.path.join(service_path, 'perfume.db')
16 
17 conn = sqlite3.connect(db)
18 curs = conn.cursor()
19 
20 app = FastAPI()
21 
22 origins = [
23     "*",
24 ]
25 
26 app.add_middleware(
27     CORSMiddleware,
28     allow_origins=origins,
29     allow_credentials=True,
30     allow_methods=["*"],
31     allow_headers=["*"],
32 )
33 
34 @app.get('/')
35 async def perfume(scent: str):
36     data = []
37     scents = scent.split(",")
38     if scent:
39         if len(scents) == 1:
40             sql = "select * from perfume_scent where scent='{}'".format(scent)
41             res = curs.execute(sql)
42             res_list = list(res)
43             lens = len(res_list)
44             if lens == 0:
45                 sql = "select * from perfume_scent where scents like '% {} %'".format(scent)
46                 res = curs.execute(sql)
47                 res_list = list(res)
48         else:
49             sign_str = ''
50             for scent in scents:
51                 sign_str += "(case when instr(scents, ' {} ') > 0 then 1 else 0 end) + ".format(scent)
52             sign_str = sign_str[:-3]
53             sql = "select *, {} as num from perfume_list order by num desc limit 5".format(sign_str)
54 
55             res = curs.execute(sql)
56             res_list = list(res)
57 
58         for item in res_list:
59             li = {
60                 'name': item[1].replace("{} ".format(item[3]), ""),
61                 'en_name': item[2],
62                 'brand': item[3],
63                 'image': item[6],
64                 'scents': item[7],
65             }
66             data.append(li)
67 
68     return {'scent': data}
69 
70 if __name__ == '__main__':
71     uvicorn.run(app=app, host='0.0.0.0', port=8000)