Python利用Plotly实现对MySQL中的数据可视化

Mysql表数据：

demo.sql内容

create table demo(
 id int
,product varchar(50)
,price decimal(18,2)
,quantity int
,amount decimal(18,2)
,orderdate datetime
);
insert into demo
select 1,'AAA',15.2,5,76,'2017-09-09' union all
select 2,'BBB',10,6,60,'2016-05-18' union all
select 3,'CCC',21,11,231,'2014-07-11' union all
select 4,'DDD',55,2,110,'2016-12-24' union all
select 5,'EEE',20,4,80,'2017-02-08' union all
select 6,'FFF',45,2,90,'2016-08-19' union all
select 7,'GGG',33,5,165,'2017-10-11' union all
select 8,'HHH',5,40,200,'2014-08-30' union all
select 9,'III',3,20,60,'2015-02-25' union all
select 10,'JJJ',10,15,150,'2015-11-02';

 

实现效果如下：

 

Python代码：

import pymysql
import pandas as pd
import plotly.plotly
import plotly.graph_objs as pg


def bar_chart(host, port, user, passwd, dbname, charset,output_path):
    try:
        conn = pymysql.Connection(
            host=host,
            port=port,
            user=user,
            passwd=passwd,
            db=dbname,
            charset=charset
        )
        cur = conn.cursor()
        cur.execute("select * from demo;")
        # cursor对象使用MySQL查询字符串执行查询，返回一个包含多个元组的元组——每行对应一个元组
        rows = cur.fetchall()
        # print(rows)

        # 使用Pandas的DataFrame来处理每一行要比使用一个包含元组的元组方便
        # 下面的Python代码片段将所有行转化为DataFrame实例
        df = pd.DataFrame([[ij for ij in i] for i in rows])
        print(df)
        df.rename(columns={0: 'id', 1: 'product', 2: 'price', 3: 'quantity', 4: 'amount', 5: 'orderdate'}, inplace=True)
        # df = df.sort(['LifeExpectancy'], ascending=[1])

        date_price = pg.Bar(x=df["product"], y=df["price"], name='价格')
        date_quantity = pg.Bar(x=df["product"], y=df["quantity"], name='数量')
        date_amount = pg.Bar(x=df["product"], y=df["amount"], name='总价')
        data = [date_price, date_quantity, date_amount]

        layout = pg.Layout(barmode='group', title="各产品销售情况")
        fig = pg.Figure(data=data, layout=layout)
        plotly.offline.plot(fig, filename=output_path)

    finally:
        if conn:
            conn.close()


if __name__ == '__main__':
    output_path = "C:/Users/fuqia/Desktop/bar.html"
    bar_chart("localhost", 3306, "root", "123456", "test", "utf8", output_path)

 

 输出df内容如下：