import pymysql, json
import app_conf

conf = app_conf.conf()


def getConn():
    return pymysql.Connect(
        host=conf.get("database", "host"),
        user=conf.get("database", "user"),
        port=conf.getint("database", "port"),
        database=conf.get("database", "database"),
        password=conf.get("database", "password"),
    )


def saveFzData(data) -> bool:
    "将数据存入数据库"
    sql = """
    INSERT INTO table_zhapian 
    VALUES (
    %(uuid)s,%(callednm)s,%(isPushZkj)s,%(called_city_name)s,%(called_province_name)s,%(called_location_county)s,
    %(insert_time)s,%(warninglevel)s,%(zpfeatures)s,%(url)s,%(data_source)s,%(url_or_app_name)s,
    %(called_location_province)s,%(pushZkjTime)s,%(zp_ip_address)s,%(first_login_time)s,%(called_location_city)s
    );
    """
    conn = getConn()
    cursor = conn.cursor()
    try:
        for message in data:
            message.setdefault("uuid",None)
            message.setdefault("isPushZkj",None)
            message.setdefault("called_city_name",None)
            message.setdefault("called_province_name",None)
            message.setdefault("called_location_county",None)
            message.setdefault("insert_time",None)
            message.setdefault("warninglevel",None)
            message.setdefault("zpfeatures",None)
            message.setdefault("url",None)
            message.setdefault("data_source",None)
            message.setdefault("url_or_app_name",None)
            message.setdefault("called_location_province",None)
            message.setdefault("pushZkjTime",None)
            message.setdefault("zp_ip_address",None)
            message.setdefault("first_login_time",None)
            message.setdefault("called_location_city",None)
            print("已保存",message.get("uuid"))
            cursor.execute(sql,message)
        conn.commit()
    except Exception as e:
        print(e)
        return False
    conn.close()
    return True

def LoadFzData():
    "从数据库读取数据"
    sql = """
    SELECT * FROM `table_zhapian` ORDER BY `insert_time` DESC LIMIT 0,1000;
    """
    conn = getConn()
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
    except:
        conn.close()
        return None
    conn.close()

    return results

def total():
    "返回数据库总条目数量"
    sql = """
    SELECT COUNT(*) AS total_count FROM table_zhapian;
    """
    conn = getConn()
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        results = cursor.fetchone()
    except:
        conn.close()
        return None
    conn.close()

    return results[0]