def mysqlTables_return(host, dbuser, dbpwd, dbport, dbname, tab_owner,
importance_table):
if len(importance_table) == 1:
importance_table.append('ASDFGHJKL')
sql = 'select table_name,column_name,column_type,COLUMN_DEFAULT,IS_NULLABLE from information_schema.columns where table_name in{};'.format(
tuple(importance_table))
#print sql
list_all = []
#print type(host),type(dbport),type(dbname),type(dbuser)
targetdb = mysql(host, dbport, dbname, dbuser, dbpwd)
with targetdb:
rows = targetdb.exec_sql(sql)
for i in rows:
a, b, c, d, e = i
if e == 'YES':
e = 'NOT NULL'
else:
e = 'ASDFGHJKL'
if str(d).strip() != 'None':
d = 'DEFAULT {}'.format(d.strip())
else:
d = 'ASDFGHJKL'
mysql_line = '{},{},{},{},{}'.format(a, b, c, d, e)
list_all.append(mysql_line)
return sorted(set(list_all))
def insertdb(md5, long_list):
mydb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
#sql = 'insert into result_scrapy(md5,long_list) values("{}","{}")'.format(md5,long_list)
sql = 'insert into result_scrapy(md5,long_list) values ("{}","{}") on duplicate key update long_list="{}"'.format(
md5, long_list, long_list)
with mydb:
rows = mydb.exec_sql(sql)
def ipInfo():
sql = 'select a.dbs,a.ipaddr,b.sysuser,b.syspassword,a.app_id,c.app_name,a.env,d.dbuser,d.dbpassword,d.port,d.dbname,d.tabowner,c.importance_table from hosts a join hosts_passwd b on a.dbs !=0 and a.host_status=1 and a.hostid=b.hostid and b.isinstance=1 join app c on a.app_id = c.app_id join database_passwd d on a.hostid=d.hostid ;'
#sql= 'select a.dbs,a.ipaddr,b.sysuser,b.syspassword,a.app_id,c.app_name,a.env,d.dbuser,d.dbpassword,d.port,d.dbname,c.importance_table from hosts a join hosts_passwd b on a.dbs !=0 and a.host_status=1 and a.hostid=b.hostid and b.isinstance=1 join app c on a.app_id = c.app_id join database_passwd d on a.hostid=d.hostid and a.ipaddr="203.3.238.3" and d.dbname="hrmsdb";'
apmdb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
with apmdb:
rows = apmdb.exec_sql(sql)
#print rows
for dbs, ipaddr, sysuser, syspassword, app_id, app_name, env, dbuser, dbpassword, db_port, db_dbname, tab_owner, importance_table in rows:
yield dbs, ipaddr.strip(), sysuser.strip(), syspassword.strip(
), app_id, app_name.strip(), env, dbuser.strip(), dbpassword.strip(
), int(db_port), db_dbname.strip(), str(
tab_owner), importance_table
def ipInfo_compare(compare_type):
#sql = 'select a.ip,a.dbuser,a.dbpwd,a.port,a.dbname,b.schema,b.compare_file,c.app_name from ods_ip a join ods_compare b on a.compare_id = b.compare_id join app c on b.app_id = c.app_id'
sql = 'select a.ip,a.dbuser,a.dbpwd,a.port,a.dbname,b.schema,c.app_name,d.ipaddr,d.dbs,f.dbuser,f.dbpassword,f.port,f.dbname,f.tabowner from compare_ip a join compare_config b on a.compare_id = b.compare_id join app c on b.app_id = c.app_id join hosts d on d.hostid = b.hostid join database_passwd f on b.hostid = f.hostid and b.compare_type = {}'.format(
int(compare_type))
apmdb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
with apmdb:
rows = apmdb.exec_sql(sql)
#print rows
for ods_ip, ods_dbuser, ods_pwd, ods_port, ods_dbname, ods_schema, app_name, target_ipaddr, target_dbs, target_dbuser, target_dbpassword, target_port, target_dbname, target_tabowner in rows:
yield ods_ip.strip(), ods_dbuser.strip(
), ods_pwd.strip(), ods_port, ods_dbname.strip(), ods_schema.strip(
), app_name, target_ipaddr, target_dbs, target_dbuser.strip(
), target_dbpassword.strip(), target_port, target_dbname.strip(
), str(target_tabowner)
def insert_apmdb(ipaddr, appname, dbname, create_date, sql_str, operation=0):
dbmd5 = md5(" ".join(ipaddr + appname + dbname + create_date + sql_str))
if operation == 0:
sql = 'insert into tablemodify(md5, ipaddr, appname, dbname, create_date, sql_text, note_warning) values ("{0}","{1}","{2}","{3}","{4}","{5}","{6}") on duplicate key update ipaddr="{1}" ,appname="{2}", dbname="{3}", create_date="{4}", sql_text="{5}", note_warning="{6}"'.format(
dbmd5, ipaddr, appname, dbname, create_date, sql_str,
note_warning2)
else:
sql = 'insert into tablemodify(md5, ipaddr, appname, dbname, create_date, sql_text, note_warning) values ("{0}","{1}","{2}","{3}","{4}","{5}","{6}") on duplicate key update ipaddr="{1}" ,appname="{2}", dbname="{3}", create_date="{4}", sql_text="{5}", note_warning="{6}"'.format(
dbmd5, ipaddr, appname, dbname, create_date, sql_str,
note_warning1)
apmdb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
with apmdb:
apmdb.exec_sql(sql)
def mysqlTables(path, host, dbuser, dbpwd, dbport, dbname, tab_owner,
importance_table):
#print path,host,dbuser,dbpwd,db_port,db_dbname,importance_table
tuple_table = fromatTuple(importance_table, 'lower')
dir_path = makeDir(path, 'mysql')
save_file = renameFile(host, dbname)
save_path_file = os.path.join(dir_path, save_file)
sql = 'select table_name,column_name,column_type,COLUMN_DEFAULT,IS_NULLABLE from information_schema.columns where table_name in{} ;'.format(
tuple_table)
#sql = 'select table_name,column_name,column_type,IS_NULLABLE,COLUMN_KEY from information_schema.columns where table_name in{};'.format(tuple_table)
#sql = 'select table_name,column_name,column_type from information_schema.columns where table_name in{};'.format(tuple_table)
targetdb = mysql(host, dbport, dbname, dbuser, dbpwd)
with targetdb:
rows = targetdb.exec_sql(sql)
log.info("saving file {}".format(save_file))
saveMysqlStr(rows, save_path_file)
def insert_apmdb(info_list):
apmdb = mysql(apm_host,apm_port,amp_database,apm_user,apm_passwd)
with apmdb:
for i in info_list:
sysmbol,ip,dbuser,tablename,tablecolumn,text,tabowner = i.split('|')[:]
#text_sysmbol = '{} {}'.format(sysmbol, text)
if sysmbol == '+':
sysmbol = '增加'
elif sysmbol == '-':
sysmbol = '减少'
else:
sysmbol = '更改'
if len(tabowner) == 0 :
rows = apmdb.exec_sql(apm_sql.format(ip,dbuser))
else:
rows = apmdb.exec_sql(apm_sql2.format(ip,dbuser,tabowner))
#print apm_sql.format(ip,dbuser)
env,dbs,app_name = rows[0]
dbmd5 = md5(" ".join(ip + dbuser + tablename + tablecolumn + str(dbs) + str(env) + create_time + text))
sql ='insert into tablecheck(ipaddr, appname, dbuser, dbtype, tablename, tablecolumn, create_time, context, env,dbmd5,sendstatus,operation,tabowner) values ("{0}","{1}","{2}",{3},"{4}","{5}","{6}","{7}",{8},"{9}",{10},"{11}","{12}") on duplicate key update ipaddr="{0}" ,appname="{1}", dbuser="******", dbtype={3}, tablename="{4}", tablecolumn="{5}", create_time="{6}", context="{7}", env={8} ,sendstatus={10}, operation="{11}",tabowner="{12}"'.format(ip,app_name, dbuser, dbs, tablename, tablecolumn, create_time, text, env,dbmd5, 0, sysmbol,tabowner)
apmdb.exec_sql(sql)
#!/usr/bin/python
import requests, json
from etc.setting_general import header, get_url, add_url, ship_url
from etc.setting_mysql import *
from lib.DBTool import mysql
#request_url=get_url.format(md5)
#respone = requests.request('get',request_url,headers=header)
#result = json.loads(respone.text)
#print result['context']['serviceNodeList']
mydb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
sql = 'select md5,long_list from result_scrapy'
with mydb:
rows = mydb.exec_sql(sql)
for i in rows:
md5 = i[0]
row = eval(i[1])
request_url = get_url.format(md5)
respone = requests.request('get', request_url, headers=header)
result = json.loads(respone.text)
myinfo = 'fbi'
if not result['context']['serviceNodeList']:
for j in row:
a, b = j.split('*')
if a != b:
#print a.split('_')[0],a.split('_')[1],b.split('_')[0],b.split('_')[0]
#a_className , a_methodName = a.split('_')
a_className = a.split('_')[0]
a_methodName = a.split('_')[1:][0]
b_className, b_methodName = b.split('_')
def goToPast(dbpath, file_path, old_date, new_date):
if os.path.exists(dbpath):
con = sqlite3.connect(dbpath)
cur = con.cursor()
cur.execute(sql_drop)
cur.execute(sql_create)
cur.execute(sqlcreate_index)
con.commit()
cur.close()
con.close()
log.info("recreate bibi")
else:
con = sqlite3.connect(dbpath)
cur = con.cursor()
cur.execute(sql_create)
cur.execute(sqlcreate_index)
con.commit()
cur.close()
con.close()
log.info("create bibi")
con = sqlite3.connect(dbpath)
cur = con.cursor()
sql_apm = "select ipaddr,appname,dbuser,dbtype,tablename,tablecolumn,context,operation,tabowner from tablecheck where create_time='{}'"
apmdb = mysql(apm_host, apm_port, amp_database, apm_user, apm_passwd)
format_date = formatDate(new_date)
#format_date = '2020-10-14'
#print sql_apm.format(format_date)
with apmdb:
#print sql_apm.format(format_date)
rows = apmdb.exec_sql(sql_apm.format(format_date))
for ip, appname, dbname, dbtype, tablename, tablecolumn, context, operation, tabowner in rows:
cur.execute(
sql_insert.format(ip, appname, dbname, dbtype, tablename,
tablecolumn, context, operation, tabowner))
con.commit()
log.info("get apmdb tablecheck {} {} {}".format(
ip, dbname, format_date))
sql_update = "update bibi set flag=1 where ip='{}' and tablename='{}' and tablecolumn='{}' and dbname='{}'"
sql_update_all = "update bibi set flag=1 where ip='{}' and tablename='{}' and dbname='{}'"
pattern_all = "select tablecolumn,text from bibi where ip='{}' and tablename='{}' and dbname='{}'"
while True:
cur.execute(select_all)
sql_target = cur.fetchone()
if sql_target is not None:
id, ip, appname, dbname, dbtype, tablename, tablecolumn, context, operation, flag, owner = sql_target
#print type(owner)
if len(owner) == 0:
filename = dbname
else:
#print owner
filename = '{}-{}'.format(dbname, owner)
#context= context.strip('\n')
#print filename
if operation == u"更改":
log.info('modify.................')
if dbtype == 1:
log.info('modify.................db2')
db2_str = context.split('-->')[1]
db2_str = db2_str.split()
if len(db2_str) == 1:
db2_set_all = sql_alter_db2.format(
tablename, tablecolumn, db2_str[0])
elif len(db2_str) == 5:
db2_set_column = sql_alter_db2.format(
tablename, tablecolumn, db2_str[0])
db2_set_default_1 = db2_str[1]
db2_set_default_2 = db2_str[2]
db2_set_default_all = '{} {}'.format(
db2_set_default_1, db2_set_default_2)
db2_set_default = sql_alter_db2_simple.format(
tablename, tablecolumn, db2_set_default_all)
db2_set_null_1 = db2_str[3]
db2_set_null_2 = db2_str[4]
db2_set_null_all = '{} {}'.format(
db2_set_null_1, db2_set_null_2)
db2_set_null = sql_alter_db2_simple.format(
tablename, tablecolumn, db2_set_null_all)
db2_set_all = '{};{};{}'.format(
db2_set_column, db2_set_default, db2_set_null)
#print db2_set_all
else:
db2_set_column = sql_alter_db2.format(
tablename, tablecolumn, db2_str[0])
db2_set_unknow_1 = db2_str[1]
db2_set_unknow_2 = db2_str[2]
db2_set_unknow_all = '{} {}'.format(
db2_set_unknow_1, db2_set_unknow_2)
db2_set_unknow = sql_alter_db2_simple.format(
tablename, tablecolumn, db2_set_unknow_all)
db2_set_all = '{};{}'.format(db2_set_column,
db2_set_unknow)
#sql_str = sql_alter_db2.format(tablename,tablecolumn,db2_set_all)
insert_apmdb(ip, appname, dbname, format_date, db2_set_all)
cur.execute(
sql_update.format(ip, tablename, tablecolumn, dbname))
con.commit()
else:
log.info('modify.................other')
sql_str = sql_alter.format(tablename, tablecolumn,
context.split('-->')[1])
insert_apmdb(ip, appname, dbname, format_date, sql_str)
cur.execute(
sql_update.format(ip, tablename, tablecolumn, dbname))
con.commit()
elif operation == u"增加":
log.info('add.................')
file_name = renameFile(ip, filename)
#print file_name
path_type = db_type.get(dbtype)
past_day_file = os.path.join(file_path, old_date, path_type)
past_file = os.path.join(past_day_file, file_name)
list_table_name = []
with open(past_file, 'r') as f:
all_table = f.readlines()
for table_line in all_table:
table_name = table_line.split(',')[0]
list_table_name.append(table_name)
list_table_name = set(list_table_name)
#增加，主要判断是单独增加字段，还是增加了整张表
if tablename in list_table_name:
log.info('add.................column')
sql_str = sql_add.format(tablename, tablecolumn, context)
insert_apmdb(ip, appname, dbname, format_date, sql_str)
cur.execute(
sql_update.format(ip, tablename, tablecolumn, dbname))
con.commit()
else:
log.info('create.................table')
cur.execute(pattern_all.format(ip, tablename, dbname))
all_target = cur.fetchall()
column_list = []
for line in all_target:
create_column, create_text = line
#create_text.strip('\n')
a = '{} {}'.format(create_column, create_text)
column_list.append(a)
column_str = ','.join(column_list)
sql_str = sql_recreate.format(tablename, column_str)
insert_apmdb(ip, appname, dbname, format_date, sql_str, 1)
cur.execute(sql_update_all.format(ip, tablename, dbname))
con.commit()
else:
file_name = renameFile(ip, filename)
path_type = db_type.get(dbtype)
now_day_file = os.path.join(file_path, new_date, path_type)
now_file = os.path.join(now_day_file, file_name)
list_table_name = []
with open(now_file, 'r') as f:
all_table = f.readlines()
for table_line in all_table:
table_name = table_line.split(',')[0]
list_table_name.append(table_name)
list_table_name = set(list_table_name)
#减少，主要判断是减少了字段，还是删除了整张表
if tablename in list_table_name:
log.info('del.................column')
sql_str = sql_del.format(tablename, tablecolumn, context)
insert_apmdb(ip, appname, dbname, format_date, sql_str)
cur.execute(
sql_update.format(ip, tablename, tablecolumn, dbname))
con.commit()
else:
#cur.execute(pattern_all.format(ip,tablename,dbname))
#all_target = cur.fetchall()
#column_list = []
#for line in all_target:
# create_column,create_text = line
# #print create_column,create_text
# a = '{} {}'.format(create_column,create_text)
# column_list.append(a)
#column_str = ','.join(column_list)
log.info('drop.................table')
sql_str = sql_redrop.format(tablename)
insert_apmdb(ip, appname, dbname, format_date, sql_str)
cur.execute(sql_update_all.format(ip, tablename, dbname))
con.commit()
cur.execute(
sql_update.format(ip, tablename, tablecolumn, dbname))
con.commit()
else:
break
cur.close()
con.close()
