python-fastapi-mm-zhcs-yj-api/common/DBgetdata.py

from models import *
import pymysql
def dbgetdata(service_code, db,query_list = {}):  # SessionLocal()
    data = []
    database_info = db.query(DatasourceEntity).filter(DatasourceEntity.scope == service_code).first()
    command_info = db.query(CommandEntity).filter(CommandEntity.scope == service_code).filter(
        CommandEntity.datasource == database_info.id).first()
    sql = command_info.sqltext
    # print(query_list)
    for key in query_list.keys():
        if key != 'access_token' and key != 'page' and key != 'limit' and key != 'create_time' and key != 'record_name' and key != 'record_cid':
            sql = sql.replace("{" + key + "}", query_list[key].replace('全部', ''))
        elif key == 'create_time':
            if query_list[key] != '全部':
                sql = sql.replace("{" + key + "}", (
                            'DATE_FORMAT(create_time,\'%Y-%m-%d\') =\'' + query_list[key] + '\' and'))  # DATE_FORMAT(create_time,'%Y-%m-%d') ='{create_time}'
            else:
                sql = sql.replace("{" + key + "}", '')
        elif key == 'record_name' or key == 'record_cid':
            if query_list[key] != '全部':
                sql = sql.replace("{" + key + "}", (key + '=\'' + query_list[key] + '\' and'))
            else:
                sql = sql.replace("{" + key + "}", '')
        # elif key =='record_cid':
        #     if val != '全部':
        #         sql = sql.replace("{" + key + "}", ('record_name =\''+val+'\' and'))
        #     else:
        #         print(111)
        #         sql = sql.replace("{" + key + "}", '')


    conn = pymysql.connect(host=database_info.host,
                           user=database_info.user,
                           password=database_info.password,
                           database=database_info.database,
                           port=database_info.port,
                           charset=database_info.charset)
    cur = conn.cursor()
    cur.execute(sql)
    rows = cur.fetchall()

    # 字段名列表
    colnames = [desc[0] for desc in cur.description]
    pages = 1  # 总页数
    current = 1  # 第几页
    total = len(rows)
    size = len(rows)
    start_index = (current - 1) * size
    end_index = current * size
    if pages <= current:
        # current = pages
        if total == size:
            end_index = (current - 1) * size + total
        elif total % size == 0:
            end_index = current * size
        else:
            end_index = (current - 1) * size + total % size
        start_index = (current - 1) * size
    if total == 0:
        start_index = end_index = 0

    for row in range(start_index, end_index):
        item = {}
        for col in range(len(colnames)):
            field_name = colnames[col]
            item[field_name] = rows[row][col]
        data.append(item)

    conn.close()

    return data