def process_templete(p_sync_id, p_templete):
v_templete = p_templete
p_sync = query_datax_by_id(p_sync_id)
print('process_templete->p_sync=', p_sync)
print('process_templete->p_templete=', p_templete)
#replace full templete
v_templete['full'] = v_templete['full'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['full'] = v_templete['full'].replace(
'$$PASSWORD$$', aes_decrypt(p_sync['password'], p_sync['user']))
v_templete['full'] = v_templete['full'].replace('$$MYSQL_COLUMN_NAMES$$',
get_mysql_columns(p_sync))
v_templete['full'] = v_templete['full'].replace('$$MYSQL_TABLE_NAME$$',
p_sync['sync_table'])
v_templete['full'] = v_templete['full'].replace('$$MYSQL_URL$$',
p_sync['mysql_url'])
v_templete['full'] = v_templete['full'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['full'] = v_templete['full'].replace('$$ZK_HOSTS',
p_sync['zk_hosts'])
v_templete['full'] = v_templete['full'].replace('$$HBASE_TABLE_NAME$$',
p_sync['sync_hbase_table'])
v_templete['full'] = v_templete['full'].replace(
'$$HBASE_ROWKEY$$', p_sync['sync_hbase_rowkey'])
v_templete['full'] = v_templete['full'].replace(
'$$HBASE_COLUMN_NAMES$$', p_sync['sync_hbase_columns'])
#replacre incr templete
v_templete['incr'] = v_templete['incr'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['incr'] = v_templete['incr'].replace(
'$$PASSWORD$$', aes_decrypt(p_sync['password'], p_sync['user']))
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_COLUMN_NAMES$$',
get_mysql_columns(p_sync))
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_TABLE_NAME$$',
p_sync['sync_table'])
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_URL$$',
p_sync['mysql_url'])
v_templete['incr'] = v_templete['incr'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['incr'] = v_templete['incr'].replace('$$ZK_HOSTS',
p_sync['zk_hosts'])
v_templete['incr'] = v_templete['incr'].replace('$$HBASE_TABLE_NAME$$',
p_sync['sync_hbase_table'])
v_templete['incr'] = v_templete['incr'].replace(
'$$HBASE_ROWKEY$$', p_sync['sync_hbase_rowkey'])
v_templete['incr'] = v_templete['incr'].replace(
'$$HBASE_COLUMN_NAMES$$', p_sync['sync_hbase_columns'])
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_WHERE$$',
p_sync['sync_incr_where'])
print('process_templete->v_templete=', v_templete)
return v_templete
def process_templete_es(p_sync_id, p_templete):
v_templete = p_templete
p_sync = query_datax_by_id(p_sync_id)
print('process_templete_es->p_sync=', p_sync)
print('process_templete_es->p_templete=', p_templete)
#replace full templete
v_templete['full'] = v_templete['full'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['full'] = v_templete['full'].replace(
'$$PASSWORD$$', aes_decrypt(p_sync['password'], p_sync['user']))
v_templete['full'] = v_templete['full'].replace('$$MYSQL_COLUMN_NAMES$$',
get_mysql_columns(p_sync))
v_templete['full'] = v_templete['full'].replace('$$MYSQL_TABLE_NAME$$',
p_sync['sync_table'])
v_templete['full'] = v_templete['full'].replace('$$MYSQL_URL$$',
p_sync['mysql_url'])
v_templete['full'] = v_templete['full'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['full'] = v_templete['full'].replace('$$ES_SERVICE$$',
p_sync['es_service'])
v_templete['full'] = v_templete['full'].replace('$$ES_INDEX_NAME$$',
p_sync['es_index_name'])
v_templete['full'] = v_templete['full'].replace('$$ES_TYPE_NAME$$',
p_sync['es_type_name'])
v_templete['full'] = v_templete['full'].replace('$$ES_COLUMN_NAMES$$',
p_sync['sync_es_columns'])
#replacre incr templete
v_templete['incr'] = v_templete['incr'].replace('$$USERNAME$$',
p_sync['user'])
v_templete['incr'] = v_templete['incr'].replace(
'$$PASSWORD$$', aes_decrypt(p_sync['password'], p_sync['user']))
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_COLUMN_NAMES$$',
get_mysql_columns(p_sync))
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_TABLE_NAME$$',
p_sync['sync_table'])
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_URL$$',
p_sync['mysql_url'])
v_templete['incr'] = v_templete['incr'].replace('$$MYSQL_WHERE$$',
p_sync['sync_incr_where'])
v_templete['full'] = v_templete['full'].replace('$$ES_SERVICE$$',
p_sync['zk_hosts'])
v_templete['full'] = v_templete['full'].replace('$$ES_INDEX_NAME$$',
p_sync['sync_hbase_table'])
v_templete['full'] = v_templete['full'].replace(
'$$ES_TYPE_NAME$$', p_sync['sync_hbase_rowkey'])
v_templete['full'] = v_templete['full'].replace('$$ES_COLUMN_NAMES$$',
p_sync['sync_es_columns'])
print('process_templete_es->v_templete=', v_templete)
return v_templete
def get_ds_by_instid(p_inst_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id as dsid,
a.inst_name as db_desc,
CASE WHEN a.is_rds='Y' THEN
a.inst_ip
ELSE
(SELECT server_ip FROM t_server b WHERE b.id=a.server_id)
END AS ip,
CASE when a.inst_mapping_port is null or a.inst_mapping_port ='' then
a.inst_port
ELSE
a.inst_mapping_port
END as port,
a.inst_type as db_type,
a.inst_env as db_env,
a.mgr_user as user,
a.mgr_pass as password,
'' as service,
date_format(created_date,'%Y-%m-%d %H:%i:%s') as created_date
FROM t_db_inst a
WHERE a.id='{0}'""".format(p_inst_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
rs['password'] = aes_decrypt(rs['password'], rs['user'])
print("rs->password=", rs['password'])
cr.close()
db.commit()
return rs
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 get_server_by_serverid(p_serverid):
db = get_connection()
cr = db.cursor()
sql = """select cast(id as char) as id,market_id,server_type,
server_ip,server_port,server_user,server_pass,
server_os,server_cpu,server_mem,status,server_desc
from t_server where id={0}
""".format(p_serverid)
print('get_server_by_serverid=', sql)
cr.execute(sql)
rs = cr.fetchall()
d_server = {}
d_server['server_id'] = rs[0][0]
d_server['market_id'] = rs[0][1]
d_server['server_type'] = rs[0][2]
d_server['server_ip'] = rs[0][3]
d_server['server_port'] = rs[0][4]
d_server['server_user'] = rs[0][5]
d_server['server_pass'] = aes_decrypt(rs[0][6], rs[0][5])
d_server['server_os'] = rs[0][7]
d_server['server_cpu'] = rs[0][8]
d_server['server_mem'] = rs[0][9]
d_server['status'] = rs[0][10]
d_server['server_desc'] = rs[0][11]
cr.close()
db.commit()
print(d_server)
return d_server
def get_ds_by_dsid_by_cdb(p_dsid, p_cdb):
db = get_connection()
cr = db.cursor()
sql = """select cast(id as char) as id,db_type,db_desc,
ip,port,
CASE WHEN service=NULL OR service='' THEN
'{0}'
ELSE
service
END AS service,
user,password,status,creation_date,creator,last_update_date,updator ,
db_env,inst_type,market_id
from t_db_source where id={1}
""".format(p_cdb, p_dsid)
cr.execute(sql)
rs = cr.fetchall()
cr.close()
db.commit()
d_ds = {}
d_ds['dsid'] = rs[0][0]
d_ds['db_type'] = rs[0][1]
d_ds['db_desc'] = rs[0][2]
d_ds['ip'] = rs[0][3]
d_ds['port'] = rs[0][4]
d_ds['service'] = rs[0][5]
d_ds['user'] = rs[0][6]
d_ds['password'] = aes_decrypt(rs[0][7], rs[0][6])
d_ds['status'] = rs[0][8]
d_ds['db_env'] = rs[0][13]
d_ds['inst_type'] = rs[0][14]
d_ds['market_id'] = rs[0][15]
d_ds['url'] = 'MySQL://{0}:{1}/{2}'.format(d_ds['ip'], d_ds['port'],
d_ds['service'])
print(d_ds)
return d_ds
def query_inst_by_id(p_inst_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id,
a.inst_name,
(SELECT id FROM t_server b WHERE b.id=a.server_id) as server_id,
(SELECT server_desc FROM t_server b WHERE b.id=a.server_id) as server_desc,
CASE WHEN a.is_rds='Y' THEN
a.inst_ip
ELSE
(SELECT server_ip FROM t_server b WHERE b.id=a.server_id)
END AS inst_ip,
a.inst_port,
a.inst_type,
a.inst_env,
a.inst_ver,
a.templete_id,
a.is_rds,
a.mgr_user,
a.mgr_pass,
date_format(a.created_date,'%Y-%m-%d %H:%i:%s') as created_date,
a.api_server,
a.python3_home,
a.script_path,
a.script_file,
inst_mapping_port
FROM t_db_inst a WHERE a.id='{0}'""".format(p_inst_id)
print(sql)
cr.execute(sql)
rs = cr.fetchone()
print('password='******'mgr_pass'], rs['mgr_user']))
rs['mgr_pass'] = aes_decrypt(rs['mgr_pass'], rs['mgr_user'])
print("rs->password=", rs['mgr_pass'])
cr.close()
db.commit()
return rs
def get_user_by_loginame(p_login_name):
db = get_connection()
cr = db.cursor()
sql = """select cast(id as char) as id,
name,
login_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 login_name='{0}'
""".format(p_login_name)
cr.execute(sql)
rs = cr.fetchall()
cr.close()
db.commit()
d_user = {}
d_user['userid'] = str(rs[0][0])
d_user['username'] = rs[0][1]
d_user['loginname'] = rs[0][2]
d_user['password'] = aes_decrypt(rs[0][3], rs[0][2])
d_user['gender'] = rs[0][4]
d_user['gender_cn'] = get_dmmc_from_dm('04', rs[0][4])
d_user['email'] = rs[0][5]
d_user['phone'] = rs[0][6]
d_user['dept'] = rs[0][7]
d_user['dept_cn'] = get_dmmc_from_dm('01', rs[0][7])
d_user['expire_date'] = rs[0][8]
d_user['status'] = rs[0][9]
d_user['file_path'] = rs[0][10]
d_user['file_name'] = rs[0][11]
d_user['project_group'] = rs[0][12]
d_user['wkno'] = rs[0][13]
return d_user
def get_logon_user():
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 " \
"from t_user_logon limit 1"
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]
print("get_logon_user=", d_user)
return d_user
def query_user_by_id(p_user_id):
db = get_connection_dict()
cr = db.cursor()
sql = """SELECT a.id,
a.inst_id,
a.description,
a.db_user,
a.db_pass,
a.user_dbs,
a.user_privs,
a.statement,
a.status,
date_format(a.created_date,'%Y-%m-%d %H:%i:%s') as created_date
FROM t_db_user a WHERE a.id='{0}'""".format(p_user_id)
print(sql)
cr.execute(sql)
rs=cr.fetchone()
rs['db_pass'] = aes_decrypt(rs['db_pass'],rs['db_user'].replace("'", "''"))
rs['user_privs_zh'] = get_user_privs_zh(rs['user_privs'])
print("rs->password=",rs['db_pass'])
cr.close()
db.commit()
return rs