# coding:utf-8
from django.db import connection


# 执行django原始sql语句 并返回一个数组对象
def query_json(sql):
    # 获得一个游标(cursor)对象
    cursor = connection.cursor()
    cursor.execute(sql)
    rawData = cursor.fetchall()
    col_names = [desc[0] for desc in cursor.description]
    result = []
    for row in rawData:
        objDict = {}
        # 把每一行的数据遍历出来放到Dict中
        for index, value in enumerate(row):
            objDict[col_names[index]] = value
        result.append(objDict)
    return result


def insert_batch(table_name: str, dict_list: list) -> None:
    """
    向{table_name}表批量插入数据,
    {table_name}: str，表名，建议带上schema，如label.t1027
    {dict_list}: dict的list，欲插入数据的列表，如[{"id": "1", "name": "Chris"}, {"id": "2", "name": "Harris"}]
    """
    if 0 == len(dict_list):
        return
    # 获得一个游标(cursor)对象
    cursor = connection.cursor()
    # 构建sql语句
    keys = ', '.join(dict_list[0].keys())
    values = []
    for val in dict_list:
        value = '(' + str(val.values()).lstrip('dict_values([').rstrip('])') + ')'
        values.append(value)
    sql = "INSERT INTO {table_name}({keys}) VALUES {values}".format(table_name=table_name, keys=keys,
                                                                    values=', '.join(values))
    cursor.execute(sql)
    connection.commit()


def get_label_json(label_code):
    tblName = "t" + str(label_code).strip(' ')[0:4]
    sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' ORDER BY tm DESC"
    return query_json(sql)


# 根据标签号取label_id, 没有返回0
def get_label_id(label_code):
    try:
        tblName = "t" + str(label_code).strip(' ')[0:4]
        sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' ORDER BY tm DESC"
        ret = query_json(sql)
        return int(ret[0]["id"]) if len(ret) > 0 else 0
    except Exception as e:
        raise e


# 根据标签号取label_pk_id, 没有返回0
def get_label_pk_id(label_code):
    try:
        tblName = "t" + str(label_code).strip(' ')[0:4]
        sql = "SELECT * FROM label." + tblName + f" WHERE code = '{label_code}' AND package_id NOT IN (3,4) ORDER BY tm DESC"
        ret = query_json(sql)
        return int(ret[0]["parent_id"]) if len(ret) > 0 else 0
    except Exception as e:
        raise e
