import pymysql

def get_connection():
    # 连接数据库
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='admin',
                           db='cov',
                           charset='utf8')
    cursor = conn.cursor()
    return conn, cursor


def close_connection(conn, cursor):
    # 关闭数据库
    cursor.close()
    conn.close()


def query(sql, *args):
    # 查询数据
    conn, cursor = get_connection()
    cursor.execute(sql, args)
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def get_c1_data():
    """
    :return: 返回大屏div id=c1 的数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = """
    SELECT confirm,confirm_now,heal,dead
    from history
    ORDER BY ds desc
    LIMIT 1;
    """
    res = query(sql)
    return res[0]


def get_c2_data():
    """
    :return:  返回各省数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,sum(confirm_now) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res


def get_l1_data():
    sql = "select ds,confirm_add,suspect_add,heal_add,dead_add from history"
    res = query(sql)
    return res


def get_l2_data():
    # 取时间戳最新的那组数据
    sql = "select end_update_time,province,city,county,address,type" \
          " from risk_area " \
          "where end_update_time=(select end_update_time " \
          "from risk_area " \
          "order by end_update_time desc limit 1) "
    res = query(sql)
    return res


def get_r1_data():
    """
    :return:  返回剩余确诊人数前5名的省份
    """
    sql = '''
    SELECT province,confirm from
    (SELECT province,SUM(confirm_now) as confirm FROM details
    WHERE update_time = (SELECT update_time FROM details ORDER BY update_time DESC LIMIT 1)
    GROUP BY province) as a
    ORDER BY confirm DESC
    LIMIT 2,5
    '''
    res = query(sql)
    return res


def get_r2_data():
    """
    :return:  返回最近的30条热搜
    """
    sql = 'select content from hotsearch order by id desc limit 20'
    res = query(sql)  # 格式 (('民警抗疫一线奋战16天牺牲 1037364',), ('四川再派两批医疗队 1537382',)
    return res

if __name__ == '__main__':
    print(get_c1_data())