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