Python搭建数据查询接口服务

启动一个服务，使用FastAPI框架，增加跨域允许

# -*- coding: UTF-8 -*-
"""
@author: cc
@file: service.py
@time: 2021/05/24
"""

import sqlite3
from fastapi import FastAPI
import uvicorn
import os
from fastapi.middleware.cors import CORSMiddleware

service_path = os.path.normpath(os.path.dirname(os.path.abspath(__file__)))
db = os.path.join(service_path, 'perfume.db')

conn = sqlite3.connect(db)
curs = conn.cursor()

app = FastAPI()

origins = [
    "*",
]

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

@app.get('/')
async def perfume(scent: str):
    data = []
    scents = scent.split(",")
    if scent:
        if len(scents) == 1:
            sql = "select * from perfume_scent where scent='{}'".format(scent)
            res = curs.execute(sql)
            res_list = list(res)
            lens = len(res_list)
            if lens == 0:
                sql = "select * from perfume_scent where scents like '% {} %'".format(scent)
                res = curs.execute(sql)
                res_list = list(res)
        else:
            sign_str = ''
            for scent in scents:
                sign_str += "(case when instr(scents, ' {} ') > 0 then 1 else 0 end) + ".format(scent)
            sign_str = sign_str[:-3]
            sql = "select *, {} as num from perfume_list order by num desc limit 5".format(sign_str)

            res = curs.execute(sql)
            res_list = list(res)

        for item in res_list:
            li = {
                'name': item[1].replace("{} ".format(item[3]), ""),
                'en_name': item[2],
                'brand': item[3],
                'image': item[6],
                'scents': item[7],
            }
            data.append(li)

    return {'scent': data}

if __name__ == '__main__':
    uvicorn.run(app=app, host='0.0.0.0', port=8000)

 

# -*- coding: UTF-8 -*-
"""
@author: cc
@file: service.py
@time: 2021/05/24
"""

import sqlite3
from fastapi import FastAPI
import uvicorn
import os
from fastapi.middleware.cors import CORSMiddleware

service_path = os.path.normpath(os.path.dirname(os.path.abspath(__file__)))
db = os.path.join(service_path, 'perfume.db')

conn = sqlite3.connect(db)
curs = conn.cursor()

app = FastAPI()

origins = [
    "*",
]

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

@app.get('/')
async def perfume(scent: str):
    data = []
    scents = scent.split(",")
    if scent:
        if len(scents) == 1:
            sql = "select * from perfume_scent where scent='{}'".format(scent)
            res = curs.execute(sql)
            res_list = list(res)
            lens = len(res_list)
            if lens == 0:
                sql = "select * from perfume_scent where scents like '% {} %'".format(scent)
                res = curs.execute(sql)
                res_list = list(res)
        else:
            sign_str = ''
            for scent in scents:
                sign_str += "(case when instr(scents, ' {} ') > 0 then 1 else 0 end) + ".format(scent)
            sign_str = sign_str[:-3]
            sql = "select *, {} as num from perfume_list order by num desc limit 5".format(sign_str)

            res = curs.execute(sql)
            res_list = list(res)

        for item in res_list:
            li = {
                'name': item[1].replace("{} ".format(item[3]), ""),
                'en_name': item[2],
                'brand': item[3],
                'image': item[6],
                'scents': item[7],
            }
            data.append(li)

    return {'scent': data}

if __name__ == '__main__':
    uvicorn.run(app=app, host='0.0.0.0', port=8000)

# -*- coding: UTF-8 -*-
"""
@author: cc
@file: smellPlayer.py
@time: 2021/01/12
"""
import serial
import os
from binascii import unhexlify
from crcmod import crcmod

os.path.join('..')
from util import logging
logger = logging.getLogger(__name__)

FRAME_HEAD = 'F5'
FRAME_TAIL = '55'

def crc16Add(str_data):
    crc16 = crcmod.mkCrcFun(0x18005, rev=True, initCrc=0xFFFF, xorOut=0x0000)
    data = str_data.replace(" ", "")
    readcrcout = hex(crc16(unhexlify(data))).upper()
    str_list = list(readcrcout)
    if len(str_list) < 6:
        str_list.insert(2, '0'*(6-len(str_list)))  # 位数不足补0
    crc_data = "".join(str_list)
    return crc_data[2:4]+' '+crc_data[4:]


def ten2sixteen(num, length):
    """
    十进制转十六进制
    :param num: 十进制数字
    :param length: 字节长度
    :return:
    """
    data = str(hex(eval(str(num))))[2:]
    data_len = len(data)
    if data_len % 2 == 1:
        data = '0' + data
        data_len += 1

    sixteen_str = "00 " * (length - data_len//2) + data[0:2] + ' ' + data[2:]
    return sixteen_str.strip()


class SmellPlayer(object):

    isOpen = False  # 串口是否开启，默认关闭
    ser = None  # 串口连接信息

    def __init__(self):
        self.init_device()

    def search_usb(self):
        logger.info("开始查找气味模块串口信息")
        import serial.tools.list_ports
        self.device = None
        port_list = list(serial.tools.list_ports.comports())
        if len(port_list) == 0:
            logger.warning("没有找到USB串口信息，请连接气味模块")
        else:
            for i in range(0, len(port_list)):
                if 'USB Serial' in str(port_list[i]):
                    self.device = str(port_list[i]).split('-')[0].strip()
                    logger.info('气味模块串口信息：{}'.format(str(port_list[i])))
                    break

            if not self.device:
                logger.warning("没有找到气味模块设备，请连接气味模块")

    def connect(self):
        """
        连接串口

        """
        if self.device:
            try:
                ser = serial.Serial(self.device, 19200, timeout=2)
                if ser.isOpen():
                    self.isOpen = True
                    self.ser = ser
                    logger.info("设备连接成功，{}".format(str(self.ser)))
                else:
                    self.isOpen = False
                    logger.warning("设备打开失败")
            except Exception as e:
                logger.warning("设备连接失败：{}".format(e))

    def init_device(self):
        self.search_usb()
        self.connect()

    def play(self, channel, seconds):
        """
        开始播放气味

        :param channel: 信道（路数）
        :return:
        """

        if self.isOpen:
            logger.info("开始播放气味，channel值为：{}， 播放时间 {} 秒".format(channel, seconds))
            self.channel = int(channel)
            self.exec(self.channel, seconds)

    def stop(self):
        """
        停止播放气味

        :return:
        """
        if self.isOpen:
            logger.info("停止播放气味")
            stop_cmd = '12 00 00 01 00 01 00'
            verify = crc16Add(stop_cmd)
            cmd = FRAME_HEAD + ' ' + stop_cmd + ' ' + verify + ' ' + FRAME_TAIL
            self.ser.write(bytes.fromhex(cmd))

    def exec(self, channel, seconds):
        """
        发送协议
        :param channel: 信道（路数）
        :param seconds: 播放时长
        :return:
        """
        play_cmd = '12 01 00 00 02 05'
        scent_channel = ten2sixteen(channel, 1)
        play_time = ten2sixteen(seconds, 4)
        str_data = play_cmd + ' ' + scent_channel + ' ' + play_time
        verify = crc16Add(str_data)
        cmd = FRAME_HEAD + ' ' + str_data + ' ' + verify + ' ' + FRAME_TAIL
        if self.isOpen:
            logger.info("通信协议内容：{}".format(str(cmd)))
            try:
                self.ser.write(bytes.fromhex(cmd))
                logger.info("协议发送成功")
            except Exception as e:
                logger.warning('执行报错：{}'.format(e))

    def close(self):
        """
        关闭串口连接
        :return:
        """

        if self.isOpen:
            logger.info("关闭设备连接")
            self.ser.close()