def main():
parser = argparse.ArgumentParser()
parser.add_argument('--dbsrc', dest='dbsrc', required=True, help='源数据库信息')
parser.add_argument('--dbtag', dest='dbtag', required=True, help='目标数据库信息')
parser.add_argument('--tabfile',
dest='tabfile',
required=True,
help='需要同步的表配置文件名称')
parser.add_argument(
'--dbin',
dest='dbin',
required=False,
help='是否直接加载到DB: Y生成datax配置文件且加载到目标DB;N只生成datax配置文件；默认不直接加载')
args = parser.parse_args()
dbsrc = mdtool.xmler(args.dbsrc).dbCFGInfo()
dbtag = mdtool.xmler(args.dbtag).dbCFGInfo()
tabfile = args.tabfile
tables_in = []
with open(mdtool.Variable.CONF_PATH + os.sep + tabfile,
'r',
encoding='utf-8') as f:
lines = f.readlines()
for line in lines:
tables_in.append(line.strip('\n').lower())
# Json任务生成器
pfs = profilegenerator.ProfileGenerator(dbsrc, dbtag, tables_in)
pfs.Profilejson()
# 是否加载到DB
if args.dbin is None:
dbin = 'N'
else:
if args.dbin.upper() == 'N' or args.dbin.upper() == 'Y':
dbin = args.dbin
else:
mdtool.log.error("dbin是否直接加载到DB参数配置错误，请检查")
sys.exit()
if dbin == 'Y':
# 添加多线程
from multiprocessing import Pool
# 执行
pool = Pool(processes=6)
JOB_PATH = mdtool.Variable.JOB_PATH
for root, dirs, files in os.walk(JOB_PATH):
# 遍历文件
for f in files:
file = os.path.join(root, f)
# pfs.DataxExecute(file)
pool.apply_async(func=pfs.DataxExecute, args=(file, ))
pool.close()
pool.join()
tag_col = '","'.join(col_array_tag)
src_col = ','.join(col_array_src)
src_sql = 'SELECT %s FROM %s' % (src_col, table)
tag_sql = 'TRUNCATE TABLE %s' % table
self.SedJson(src, src_jdbc, src_sql, tag, tag_col,
tag_sql, tag_jdbc, table)
@staticmethod
def DataxExecute(json_file):
DATAX_BIN_PATH = mdtool.Variable.DATAX_BIN_PATH
command = 'python %s' % DATAX_BIN_PATH + os.sep + 'datax.py' + ' %s' % json_file
# os.system(command)
# print('进程', os.getpid())
# print('多进程开始处理====>>>>')
# time.sleep(2)
if __name__ == '__main__':
dbsrc = mdtool.xmler('ORACLE_172.21.86.201').dbCFGInfo()
# dbtag = mdtool.xmler('POSTGRESQL_10.45.59.178_META').dbCFGInfo()
dbtag = mdtool.xmler('POSTGRESQL_172.21.86.201').dbCFGInfo()
tables_in = 'bfm_staff_his,metric_items,pub_restriction'.split(',')
ps = ProfileGenerator(dbsrc, dbtag, tables_in)
ps.Profilejson()
# JOB_PATH = mdtool.Variable.JOB_PATH
# for root, dirs, files in os.walk(JOB_PATH):
# # 遍历文件
# for f in files:
# file = os.path.join(root, f)
# ps.DataxExecute(file)
mdtool.log.error("索引数据异常")
sys.exit()
# 写入dbsyncer管理库 - mysql
sql = """
INSERT INTO tables_indexes_tabdb
(auth_id,
db_type,
table_schema,
table_name,
uniqueness,
index_name,
column_name,
ordinal_position,
index_type)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
self.dbmgr_executor.dbexecutemany(sql, dataset)
mdtool.log.info("%s索引数据数据加载到mdsyncer库表tables_indexes_tabdb成功" %
self.dbtype)
if __name__ == '__main__':
dbsrc = mdtool.xmler('MYSQL_172.21.86.205').dbCFGInfo()
dbmgr = mdtool.xmler('MGR_172.21.86.205').dbCFGInfo()
dialect = MysqlDialect(dbsrc, dbmgr)
dialect.mdsyncer_tables()
dialect.mdsyncer_columns()
dialect.tables_constraints()
dialect.tables_indexes()
table_schema,
table_name,
uniqueness,
index_name,
column_name,
ordinal_position,
index_type)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s)
"""
self.dbmgr_executor.dbexecutemany(sql, dataset)
mdtool.log.info("%s索引数据数据加载到mdsyncer库表tables_indexes成功" % self.dbtype)
if __name__ == '__main__':
dbsrc = mdtool.xmler('ORACLE_10.45.59.187_HAINAN').dbCFGInfo()
dbmgr = mdtool.xmler('MGR_172.21.86.205').dbCFGInfo()
# tables_in = 'pub_elec_label,pub_drop_item'
array = []
with open(mdtool.Variable.CONF_PATH + os.sep + 'RC_RES.txt', 'r', encoding='utf-8') as f:
lines = f.readlines()
for line in lines:
array.append(line.strip('\n').lower())
tables_in = ','.join(array)
dialect = OracleDialect(dbsrc, dbmgr, tables_in)
# dialect = OracleDialect(dbsrc, dbmgr)
# dialect.mdsyncer_tables()
# dialect.mdsyncer_columns()
dialect.tables_constraints()
# dialect.tables_indexes()
AND pidx.indisprimary = 'false'
"""
dataset = self.dbsrc_executor.dbfetchall(query, params)
# 写入dbsyncer管理库 - mysql
sql = """
INSERT INTO tables_indexes_tabdb
(auth_id,
db_type,
table_schema,
table_name,
uniqueness,
index_name,
index_type,
indexdef)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s)
"""
self.dbmgr_executor.dbexecutemany(sql, dataset)
mdtool.log.info("%s索引数据数据加载到mdsyncer库表tables_indexes_tabdb成功" %
self.dbtype)
if __name__ == '__main__':
dbsrc = mdtool.xmler('POSTGRESQL_10.45.59.202').dbCFGInfo()
dbmgr = mdtool.xmler('MGR_172.21.86.205').dbCFGInfo()
dialect = PGDialect(dbsrc, dbmgr)
dialect.mdsyncer_tables()
dialect.mdsyncer_columns()
dialect.tables_constraints()
dialect.tables_indexes()
if len(array_fk) > 0:
mdtool.log.info("约束-外键对象创建进度：")
for elem in trange(len(array_fk)):
elem_sql = array_fk[elem]
try:
self.dbtag_executor.sql_execute(elem_sql)
self.dbtag_executor.dbclose()
except Exception as err:
mdtool.log.error("失败的约束-外键语句：%s;" % elem_sql)
mdtool.log.error("约束-外键对象创建失败：" + str(err))
f.close()
else:
mdtool.log.warning("模型生成器未生成约束-外键对象, 请检查是否正确")
mdtool.log.info("约束(&外键)对象创建完成")
if __name__ == '__main__':
# dbsrc = mdtool.xmler('MYSQL_172.21.86.205').dbCFGInfo()
dbsrc = mdtool.xmler('ORACLE_10.45.59.187_HAINAN').dbCFGInfo()
# dbtag = mdtool.xmler('ORACLE_172.21.86.201').dbCFGInfo()
dbtag = mdtool.xmler('POSTGRESQL_10.45.59.178_META').dbCFGInfo()
# flag = 'table,constraint,index'
flag = 'table'
# flag = 'constraint'
# flag = 'index'
# w = Modeltodb(dbsrc, dbtag, flag)
# w.modelToDB()
# tables = 'bfm_staff_org_his'
# dbmgr = mdtool.xmler('MGR_172.21.86.205').dbCFGInfo()
# Modelgenerator(dbsrc, dbmgr, dbtag, tables).tablesGenerator()
def main():
parser = argparse.ArgumentParser()
parser.add_argument('--dbsrc',
dest='dbsrc',
required=True,
help='模型需要转换的数据库信息')
parser.add_argument('--dbtag',
dest='dbtag',
required=True,
help='模型转换后的数据库信息')
parser.add_argument('--dbmgr',
dest='dbmgr',
required=True,
help='mbsyncer工具管理数据库信息')
parser.add_argument('--flag',
dest='flag',
required=False,
help='表对象、约束、索引等可选，以逗号为分隔符 格式：table,constraint,index')
parser.add_argument('--dbin',
dest='dbin',
required=False,
help='是否直接加载到DB: Y生成模型文件且加载到DB;N生成模型文件不加载到DB；默认不直接加载')
parser.add_argument('--tabfile_flag',
dest='tabfile_flag',
required=False,
help='是否读取文件方式批量导出表对象: Y读取文件，N不用读取')
parser.add_argument('--tabfile',
dest='tabfile',
required=False,
help='需要同步的表配置文件名称,配合tabfile_flag参数一起使用')
args = parser.parse_args()
dbsrc = mdtool.xmler(args.dbsrc).dbCFGInfo()
dbtag = mdtool.xmler(args.dbtag).dbCFGInfo()
dbmgr = mdtool.xmler(args.dbmgr).dbCFGInfo()
# 对象处理
# 默认导出 表\约束\索引对象
if args.flag is None:
flag = 'table,index,constraint'
else:
flag = args.flag.lower()
# 表名处理
# 批量导出表采用配置表的方式，避免输入内容过多 或者配置过多，遍历引发效率降低
cfgdb = modelgenerator_tabdb.Modelgenerator(dbsrc, dbmgr, dbtag)
# 配置表文件判断
if args.tabfile_flag is None or args.tabfile_flag.upper() == 'N':
if cfgdb.dbtype.lower() == 'oracle':
result = cfgdb.dbsrc_executor.dbfetchall(
"SELECT :auth_id,lower(table_name) FROM user_tables ORDER BY table_name",
{'auth_id': cfgdb.keys})
elif cfgdb.dbtype.lower() == 'mysql':
result = cfgdb.dbsrc_executor.dbfetchall(
"SELECT %s,lower(table_name) FROM information_schema.tables where table_schema=%s ORDER BY table_name",
(cfgdb.keys, cfgdb.dbname))
elif cfgdb.dbtype.lower() == 'postgresql':
result = cfgdb.dbsrc_executor.dbfetchall(
"SELECT %s,lower(table_name) FROM information_schema.tables where table_schema=%s ORDER BY table_name",
(cfgdb.keys, cfgdb.schema))
else:
mdtool.log.error('数据库类型错误')
# 插入新数据前清空表
cfgdb.dbmgr_executor.dbexecute(
"DELETE FROM cfg_tables WHERE auth_id = %s", (cfgdb.keys, ))
cfgdb.dbmgr_executor.dbexecutemany(
"INSERT INTO cfg_tables(auth_id,table_name) VALUES(%s,%s)", result)
elif args.tabfile_flag.upper() == 'Y':
if args.tabfile is None:
mdtool.log.error("tabfile同步的表配置文件名称参数配置错误，请检查")
sys.exit()
else:
tabfile = args.tabfile
array = []
with open(mdtool.Variable.CONF_PATH + os.sep + tabfile,
'r',
encoding='utf-8') as f:
lines = f.readlines()
for line in lines:
line_str = line.strip('\n').lower()
array.append((cfgdb.keys, line_str))
f.close()
# 插入新数据前清空表
cfgdb.dbmgr_executor.dbexecute(
"DELETE FROM cfg_tables WHERE auth_id = %s", (cfgdb.keys, ))
cfgdb.dbmgr_executor.dbexecutemany(
"INSERT INTO cfg_tables(auth_id,table_name) VALUES(%s,%s)",
array)
else:
mdtool.log.error("tabfile_flag是否读取文件参数配置错误，请检查")
sys.exit()
# 是否加载到DB
if args.dbin is None:
dbin = 'N'
else:
if args.dbin.upper() == 'N' or args.dbin.upper() == 'Y':
dbin = args.dbin
else:
mdtool.log.error("dbin是否直接加载到DB参数配置错误，请检查")
sys.exit()
# 导出对象信息
mls = modelgenerator_tabdb.ModelDialect(dbsrc, dbmgr, flag)
mls.main()
# 对象生成器
mlm = modelgenerator_tabdb.Modelgenerator(dbsrc, dbmgr, dbtag)
# 删除非必要对象
mlm.DelObject()
# 表对象
mlm.tablesGenerator()
# 约束对象
mlm.constraintsGenerator()
# 索引对象
mlm.indexesGenerator()
if dbin == 'Y':
# 模型加载到目标库
# 添加唯一性索引后可以再添加主键，故先执行索引对象
mlt = modelgenerator_tabdb.Modeltodb(dbsrc, dbtag, flag)
mlt.modelToDB()
def main():
parser = argparse.ArgumentParser()
parser.add_argument('--dbsrc',
dest='dbsrc',
required=True,
help='模型需要转换的数据库信息')
parser.add_argument('--dbtag',
dest='dbtag',
required=True,
help='模型转换后的数据库信息')
parser.add_argument('--dbmgr',
dest='dbmgr',
required=True,
help='mbsyncer工具管理数据库信息')
parser.add_argument('--flag',
dest='flag',
required=False,
help='表对象、约束、索引等可选，以逗号为分隔符 格式：table,constraint,index')
parser.add_argument('--tables_in',
dest='tables_in',
required=False,
help='按表对象导出，以逗号为分隔符 格式：tab1,tab2,tab3')
parser.add_argument('--dbin',
dest='dbin',
required=False,
help='是否直接加载到DB: Y生成模型文件且加载到DB;N生成模型文件不加载到DB；默认不直接加载')
# parser.add_argument('--tabfile_flag', dest='tabfile_flag', required=False,
# help='是否读取文件方式批量导出表对象: Y读取文件，N不用读取')
# parser.add_argument('--tabfile', dest='tabfile', required=False, help='需要同步的表配置文件名称,配合tabfile_flag参数一起使用')
args = parser.parse_args()
dbsrc = mdtool.xmler(args.dbsrc).dbCFGInfo()
dbtag = mdtool.xmler(args.dbtag).dbCFGInfo()
dbmgr = mdtool.xmler(args.dbmgr).dbCFGInfo()
# 对象处理
# 默认导出 表\约束\索引对象
if args.flag is None:
flag = 'table,index,constraint'
else:
flag = args.flag.lower()
# 表名处理
# 批量导出表采用配置文件的方式，避免输入内容过多
if args.tables_in is None:
tables_in = None
else:
tables_in = args.tables_in.lower()
# 配置表文件判断
# if args.tabfile_flag is None:
# tabfile_flag = 'N'
# else:
# if args.tabfile_flag.upper() == 'N' or args.tabfile_flag.upper() == 'Y':
# tabfile_flag = args.tabfile_flag
# else:
# mdtool.log.error("tabfile_flag是否读取文件参数配置错误，请检查")
# sys.exit()
# if tabfile_flag.upper() == 'Y':
# if args.tabfile is None:
# mdtool.log.error("tabfile同步的表配置文件名称参数配置错误，请检查")
# sys.exit()
# else:
# tabfile = args.tabfile
# array = []
# with open(mdtool.Variable.CONF_PATH + os.sep + tabfile, 'r', encoding='utf-8') as f:
# lines = f.readlines()
# for line in lines:
# array.append(line.strip('\n').lower())
# tables_in = ','.join(array)
# f.close()
# 是否加载到DB
if args.dbin is None:
dbin = 'N'
else:
if args.dbin.upper() == 'N' or args.dbin.upper() == 'Y':
dbin = args.dbin
else:
mdtool.log.error("dbin是否直接加载到DB参数配置错误，请检查")
sys.exit()
# 导出对象信息
mls = modelgenerator.ModelDialect(dbsrc, dbmgr, flag, tables_in)
mls.main()
# 对象生成器
mlm = modelgenerator.Modelgenerator(dbsrc, dbmgr, dbtag, tables_in)
# 表对象
mlm.tablesGenerator()
# 约束对象
mlm.constraintsGenerator()
# 索引对象
mlm.indexesGenerator()
if dbin == 'Y':
# 模型加载到目标库
# 添加唯一性索引后可以再添加主键，故先执行索引对象
mlt = modelgenerator.Modeltodb(dbsrc, dbtag, flag)
mlt.modelToDB()
# -*- coding: utf-8 -*-
# @Author : 陈朋
# @Time : 2021/3/25 18:24
# @Param :
# @File : cp_test.py
# @Software: mdsyncer
import mdtool
import sys
db_array = 'MYSQL_134.96.188.49_iom_inst1'
dbsrc = mdtool.xmler(db_array).dbCFGInfo()
for value in dbsrc.values():
host = value['host']
port = value['port']
user = value['user']
passwd = value['passwd']
dbtype = value['dbtype'].lower()
dbname = value['dbname'].lower()
dbsrc_executor = mdtool.DbManager(host, port, user, passwd, dbname, dbtype)
keys = 'MYSQL_134.96.188.49_iom_inst1'
params = 'iom_inst1'
query = """
SELECT
CONCAT (
table_schema,
'.',
table_name
