oracle数据库报表生成框架

[python]代码库

#!/usr/bin/env python
# coding:utf-8
# cx_Oracle 用于访问oracle和导出数据
import cx_Oracle
# xlsxwriter 用于生成xlsx文件
import xlsxwriter
import time
import sys
def orcale_excl(sql,filename):
    conn_str = r"gkxb60/gkxb2018@192.168.70.176:1521/cmsxb"
    con = cx_Oracle.connect(conn_str)
    cursor = con.cursor()
    # 定义SQL脚本 由于脚本包含中文，使用decode('utf-8').encode('gbk') 对其进行转换
    print("报表生成开始。。。。")
    query1 = cursor.execute(sql)  # 执行查询
    print(type(query1))
    title = [i[0] for i in query1.description]
    date_now = time.strftime("%Y%m%d", time.localtime())
    # 文件名及其路径
    report_name = 'D:\\' +filename+ date_now + '.xlsx'
    # 生成xlsx格式oracle查询统计报表
    workbook = xlsxwriter.Workbook(report_name, {'constant_memory': True})
    worksheet = workbook.add_worksheet()
    data = cursor.fetchall()
    worksheet.write_row(0, 0, title)
    for row, row_date in enumerate(data):
        worksheet.write_row(row + 1, 0, row_date)
    print("报表已生成，请在%s中查看" %report_name)
    cursor.close()
    con.close()
    workbook.close()
def getsql():
    cstcode = input("请输入客户代码：")
    createdate=input("请输入创建日期：")
    filename = input("请输入你要生成的报表名称：")
    sqlstr = """ 
      select c.cstcode, c.dname, sum(a.sumvalue)
     from scm_salbill_hdr a, pub_clients c
    where a.cstid = c.cstid"""
    if cstcode != '':                        #按客户查
        sql = sqlstr + " and c.cstcode = " + "'" + cstcode + "'" + " group by c.cstcode, c.dname"
    elif createdate!="":                     #按日期查
        sql = sqlstr + " and a.createyear=" + "'" + createdate + "'" + " group by c.cstcode, c.dname"
    else:
        sql = sqlstr + " group by c.cstcode, c.dname"
    print(sql)
    orcale_excl(sql, filename)
print("""
*******************************************************************
                        报表生成系统V1.0
            欢迎使用，国药控股陕西有限公司，报表生成系统。
            1、按客户生成报表
            0、系统退出
*******************************************************************                        
""")
while True:
    mycheck=input("请选择：")
    if mycheck=='0':
        break
    elif mycheck=='1':
        getsql()