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