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