sql_tools.py

import pymysql

from air_case.public1.public1.public1 import *
from common.fileUtls import FileUtils


def get_db():
    # 打开数据库连接
    db = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2",
                         charset="utf8")
    return db


def get_sql_conn():
    """
    获取数据库连接
    """
    db = get_db()
    cursor = db.cursor()
    return db, cursor


def get_index_dict(cursor):
    """
    获取数据库对应表中的字段名
    """
    index_dict = dict()
    index = 0
    for desc in cursor.description:
        index_dict[desc[0]] = index
        index = index + 1
    return index_dict


def get_dict_data_sql(cursor, sql):
    """
    运行sql语句，获取结果，并根据表中字段名，转化成dict格式（默认是tuple格式）
    """
    cursor.execute(sql)
    data = cursor.fetchall()
    index_dict = get_index_dict(cursor)
    res = []
    for datai in data:
        resi = dict()
        for indexi in index_dict:
            resi[indexi] = datai[index_dict[indexi]]
        res.append(resi)
    return res


def main2(hos_goods_id):
    db, cursor = get_sql_conn()
    sql = """SELECT hos_goods_code,
                    hos_id,
                    prov_id,
                    prov_hos_goods_id,
                    srv_id,
                    mdm_goods_code,
                    mdm_goods_spec_code,
                    spd_goods_code,
                    goods_name,
                    goods_general_name,
                    goods_mfrs_id,
                    goods_mfrs_name,
                    goods_reg_cert,
                    goods_agent_mfrs_id,
                    brand,
                    short_pinyin,
                    kind_sixtyeight_code,
                    unit,