# -*- coding: utf-8 -*-
# Create time: 2019-10-16
# Update time: 2019-11-28
# Version: 1.0
# Version: 2.0 增加多線程/出錯(cuò)自動重新運(yùn)行模塊
# 導(dǎo)入模塊
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() + '\\' # 獲取當(dāng)前文件夾目錄,若不正確則使用后面的代碼直接輸入 sqlpath = 'E:\\'
# 設(shè)置運(yùn)行日志
logging.basicConfig(format='%(asctime)s - %(pathname)s] - %(levelname)s: %(message)s', level=logging.INFO, filename=sqlpath+'log.txt')
# 獲取系統(tǒng)日期(用于命名文件)
time_start=time.time()
date = time.strftime("%Y%m%d")
# 定義變量
name = '經(jīng)營日報(bào)' # 模版名稱
print('開始運(yùn)行: %s' %name)
# sql代碼文件名
sql1 = sqlpath + 'hangxian_ri.sql'
sql2 = sqlpath + 'hangxian_zhou.sql'
sql3 = sqlpath + 'hangxian_yue.sql'
# 定義空DataFrame(函數(shù)中要使用,必須要先定義)
result1 = pd.DataFrame()
result2 = pd.DataFrame()
result3 = pd.DataFrame()
# 定義可以讀取運(yùn)行sql的函數(shù)
def read_run_write1(sql_name):
  global result1 # 調(diào)用全局變量result1,使用global才能對全局變量進(jìn)行修改
  print('正在運(yùn)行代碼: %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地址/數(shù)據(jù)庫名稱')
  code = sql_list.replace('\n', ' ').replace(';','') # 將換行符轉(zhuǎn)為空格,去除分號
  for i in range(5): # 運(yùn)行oracle代碼,若運(yùn)行失敗則10秒自動重新運(yùn)行
    try:
      result1 = sql.read_sql(code, connection) # 利用pd包的read_sql函數(shù)運(yùn)行代碼,返回DataFrame類型的結(jié)果
      break # 代碼運(yùn)行成功則跳出循環(huán)
    except:
      print('代碼%s運(yùn)行出錯(cuò),正在重新運(yùn)行第%d次' %(sql_name,(i+1)))
      time.sleep(10) # 代碼運(yùn)行失敗則10秒后再重新運(yùn)行
  print('代碼運(yùn)行完成: %s' %sql_name)

def read_run_write2(sql_name):
  global result2
  print('正在運(yùn)行代碼: %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地址/數(shù)據(jù)庫名稱')
  code = sql_list.replace('\n', ' ').replace(';','') # 將換行符轉(zhuǎn)為空格,去除分號
  for i in range(5):
    try:
      result2 = sql.read_sql(code, connection)
      break
    except:
      print('代碼%s運(yùn)行出錯(cuò),正在重新運(yùn)行第%d次' %(sql_name,(i+1)))
      time.sleep(10)
  print('代碼運(yùn)行完成: %s' % sql_name)

def read_run_write3(sql_name):
  global result3
  print('正在運(yùn)行代碼: %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地址/數(shù)據(jù)庫名稱')
  code = sql_list.replace('\n', ' ').replace(';','') # 將換行符轉(zhuǎn)為空格,去除分號
  for i in range(5): 
    try:
      result3 = sql.read_sql(code, connection)
      break
    except:
      print('代碼%s運(yùn)行出錯(cuò),正在重新運(yùn)行第%d次' %(sql_name,(i+1)))
      time.sleep(10)
  print('代碼運(yùn)行完成: %s' % sql_name)

# 多線程運(yùn)行sql代碼
if __name__ == '__main__':
  t1 = threading.Thread(target=read_run_write1, args=(sql1,)) # 調(diào)用函數(shù),并傳遞參數(shù)sql1,注意當(dāng)只有一個(gè)參數(shù)時(shí),參數(shù)后面需要有逗號
  t2 = threading.Thread(target=read_run_write2, args=(sql2,))
  t3 = threading.Thread(target=read_run_write3, args=(sql3,))
  t1.start() # 開始運(yùn)行
  t2.start()
  t3.start()
  t1.join() # 加入線程,若無join()則運(yùn)行完一個(gè)主線程后則會自動跳出,運(yùn)行下面的代碼,而不會等待其他線程運(yùn)行完成
  t2.join()
  t3.join()

# 寫入數(shù)據(jù)
print('正在寫入數(shù)據(jù)')
app = xw.App(visible=False,add_book=False) # visible=False后臺打開Excel程序
wb = app.books.open(sqlpath+name+'.xlsx') # 打開給定只保留標(biāo)題和公式的空模版
wb.sheets['單日'].range('A4').options(expand='table').value=result1[:].values # result[:].values寫法可以去掉索引和標(biāo)題,將result1寫入到表格'單日',從A4單元格開始
wb.sheets['滾動一周'].range('A4').options(expand='table').value=result2[:].values
wb.sheets['月累計(jì)'].range('A4').options(expand='table').value=result3[:].values
filename = sqlpath+name+date+'.xlsx' # 命名新生成的excel
wb.save(filename) # 另存為新Excel,不改變模版
wb.close() # 關(guān)閉工作簿
print(filename+'已自動生成') 
time_end=time.time() 
print("程序運(yùn)行時(shí)間：%.2f s" % (time_end-time_start)) 
logging.info("程序運(yùn)行時(shí)間：%.2f s" % (time_end-time_start)) # 記錄程序運(yùn)行時(shí)間到運(yùn)行日志log.txt中