#Python##数据库查询##多库联查#
一、简要说明
博主主要工作为数仓建设,其中涉及工作较多,包括:数据标准、主数据、数据资源目录维护、数仓等,其中数仓建设过程中最重要的一环就是数据同步的及时性、准确性。在多年建设过程中发现,数仓质检过程中最严重、易出现的问题就是数据同步不及时问题,故而有了许多数据同步检查的需求。
由于生产环境特殊性,博主从事的数仓建设需要从多达10几个同构源数据库中汇集数据到数仓中,这就导致了统一脚本 需要在所有生产库进行执行查询排查同步问题的现状。故而博主考虑了多种方式进行自动化执行:
1、通过批处理命令和SQL脚本输出查询结果,此方法简单快捷,只需要替换脚本便可执行,不过输出文件内容需要再次处理加工,仍需少量工作量。
2、通过Python处理输出成标准格式下的excel文件内容,同时可以在历史记录中在扩展,保留历史查询记录对比分析。
下面开始分析Python执行同构多库查询的脚本。
二、Python 脚本开发过程
1)导入使用的包,需要先安装相应的包
# encoding:utf-8
import datetime
import openpyxl
import pandas as pd
import win32ui
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
from queryData import MyOracle
2)使用win32空间打开对话框,可实现选择相应的脚本文件
数据源文件内容 格式
# 打开文件选择对话框,读取sql文件
curDate = datetime.datetime.now().strftime('%Y%m%d%H%M')
rowNum = 0
dlg = win32ui.CreateFileDialog(1) # 1表示打开文件对话框
dlg.SetOFNInitialDir(r'C:\Users\Administrator\Desktop\test') # 设置打开文件对话框中的初始显示目录
dlg.SetOFNTitle("请选择需要执行的脚本文件")
dlg.DoModal()
sqlFilePath = dlg.GetPathName() # 获取选择的文件名称
#确认执行了脚本文件则继续,否则继续执行毫无意义
if sqlFilePath:
# 根据选择的脚本文件名称创建结果Excel文件
stp = sqlFilePath.find('.', 0)
resFilePath = sqlFilePath[0: stp] + ".xlsx"
print("========" + resFilePath + "========")
resSheetName = "批量数据查询结果"
try:
# 尝试加载现有的Excel文件
workbook = load_workbook(resFilePath)
sheet_exists = resSheetName in workbook.sheetnames
if sheet_exists:
print("Excel文件和工作表已存在。")
else:
print("Excel文件存在,但工作表不存在。")
# 创建新的工作表
workbook.create_sheet(resSheetName)
workbook.save(resFilePath)
except FileNotFoundError:
workbook = Workbook()
workbook.create_sheet(resSheetName)
print("已创建Excel文件。")
workbook.save(resFilePath)
# 保存Excel文件
sqlFile = open(sqlFilePath, 'r')
content = sqlFile.readlines()[0]
3)读取数据库配置文件,根据数据库配置信息循环链接数据库执行SQL脚本
workbook = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\test\数据库配置信息.xlsx')
worksheet = workbook['数据库配置']
res_workbook = load_workbook(resFilePath)
res_worksheet = res_workbook[resSheetName]
res_worksheet.insert_rows(0, 1)
for row in worksheet.iter_rows():
row_data = []
for cell in row:
row_data.append(cell.value)
xh = row_data[0]
ds_id = row_data[1]
ds_username = row_data[2]
ds_pwd = row_data[3]
ds_tnsName = str(row_data[4]) + ":" + str(row_data[5]) + "/" + str(row_data[6])
print("**********开始时间:" + str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) + "*********")
# 连接数据库
myOra = MyOracle(ds_username, ds_pwd, ds_tnsName)
cur = myOra.mycursor()
df = cur.execute(content)
df_result = df.fetchall()
df_result = [(ds_id, *row) for row in df_result]
4)执行结果追加记录到结果文件中
for row1 in df_result:
print(f"行:{row1}")
res_worksheet.append(row1)
res_workbook.save(resFilePath)
myOra.mycurclose(cur)
myOra.myconnclose()
print("**********结束时间:" + str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')) + "*********")
将所有脚本拷出到python 并按配置格式整理数据源信息,导入包后可直接执行。