Python利用Plotly實現對MySQL中的資料視覺效果

標籤：arc   try   etc   obj   字元   cursor   into   fetchall   test   

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 demoselect 1,‘AAA‘,15.2,5,76,‘2017-09-09‘ union allselect 2,‘BBB‘,10,6,60,‘2016-05-18‘ union allselect 3,‘CCC‘,21,11,231,‘2014-07-11‘ union allselect 4,‘DDD‘,55,2,110,‘2016-12-24‘ union allselect 5,‘EEE‘,20,4,80,‘2017-02-08‘ union allselect 6,‘FFF‘,45,2,90,‘2016-08-19‘ union allselect 7,‘GGG‘,33,5,165,‘2017-10-11‘ union allselect 8,‘HHH‘,5,40,200,‘2014-08-30‘ union allselect 9,‘III‘,3,20,60,‘2015-02-25‘ union allselect 10,‘JJJ‘,10,15,150,‘2015-11-02‘;

 

實現效果如下：

 

Python代碼：

 1 import pymysql 2 import pandas as pd 3 import plotly.plotly 4 import plotly.graph_objs as pg 5  6  7 def bar_chart(host, port, user, passwd, dbname, charset,output_path): 8     try: 9         conn = pymysql.Connection(10             host=host,11             port=port,12             user=user,13             passwd=passwd,14             db=dbname,15             charset=charset16         )17         cur = conn.cursor()18         cur.execute("select * from demo;")19         # cursor對象使用MySQL查詢字串執行查詢，返回一個包含多個元組的元組——每行對應一個元組20         rows = cur.fetchall()21         # print(rows)22 23         # 使用Pandas的DataFrame來處理每一行要比使用一個包含元組的元組方便24         # 下面的Python程式碼片段將所有行轉化為DataFrame執行個體25         df = pd.DataFrame([[ij for ij in i] for i in rows])26         print(df)27         df.rename(columns={0: ‘id‘, 1: ‘product‘, 2: ‘price‘, 3: ‘quantity‘, 4: ‘amount‘, 5: ‘orderdate‘}, inplace=True)28         # df = df.sort([‘LifeExpectancy‘], ascending=[1])29 30         date_price = pg.Bar(x=df["product"], y=df["price"], name=‘價格‘)31         date_quantity = pg.Bar(x=df["product"], y=df["quantity"], name=‘數量‘)32         date_amount = pg.Bar(x=df["product"], y=df["amount"], name=‘總價‘)33         data = [date_price, date_quantity, date_amount]34 35         layout = pg.Layout(barmode=‘group‘, title="各產品銷售情況")36         fig = pg.Figure(data=data, layout=layout)37         plotly.offline.plot(fig, filename=output_path)38 39     finally:40         if conn:41             conn.close()42 43 44 if __name__ == ‘__main__‘:45     output_path = "C:/Users/fuqia/Desktop/bar.html"46     bar_chart("localhost", 3306, "root", "123456", "test", "utf8", output_path)

 

 輸出df內容如下：

Python利用Plotly實現對MySQL中的資料視覺效果