python实现自动化报表功能(Oracle/plsql/Excel/多线程)

# -*- coding: utf-8 -*-
# Create time: 2019-10-16
# Update time: 2019-11-28
# Version: 1.0
# Version: 2.0 增加多线程/出错自动重新运行模块
# 导入模块
import cx_Oracle
import os
import pandas as pd
import pandas.io.sql as sql
import time
import openpyxl
import xlwings as xw
import logging 
import re
import threading
# 获取工作目录
sqlpath = os.getcwd() + "\" # 获取当前文件夹目录,若不正确则使用后面的代码直接输入 sqlpath = "E:\"
# 设置运行日志
logging.basicConfig(format="%(asctime)s - %(pathname)s] - %(levelname)s: %(message)s", level=logging.INFO, filename=sqlpath+"log.txt")
# 获取系统日期(用于命名文件)
time_start=time.time()
date = time.strftime("%Y%m%d")
# 定义变量
name = "经营日报" # 模版名称
print("开始运行: %s" %name)
# sql代码文件名
sql1 = sqlpath + "hangxian_ri.sql"
sql2 = sqlpath + "hangxian_zhou.sql"
sql3 = sqlpath + "hangxian_yue.sql"
# 定义空DataFrame(函数中要使用,必须要先定义)
result1 = pd.DataFrame()
result2 = pd.DataFrame()
result3 = pd.DataFrame()
# 定义可以读取运行sql的函数
def read_run_write1(sql_name):
  global result1 # 调用全局变量result1,使用global才能对全局变量进行修改
  print("正在运行代码: %s" %sql_name)
  with open(sql_name,encoding="utf-8-sig",mode="r") as f: # 读取oracle代码,中文编码utf-8-sig,
    sql_list = f.read()
    sql_list = re.sub(r"--.*", "", sql_list) # 去除注释
  connection = cx_Oracle.connect("账号/密码@IP地址/数据库名称")
  code = sql_list.replace("
", " ").replace(";","") # 将换行符转为空格,去除分号
  for i in range(5): # 运行oracle代码,若运行失败则10秒自动重新运行
    try:
      result1 = sql.read_sql(code, connection) # 利用pd包的read_sql函数运行代码,返回DataFrame类型的结果
      break # 代码运行成功则跳出循环
    except:
      print("代码%s运行出错,正在重新运行第%d次" %(sql_name,(i+1)))
      time.sleep(10) # 代码运行失败则10秒后再重新运行
  print("代码运行完成: %s" %sql_name)

def read_run_write2(sql_name):
  global result2
  print("正在运行代码: %s" %sql_name)
  with open(sql_name,encoding="utf-8-sig",mode="r") as f:
    sql_list = f.read()
    sql_list = re.sub(r"--.*", "", sql_list) # 去除注释
  connection = cx_Oracle.connect("账号/密码@IP地址/数据库名称")
  code = sql_list.replace("
", " ").replace(";","") # 将换行符转为空格,去除分号
  for i in range(5):
    try:
      result2 = sql.read_sql(code, connection)
      break
    except:
      print("代码%s运行出错,正在重新运行第%d次" %(sql_name,(i+1)))
      time.sleep(10)
  print("代码运行完成: %s" % sql_name)

def read_run_write3(sql_name):
  global result3
  print("正在运行代码: %s" %sql_name)
  with open(sql_name,encoding="utf-8-sig",mode="r") as f:
    sql_list = f.read()
    sql_list = re.sub(r"--.*", "", sql_list) # 去除注释
  connection = cx_Oracle.connect("账号/密码@IP地址/数据库名称")
  code = sql_list.replace("
", " ").replace(";","") # 将换行符转为空格,去除分号
  for i in range(5): 
    try:
      result3 = sql.read_sql(code, connection)
      break
    except:
      print("代码%s运行出错,正在重新运行第%d次" %(sql_name,(i+1)))
      time.sleep(10)
  print("代码运行完成: %s" % sql_name)

# 多线程运行sql代码
if __name__ == "__main__":
  t1 = threading.Thread(target=read_run_write1, args=(sql1,)) # 调用函数,并传递参数sql1,注意当只有一个参数时,参数后面需要有逗号
  t2 = threading.Thread(target=read_run_write2, args=(sql2,))
  t3 = threading.Thread(target=read_run_write3, args=(sql3,))
  t1.start() # 开始运行
  t2.start()
  t3.start()
  t1.join() # 加入线程,若无join()则运行完一个主线程后则会自动跳出,运行下面的代码,而不会等待其他线程运行完成
  t2.join()
  t3.join()

# 写入数据
print("正在写入数据")
app = xw.App(visible=False,add_book=False) # visible=False后台打开Excel程序
wb = app.books.open(sqlpath+name+".xlsx") # 打开给定只保留标题和公式的空模版
wb.sheets["单日"].range("A4").options(expand="table").value=result1[:].values # result[:].values写法可以去掉索引和标题,将result1写入到表格"单日",从A4单元格开始
wb.sheets["滚动一周"].range("A4").options(expand="table").value=result2[:].values
wb.sheets["月累计"].range("A4").options(expand="table").value=result3[:].values
filename = sqlpath+name+date+".xlsx" # 命名新生成的excel
wb.save(filename) # 另存为新Excel,不改变模版
wb.close() # 关闭工作簿
print(filename+"已自动生成") 
time_end=time.time() 
print("程序运行时间：%.2f s" % (time_end-time_start)) 
logging.info("程序运行时间：%.2f s" % (time_end-time_start)) # 记录程序运行时间到运行日志log.txt中