疫情可视化大屏的实现

主要是对热搜榜的词进行爬取。

# -*- coding:utf-8 -*-

import time
import pymysql
import string
from jieba.analyse import extract_tags

def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")

# 连接数据库
def get_conn():
    conn = pymysql.connect(
        host="localhost",
        user="root",
        password="12345678",
        db="yiqing",
        charset="utf8",
        port=3306,
    )
    # 创建游标：
    cursor = conn.cursor()
    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql, *args):
    '''
    :param sql:
    :param args:
    :return:返回结果，((),())形式
    '''
    conn, cursor= get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall() # 获取结果
    close_conn(conn, cursor)
    return res

def get_c1_data():
    sql = "select sum(confirm)," \
    "(select suspect from history order by ds desc limit 1)," \
    "sum(heal)," \
    "sum(dead) " \
    "from details " \
    "where update_time=(select update_time from details order by update_time desc limit 1) "

    res = query(sql)
    return res[0]

def get_c2_data():
    sql = "select province,sum(confirm) 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,suspect,heal,dead from history"

    res = query(sql)
    return res

def get_l2_data():
    sql = "select ds,confirm_add,suspect_add,heal_add,dead_add from history"

    res = query(sql)
    return res

def get_r1_data():
    # union_all 两块相加
    sql = 'SELECT city,confirm FROM ' \
    '(select city,confirm from details  ' \
    'where update_time=(select update_time from details order by update_time desc limit 1) ' \
    'and province not in ("湖北","北京","上海","天津","重庆") ' \
    'union all ' \
    'select province as city,sum(confirm) as confirm from details  ' \
    'where update_time=(select update_time from details order by update_time desc limit 1) ' \
    'and province in ("北京","上海","天津","重庆") group by province) as a ' \
    'ORDER BY confirm DESC limit 5'

    res = query(sql)
    return res

def get_r2_data():
    sql = "select content from jinri_hot order by id desc limit 20"

    res = query(sql)
    return res
from flask import jsonify
if __name__ == '__main__':
    # get_l1_data()
    #  get_r1_data()
    print(get_r2_data())
    pass

爬取的数据结果如下：