def save_db_user(d_db_user): result = {} val = check_db_user(d_db_user,'I') if val['code']=='-1': return val try: db = get_connection() cr = db.cursor() result = {} db_pass = '' if d_db_user['db_pass'] != '': db_pass = aes_encrypt(d_db_user['db_pass'], d_db_user['db_user'].replace("'","''")) else: db_pass = d_db_user['db_pass'] sql="""insert into t_db_user(inst_id,db_user,db_pass,user_dbs,user_privs,statement,status,description,created_date) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',now()) """.format(d_db_user['inst_id'],d_db_user['db_user'].replace("'","''"),db_pass, d_db_user['user_dbs'],d_db_user['user_privs'], format_sql(d_db_user['statement']),d_db_user['status'],format_sql(d_db_user['desc'])) print(sql) cr.execute(sql) cr.close() db.commit() result['code']='0' result['message']='保存成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def upd_backup(p_backup): result={} val = check_backup(p_backup) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() backupid = p_backup['backup_id'] backup_server = p_backup['backup_server'] db_server = p_backup['db_server'] db_type = p_backup['db_type'] backup_tag = p_backup['backup_tag'] backup_expire = p_backup['backup_expire'] backup_base = format_sql(p_backup['backup_base']) script_base = format_sql(p_backup['script_base']) script_name = p_backup['script_name'] cmd_name = p_backup['cmd_name'] run_time = p_backup['run_time'] task_desc = p_backup['task_desc'] python3_home = format_sql(p_backup['python3_home']) backup_databases = p_backup['backup_databases'] api_server = p_backup['api_server'] status = p_backup['status'] sql="""update t_db_config set server_id ='{0}', db_id ='{1}', db_type ='{2}', db_tag ='{3}', expire ='{4}', bk_base ='{5}', script_path ='{6}', script_file ='{7}', bk_cmd ='{8}', run_time ='{9}', comments ='{10}', python3_home ='{11}', backup_databases ='{12}', api_server ='{13}', STATUS ='{14}' where id='{15}'""".format(backup_server,db_server,db_type,backup_tag,backup_expire,backup_base, script_base,script_name,cmd_name,run_time,task_desc,python3_home, backup_databases,api_server,status,backupid) print(sql) cr.execute(sql) cr.close() db.commit() result={} result['code']='0' result['message']='更新成功!' except : result['code'] = '-1' result['message'] = '更新失败!' return result
def upd_sys_code_detail(code): result = {} try: db = get_connection() cr = db.cursor() sql = """update t_dmmx set dmm ='{}', dmmc ='{}', flag = '{}', update_time=now() where dm = '{}' and dmm='{}' """.format(code['detail_code'], code['detail_name'], code['detail_status'], code['type_code'], format_sql(code['detail_code_old'])) print('upd_sys_code_detail=', sql) cr.execute(sql) cr.close() db.commit() result['code'] = '0' result['message'] = '更新成功!' return result except Exception as e: print(traceback.format_exc()) result['code'] = '-1' result['message'] = '更新失败!' return result
def update_db_user(d_db_user): result = {} val = check_db_user(d_db_user,'U') if val['code']=='-1': return val try: db = get_connection() cr = db.cursor() result = {} db_pass = '' if d_db_user['db_pass'] != '': db_pass = aes_encrypt(d_db_user['db_pass'], d_db_user['db_user'].replace("'","''")) else: db_pass = d_db_user['db_pass'] sql="""update t_db_user set inst_id='{}', db_user='******', db_pass='******', user_dbs='{}', user_privs='{}', statement='{}', status='{}', description='{}', last_update_date=now() where id={} """.format(d_db_user['inst_id'], d_db_user['db_user'].replace("'","''"), db_pass, d_db_user['user_dbs'], d_db_user['user_privs'], format_sql(d_db_user['statement']), d_db_user['status'], format_sql(d_db_user['desc']), d_db_user['user_id']) print(sql) cr.execute(sql) cr.close() db.commit() result['code']='0' result['message']='更新成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def upd_transfer(p_transfer): result={} val = check_transfer(p_transfer) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() transfer_id = p_transfer['transfer_id'] transfer_tag = p_transfer['transfer_tag'] task_desc = p_transfer['task_desc'] transfer_server = p_transfer['transfer_server'] transfer_type = p_transfer['transfer_type'] sour_db_server = p_transfer['sour_db_server'] sour_db_name = p_transfer['sour_db_name'] sour_tab_name = p_transfer['sour_tab_name'] sour_tab_where = p_transfer['sour_tab_where'] dest_db_server = p_transfer['dest_db_server'] dest_db_name = p_transfer['dest_db_name'] script_base = p_transfer['script_base'] script_name = p_transfer['script_name'] python3_home = p_transfer['python3_home'] batch_size = p_transfer['batch_size'] api_server = p_transfer['api_server'] status = p_transfer['status'] sql="""update t_db_transfer_config set transfer_tag ='{0}', server_id ='{1}', comments ='{2}', sour_db_id ='{3}', sour_schema ='{4}', sour_table ='{5}', sour_where ='{6}', dest_db_id ='{7}', dest_schema ='{8}', script_path ='{9}', script_file ='{10}', python3_home ='{11}', api_server ='{12}', status ='{13}', batch_size ='{14}', transfer_type ='{15}' where id={16}""".format(transfer_tag,transfer_server,task_desc,sour_db_server,sour_db_name, sour_tab_name,format_sql(sour_tab_where),dest_db_server,dest_db_name,script_base, script_name,python3_home,api_server,status,batch_size,transfer_type,transfer_id) print(sql) cr.execute(sql) cr.close() db.commit() result={} result['code']='0' result['message']='更新成功!' except : print(traceback.format_exc()) result['code'] = '-1' result['message'] = '更新失败!' return result
def save_backup(p_backup): result = {} val=check_backup(p_backup) if val['code']=='-1': return val try: db = get_connection() cr = db.cursor() result = {} backup_server = p_backup['backup_server'] db_server = p_backup['db_server'] db_type = p_backup['db_type'] backup_tag = p_backup['backup_tag'] backup_expire = p_backup['backup_expire'] backup_base = format_sql(p_backup['backup_base']) script_base = format_sql(p_backup['script_base']) script_name = p_backup['script_name'] cmd_name = p_backup['cmd_name'] run_time = p_backup['run_time'] task_desc = p_backup['task_desc'] python3_home = format_sql(p_backup['python3_home']) backup_databases = p_backup['backup_databases'] api_server = p_backup['api_server'] status = p_backup['status'] sql="""insert into t_db_config( server_id,db_id,db_type,db_tag,expire,bk_base,script_path,script_file, bk_cmd,run_time,comments,python3_home,backup_databases,api_server,status) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}', '{8}','{9}','{10}','{11}','{12}','{13}','{14}') """.format(backup_server,db_server,db_type,backup_tag,backup_expire,backup_base,script_base,script_name, cmd_name,run_time,task_desc,python3_home,backup_databases,api_server,status) print(sql) cr.execute(sql) cr.close() db.commit() result['code']='0' result['message']='保存成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def save_transfer(p_transfer): result = {} #增加tag重复验证 val=check_transfer(p_transfer) if val['code']=='-1': return val try: db = get_connection() cr = db.cursor() result = {} transfer_tag = p_transfer['transfer_tag'] task_desc = p_transfer['task_desc'] transfer_server = p_transfer['transfer_server'] transfer_type = p_transfer['transfer_type'] sour_db_server = p_transfer['sour_db_server'] sour_db_name = p_transfer['sour_db_name'] sour_tab_name = p_transfer['sour_tab_name'] sour_tab_where = format_sql(p_transfer['sour_tab_where']) dest_db_server = p_transfer['dest_db_server'] dest_db_name = p_transfer['dest_db_name'] python3_home = p_transfer['python3_home'] script_base = p_transfer['script_base'] script_name = p_transfer['script_name'] batch_size = p_transfer['batch_size'] api_server = p_transfer['api_server'] status = p_transfer['status'] sql="""insert into t_db_transfer_config( transfer_tag,server_id,comments,sour_db_id,sour_schema, sour_table,sour_where,dest_db_id,dest_schema,script_path, script_file,python3_home,api_server,batch_size,status,transfer_type) values('{0}','{1}','{2}','{3}','{4}', '{5}','{6}','{7}','{8}','{9}', '{10}','{11}','{12}','{13}','{14}','{15}') """.format(transfer_tag,transfer_server,task_desc,sour_db_server,sour_db_name, sour_tab_name,sour_tab_where,dest_db_server,dest_db_name,script_base, script_name,python3_home,api_server,batch_size,status,transfer_type) print(sql) cr.execute(sql) cr.close() db.commit() result['code']='0' result['message']='保存成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def write_mysql_opr_log(p_userid, p_instid, p_sql, curdb): result = {} db = get_connection() cr = db.cursor() st = '''insert into t_db_inst_opt_log(user_id,inst_id,db,statement,status) values('{}','{}','{}','{}','{}') '''.format(p_userid, p_instid, curdb, format_sql(p_sql), '1') cr.execute(st) db.commit() result['status'] = '2' result['msg'] = '发布成功!' result['data'] = '' result['column'] = '' return result
def save_ds(p_ds): result = {} val = check_ds(p_ds, 'add') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() ds_id = get_dsid() ds_market_id = p_ds['market_id'] ds_inst_type = p_ds['inst_type'] ds_db_type = p_ds['db_type'] ds_db_env = p_ds['db_env'] ds_db_desc = p_ds['db_desc'] ds_ip = format_sql(p_ds['ip']) ds_port = p_ds['port'] ds_service = p_ds['service'] ds_user = p_ds['user'] ds_proxy_status = p_ds['proxy_status'] ds_proxy_server = p_ds['proxy_server'] if p_ds['pass'] != '': ds_pass = aes_encrypt(p_ds['pass'], ds_user) else: ds_pass = p_ds['pass'] status = p_ds['status'] sql = """insert into t_db_source (id,db_type,db_env,db_desc,ip,port,service,user,password,status,creation_date,creator,last_update_date,updator,market_id,inst_type,proxy_status,proxy_server) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}') """.format(ds_id, ds_db_type, ds_db_env, ds_db_desc, ds_ip, ds_port, ds_service, ds_user, ds_pass, status, current_rq(), 'DBA', current_rq(), 'DBA', ds_market_id, ds_inst_type, ds_proxy_status, ds_proxy_server) print(sql) cr.execute(sql) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '保存成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def save_sys_code_detail(code): result = {} try: db = get_connection() cr = db.cursor() sql= "insert into t_dmmx(dm,dmm,dmmc,flag,create_time,update_time) values('{}','{}','{}','{}',now(),now())".\ format(code['type_code'],format_sql(code['detail_code']),code['detail_name'],code['detail_status']) print('save_sys_code_detail=', sql) cr.execute(sql) cr.close() db.commit() result['code'] = '0' result['message'] = '保存成功!' return result except Exception as e: print(traceback.format_exc()) result['code'] = '-1' result['message'] = '保存失败!' return result
def check_db_user_rep(p_db_user): db = get_connection() cr = db.cursor() sql = "select count(0) from t_db_user where concat(inst_id,db_user)='{0}{1}'".format(p_db_user["inst_id"],format_sql(p_db_user['db_user'])) print(sql) cr.execute(sql) rs=cr.fetchone() cr.close() db.commit() return rs[0]
def upd_sync(p_sync): result = {} val = check_sync(p_sync, 'upd') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() sync_server = p_sync['sync_server'] sour_db_server = p_sync['sour_db_server'] desc_db_server = p_sync['desc_db_server'] sync_tag = p_sync['sync_tag'] sync_ywlx = p_sync['sync_ywlx'] sync_type = p_sync['sync_data_type'] script_base = p_sync['script_base'] script_name = p_sync['script_name'] run_time = p_sync['run_time'] task_desc = p_sync['task_desc'] python3_home = p_sync['python3_home'] sync_schema = p_sync['sync_schema'] sync_schema_dest = p_sync['sync_schema_dest'] sync_tables = p_sync['sync_tables'] sync_batch_size = p_sync['sync_batch_size'] sync_batch_size_incr = p_sync['sync_batch_size_incr'] sync_gap = p_sync['sync_gap'] sync_col_name = p_sync['sync_col_name'] sync_col_val = format_sql(p_sync['sync_col_val']) sync_time_type = p_sync['sync_time_type'] sync_repair_day = p_sync['sync_repair_day'] api_server = p_sync['api_server'] status = p_sync['status'] sync_id = p_sync['sync_id'] sql = '' if sync_schema_dest == '': sql = """update t_db_sync_config set server_id ='{0}', sour_db_id ='{1}', desc_db_id ='{2}', sync_tag ='{3}', sync_ywlx ='{4}', sync_type ='{5}', comments ='{6}', run_time ='{7}', sync_table ='{8}', sync_schema ='{9}', batch_size ='{10}', batch_size_incr ='{11}', sync_gap ='{12}', script_path ='{13}', script_file ='{14}', python3_home ='{15}', api_server ='{16}', sync_col_name ='{17}', sync_col_val ='{18}', sync_time_type ='{19}', status ='{20}', sync_schema_dest = null, sync_repair_day = '{21}' where id={22}""".format( sync_server, sour_db_server, desc_db_server, sync_tag, sync_ywlx, sync_type, task_desc, run_time, sync_tables, sync_schema, sync_batch_size, sync_batch_size_incr, sync_gap, script_base, script_name, python3_home, api_server, sync_col_name, sync_col_val, sync_time_type, status, sync_repair_day, sync_id) else: sql = """update t_db_sync_config set server_id ='{0}', sour_db_id ='{1}', desc_db_id ='{2}', sync_tag ='{3}', sync_ywlx ='{4}', sync_type ='{5}', comments ='{6}', run_time ='{7}', sync_table ='{8}', sync_schema ='{9}', batch_size ='{10}', batch_size_incr ='{11}', sync_gap ='{12}', script_path ='{13}', script_file ='{14}', python3_home ='{15}', api_server ='{16}', sync_col_name ='{17}', sync_col_val ='{18}', sync_time_type ='{19}', status ='{20}', sync_schema_dest ='{21}', sync_repair_day ='{22}' where id={23}""".format( sync_server, sour_db_server, desc_db_server, sync_tag, sync_ywlx, sync_type, task_desc, run_time, sync_tables, sync_schema, sync_batch_size, sync_batch_size_incr, sync_gap, script_base, script_name, python3_home, api_server, sync_col_name, sync_col_val, sync_time_type, status, sync_schema_dest, sync_repair_day, sync_id) print(sql) cr.execute(sql) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '更新成功!' except: result['code'] = '-1' result['message'] = '更新失败!' return result
def save_sync(p_backup): result = {} #增加tag重复验证 val = check_sync(p_backup, 'add') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sync_server = p_backup['sync_server'] sour_db_server = p_backup['sour_db_server'] desc_db_server = p_backup['desc_db_server'] sync_tag = p_backup['sync_tag'] sync_ywlx = p_backup['sync_ywlx'] sync_type = p_backup['sync_data_type'] script_base = p_backup['script_base'] script_name = p_backup['script_name'] run_time = p_backup['run_time'] task_desc = p_backup['task_desc'] python3_home = p_backup['python3_home'] sync_schema = p_backup['sync_schema'] sync_schema_dest = p_backup['sync_schema_dest'] sync_tables = p_backup['sync_tables'] sync_batch_size = p_backup['sync_batch_size'] sync_batch_size_incr = p_backup['sync_batch_size_incr'] sync_gap = p_backup['sync_gap'] sync_col_name = p_backup['sync_col_name'] sync_col_val = format_sql(p_backup['sync_col_val']) sync_time_type = p_backup['sync_time_type'] sync_repair_day = p_backup['sync_repair_day'] api_server = p_backup['api_server'] status = p_backup['status'] sql = '' if sync_schema_dest == '': sql = """insert into t_db_sync_config( sour_db_id,desc_db_id,server_id, sync_tag,sync_ywlx,sync_type, comments,run_time,sync_table,sync_schema, batch_size,batch_size_incr,sync_gap, script_path,script_file,python3_home,api_server, sync_col_name,sync_col_val,sync_time_type,status,sync_schema_dest,sync_repair_day) values('{0}','{1}','{2}', '{3}','{4}','{5}', '{6}','{7}','{8}','{9}', '{10}','{11}','{12}', '{13}','{14}','{15}','{16}', '{17}','{18}','{19}','{20}',null,'{21}') """.format(sour_db_server, desc_db_server, sync_server, sync_tag, sync_ywlx, sync_type, task_desc, run_time, sync_tables, sync_schema, sync_batch_size, sync_batch_size_incr, sync_gap, script_base, script_name, python3_home, api_server, sync_col_name, sync_col_val, sync_time_type, status, sync_repair_day) else: sql = """insert into t_db_sync_config( sour_db_id,desc_db_id,server_id, sync_tag,sync_ywlx,sync_type, comments,run_time,sync_table,sync_schema, batch_size,batch_size_incr,sync_gap, script_path,script_file,python3_home,api_server, sync_col_name,sync_col_val,sync_time_type,status,sync_schema_dest,sync_repair_day) values('{0}','{1}','{2}', '{3}','{4}','{5}', '{6}','{7}','{8}','{9}', '{10}','{11}','{12}', '{13}','{14}','{15}','{16}', '{17}','{18}','{19}','{20}','{21}','{22}') """.format(sour_db_server, desc_db_server, sync_server, sync_tag, sync_ywlx, sync_type, task_desc, run_time, sync_tables, sync_schema, sync_batch_size, sync_batch_size_incr, sync_gap, script_base, script_name, python3_home, api_server, sync_col_name, sync_col_val, sync_time_type, status, sync_schema_dest, sync_repair_day) print(sql) cr.execute(sql) cr.close() db.commit() result['code'] = '0' result['message'] = '保存成功!' return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def upd_ds(p_ds): result = {} val = check_ds(p_ds, 'upd') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() ds_id = p_ds['dsid'] ds_market_id = p_ds['market_id'] ds_inst_type = p_ds['inst_type'] ds_db_type = p_ds['db_type'] ds_db_env = p_ds['db_env'] ds_db_desc = p_ds['db_desc'] ds_ip = format_sql(p_ds['ip']) ds_port = p_ds['port'] ds_service = p_ds['service'] ds_user = p_ds['user'] ds_proxy_status = p_ds['proxy_status'] ds_proxy_server = p_ds['proxy_server'] print('upd_ds...p_ds=', p_ds) if p_ds['pass'] != '': ds_pass = aes_encrypt(p_ds['pass'], ds_user) else: ds_pass = p_ds['pass'] status = p_ds['status'] sql = """update t_db_source set db_type ='{0}', db_env ='{1}', db_desc ='{2}' , ip ='{3}', port ='{4}' , service ='{5}' , user ='******' , password ='******' , status ='{8}' , last_update_date ='{9}' , updator ='{10}', market_id ='{11}', inst_type ='{12}', proxy_status ='{13}', proxy_server ='{14}' where id='{15}'""".format(ds_db_type, ds_db_env, ds_db_desc, ds_ip, ds_port, ds_service, ds_user, ds_pass, status, current_rq(), 'DBA', ds_market_id, ds_inst_type, ds_proxy_status, ds_proxy_server, ds_id) print(sql) cr.execute(sql) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '更新成功!' except: result['code'] = '-1' result['message'] = '更新失败!' return result