import matplotlib.pyplot as plt

from pylab import mpl

mpl.rcParams['font.sans-serif'] = ['STZhongsong']    # 指定默认字体：解决plot不能显示中文问题

mpl.rcParams['axes.unicode_minus'] = False

# 引入pymysql包

import pymysql

# 连接数据库并打开library数据库

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='qskh_091')

# 获取游标对象

cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行SQL语句

cur.execute("SELECT * FROM xiaoliang t1 ORDER BY t1.销售数量+'0';")

# 获取执行结果

rows = cur.fetchall()

print(rows)

print("number of records: ", len(rows))

lists=[[],[]]

for row in rows:

    lists[0].append(row["产品代码"])

    lists[1].append(row["销售数量"])

# 关闭游标对象

print(lists)

x=lists[0]

y=lists[1]

plt.bar(x,y,width=0.5,align="center",label="销售数量",color="coral")

plt.title("各产品销售状况")

for a,b in zip(x,y):

 plt.text(a,b,b,ha='center',va="bottom",fontsize=12)

plt.xlabel('产品名称')

plt.ylabel('销售数量')

plt.legend()

plt.show()

cur.close()

# 关闭数据库连接

conn.close()