【Python】查询SQL并用柱状图展示

需求：

查询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')

结果展示：