需求:
查询2022年各月订单量,并用柱状图展示
SQL:
select
date_format(create_time,'%Y-%m') as mon
,count(distinct order_id) as ord_cnt
from prod.order_info
where date_format(create_time,'%Y-%m') between '2022-01' and '2022-12'
group by date_format(create_time,'%Y-%m')
order by date_format(create_time,'%Y-%m')
Python代码:
# -*- coding: utf-8 -*-
import matplotlib.pyplot as plt
import pandas as pd
import sqlalchemy
#import DataFrame
from matplotlib.font_manager import FontProperties
con=sqlalchemy.create_engine(数据库连接信息)
#创建数据库的连接引擎
font = FontProperties(fname=r"c:\windows\fonts\simsun.ttc", size=14)
data=pd.read_sql_query(sql="""select
date_format(create_time,'%%Y-%%m') as mon
,count(distinct order_id) as ord_cnt
from prod.order_info
where date_format(create_time,'%%Y-%%m') between '2022-01' and '2022-12'
group by date_format(create_time,'%%Y-%%m')
order by date_format(create_time,'%%Y-%%m')"""
,con=con,index_col=['mon'])
#双%处理
print(data['ord_cnt'].values)
tuli=['订单量']
df=pd.DataFrame(data['ord_cnt'].values,
columns=tuli,
index=data.index)
df.plot(grid=True,xlim=[0,12],ylim=[0,5000],kind='bar')
plt.title(u"2022年每月订单量",fontproperties=font)
plt.savefig('fig5.jpg')
结果展示:
文章来源:https://www.toymoban.com/news/detail-544407.html
文章来源地址https://www.toymoban.com/news/detail-544407.html
到了这里,关于【Python】查询SQL并用柱状图展示的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!