python基于数据库调取数据画动态折线图学习pyecharts

数据库：

 

import pandas as pd
import datetime
from pyecharts import options as opts
from pyecharts.charts import Line, Timeline
import pymysql
import numpy as np
import time
import datetime #导入日期时间模块


the_time = {}
sal = {}

host='*****'#数据库ip地址（本机地址）
user='*****'#数据库用户
password='*****'#数据库连接密码
db='*****'#数据库的名称

conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db, charset='utf8')
# 2.获取游标，来进行查询,这样默认查询的都为tuple列表
cursor = conn.cursor(pymysql.cursors.DictCursor)  # 这样查询的为字典
data = cursor.execute('select id from tb_cha_sal where message=%s', '微生物已经超标！')
selectResultList = cursor.fetchall()
id=selectResultList[- 1]['id']
data = cursor.execute('select Salmonella,The_time from tb_cha_sal')
selectResultList = cursor.fetchall()
cursor.close()
conn.close()
color0 = ['#FF76A2','#000000']

for i in range(21):
    the_time[i] = selectResultList[id - 20 + i]['The_time']
    the_time[i] = pd.to_datetime(the_time[i], format="%Y-%m-%d")
    sal[i] = selectResultList[id-20+i]['Salmonella']
The_time = pd.Series(the_time)
Sal = pd.Series(sal)

tl = Timeline()  #时间轴的工具包
for i in range(0, len(The_time)):
    x_max = list(The_time)[i]+datetime.timedelta(hours=3)
    y_max = int(max(list(Sal)[0:i+1]))+4
    title_date = The_time[i]

    c = (
        Line(
            init_opts=opts.InitOpts(
            width='1500px',
            height='900px', )
        )
        .add_xaxis(list(The_time)[0:i])
        .add_yaxis(
            series_name="",
            y_axis=list(Sal)[0:i], is_smooth=True,is_symbol_show=True,
            linestyle_opts=opts.LineStyleOpts(color=color0[0],width=3),
            markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(name="阈值",y=7.0,symbol_size=2)])

        )


        .set_global_opts(
            title_opts=opts.TitleOpts("微生物含量显示图\n\n{0}\n\n微生物含量：{1}".format(title_date,Sal[i]),pos_left=350,padding=[20,20]),
            xaxis_opts=opts.AxisOpts(type_="time",max_=x_max),#, interval=10,min_=i-5,split_number=20,axistick_opts=opts.AxisTickOpts(length=2500),axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color="grey"))
            yaxis_opts=opts.AxisOpts(min_=3.5,max_=y_max),#坐标轴颜色,axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color="grey"))
        )
    )
    tl.add(c, "{}".format(list(The_time)[i]))
    tl.add_schema(axis_type='time',play_interval=300, pos_left="center",pos_bottom="-29px",is_auto_play=True,
                  is_loop_play=False,width="780px",height="50px")#,pos_top=470,label_opts = opts.LabelOpts(is_show=False)
tl.render("微生物.html")

运行结果：