def query_role(p_name):
db = get_connection()
cr = db.cursor()
if p_name == "":
sql = """select id,name,
case status when '1' then '是'
when '0' then '否'
end status,
creator,date_format(creation_date,'%Y-%m-%d') creation_date,
updator,date_format(last_update_date,'%Y-%m-%d') last_update_date
from t_role
order by name""".format(p_name)
else:
sql = """select id,name,
case status when '1' then '是'
when '0' then '否'
end status,
creator,date_format(creation_date,'%Y-%m-%d') creation_date,
updator,date_format(last_update_date,'%Y-%m-%d') last_update_date
from t_role
where binary name like '%{0}%'
order by name""".format(p_name)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_db_inst_para(para_code):
db = get_connection()
cr = db.cursor()
v_where = ' '
if para_code != '':
v_where = " where a.para_code like '%{0}%' or a.para_code like '%{1}%'".format(
para_code, para_code)
sql = """SELECT
id,
para_name,
para_value,
para_desc,
CASE a.para_status WHEN '1' THEN '启用' WHEN '0' THEN '禁用' END AS flag
FROM t_db_inst_para a
{0}
order by a.para_name,a.id
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def imp_port(p_file,p_name):
try:
result={}
file = xlrd.open_workbook(p_file)
name = file.sheet_names()[0]
sheet = file.sheet_by_name(name)
vals = ''
for i in range(1, sheet.nrows):
val=''
for j in range(0, sheet.ncols):
val=val+"'"+str(sheet.cell(i, j).value)+"',"
vals =vals +'('+val[0:-1]+'),'
print('vals=',vals)
db = get_connection()
cr = db.cursor()
sql="insert into t_port(market_id,market_name,app_desc,local_ip,local_port,mapping_port,mapping_domain) values {0}".format(vals[0:-1])
print(sql)
cr.execute(sql)
sql2 = """update t_port set creater='{}',create_date=now()""".format(p_name)
print(sql2)
cr.execute(sql2)
cr.close()
db.commit()
result={}
result['code']='0'
result['message']='导入成功！'
except :
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 query_db_inst_log(p_log_name):
db = get_connection()
cr = db.cursor()
v_where = ' '
if p_log_name != '':
v_where = " where a.statement like '%{0}%' or a.db like '%{1}%'".format(
p_log_name, p_log_name)
sql = """SELECT
a.id,
b.name,
c.inst_name,
a.db,
DATE_FORMAT(a.start_time,'%Y-%m-%d %H:%i:%s') AS start_time,
DATE_FORMAT(a.end_time,'%Y-%m-%d %H:%i:%s') AS end_time,
d.dmmc as status,
a.statement,
a.message
FROM t_db_inst_opt_log a,t_user b,t_db_inst c,t_dmmx d
WHERE a.user_id=b.id
AND a.inst_id=c.id
AND a.status=d.dmm
AND d.dm='28'
{0}
ORDER BY a.start_time desc ,a.db,a.id
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
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 query_db_config(p_inst_env, p_inst_id):
db = get_connection()
cr = db.cursor()
v_where = ''
if p_inst_id != '':
v_where = v_where + " and a.inst_id= '{0}'".format(p_inst_id)
if p_inst_env != '':
v_where = v_where + " and b.inst_env= '{0}'".format(p_inst_env)
sql = """SELECT
a.id as para_id,
SUBSTR(a.value,1,INSTR(a.value,'=')-1) AS para_name,
SUBSTR(a.VALUE,INSTR(a.value,'=')+1) AS para_val,
a.type AS para_type,
a.name AS para_desc,
DATE_FORMAT(a.create_date,'%Y-%m-%d %h:%i:%s') AS create_date
FROM `t_db_inst_parameter` a ,t_db_inst b
WHERE a.inst_id = b.id
and SUBSTR(a.value,1,INSTR(a.value,'=')-1)
NOT IN('basedir','port','socket','log-error','pid-file','datadir')
{}
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def get_transfer_by_transferid(p_transferid):
db = get_connection()
cr = db.cursor()
sql = """select id,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,status,batch_size,transfer_type
from t_db_transfer_config where id={0}
""".format(p_transferid)
cr.execute(sql)
rs = cr.fetchall()
d_transfer = {}
d_transfer['server_id'] = rs[0][0]
d_transfer['transfer_tag'] = rs[0][1]
d_transfer['server_id'] = rs[0][2]
d_transfer['task_desc'] = rs[0][3]
d_transfer['sour_db_id'] = rs[0][4]
d_transfer['sour_schema'] = rs[0][5]
d_transfer['sour_table'] = rs[0][6]
d_transfer['sour_where'] = rs[0][7]
d_transfer['dest_db_id'] = rs[0][8]
d_transfer['dest_schema'] = rs[0][9]
d_transfer['script_path'] = rs[0][10]
d_transfer['script_file'] = rs[0][11]
d_transfer['python3_home'] = rs[0][12]
d_transfer['api_server'] = rs[0][13]
d_transfer['status'] = rs[0][14]
d_transfer['batch_size'] = rs[0][15]
d_transfer['transfer_type'] = rs[0][16]
cr.close()
db.commit()
print(d_transfer)
return d_transfer
def query_transfer_detail(transfer_id):
db = get_connection()
cr = db.cursor()
sql = """SELECT a.transfer_tag,
a.comments,
b.server_desc,
e.dmmc AS transfer_type,
CONCAT(c.ip,':',c.port,'/',a.sour_schema) AS transfer_db_sour,
a.sour_schema,
LOWER(a.sour_table) AS sour_table,
a.sour_where,
CONCAT(d.ip,':',d.port,'/',a.dest_schema) AS transfer_db_dest,
a.`dest_schema`,
a.python3_home,
a.script_path,
a.script_file,
a.batch_size,
a.api_server,
a.status
FROM t_db_transfer_config a,t_server b,t_db_source c,t_db_source d,t_dmmx e
WHERE a.server_id=b.id
AND a.sour_db_id=c.id
AND a.dest_db_id=d.id
AND a.transfer_type=e.dmm
AND e.dm='09'
AND a.id='{0}'
ORDER BY a.id
""".format(transfer_id)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
v_list=list(rs)
cr.close()
db.commit()
return v_list
def check_sync_repeat(p_sync):
result = {}
db = get_connection()
cr = db.cursor()
sql = """select count(0) from t_db_sync_config where sync_tag='{0}' """.format(
p_sync["sync_tag"])
print('check_sync_repeat1=', sql)
cr.execute(sql)
rs1 = cr.fetchone()
sql = """select count(0) from t_db_sync_config where comments='{0}' """.format(
p_sync["task_desc"])
print('check_sync_repeat2=', sql)
cr.execute(sql)
rs2 = cr.fetchone()
if rs1[0] > 0:
result['code'] = True
result['message'] = '数据标识不能重复!'
elif rs2[0] > 0:
result['code'] = True
result['message'] = '同步描述不能重复!'
else:
result['code'] = False
result['message'] = '!'
cr.close()
db.commit()
return result
def query_sync_flow():
# ds = get_ds_by_dsid()
# db = get_connection_ds(ds)
db = get_connection()
cr = db.cursor()
sql = """SELECT
b.sync_col_val,
b.comments,
date_format(a.create_date,'%Y-%m-%d %H:%i:%s') as create_date,
concat(a.duration,''),
concat(a.amount,''),
CASE WHEN TIMESTAMPDIFF(MINUTE,a.create_date,NOW())<60 THEN '√' ELSE '×' END AS flag
FROM t_db_sync_tasks_log a,t_db_sync_config b
WHERE a.sync_tag = b.sync_tag
AND b.sync_ywlx='1' AND b.status='1'
AND (a.sync_tag,a.create_date) IN(
SELECT
a.sync_tag,
MAX(a.create_date)
FROM t_db_sync_tasks_log a
WHERE a.create_date>DATE_SUB(DATE(NOW()),INTERVAL 2 DAY)
GROUP BY a.sync_tag
)
"""
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_sync_case_log(p_tag):
result = {}
db = get_connection()
cr = db.cursor()
sql = """ SELECT
date_format(create_date,'%H:%i') as create_date,
amount
FROM `t_db_sync_tasks_log`
WHERE create_date>=DATE_ADD(NOW(),INTERVAL -3 HOUR)
AND sync_tag='{}'
ORDER BY create_date
""".format(p_tag.split(',')[0])
print(sql)
cr.execute(sql)
v_list = []
x = []
y = []
result['amount'] = {}
for r in cr.fetchall():
v_list.append(list(r))
x.append(r[0])
y.append(r[1])
result['amount']['x'] = x
result['amount']['y'] = y
result['amount']['t'] = p_tag
print('query_sync_case_log=', result)
cr.close()
db.commit()
return result
def query_sync_case():
result = {}
db = get_connection()
cr = db.cursor()
sql = """ SELECT
market_id,
market_name,
flow_flag,
flow_real_flag,
flow_device_flag,
park_flag,
park_real_flag,
sales_dldf_flag,
date_format(create_date,'%Y-%m-%d %H:%i') as create_date
FROM t_db_sync_monitor
ORDER BY CASE WHEN market_name LIKE '%北京%' THEN 1
WHEN market_name LIKE '%上海%' THEN 2
WHEN market_name LIKE '%成都%' THEN 3
WHEN market_name LIKE '%广州%' THEN 4
ELSE 5 END
"""
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
result['data'] = v_list
cr.close()
db.commit()
return result
def upd_role(p_role):
result = {}
try:
db = get_connection()
cr = db.cursor()
roleid = p_role['roleid']
rolename = p_role['name']
status = p_role['status']
privs = p_role['privs']
func_privs= p_role['func_privs']
print("upd_role=",roleid,rolename,status)
print("privs=", privs)
print("func_privs=", func_privs)
sql="""update t_role
set name ='{0}',
status ='{1}' ,
last_update_date ='{2}' ,
updator='{3}'
where id='{4}'""".format(rolename,status,current_rq(),'DBA',roleid)
print(sql)
cr.execute(sql)
upd_role_privs(roleid,privs)
upd_role_func_privs(roleid,func_privs)
cr.close()
db.commit()
result={}
result['code']='0'
result['message']='更新成功！'
except :
result['code'] = '-1'
result['message'] = '更新失败！'
return result
def query_user_proj_privs(p_name, p_dsid, is_grants):
db = get_connection()
cr = db.cursor()
if p_name == "":
sql = """select u.id,u.login_name,u.name,u.email,u.phone,u.dept,
(select count(0) from t_user_proj_privs
where proj_id='{0}' and user_id=u.id and priv_id='1') as query_priv,
(select count(0) from t_user_proj_privs
where proj_id='{1}' and user_id=u.id and priv_id='2') as release_priv
from t_user u order by convert(name using gbk) asc""".format(
p_dsid, p_dsid)
else:
sql = """select u.id,u.login_name,u.name,u.email,u.phone,u.dept,
(select count(0) from t_user_proj_privs
where proj_id='{0}' and user_id=u.id and priv_id='1') as query_priv,
(select count(0) from t_user_proj_privs
where proj_id='{1}' and user_id=u.id and priv_id='2') as release_priv
from t_user u
where binary u.name like '%{2}%'
order by convert(name using gbk) asc""".format(
p_dsid, p_dsid, p_name)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_db_user(p_user_name,p_inst_env,p_inst_id):
db = get_connection()
cr = db.cursor()
v_where =''
if p_user_name != '':
v_where = " and b.db_user like '%{0}%'".format(p_user_name)
if p_inst_env != '':
v_where = " and a.inst_env= '{0}'".format(p_inst_env)
if p_inst_id != '':
v_where = " and a.id= '{0}'".format(p_inst_id)
sql = """SELECT a.id,
a.inst_name,
(select dmmc from t_dmmx x where x.dm='02' and x.dmm=a.inst_type) as inst_type,
(select dmmc from t_dmmx x where x.dm='03' and x.dmm=a.inst_env) as inst_env,
b.id,
b.db_user,
(select dmmc from t_dmmx x where x.dm='25' and x.dmm=b.status) as STATUS,
b.description,
date_format(b.created_date,'%Y-%m-%d %h:%i:%s') as created_date
FROM t_db_inst a,t_db_user b
where a.id=b.inst_id
{0}
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def get_user_by_userid(p_userid):
db = get_connection()
cr = db.cursor()
sql = """select cast(id as char) as id,login_name,name,password,gender,email,phone,dept,
date_format(expire_date,'%Y-%m-%d') as expire_date,status,file_path,file_name,project_group,wkno
from t_user where id={0}""".format(p_userid)
cr.execute(sql)
rs = cr.fetchall()
cr.close()
db.commit()
d_user = {}
d_user['userid'] = rs[0][0]
d_user['loginname'] = rs[0][1]
d_user['username'] = rs[0][2]
d_user['password'] = aes_decrypt(rs[0][3], rs[0][1])
d_user['gender'] = rs[0][4]
d_user['email'] = rs[0][5]
d_user['phone'] = rs[0][6]
d_user['dept'] = rs[0][7]
d_user['expire_date'] = rs[0][8]
d_user['status'] = rs[0][9]
d_user['image_path'] = rs[0][10] if rs[0][10] else ''
d_user['image_name'] = rs[0][11] if rs[0][11] else ''
d_user['project_group'] = rs[0][12]
d_user['wkno'] = rs[0][13]
print("get_user_by_userid=", d_user, rs[0][3], rs[0][1])
return d_user
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 query_archive(sync_tag):
db = get_connection()
cr = db.cursor()
v_where = ' and 1=1 '
if sync_tag != '':
v_where = v_where + " and a.archive_tag='{0}'\n".format(sync_tag)
sql = """SELECT a.id,
CONCAT(SUBSTR(a.archive_tag,1,40),'...') AS archive_tag,
a.archive_tag,
a.comments,
b.server_desc,
-- CONCAT(SUBSTR(CONCAT(sour_schema,'.',sour_table),1,40),'...') AS archive_obj,
a.api_server,
CASE a.STATUS WHEN '1' THEN '启用' WHEN '0' THEN '禁用' END AS flag
FROM t_db_archive_config a,t_server b
WHERE a.server_id=b.id AND b.status='1'
{0}
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_slow(p_inst_id,p_inst_env):
db = get_connection()
cr = db.cursor()
vv = ''
if p_inst_id != '':
vv = " where a.inst_id ='{0}' ".format(p_inst_id)
if p_inst_env != '':
vv = vv +" and b.inst_env ='{0}' ".format(p_inst_env)
sql = """select a.id,
b.inst_name,
(SELECT dmmc FROM t_dmmx X WHERE x.dm='03' AND x.dmm=b.inst_env) AS env_name,
a.log_file,
a.query_time,
a.script_file,
a.api_server,
case a.status when '1' then '是' when '0' then '否' end status,
date_format(create_date,'%Y-%m-%d') create_date
from t_slow_log a,t_db_inst b
where a.inst_id=b.id
{}
order by a.id""".format(vv)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
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 del_slow(p_slowid):
result={}
try:
db = get_connection()
cr = db.cursor()
sl = get_slow_by_slowid(p_slowid)
print('del_slow.s1=',sl)
sql="delete from t_slow_log where id='{0}'".format(p_slowid)
print(sql)
cr.execute(sql)
sql = """delete from t_db_inst_parameter
where inst_id={}
and (value like 'slow_query_log%' or value like 'long_query_time%')""".format(sl['inst_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 upd_db_inst_para(p_para):
result = {}
# val = check_db_inst_para(p_para)
# if val['code'] == '-1':
# return val
try:
db = get_connection()
cr = db.cursor()
sql = """update t_db_inst_para set
para_name ='{0}',
para_value ='{1}',
para_desc ='{2}',
para_status ='{3}'
where id='{4}'
""".format(p_para['para_name'], p_para['para_value'],
p_para['para_desc'], p_para['para_status'],
p_para['para_id'])
print(sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '更新成功！'
except:
print(traceback.format_exc())
result['code'] = '-1'
if traceback.format_exc().count('Duplicate') > 0:
result['message'] = '更新失败(违反唯一键)！'
else:
result['message'] = '更新失败'
return result
def del_sys_code(code):
result = {}
try:
db = get_connection()
cr = db.cursor()
cr.execute("select count(0) from t_dmmx where dm='{}'".format(code))
rs = cr.fetchone()
if rs[0] > 0:
result['code'] = '-1'
result['message'] = '存在代码明细数据不能删除！'
return result
sql = "delete from t_dmlx where dm = '{}'".format(code)
print('del_sys_code=', sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '删除成功！'
return result
except Exception as e:
print(traceback.format_exc())
result['code'] = '-1'
result['message'] = '删除失败！'
return result
def get_inst_id():
db = get_connection()
cr = db.cursor()
cr.execute('select max(id)+1 from t_db_inst for update')
rs = cr.fetchone()
cr.close()
return rs[0]
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 query_port(p_market_id):
db = get_connection()
cr = db.cursor()
v_where =''
if p_market_id != '':
v_where = " and a.market_id='{}'".format(p_market_id)
sql = """SELECT a.id,
b.dmmc,
app_desc,
local_ip,
local_port,
mapping_port,
mapping_domain,
c.name,
date_format(create_date,'%Y-%m-%d') create_date
FROM t_port a,t_dmmx b,t_user c
WHERE a.market_id=b.dmm
AND a.creater=c.login_name
AND b.dm='05'
{}
order by a.market_id
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_dm_detail(p_code):
db = get_connection()
cr = db.cursor()
v_where = ' '
if p_code != '':
v_where = " and (b.dm='{0}' or b.dmm='{1}' or a.mc like '%{2}%')".format(
p_code, p_code, p_code)
sql = """SELECT
a.dm,
a.mc,
b.dmm,
b.dmmc,
case b.flag when '1' then '启用' when '0' then '禁用' end as flag,
date_format(b.create_time,'%Y-%m-%d %H:%i:%s') as create_time,
date_format(b.update_time,'%Y-%m-%d %H:%i:%s') as update_time
FROM t_dmlx a,t_dmmx b
where a.dm=b.dm
{0}
ORDER BY b.dm,b.dmm,a.create_time
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def query_minio(tagname):
db = get_connection()
cr = db.cursor()
v_where = ' and 1=1 '
if tagname != '':
v_where = v_where + " and a.sync_tag='{0}'\n".format(tagname)
sql = """SELECT
a.sync_tag,
a.comments,
concat(b.server_ip,':',b.server_port),
(select dmmc from t_dmmx x where x.dm='34' and x.dmm=a.sync_type) as sync_type,
a.minio_server,
a.run_time,
a.api_server,
CASE a.STATUS WHEN '1' THEN '启用' WHEN '0' THEN '禁用' END STATUS
FROM t_minio_config a,t_server b
WHERE a.server_id=b.id
AND b.status='1'
{0}
""".format(v_where)
print(sql)
cr.execute(sql)
v_list = []
for r in cr.fetchall():
v_list.append(list(r))
cr.close()
db.commit()
return v_list
def save_role(p_role):
result = {}
val = check_role(p_role)
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
role_id = get_roleid()
role_name = p_role['name']
status = p_role['status']
privs = p_role['privs']
func_privs = p_role['func_privs']
print('privs=',privs)
sql="""insert into t_role(id,name,status,creation_date,creator,last_update_date,updator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')
""".format(role_id,role_name,status,current_rq(),'DBA',current_rq(),'DBA');
print(sql)
cr.execute(sql)
save_role_privs(role_id,privs)
save_role_func_privs(role_id,func_privs)
cr.close()
db.commit()
result={}
result['code']='0'
result['message']='保存成功！'
return result
except:
result['code'] = '-1'
result['message'] = '保存失败！'
return result