python从Oracle读取数据生成图表

import os
import cx_Oracle
import openpyxl
import time
import csv
import xlrd
from matplotlib import pyplot as plt
from matplotlib import font_manager

import os
os.environ[\'NLS_LANG\'] = \'SIMPLIFIED CHINESE_CHINA.UTF8\' #字符转换，如果配置了环境变量这里可用不要
os.environ[\'TNS_ADMIN\'] = \'D:\\\\Python\\\\instantclient_12_2\' #oracle客户端，如果配置了环境变量这里可用不要
os.environ[\'Path\'] = \'D:\\\\Python\\\\instantclient_12_2\' #自己电脑oracle客户端所在位置

import cx_Oracle

# 方法一：用户名、密码和监听分开写
#  conn=cx_Oracle.connect(\'username/password@host/orcl\')  #连接数据库

# 方法二：用户名、密码和监听写在一起
  conn = cx_Oracle.connect(\'username/password@host:1521/ORCL\') # utf-8显示中文

#方法三：配置监听并连接
#  tns=cx_Oracle.makedsn(\'host\',1521,\'orcl\',encoding = \'utf-8\',nencoding = \"UTF-8\")
#  conn=cx_Oracle.connect(\'username\',\'password\',tns)

curs=conn.cursor()     #获取cursor
printHeader = True # include column headers in each table output
sql=\"select b.name,sum（a.tot_qty） from m_retail a ,c_store b \" \\
  \"where a.c_store_id=b.id and a.billdate=20200923 group by b.name\" #sql语句
x=curs.execute(sql)             #使用cursor进行各种操作
rows= x.fetchall()
for row in rows:
  print(row)

def export_excel(sql,fileName):
  rr = curs.execute(sql)
  rows = curs.fetchall()

  #获取字段名
  title = [ i[0] for i in curs.description ]

  #创建excel表
  wb = openpyxl.Workbook()
  ws = wb.active

  #插入字段名到第一行
  for c in range(len(title)):
    ws.cell(1,c+1,value = title[c])

  #写入查询数据
  for r in range(len(rows)):
    for c in range(len(rows[r])):
      if rows[r][c]: #值不为空时写入，空值不写入
        ws.cell(r+2,c+1,value=str(rows[r][c])) #str()防止用科学计数法写入造成信息丢失
  #
  # #保存sql脚本
  # ws1 = wb.create_sheet(\'sql\')
  # ws1.cell(1,1,value=sql)
  #
  wb.save(fileName)
  wb.close()
  curs.close()

if __name__ == \'__main__\':
# 方法一：用户名、密码和监听分开写
#  conn=cx_Oracle.connect(\'username/password@host/orcl\')  #连接数据库

# 方法二：用户名、密码和监听写在一起
  conn = cx_Oracle.connect(\'username/password@host:1521/ORCL\') # utf-8显示中文

#方法三：配置监听并连接
#  tns=cx_Oracle.makedsn(\'host\',1521,\'orcl\',encoding = \'utf-8\',nencoding = \"UTF-8\")
#  conn=cx_Oracle.connect(\'username\',\'password\',tns)

  curs= conn.cursor()
  #打开sql文件获取sql语句
  with open(\'零售查询.sql\',encoding= \'utf-8\') as sql_0:#encoding= \'utf-8\'
    sql = sql_0.read()
  # sql = \"select b.name as 店仓,sum（a.tot_qty） as 数量 from m_retail a ,c_store b \" \\
  #    \"where a.c_store_id=b.id and a.billdate=20201010 group by b.name\"
  cur_date = time.strftime(\"%Y-%m-%d\", time.localtime())#\"%Y-%m-%d-%H%M%S\",取值日期
  wjm=\'零售2\'+cur_date+\'.xlsx\'
  export_excel(sql,wjm)
  conn.close()

# 打开文件
data = xlrd.open_workbook(wjm)
cur_month = time.strftime(\"%m\", time.localtime())#取值月份
# 查看工作表
# data.sheet_names()
# print(\"sheets：\" + str(data.sheet_names()))
# 通过文件名获得工作表,获取工作表Sheet
# table = data.sheet_by_name(Sheet1)
table =data.sheet_by_index(0)#默认读取sheet1

cel_A1=table.cell(0,0)#取指定单元格的值
cel_B1=table.cell(0,1)
col_A=table.col_values(0,1)#取指定列的值
col_B=table.col_values(1,1)#
col_B = [ int(x) for x in col_B ]#文本转数字
# print(cel_A1)
# print(col_B)
# print(\"整行值：\" + str(table.row_values(0)))
# print(\"整列值：\" + str(table.col_values(0,1)))
# print(\"整列值：\" + str(table.col_values(1,1)))
# print(col_A)

#设置绘图时的，中文字符显示
#my_font=font_manager.FontProperties(fname=r\"C:/Windows/Fonts/simhei.ttf\", size=18)
plt.rcParams [\'font.family\']=[\'sans-serif\']
plt.rcParams [\'font.sans-serif\']=[\'simhei\' ]
plt.rcParams[\'font.size\'] = \'20\'
plt.rcParams[\'xtick.labelsize\']=16
plt.rcParams[\'ytick.labelsize\']=16

#设置图形大小
plt.figure(figsize=(20,8),dpi=80)

#绘制条形图
plt.bar(range(len(col_A)),col_B)

#设置x轴
plt.xticks(range(len(col_A)),col_A)

plt.title(cur_month+\"月份销售分布\") #添加标题
plt.savefig(\"./sig_size3.png\")#将图形保存到工程目录

plt.show()