参数为空时,返回数据库中所有数据,根据此设计的后端响应函数(主要在查表语句拼接上)

发布时间 2023-08-03 17:00:38作者: 荣耀之心
把函数的功能转移到查表上,不是查找所有数据之后处理,而是根据参数来组装不同的查表语句;利用了SQL语句中的WHERE 1 AND 1 的运算特性,代码如下:
def station_specialchart_http_deal(time="", train=""):
    """
    获取 站场-特殊场景 需要响应的数据
    :param time: 时间,为空时返回数据库全部所有相关数据
    :param train: 机车,为空时返回数据库中所有机车的集合
    :return: station, crossline, yxhWork, emergencyBreak, commonBreak, unload, bigUnlock, smallUnlock
    """
    if train == "":  # tarin为空,读取全部列车信息
        TRAIN_SQL_EXTENCE = 1
    else:  # 读取该train的特殊场景所涉及到的信息
        train_model_num = (train.split("-"))
        train_no_str = str(get_train_int_by_str(train_model_num[0])) + "-" + train_model_num[1]
        TRAIN_SQL_EXTENCE = "Loco_NO = '%s'" % train_no_str
    if time == "":
        TIME_SQL_EXTENCE = 1
    else:
        TIME_SQL_EXTENCE = "Time LIKE '{0}%'".format(time)
    SQL_EXTENCE = "WHERE ({0} AND {1}".format(TIME_SQL_EXTENCE, TRAIN_SQL_EXTENCE)
    station_special_scens = {
        "大同站": [0, 0, 0, 0, 0, 0, 0],
        # crossline, yxhWork, emergencyBreak, commonBreak, unload, bigUnlock, smallUnlock
        "大同北站": [0, 0, 0, 0, 0, 0, 0],
        "大同东站": [0, 0, 0, 0, 0, 0, 0],
        "大同西": [0, 0, 0, 0, 0, 0, 0],
        "大西I场": [0, 0, 0, 0, 0, 0, 0],
        "湖东编组站I场": [0, 0, 0, 0, 0, 0, 0],
        "湖东编组站II场": [0, 0, 0, 0, 0, 0, 0],
        "湖东III场峰尾": [0, 0, 0, 0, 0, 0, 0],
        "平旺站": [0, 0, 0, 0, 0, 0, 0],
        "古店": [0, 0, 0, 0, 0, 0, 0],
    }
    global SQL_connect
    table_list = SQL_connect.get_table_list()  # 获取当前数据库中所有表的名字
    for i in table_list:
        temp_tablename = i.split("_")
        if len(temp_tablename) < 2:
            continue
        insert_station = get_station_str_by_int(int(temp_tablename[0]))  # 要插入到的站场名称
        insert_special_scen = specialscen_str_to_name(temp_tablename[1])
        insert_index = -1  # 要插入到特殊场景数组中的位置
        match insert_special_scen:
            case "穿越正线":
                insert_index = 0
            case "压信号作业":
                insert_index = 1
            case "LKJ紧急制动":
                insert_index = 2
            case "常用制动":
                insert_index = 3
            case "卸载":
                insert_index = 4
            case "大解锁":
                insert_index = 5
            case "小解锁":
                insert_index = 6
        SQL_ORDER = "SELECT COUNT(*) AS RESULT FROM %s" % i + " " + SQL_EXTENCE

参数不为空时的返回:

  时间不空:time = "2023-05" ,train = "东风7-3003”

    SELECT COUNT(*) AS RESULT FROM 38394_uninstal WHERE (Time LIKE '2023-05%' AND Loco_NO = '110-3003')

  机车不为空:time = "" ,train = "东风7-3003”

    SELECT COUNT(*) AS RESULT FROM 38394_small_unlock WHERE (1 AND Loco_NO = '110-3003')

  时间不为空:

    SELECT COUNT(*) AS RESULT FROM 12393_mainline WHERE (Time LIKE '2023-05%' AND 1)

参数为空时的返回:

    SELECT COUNT(*) AS RESULT FROM 38394_small_unlock WHERE (1 AND 1)

这样查询操作只有一次,而且不用将所有结果差出来后再在代码里面处理,很高效