简谈python从Oracle读取数据生成图形

            1
            import
             os

            2
            import
             cx_Oracle

            3
            import
             openpyxl

            4
            import
             time

            5
            import
             csv

            6
            import
             xlrd

            7
            from matplotlib import pyplot as plt
8from matplotlib import font_manager

             1
            import
             os

             2 os.environ[‘NLS_LANG‘] = ‘SIMPLIFIED CHINESE_CHINA.UTF8‘#字符转换，如果配置了环境变量这里可用不要 3 os.environ[‘TNS_ADMIN‘] = ‘D:\Python\instantclient_12_2‘#oracle客户端，如果配置了环境变量这里可用不要 4 os.environ[‘Path‘] = ‘D:\Python\instantclient_12_2‘#自己电脑oracle客户端所在位置 5 6import cx_Oracle
 7 8# 方法一：用户名、密码和监听分开写 9#   conn=cx_Oracle.connect(‘username/password@host/orcl‘)    #连接数据库1011# 方法二：用户名、密码和监听写在一起12     conn = cx_Oracle.connect(‘username/password@host:1521/ORCL‘)  # utf-8显示中文1314#方法三：配置监听并连接15#   tns=cx_Oracle.makedsn(‘host‘,1521,‘orcl‘,encoding = ‘utf-8‘,nencoding = "UTF-8")16#   conn=cx_Oracle.connect(‘username‘,‘password‘,tns)1718 curs=conn.cursor()          #获取cursor19 printHeader = True # include column headers in each table output20 sql="select b.name,sum（a.tot_qty） from m_retail a ,c_store b "21"where a.c_store_id=b.id and a.billdate=20200923 group by b.name"#sql语句22 x=curs.execute(sql)                         #使用cursor进行各种操作23 rows= x.fetchall()
24for row in rows:
25print(row)

             1
            def
             export_excel(sql,fileName):

             2     rr = curs.execute(sql)
 3     rows = curs.fetchall()
 4 5#获取字段名 6     title = [ i[0] for i in curs.description ]
 7 8#创建excel表 9     wb = openpyxl.Workbook()
10     ws = wb.active
1112#插入字段名到第一行13for c in range(len(title)):
14         ws.cell(1,c+1,value = title[c])
1516#写入查询数据17for r in range(len(rows)):
18for c in range(len(rows[r])):
19if rows[r][c]: #值不为空时写入，空值不写入20                 ws.cell(r+2,c+1,value=str(rows[r][c])) #str()防止用科学计数法写入造成信息丢失21#
22# #保存sql脚本23# ws1 = wb.create_sheet(‘sql‘)24# ws1.cell(1,1,value=sql)25#
26    wb.save(fileName)
27    wb.close()
28    curs.close()
2930if__name__ == ‘__main__‘:
31# 方法一：用户名、密码和监听分开写32#   conn=cx_Oracle.connect(‘username/password@host/orcl‘)    #连接数据库3334# 方法二：用户名、密码和监听写在一起35     conn = cx_Oracle.connect(‘username/password@host:1521/ORCL‘)  # utf-8显示中文3637#方法三：配置监听并连接38#   tns=cx_Oracle.makedsn(‘host‘,1521,‘orcl‘,encoding = ‘utf-8‘,nencoding = "UTF-8")39#   conn=cx_Oracle.connect(‘username‘,‘password‘,tns)4041     curs= conn.cursor()
42#打开sql文件获取sql语句43     with open(‘零售查询.sql‘,encoding= ‘utf-8‘) as sql_0:#encoding= ‘utf-8‘44         sql = sql_0.read()
45# sql = "select b.name as 店仓,sum（a.tot_qty） as 数量 from m_retail a ,c_store b " 46#       "where a.c_store_id=b.id and a.billdate=20201010 group by b.name"47     cur_date = time.strftime("%Y-%m-%d", time.localtime())#"%Y-%m-%d-%H%M%S",取值日期48     wjm=‘零售2‘+cur_date+‘.xlsx‘49    export_excel(sql,wjm)
50     conn.close()

             1
            #
             打开文件
             2 data = xlrd.open_workbook(wjm)
 3 cur_month = time.strftime("%m", time.localtime())#取值月份 4# 查看工作表 5# data.sheet_names() 6# print("sheets：" + str(data.sheet_names())) 7# 通过文件名获得工作表,获取工作表Sheet 8# table = data.sheet_by_name(Sheet1) 9 table =data.sheet_by_index(0)#默认读取sheet11011 cel_A1=table.cell(0,0)#取指定单元格的值12 cel_B1=table.cell(0,1)
13 col_A=table.col_values(0,1)#取指定列的值14 col_B=table.col_values(1,1)#
15 col_B = [ int(x) for x in col_B ]#文本转数字16# print(cel_A1)17# print(col_B)18# print("整行值：" + str(table.row_values(0)))19# print("整列值：" + str(table.col_values(0,1)))20# print("整列值：" + str(table.col_values(1,1)))21# print(col_A)

             1
            #
            设置绘图时的，中文字符显示
             2
            #
            my_font=font_manager.FontProperties(fname=r"C:/Windows/Fonts/simhei.ttf", size=18)
             3 plt.rcParams [‘font.family‘]=[‘sans-serif‘]
 4 plt.rcParams [‘font.sans-serif‘]=[‘simhei‘ ]
 5 plt.rcParams[‘font.size‘] = ‘20‘ 6 plt.rcParams[‘xtick.labelsize‘]=16
 7 plt.rcParams[‘ytick.labelsize‘]=16
 8 9#设置图形大小10 plt.figure(figsize=(20,8),dpi=80)
1112#绘制条形图13plt.bar(range(len(col_A)),col_B)
1415#设置x轴16plt.xticks(range(len(col_A)),col_A)
1718 plt.title(cur_month+"月份销售分布") #添加标题19 plt.savefig("./sig_size3.png")#将图形保存到工程目录2021 plt.show()