提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
- 前言
- 一、前提条件
- 1、Python驱动部署
- 2、达梦数据库安装
- 二、主函数
- 三、基础信息
- 四、数据库IP读取
- 五、日志文件生成
- 六 SQL解析
- 列表拆分
- 数据库访问
- 总结
前言
本期和大家一起学习如何基于Python3.X将SQL批量应用到多个达梦多服务器，很多同学在数据库维护过程中可能都会遇到这样的情况，一条DDL或者DML语句需要应用到多个数据库，几个数据库实例都能轻松搞定，但实例达到一定的体量靠人工处理耗费大量时间。
一、前提条件
1、Python驱动部署
Python访问达梦数据库参考
https://eco.dameng.com/docs/zh-cn/start/python-development.html2、达梦数据库安装
达梦数据库安装请参考
https://blog.csdn.net/qq_39181479/article/details/124699286二、主函数
# -*- coding: utf-8 -*-
# @File : runMainFun.py
# @Date : 2021-08-27
# @Author : Richard
import datetime
import os
import time
from RunSqlToDm.app.get_base_info import Read_ini
from RunSqlToDm.app.get_server_info import ExcelData
from RunSqlToDm.app.get_sql_stam import ResoluSql
from RunSqlToDm.app.db import DB
from RunSqlToDm.app.get_comp_info import GetCompName
from RunSqlToDm.app.set_log import write_log
from RunSqlToDm.app.run_sql_stam import run_sql_tra
from RunSqlToDm.explog.out_log import log_out
from RunSqlToDm.app.mk_log import mk_log_file
from RunSqlToDm.app.getGuid import getGuidByTenaID
from multiprocessing import Pool, Process, cpu_count
from RunSqlToDm.app.list_cut import CutList
"""
内部使用不写MD文件
使用方法
1、修改conf目录下base.ini，comp_info.ini,pwd.ini文件,文件里有说明如何填写
2、编辑serverInfo.xlsx 追加需要应用SQL的数据库节点，可加多个，目前不限制服务器个数
"""
# 主函数
def main():
# 数据库及文件目录信息
read_ini = Read_ini()
dir_data = read_ini.values_by_key(sec="dir")
db_data = read_ini.values_by_key(sec="database")
# 服务器IP地址文件
xls_dir = os.path.join(dir_data[0], "cnf\serverInfo.xlsx")
# SQL文件所在目录
sql_dir = os.path.join(dir_data[0], "sql")
# 单位字典文件所在目录
com_dir = os.path.join(dir_data[0], "cnf\comp_info.ini")
# pwd字典
pwd_file = os.path.join(dir_data[0], "cnf\pwd.ini")
# 获取服务器IP
get_excel_data = ExcelData(file=xls_dir, sh=db_data[3])
server_data = get_excel_data.readExcel()
# 核数
num_cores = cpu_count()
# 进程池
p = Pool(num_cores)
# 生成日志文件
mk_file_flag = mk_log_file(dir_data[0])
if int(mk_file_flag) == 1:
log_out(msg="日志文件已存在！")
else:
log_out(msg="日志文件创建成功")
# 获取SQL文件
resolusql = ResoluSql(file=sql_dir)
sql_file = resolusql.get_sql_file()
# 获取单位信息
# getCompName = GetCompName(file=com_dir)
# 开始处理SQL语句
for itmes in server_data:
ip = itmes[db_data[2]]
com_name = itmes[db_data[13]]
tena_id = itmes[db_data[9]]
system_ident = itmes[db_data[11]]
domain_name = itmes[db_data[14]]
pbx_code = itmes[db_data[12]]
pass_id = itmes[db_data[4]]
meting_api = itmes[db_data[5]]
id7 = itmes[db_data[6]]
id8 = itmes[db_data[7]]
# guid_id = itmes[db_data[8]]
log_out(msg="即将在[{0}]单位执行".format(com_name))
# list_subsets = []
# 遍历SQL文件
for sf in sql_file:
# 整个文件读
if int(dir_data[1]) == 0:
sqls = resolusql.read(sf)
guid = getGuidByTenaID(ip=ip, user=db_data[0], port=db_data[1], tena_id=tena_id)
db = DB(sql=sqls, ip=ip, user=db_data[0], port=db_data[1], comp_file=com_dir, pwd_file=pwd_file)
db.run_sql(guid, tena_id, system_ident, domain_name, pbx_code, pass_id, meting_api, id7, id8)
# for i in range(1):
# res = p.apply_async(func=db.run_sql, args=(
# guid, tena_id, system_ident, domain_name, pbx_code, pass_id, meting_api, id7, id8,))
# 行方式读
elif int(dir_data[1]) == 1:
sqls = resolusql.read_by_row(file=sf)
guid = getGuidByTenaID(ip=ip, user=db_data[0], port=db_data[1], tena_id=tena_id)
# 切割SQL列表，根据实际CPU核数切割
cutList = CutList(list_info=sqls, per_len=num_cores)
list_subsets = cutList.cut() # 当列表长度小于cpu核数时,被切割的小列表将会小于num_cores,
# 列表切割数量和CPU核数相等
if int(len(list_subsets)) == num_cores:
for i in range(num_cores):
db = DB(ip=ip, user=db_data[0], port=db_data[1], comp_file=com_dir, pwd_file=pwd_file)
res = p.apply_async(func=db.run_mul,
args=(
list_subsets[i], tena_id, com_name, domain_name, guid, system_ident,
pbx_code, pass_id, meting_api, id7, id8))
else: # 子列表不等于CPU核数
# print("子列表小于CPU核数！将按照子列表个数开辟进程")
for fi in range(len(list_subsets)):
db = DB(ip=ip, user=db_data[0], port=db_data[1], comp_file=com_dir, pwd_file=pwd_file)
res = p.apply_async(func=db.run_mul,
args=(
list_subsets[fi], tena_id, com_name, domain_name, guid, system_ident,
pbx_code, pass_id, meting_api, id7, id8))
# 按照;号获取SQL文件，只适用创建表语句
elif int(dir_data[1]) == 3:
guid = getGuidByTenaID(ip=ip, user=db_data[0], port=db_data[1], tena_id=tena_id)
sqls = resolusql.open_sql_file(sql_file=sf)
# 切割SQL列表，根据实际CPU核数切割
cutList = CutList(list_info=sqls[0], per_len=num_cores)
list_subsets = cutList.cut()
# 子列表和CPU核数相等的时候
if int(len(list_subsets)) == num_cores:
for i in range(num_cores):
db = DB(ip=ip, user=db_data[0], port=db_data[1], comp_file=com_dir, pwd_file=pwd_file)
res = p.apply_async(func=db.run_mul,
args=(list_subsets[i], tena_id, com_name, domain_name, guid,))
else: # 子列表小于CPU核数
# print("创建数据库对象子列表机器CPU核数不相等！")
for fi in range(len(list_subsets)):
db = DB(ip=ip, user=db_data[0], port=db_data[1], comp_file=com_dir, pwd_file=pwd_file)
res = p.apply_async(func=db.run_mul,
args=(list_subsets[fi], tena_id, com_name, domain_name, guid,))
# 作为一个事务执行
elif int(dir_data[1]) == 2:
sqls = resolusql.read_by_row(file=sf)
guid = getGuidByTenaID(ip=ip, user=db_data[0], port=db_data[1], tena_id=tena_id)
run_sql_tra(ip=ip, user=db_data[0], port=db_data[1], data=sqls, com_file=com_dir, tena_id=tena_id,
rowguid=guid, system_ident=system_ident, pwd_file=pwd_file)
p.close()
p.join()
if __name__ == '__main__':
main()三、基础信息
# -*- coding: utf-8 -*-
# @File : get_base_info.py
# @Date : 2021-11-25
# @Author : Richard
import os
import time
import configparser
class Read_ini(object):
def __init__(self):
self.ini_file = "D:\PycharmProjects\sqlRun\RunSqlToDm\cnf\\base.ini"
self.sec = "dir"
def get_values(self):
"""
读取ini文件，通过file与sec读取
:return: 返回对应的sec的values值
"""
try:
data = []
cf = configparser.ConfigParser()
cf.read(self.ini_file)
options = cf.options(self.sec)
for i in options:
itmes = cf.get(self.sec, i)
data.append(itmes)
return data
except Exception as Err:
print("read config path_files error:{0}".format(Err))
def values_by_key(self, sec):
try:
data = []
cf = configparser.ConfigParser()
cf.read(self.ini_file)
options = cf.options(sec)
for i in options:
res = cf.get(sec, i)
data.append(res)
return data
except Exception as Err:
print("open file config error,file type is ini：{0}".format(Err))四、数据库IP读取
# -*- coding: utf-8 -*-
# @File : get_server_info.py
# @Date : 2021-11-25
# @Author : Richard
import os
import time
import pandas
import xlrd
from xlrd import xldate_as_tuple
# 解析Excel 类
class ExcelData(object):
# 初始化方法
def __init__(self, file, sh):
self.file = file
self.sh = sh
self.data = xlrd.open_workbook(self.file)
self.table = self.data.sheet_by_name(self.sh)
self.keys = self.table.row_values(0)
self.rowNum = self.table.nrows
self.colNum = self.table.ncols
# 定义一个读取excel表的方法
def readExcel(self):
datas = []
for i in range(1, self.rowNum):
sheet_data = {}
for j in range(self.colNum):
c_type = self.table.cell(i, j).ctype
# 获取单元格数据
c_cell = self.table.cell_value(i, j)
if c_type == 2 and c_cell % 1 == 0: # 如果是整形
c_cell = int(c_cell)
elif c_type == 3:
# 转成datetime对象
date = pandas.datetime.datetime(*xldate_as_tuple(c_cell, 0))
c_cell = date.strftime('%Y/%d/%m %H:%M:%S')
elif c_type == 4:
c_cell = True if c_cell == 1 else False
sheet_data[self.keys[j]] = c_cell
datas.append(sheet_data)
return datas
# 处理Excel
def open_excel(self):
"""
:param file_path:
:return: Database IP address
"""
data = []
try:
book = xlrd.open_workbook(self.file)
res_obj = book.sheet_by_name("server_ip")
for i in range(1, res_obj.nrows):
data.append(res_obj.cell(i, 1))
return data
except Exception as Err:
print("open excel error is :{0}".format(Err))五、日志文件生成
# -*- coding: utf-8 -*-
# @File : mk_log.py
# @Date : 2022-01-10
# @Author : Richard
import os
import time
import datetime
def mk_log_file(base_path):
status = 0
if base_path:
try:
tt_time = datetime.datetime.now()
file_name = os.path.join(base_path,"logs") + '\\' + str(tt_time)[0:10] + '.log'
# 判断文件是否存在
file_exis = os.path.exists(file_name)
if file_exis:
status = 1
else:
with open(file_name, 'w') as file:
file.close()
status = 2
except Exception as Err:
print("创建日志文件发生错误，错误详细信息：{0}".format(Err))
finally:
return status六 SQL解析
# -*- coding: utf-8 -*-
# @File : get_sql_stam.py
# @Date : 2021-11-25
# @Author : Richard
import os
import re
import time
# 解析SQL文件
class ResoluSql(object):
def __init__(self, file):
self.filepath = file
#
def ergodic_sql(self):
"""
:param:sql_file_path
:return: sqls info list
"""
try:
with open(self.filepath) as f:
sqls = f.readlines(100000)
return sqls
except Exception as Err:
print("ergodic statement error is :{0}".format(Err))
# 解析SQL文件
def get_sql_file(self):
"""
功能：根据目录遍历SQL文件
:param:sql_file_path
:return: SQL file ，data type is list
"""
try:
for root, dirs, files in os.walk(self.filepath):
data = []
for file in files:
res = os.path.join(root, file).encode("utf8")
data.append(res)
return data
except Exception as Err:
print("ergodic statement error is :{0}".format(Err))
def open_sql_file(self, sql_file):
"""
功能：根据SQL文件解析出SQL语句
:param sql_file:
:return: sql statement list
"""
if sql_file:
try:
# with open(sql_file, encoding="GBK") as f:
with open(sql_file, encoding="utf-8") as f:
# res = f.readlines()
res = f.read()
if re.findall(r"begin", res) and re.findall(r"create", res):
resource = res.split("--")
codeline = 1
# 非代码块SQL
else:
resource = res.split(";")[:-1]
codeline = 0
return [resource, codeline]
except Exception as Err:
print("open sql file error,information more check[0]".format(Err))
else:
print("sql file not exists")
def read(self, sql_file):
"""
功能：根据SQL文件解析出SQL语句
:param sql_file:
:return: sql statement list
"""
if sql_file:
try:
# with open(sql_file, encoding="GBK") as f:
with open(sql_file, encoding="utf8") as f:
# res = f.readlines()
res = f.read()
if res:
return res
except Exception as Err:
print("open sql file error,information more check[{0}]".format(Err))
else:
print("sql file not exists")
def read_by_row(self, file):
"""
功能：根据SQL文件解析出SQL语句,以行的方式
:param sql_file:sql文件
:return: sql statement list
"""
if file:
try:
# with open(file, encoding="GBK") as f:
with open(file, encoding="utf8") as f:
res = f.read().split("\n")
if res:
return res
except Exception as Err:
print("open sql file error,information more check[0]".format(Err))
else:
print("sql file not exists")列表拆分
# -*- coding: utf-8 -*-
# @File : list_cut.py
# @Date : 2022-03-05
# @Author : Richard
import os
import time
from math import ceil
class CutList(object):
def __init__(self, list_info=None, per_len=None):
self.list_info = list_info
self.per_len = per_len
def cut(self):
"""
列表切割，
:param list_info: 传入列的表
:param per_len: 切割的个数
:return: 返回切割后的列表
:return:
"""
try:
n = int(ceil(len(self.list_info) / float(self.per_len)))
return [self.list_info[i:i + n] for i in range(0, len(self.list_info), n)]
except Exception as Err:
print("列表切割失败！错误信息如下：".format(Err))
def list_cut(self):
"""
:param list_info: 传入列的表
:param per_list_len: 切割后的列表个数
:return: 返回列表
:return:
"""
try:
tmp_list = []
for i in range(0, len(self.list_info), self.per_len):
a = self.list_info[i:i + self.per_len]
tmp_list.append(a)
return tmp_list
except Exception as ErrE:
print("列表切割失败！错误信息如下：".format(ErrE))数据库访问
# -*- coding: utf-8 -*-
# @File : get_cols.py
# @Date : 2021-11-25
# @Author : Richard
import os
import time
import dmPython
from RunSqlToDm.app.set_log import write_log
from RunSqlToDm.app.get_comp_info import GetCompName
from RunSqlToDm.app.get_pwd_info import GetPwd
class DB(object):
def __init__(self, sql=None, ip=None, user=None, port=None, comp_file=None, pwd_file=None):
"""
构造方法
:param sql: SQL语句，这里一条完整的SQL语句
:param ip: 数据库服务器地址
:param user: 数据库用户名
:param port: 数据库端口
:param comp_file: 单位字段文件
"""
self.sql = sql
self.ip = ip
self.user = user
self.port = port
self.comp_file = comp_file
self.pwd_file = pwd_file
def run_sql(self, guid=None, tena_id=None, system_ident=None, domain_name=None, pbx_code=None, pass_id=None,
meting_api=None, id7=None, id8=None):
"""
运行SQL
:return: 无返回值
"""
ip_list = ["190.45.163.100"]
get_pwd = GetPwd(file=self.pwd_file)
re_pwd = get_pwd.get_comp(user_name=str(self.user).upper())
# 密码选择
if self.ip in ip_list and str(self.user).upper() == "SYSDBA":
re_pwd = "DSdba@00"
elif self.ip not in ip_list and str(self.user).upper() == "SYSDBA":
re_pwd = "DSdba@11"
getCompName = GetCompName(file=self.comp_file)
try:
# 替换一些关键词
new_sql = str(self.sql).replace("#guid#", str(guid)).replace("#Tena_id#", str(tena_id)). \
replace("#system#", str(system_ident)).replace("#domain_name#", str(domain_name)). \
replace("#pbx_code#", str(pbx_code)).replace("#pass_id#", str(pass_id)). \
replace("#meting_api#", str(meting_api)).replace("#id7#", str(id7)).replace("#id8#", str(id8))
print("new_sql:\n{}".format(new_sql))
write_log(msg_info="即将在【{0}】执行:\n{1}".format(getCompName.get_comp(ip=self.ip), self.sql))
# print("sql:\n{0}".format(self.sql))
conn = dmPython.connect(user=self.user, password=re_pwd, server=self.ip, port=self.port)
cursor = conn.cursor()
# cursor.execute(self.sql)
cursor.execute(new_sql)
conn.commit()
cursor.close()
conn.close()
write_log(msg_info="【{0}】执行成功!\n".format(getCompName.get_comp(ip=self.ip)))
print("【{0}】执行成功！！\n".format(getCompName.get_comp(ip=self.ip)))
except Exception as Err:
print("【{0}】执行失败！！,错误信息：{1}".format(getCompName.get_comp(ip=self.ip), Err))
write_log("【{0}】执行失败! 单位：{1},IP:{2},详细SQL:\n{3}\n".format(getCompName.get_comp(ip=self.ip),
getCompName.get_comp(ip=self.ip), self.ip,
self.sql))
def run_mul(self, sqls, tena_id=None, com_name=None, domain_name=None, rowguid=None, system_ident=None,
pbx_code=None, pass_id=None, meting_api=None, id7=None, id8=None):
"""
# 运行SQL，多进程专用方法
:param sqls: SQL语句，是列表类型
:return: 无返回值
"""
ip_list = ["131.7.163.150"]
get_pwd = GetPwd(file=self.pwd_file)
re_pwd = get_pwd.get_comp(user_name=str(self.user).upper())
if self.ip in ip_list and str(self.user).upper() == "SYSDBA":
re_pwd = "DSdba@000"
elif self.ip not in ip_list and str(self.user).upper() == "SYSDBA":
re_pwd = "DSdba@111"
try:
getCompName = GetCompName(file=self.comp_file)
for j in sqls:
# 已经在主函数优化，不会走到这里
# 跳过空列表，主要是针对创建表的时候，若只有一个创建语句，在多核CPU的时候会切割出空列表，这里过滤掉可以避免出错
if not j:
continue
else:
# 替换一些关键词
new_sql = str(j).replace("#tena_id#", str(tena_id)).replace("#com_name#", str(com_name)) \
.replace("#domain_name#", str(domain_name)).replace("#rowguid#", str(rowguid)) \
.replace("#system#", str(system_ident)).replace("#pbx_code#", str(pbx_code)) \
.replace("#pass_id#", str(pass_id)).replace("#meting_api#", str(meting_api)) \
.replace("#id7#", str(id7)).replace("#id8#", str(id8))
try:
conn = dmPython.connect(user=self.user, password=re_pwd, server=self.ip, port=self.port)
cursor = conn.cursor()
write_log(msg_info="即将在【{0}】执行：\n{1}".format(getCompName.get_comp(ip=self.ip), new_sql))
cursor.execute(new_sql)
write_log(msg_info="【{0}】执行成功！：\n{1}".format(getCompName.get_comp(ip=self.ip), new_sql))
print("【{0}】 【{1}】 执行成功！".format(getCompName.get_comp(ip=self.ip), new_sql))
conn.commit()
cursor.close()
conn.close()
except Exception as Err_Ex:
write_log(
msg_info="【{0}】执行失败！,IP:{1},详细sql:{2},错误：".format(getCompName.get_comp(ip=self.ip), self.ip,
new_sql, Err_Ex))
print("SQL运行失败！错误信息：{0}".format(Err_Ex))
except Exception as Err:
print("SQL运行出错！错误信息:{0}".format(Err))
return False总结
以上就是今天要讲的内容，本文仅仅简单介绍了Python3操作达梦数据库的使用，而达梦提供了大量能使我们快速便捷地处理数据的Python接口，详情请参考 https://eco.dameng.com/
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
