def check_ds_valid(p_id): result = {} try: p_ds = get_ds_by_dsid(p_id) if p_ds['db_type'] == '0': conn = get_connection_ds(p_ds) elif p_ds['db_type'] == '1': conn = get_connection_ds_oracle(p_ds) elif p_ds['db_type'] == '2': conn = get_connection_ds_sqlserver(p_ds) elif p_ds['db_type'] == '3': conn = get_connection_ds_pg(p_ds) elif p_ds['db_type'] == '4': conn = get_connection_ds_es(p_ds) elif p_ds['db_type'] == '5': conn = get_connection_ds_redis(p_ds) elif p_ds['db_type'] == '6': conn = get_connection_ds_mongo(p_ds) result['code'] = '0' result['message'] = '验证通过' print('ds=', conn) return result except: exception_info() result['code'] = '-1' result['message'] = '验证失败' return result
def upd_password(p_user): result = {} try: db = get_connection() cr = db.cursor() userid = p_user['userid'] loginname = p_user['loginname'] password = aes_encrypt(p_user['password'], loginname) sql = """update t_user set password ='******', last_update_date ='{1}' , updator='{2}' where id='{3}'""".format(password, current_rq(), 'DBA', userid) print("upd_password=", sql) cr.execute(sql) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '修改成功!' except: exception_info() result['code'] = '-1' result['message'] = '修改失败!' return result
def update_db_config(d_db_para): result = {} val = check_db_para(d_db_para) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sql = """update t_db_inst_parameter set value='{}={}',last_update_date=now() where id={} """.format(d_db_para['para_name'], d_db_para['para_val'], d_db_para['para_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 save_db_inst_para(p_index): result = {} val = check_db_inst_para(p_index) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sql = """insert into t_db_inst_para(para_name,para_value,para_desc,para_status) values('{0}','{1}','{2}','{3}') """.format(p_index['para_name'], p_index['para_value'], p_index['para_desc'], p_index['para_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_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 save_sync_tab(p_sync): result = {} try: db = get_connection() cr = db.cursor() result = {} sync_id = p_sync['sync_id'] sync_tag = p_sync['sync_tag'] db_name = p_sync['db_name'] schema_name = p_sync['schema_name'] tab_name = p_sync['tab_name'] sync_cols = p_sync['sync_cols'] sync_incr_col = p_sync['sync_incr_col'] sync_time = p_sync['sync_time'] if check_sync_tab(sync_id) == 0: sql = """insert into t_db_sync_tab_config(sync_tag,db_name,schema_name,tab_name, sync_cols, sync_incr_col,sync_time,status,create_date) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',now()) """.format(sync_tag, db_name, schema_name, tab_name, sync_cols, sync_incr_col, sync_time, '1') result['code'] = '0' result['message'] = '保存成功!' else: sql = """update t_db_sync_tab_config set sync_tag = '{}', db_name = '{}', schema_name ='{}', tab_name = '{}', sync_cols = '{}', sync_incr_col = '{}', sync_time = '{}', update_date = now() where id = '{}' """.format(sync_tag, db_name, schema_name, tab_name, sync_cols, sync_incr_col, sync_time, sync_id) result['code'] = '0' result['message'] = '更新成功!' print(sql) cr.execute(sql) # 更新同步任务sync_tables值 cr.execute( "update t_db_sync_config set sync_table='{}' where sync_tag='{}'". format(sync_tag, query_sync_tab_cfg(sync_tag))) cr.close() db.commit() return result except: e_str = exception_info() print(e_str) result['code'] = '-1' result['message'] = '保存失败!' return result
def check_server_valid(p_id): result = {} try: p_ds = get_server_by_serverid(p_id) if p_ds['db_type'] == '0': conn = get_connection_ds(p_ds) elif p_ds['db_type'] == '1': conn = get_connection_ds_oracle(p_ds) elif p_ds['db_type'] == '2': conn = get_connection_ds_sqlserver(p_ds) elif p_ds['db_type'] == '3': conn = get_connection_ds_pg(p_ds) result['code'] = '0' result['message'] = '验证通过' return result except: exception_info() 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 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 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 save_minio(p_sync): result = {} val = check_minio(p_sync, 'I') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sql = """insert into t_minio_config( sync_tag,comments,sync_type, server_id,sync_path,sync_service, minio_server, python3_home,script_path, script_file,api_server,run_time, status,minio_user,minio_pass, minio_bucket,minio_dpath,minio_incr,minio_incr_type) values('{}','{}','{}', '{}','{}','{}', '{}','{}','{}', '{}','{}','{}', '{}','{}','{}', '{}','{}','{}','{}') """.format(p_sync['sync_tag'], p_sync['task_desc'], p_sync['sync_type'], p_sync['server_id'], p_sync['sync_dir'], p_sync['sync_service'], p_sync['minio_server'], p_sync['python3_home'], p_sync['script_base'], p_sync['script_name'], p_sync['api_server'], p_sync['run_time'], p_sync['status'], p_sync['minio_user'], p_sync['minio_pass'], p_sync['minio_bucket'], p_sync['minio_dpath'], p_sync['minio_incr'], p_sync['minio_incr_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 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_archive(p_archive): result = {} val = check_archive(p_archive) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sql = """insert into t_db_archive_config( archive_tag,comments,archive_db_type, server_id,sour_db_id,sour_schema, sour_table,archive_time_col,archive_rentition, rentition_time,rentition_time_type,dest_db_id, dest_schema,python3_home,script_path, script_file,batch_size,api_server, status,if_cover,run_time) values('{0}','{1}','{2}','{3}','{4}','{5}', '{6}','{7}','{8}','{9}','{10}','{11}', '{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}') """.format( p_archive['archive_tag'], p_archive['task_desc'], p_archive['archive_db_type'], p_archive['archive_server'], p_archive['sour_db_server'], p_archive['sour_db_name'], p_archive['sour_tab_name'], p_archive['archive_time_col'], p_archive['archive_rentition'], p_archive['rentition_time'], p_archive['rentition_time_type'], p_archive['dest_db_server'], p_archive['dest_db_name'], p_archive['python3_home'], p_archive['script_base'], p_archive['script_name'], p_archive['batch_size'], p_archive['api_server'], p_archive['status'], p_archive['if_cover'], p_archive['run_time']) 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 delete_db_user(p_db_user_id): result = {} try: db = get_connection() cr = db.cursor() result = {} sql = "delete from t_db_user where id='{}'".format(p_db_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 del_sync_tab(p_sync): result = {} try: db = get_connection() cr = db.cursor() result = {} sql = "delete from t_db_sync_tab_config where id={}".format( p_sync['sync_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 save_server(p_server): result = {} val = check_server(p_server) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} market_id = p_server['market_id'] server_desc = p_server['server_desc'] server_type = p_server['server_type'] server_ip = p_server['server_ip'] server_port = p_server['server_port'] server_user = p_server['server_user'] server_pass = aes_encrypt(p_server['server_pass'], server_user) server_os = p_server['server_os'] server_cpu = p_server['server_cpu'] server_mem = p_server['server_mem'] status = p_server['status'] sql = """insert into t_server(market_id,server_desc,server_type,server_ip,server_port,server_user,server_pass,server_os,server_cpu,server_mem,status) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}') """.format(market_id, server_desc, server_type, server_ip, server_port, server_user, server_pass, server_os, server_cpu, server_mem, 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_port(p_port): result = {} val=check_port(p_port) if val['code']=='-1': return val try: db = get_connection() cr = db.cursor() result = {} market_id = p_port['market_id'] market_name = p_port['market_name'] app_desc = p_port['app_desc'] local_ip = p_port['local_ip'] local_port = p_port['local_port'] mapping_port = p_port['mapping_port'] mapping_domain = p_port['mapping_domain'] mapping_type = p_port['mapping_type'] creater = p_port['creater'] sql="""insert into t_port(market_id,market_name,app_desc,local_ip,local_port,mapping_port,mapping_domain,mapping_type,creater,create_date) values('{}','{}','{}','{}','{}','{}','{}','{}','{}',now()) """.format(market_id,market_name,app_desc,local_ip,local_port,mapping_port,mapping_domain,mapping_type,creater) 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_db_inst(d_inst): result = {} val = check_db_inst(d_inst, 'add') if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} inst_id = get_inst_id() if d_inst['mgr_pass'] != '': inst_pass = aes_encrypt(d_inst['mgr_pass'], d_inst['mgr_user']) else: inst_pass = d_inst['mgr_pass'] # 保存实例元数据 sql = """insert into t_db_inst(id,inst_name,server_id,templete_id,inst_ip,inst_port,inst_type,inst_env, is_rds,inst_ver,mgr_user,mgr_pass,created_date,api_server,python3_home,script_path,script_file,inst_mapping_port) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}', '{8}','{9}','{10}','{11}',now(),'{12}','{13}','{14}','{15}','{16}') """.format(inst_id, d_inst['inst_name'], d_inst['server_id'], d_inst['templete_id'], d_inst['inst_ip'], d_inst['inst_port'], d_inst['inst_type'], d_inst['inst_env'], d_inst['is_rds'], d_inst['inst_ver'], d_inst['mgr_user'], inst_pass, d_inst['api_server'], d_inst['python3_home'], d_inst['script_path'], d_inst['script_file'], d_inst['inst_mapping_port']) print(sql) cr.execute(sql) # 生成实例配置信息 sql = """insert into t_db_inst_parameter(inst_id,name,value,type,STATUS,create_date) select a.id,b.dmmc,b.dmm,'mysqld',b.flag,NOW() from t_db_inst a,t_dmmx b where a.templete_id=b.dm and b.dm='30' and b.flag='1' and b.dmm NOT LIKE '%default-character-set=utf8mb4%' and a.id={} UNION ALL select a.id,b.dmmc,b.dmm,'mysql',b.flag,NOW() from t_db_inst a,t_dmmx b where a.templete_id=b.dm and b.dm='30' AND b.flag='1' and b.dmm LIKE 'default-character-set%' and a.id={} UNION ALL select a.id,b.dmmc,b.dmm,'client',b.flag,NOW() from t_db_inst a,t_dmmx b where a.templete_id=b.dm and b.dm='30' AND b.flag='1' and (b.dmm LIKE 'default-character-set%' OR b.dmm LIKE 'socket%') and a.id={} """.format(inst_id, inst_id, inst_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 save_datax_sync(p_sync): result = {} val = check_datax_sync(p_sync) if val['code'] == '-1': return val try: db = get_connection() cr = db.cursor() result = {} sync_tag = p_sync['sync_tag'] sync_server = p_sync['sync_server'] sour_db_server = p_sync['sour_db_server'] sour_db_name = p_sync['sour_db_name'] sour_tab_name = p_sync['sour_tab_name'] sour_tab_cols = p_sync['sour_tab_cols'] sour_incr_col = p_sync['sour_incr_col'] zk_hosts = p_sync['zk_hosts'] hbase_thrift = p_sync['hbase_thrift'] sync_ywlx = p_sync['sync_ywlx'] sync_data_type = p_sync['sync_data_type'] python3_home = p_sync['python3_home'] script_base = p_sync['script_base'] run_time = p_sync['run_time'] task_desc = p_sync['task_desc'] datax_home = p_sync['datax_home'] sync_time_type = p_sync['sync_time_type'] sync_gap = p_sync['sync_gap'] api_server = p_sync['api_server'] status = p_sync['status'] sync_hbase_table = p_sync['sync_hbase_table'] sync_hbase_rowkey_sour = p_sync['sync_hbase_rowkey'] sync_hbase_rowkey = get_hbase_rowkey(p_sync) sync_hbase_columns = get_hbase_columns(p_sync) sync_hbase_rowkey_separator = p_sync['sync_hbase_rowkey_separator'] es_service = p_sync['es_service'] es_index_name = p_sync['es_index_name'] es_type_name = p_sync['es_type_name'] sync_incr_where = get_sync_incr_where(p_sync) sync_es_columns = get_es_columns(p_sync) sql = """insert into t_datax_sync_config( sync_tag,server_id,sour_db_id,sync_schema,sync_table, sync_columns,sync_incr_col,zk_hosts,sync_ywlx,sync_type, script_path,run_time,comments,datax_home,sync_time_type, sync_gap,api_server,status,sync_hbase_table,sync_hbase_rowkey, sync_hbase_rowkey_separator,sync_hbase_columns,sync_hbase_rowkey_sour, sync_incr_where,python3_home,hbase_thrift,es_service,es_index_name,es_type_name,sync_es_columns) values('{0}','{1}','{2}','{3}','{4}', '{5}','{6}','{7}','{8}','{9}', '{10}','{11}','{12}','{13}','{14}', '{15}','{16}','{17}','{18}','{19}', '{20}','{21}','{22}','{23}','{24}','{25}','{26}') """.format(sync_tag, sync_server, sour_db_server, sour_db_name, sour_tab_name, sour_tab_cols, sour_incr_col, zk_hosts, sync_ywlx, sync_data_type, script_base, run_time, task_desc, datax_home, sync_time_type, sync_gap, api_server, status, sync_hbase_table, sync_hbase_rowkey, sync_hbase_rowkey_separator, sync_hbase_columns, sync_hbase_rowkey_sour, sync_incr_where, python3_home, hbase_thrift, es_service, es_index_name, es_type_name, sync_es_columns) 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_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_user(p_user): result = {} try: db = get_connection() cr = db.cursor() userid = p_user['userid'] loginname = p_user['loginname'] wkno = p_user['wkno'] username = p_user['username'] password = aes_encrypt(p_user['password'], loginname) gender = p_user['gender'] email = p_user['email'] phone = p_user['phone'] proj_group = p_user['proj_group'] dept = p_user['dept'] expire_date = p_user['expire_date'] status = p_user['status'] roles = p_user['roles'] file_path = p_user['file_path'] file_name = p_user['file_name'] if file_path == '': file_path = '/static/assets/images/users' if file_name == '': if gender == '1': file_name = 'boy.png' else: file_name = 'girl.png' sql = """update t_user set name ='{0}', login_name='{1}', password ='******', gender ='{3}', email ='{4}', phone ='{5}', dept ='{6}', expire_date ='{7}' , status ='{8}' , last_update_date ='{9}' , updator ='{10}', file_path ='{11}', file_name = '{12}', project_group = '{13}', wkno = '{14}' where id='{15}'""".format(username, loginname, password, gender, email, phone, dept, expire_date, status, current_rq(), 'DBA', file_path, file_name, proj_group, wkno, userid) print("upd_user=", sql) cr.execute(sql) upd_user_role(userid, roles) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '更新成功!' except: exception_info() result['code'] = '-1' result['message'] = '更新失败!' return result
def save_user_proj_privs(d_proj): result = {} dsid = d_proj['dsid'] userid = d_proj['userid'] priv_query = d_proj['priv_query'] priv_release = d_proj['priv_release'] priv_audit = d_proj['priv_audit'] priv_execute = d_proj['priv_execute'] priv_order = d_proj['priv_order'] try: db = get_connection() cr = db.cursor() #process query privs if priv_query == '1': sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='1'""".format( dsid, userid) cr.execute(sql) sql = """insert into t_user_proj_privs(proj_id,user_id,priv_id) values('{0}','{1}','{2}') """.format( dsid, userid, '1') cr.execute(sql) else: sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='1'""".format( dsid, userid) cr.execute(sql) # process release privs if priv_release == '1': sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='2'""".format( dsid, userid) cr.execute(sql) sql = """insert into t_user_proj_privs(proj_id,user_id,priv_id) values('{0}','{1}','{2}') """.format( dsid, userid, '2') cr.execute(sql) else: sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='2'""".format( dsid, userid) cr.execute(sql) # process audit privs if priv_audit == '1': sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='3'""".format( dsid, userid) cr.execute(sql) sql = """insert into t_user_proj_privs(proj_id,user_id,priv_id) values('{0}','{1}','{2}') """.format( dsid, userid, '3') cr.execute(sql) else: sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='3'""".format( dsid, userid) cr.execute(sql) #process execute privs if priv_execute == '1': sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='4'""".format( dsid, userid) cr.execute(sql) sql = """insert into t_user_proj_privs(proj_id,user_id,priv_id) values('{0}','{1}','{2}') """.format( dsid, userid, '4') cr.execute(sql) else: sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='4'""".format( dsid, userid) cr.execute(sql) #process order privs if priv_order == '1': sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='5'""".format( dsid, userid) cr.execute(sql) sql = """insert into t_user_proj_privs(proj_id,user_id,priv_id) values('{0}','{1}','{2}') """.format( dsid, userid, '5') cr.execute(sql) else: sql = """delete from t_user_proj_privs where proj_id='{0}' and user_id='{1}' and priv_id='5'""".format( dsid, userid) cr.execute(sql) cr.close() db.commit() result = {} result['code'] = '0' result['message'] = '保存成功!' return result except: exception_info() result['code'] = '-1' result['message'] = '保存失败!' return result