Python连接MySQL并绘制条形图实战

一只小鹿

发布时间：2026-10-13| 6人浏览过

原创

本文介绍了如何使用Python连接MySQL数据库，读取产品、价格、数量和金额数据，并利用Plotly库绘制分组条形图。通过完整的数据库建表脚本和Python代码示例，展示了从数据获取到生成交互式HTML图表的全过程，帮助开发者快速实现数据可视化分析。

Python连接MySQL绘制条形图实战

本文分享了一个使用Python从MySQL数据库读取数据并绘制条形图的具体实现方案，供读者参考学习。

首先准备MySQL数据库脚本，用于创建测试表并插入示例数据。

MySQL测试数据准备


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进行绘图分析，连接数据库并提取相关字段进行可视化处理。

Python绘图代码实现


# -*- coding: utf-8 -*-
#import numpy
import MySQLdb
import plotly.plotly
import plotly.graph_objs as pg
 
host = "localhost"
port = 3306
user = "root"
passwd = "mysql"
charset = "utf8"
dbname = "test"
conn = None
 
try:
  conn = MySQLdb.Connection(
    host = host,
    port = port,
    user = user,
    passwd = passwd,
    db = dbname,
    charset = charset
    )
  cur = conn.cursor(MySQLdb.cursors.DictCursor)
  cur.execute("select * from demo;")
  rows = cur.fetchall()
  #rows = numpy.array(rows)
  lists = [[],[],[],[]]
  for row in rows:
    lists[0].append(row["product"])
    lists[1].append(row["price"])
    lists[2].append(row["quantity"])
    lists[3].append(row["amount"])
  #print(lists)
  #print(lists[0])
  #print(([x[0] for x in lists]))
 
  date_price = pg.Bar( x=lists[0], y=lists[1], name='价格')
  date_quantity = pg.Bar( x=lists[0], y=lists[2], name='数量')
  date_amount = pg.Bar( x=lists[0], y=lists[3], name='总价')
  data = [date_price, date_quantity, date_amount]
  #barmode = [stack,group,overlay,relative]
  layout = pg.Layout( barmode='group',title="各产品销售情况" )
  fig = pg.Figure(data=data, layout=layout)
  plotly.offline.plot(fig, filename = "C:/Users/huangzecheng/Desktop/test.html")
  
finally:
  if conn:
    conn.close()

将上述Python代码保存为文件 bartest.py，通过命令行执行 python bartest.py 脚本，即可生成对应的 html 文件。