Ejemplo n.º 1
0
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
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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
Ejemplo n.º 11
0
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
Ejemplo n.º 12
0
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
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
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
Ejemplo n.º 16
0
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
Ejemplo n.º 17
0
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
Ejemplo n.º 18
0
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
Ejemplo n.º 19
0
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
Ejemplo n.º 20
0
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
Ejemplo n.º 21
0
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
Ejemplo n.º 22
0
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
Ejemplo n.º 23
0
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
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
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]
Ejemplo n.º 26
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
Ejemplo n.º 27
0
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
Ejemplo n.º 28
0
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
Ejemplo n.º 29
0
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
Ejemplo n.º 30
0
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