def reviewPreExecute(request): '''内容预审核''' try: databaseid = int(request.POST.get('id',None)) content = request.POST.get('content',None) sql_id = request.POST.get('sqlid',None) sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format(databaseid) cursor = connections['default'].cursor() cursor.execute(sql_database_info) row = cursor.fetchone() logging.debug(sql_database_info) audit_sql = preAuditExecute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=content) if request.method == "POST" and request.user.is_staff == 1: result = inceptionQuery(audit_sql) flag = [] for row in result: flag.append(row[2]) rep_str = row[5] strs = rep_str.replace("'","\\'") strss = strs.replace('"', "\\'") sql = '''REPLACE into tb_review_detail(tid,stage,errlevel,stagestatus,errormessage,`sql`,Affected_rows,sequence,backup_dbname,execute_time,sqlsha1,sql_id) values({0},"{1}",{2},"{3}","{4}",'{5}',{6},"{7}","{8}","{9}","{10}",{11}) ;''' .format(row[0],row[1],row[2],row[3],row[4],strss,row[6],row[7],row[8],row[9],row[10],sql_id) print sql cursor = connections['default'].cursor() cursor.execute(sql) cursor.close() return write(1) else: return write(0) except Exception,e: logging.error(e)
def sqlReportActive(request): try: if request.method == "POST" and request.user.is_staff == 0: # 1、获取上报内容 select = request.POST.getlist('select[]',None) content = request.POST.get('text',None) bak = request.POST.get('text2',None) userid = request.user.id leng = len(select) title = '''SQL审核通知!''' message = '''用户{0}已提交新的SQL,请尽快审核!'''.format(request.user.username) if leng > 1: for i in range(len(select)): review_report = tb_review(database_id=select[i], content=content,remark_user=bak, creator=userid, flag=0, review_id=0) review_report.save(using='default') #em = sendEmail(title=title, message=message, from_email=from_email,to_email=to_email) #em.send() return write(1) elif leng == 1: review_report = tb_review(database_id=select[0],content=content,remark_user=bak,creator=userid,flag=0,review_id=0) review_report.save(using='default') #em = sendEmail(title=title, message=message, from_email=from_email, to_email=to_email) #em.send() return write(1) else: return write(0) except Exception, e: logging.error(e)
def update_sql(request): try: id = request.POST.get('id',None) sql = request.POST.get('sql',None) SqlAuditExecute.updateSql(sql_id=id,cont=sql) return write(1) except Exception as e: print (e) return write(0)
def reviewTempActive(request): try: if request.method == "POST" and request.user.is_staff == 1: # 1、获取审核内容 select = request.POST.getlist('select',None) content = request.POST.get('text',None) userid = request.user.id #2、获取审核目标数据库信息 sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format( select[0]) cursor = connections['default'].cursor() cursor.execute(sql_database_info) row = cursor.fetchone() cursor.close() #3、执行审核 target_sql = '''/*--user={0};--password={1};--host={2};--execute=1;--enable-ignore-warnings;--port={3};*/'''.format( row[1], row[2], row[0], row[3]) db_sql = '''use {0};'''.format(row[4]) ct = content.replace('"', '\'') ddl_dml_sql = '''{0}'''.format(ct) main_sql = '''{0}inception_magic_start;{1}{2} inception_magic_commit;'''.format(target_sql, db_sql, ddl_dml_sql) conn = MySQLdb.connect(host='172.16.16.20', user='******', passwd='', db='', port=6669) cur = conn.cursor() cur.execute(main_sql.encode('utf-8')) results = cur.fetchall() cur.close() # print results # column_name_max_size = max(len(i[0]) for i in cur.description) # row_num = 0 # # stagestatus = '' # res = '' # for result in results: # row_num = row_num + 1 # print '*'.ljust(27, '*'), row_num, '.row', '*'.ljust(27, '*') # rows = map(lambda x, y: (x, y), (i[0] for i in cur.description), result) # for each_column in rows: # if each_column[0] != 'errormessage': # q = each_column[0].rjust(column_name_max_size),":",each_column[1] # # else: # q = res+each_column[0].rjust(column_name_max_size), ':', each_column[1].replace('\n', '\n'.ljust( # column_name_max_size + 4)) cur.close() conn.close() return write(results) else: return render(request, 'review/404.html', context=None) except Exception, e: logging.error(e) req = 0 return write(req)
def reviewExecute(request): '''审核内容执行''' try: databaseid = int(request.POST.get('id',None)) content = request.POST.get('content',None) creatorid = request.POST.get('creator', None) sql_id = request.POST.get('sqlid',None) sql_database_info = '''select host,user,passwd,port,db_name,creator from tb_databases_config where id={0}'''.format(databaseid) cursor = connections['default'].cursor() cursor.execute(sql_database_info) row = cursor.fetchone() logging.debug(sql_database_info) audit_sql = Execute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=content) if request.method == "POST" and request.user.is_staff == 1: result = inceptionQuery(audit_sql) flag = [] for row in result: flag.append(row[2]) rep_str = row[5] strs = rep_str.replace("'", "\\'") strss = strs.replace('"', "\\'") sql = '''REPLACE into tb_review_detail(tid,stage,errlevel,stagestatus,errormessage,`sql`,Affected_rows,sequence,backup_dbname,execute_time,sqlsha1,sql_id) values({0},"{1}",{2},"{3}","{4}","{5}",{6},"{7}","{8}","{9}","{10}",{11}) ;''' .format(row[0],row[1],row[2],row[3],row[4],strss,row[6],row[7],row[8],row[9],row[10],sql_id) cursor = connections['default'].cursor() cursor.execute(sql) cursor.close() title = '''SQL审核通知!''' falis_message = '''sqlid为{0}的审核未通过!''' .format(sql_id) suss_message = '''sqlid为{0}的已审核通过!''' .format(sql_id) sql_to_eamil = '''select username from auth_user where id= (select creator from tb_review where id={0})''' .format(sql_id) cursor = connections['default'].cursor() cursor.execute(sql_to_eamil) re_to_uname = cursor.fetchone() to_email = '*****@*****.**' % re_to_uname if 2 in flag: sql_flag ='''update tb_review set flag=1,review_id={0},review_time=now() where id={1}''' .format(request.user.id,sql_id) cursor = connections['default'].cursor() cursor.execute(sql_flag) em = sendEmail(title=title, message=falis_message, from_email=from_email, to_email=to_email) em.send() return write(0) else: sql_flag_02 = '''update tb_review set flag=2,review_id={0},review_time=now() where id={1}'''.format(request.user.id,sql_id) cursor = connections['default'].cursor() cursor.execute(sql_flag_02) em = sendEmail(title=title, message=suss_message, from_email=from_email, to_email=to_email) em.send() return write(1) else: return write(0) except Exception,e: logging.error(e)
def reviewPostHistory(request): '''所有审核内容上报列表''' try: if request.method == "GET" and request.user.is_staff == 1: start = 0 length = 0 sumcnt = 0 draw = request.GET.get('draw',None) start = request.GET.get('start',None) length = request.GET.get('length',None) sql_sum ='''select count(*) from tb_review where create_time <date_format(now(),'%Y-%m-%d 00:00:00')''' cursor = connections['default'].cursor() cursor.execute(sql_sum) sum_row = cursor.fetchone() sql_arry = '''SELECT t.id,t.name,t.db_name,t.content,t.flag,t.review_time, uu.username,t.remark_user FROM (SELECT u.username as name, d.db_name, r.id,r.content, r.flag, r.remarks, r.review_time, r.review_id,r.remark_user FROM auto_database.tb_review r, tb_databases_config d, auth_user u WHERE r.database_id = d.id AND r.creator = u.id and r.create_time <date_format(now(),'%Y-%m-%d 00:00:00')) AS t LEFT JOIN auth_user uu ON t.review_id = uu.id order by t.id desc limit {0},{1}''' .format(start,length) cursor = connections['default'].cursor() cursor.execute(sql_arry) all_row = cursor.fetchall() dict = collections.OrderedDict() dict['draw'] = draw dict['recordsTotal'] = sum_row[0] dict['recordsFiltered'] = sum_row[0] dict['data'] = all_row json_object = json.dumps(dict,cls=DateEncoder) return write(json_object) else: return render(request, 'review/404.html', context=None) except Exception,e: logging.error(e) return render(request, 'review/500.html',context=None)
def reviewPost(request): '''所有审核内容上报列表''' try: if request.method == "GET" and request.user.is_staff == 1: start = 0 length = 0 sumcnt = 0 draw = request.GET.get('draw',None) start = request.GET.get('start',None) length = request.GET.get('length',None) sql_sum ='''select count(*) from tb_review where create_time >date_format(now(),'%Y-%m-%d 00:00:00')''' # print sql_sum cursor = connections['default'].cursor() cursor.execute(sql_sum) sum_row = cursor.fetchone() sql_arry = '''select t.id,d.db_name,t.database_id,t.content,t.flag,t.create_time,t.username,t.remarks,t.remark_user from (SELECT r.id as id,database_id,content,create_time ,u.username,r.flag,r.remarks,r.remark_user FROM tb_review as r left join auth_user as u ON r.creator=u.id where r.create_time>date_format(now(),'%Y-%m-%d 00:00:00') ) as t left join tb_databases_config d on t.database_id=d.id order by t.id desc limit {0},{1};''' .format(start,length) cursor = connections['default'].cursor() cursor.execute(sql_arry) all_row = cursor.fetchall() dict = collections.OrderedDict() dict['draw'] = draw dict['recordsTotal'] = sum_row[0] dict['recordsFiltered'] = sum_row[0] dict['data'] = all_row json_object = json.dumps(dict,cls=DateEncoder) return write(json_object) else: return render(request, 'review/404.html', context=None) except Exception,e: logging.error(e) return render(request, 'review/500.html',context=None)
def index_char_pie_ddl(request): try: if request.user.is_staff == 1 and request.method == "POST": sql_stat = '''select SUM(altertable) AS altab, SUM(renaming) AS renm, SUM(createindex) AS cidx, SUM(dropindex) AS didx, SUM(addcolumn) AS addc, SUM(changecolumn) AS chgc, SUM(createtable) AS ctab, SUM(truncating) AS tcat from statistic;''' cursor = connections['data_backup'].cursor() cursor.execute(sql_stat) sum_stat = cursor.fetchone() print sum_stat dict = collections.OrderedDict() dict['stat'] = sum_stat json_object = json.dumps(dict, cls=DecimalEncoder) return write(json_object) else: return render(request, 'review/404.html', context=None) except Exception, e: logging.info(e)
def userPofileUpdate(request): try: print request.method if request.method == 'POST': password = request.POST.get('password', None) email = request.POST.get('email', None) confirm = request.POST.get('confirm', None) print "%s,%s,%s" % (confirm, email, password) if confirm == password: print '1' hash_passwd = make_password(confirm) print hash_passwd id = request.user.id print id sql = '''update auth_user set email="{0}",password="******" where id={2}'''.format( email, hash_passwd, id) print sql cursor = connections['default'].cursor() cursor.execute(sql) sql_sum = '''select count(*) from tb_review where flag=0 and create_time >date_format(now(),'%Y-%m-%d 00:00:00')''' cursor = connections['default'].cursor() cursor.execute(sql_sum) sum_row = cursor.fetchone() sql_database = '''select id,db_tag from tb_databases_config''' cursor = connections['default'].cursor() cursor.execute(sql_database) data_row = cursor.fetchall() name = request.user.username dict = {} dict['flag'] = 1 dict['user'] = name dict['taskCount'] = sum_row dict['database'] = data_row return render(request, 'review/user_profile_update.html', context=dict) else: sql_sum = '''select count(*) from tb_review where flag=0 and create_time >date_format(now(),'%Y-%m-%d 00:00:00')''' cursor = connections['default'].cursor() cursor.execute(sql_sum) sum_row = cursor.fetchone() sql_database = '''select id,db_tag from tb_databases_config''' cursor = connections['default'].cursor() cursor.execute(sql_database) data_row = cursor.fetchall() name = request.user.username dict = {} dict['flag'] = 0 dict['user'] = name dict['taskCount'] = sum_row dict['database'] = data_row return render(request, 'review/user_profile_update.html', context=dict) else: return render(request, 'review/500.html', context=None) except Exception, e: logging.info(e) return write(0)
def get_queryset(self): try: sid = self.request.GET.get('sqlid', None) sql_result = SqlAuditExecute.executeResultList(sql_id=sid) return sql_result except Exception as e: return write(e)
def index_char_line(request): try: if request.user.is_staff == 1 and request.method == "POST": sql_stat = '''SELECT * FROM (SELECT amount, DATE_FORMAT(data_date, '%Y-%m-%d') dt FROM auto_database.tb_review_stat ORDER BY create_date DESC LIMIT 7) t ORDER BY t.dt ASC''' print sql_stat cursor = connections['default'].cursor() cursor.execute(sql_stat) sum_stat = cursor.fetchall() dict = collections.OrderedDict() dict['stat'] = sum_stat json_object = json.dumps(dict, cls=DecimalEncoder) return write(json_object) else: return render(request, 'review/404.html', context=None) except Exception, e: logging.info(e)
def sql_rollback(request): try: if request.method == 'POST': x_id = request.POST.get('xlh', None) db_bak = request.POST.get('db_bak', None) sql_id = request.POST.get('sql_id', None) sql_1 = '''select tablename from {0}.$_$Inception_backup_information$_$ where opid_time={1};'''.format( db_bak, x_id) cursor = connections['data_backup'].cursor() cursor.execute(sql_1) row_1 = cursor.fetchone() sql_2 = '''select rollback_statement from {0}.{1} where opid_time={2};'''.format(db_bak, row_1[0], x_id) cursor = connections['data_backup'].cursor() cursor.execute(sql_2) row_2 = cursor.fetchall() SqlAuditExecute().rollback(sqlid=sql_id, content=row_2[0]) return write(1) else: return write(0) except Exception as e: print (e) return write(0)
def sql_pre_audit(request): try: dbid = request.POST.get('dbid',None) ct = request.POST.get('content',None) sql_id = request.POST.get('sqlid',None) row = SqlAuditExecute.get_db_info(db_id=dbid) audit_sql = Inception().preAuditExecute(user=row[1],password=row[2],host=row[0],port=row[3],dbname=row[4],content=ct) if request.method == "POST": result = Inception().inceptionQuery(audit_sql) flag = [] for row in result: flag.append(row[2]) rep_str = row[5] strs = rep_str.replace("'","\\'") strss = strs.replace('"', "\\'") Inception().insert_audit_res(row=row,strss=strss,sql_id=sql_id ) Inception().update_audit_flag(sql_id=sql_id) return write(1) else: return write(0) except Exception as e: return write(2)
def reviewBak(request): try: if request.method == 'POST' and request.user.is_staff == 1: sql_id = request.POST.get('id',None) bak = request.POST.get('bak',None) res = tb_review.objects.get(id=sql_id) res.remarks=bak res.save() return write(1) else: return render(request, 'review/500.html', context=None) except Exception,e: logging.info(e) return render(request, 'review/404.html', context=None)
def reviewRollBack(request): try: if request.method == 'POST': x_id = request.POST.get('xlh', None) db_bak = request.POST.get('db_bak', None) sql_id = request.POST.get('sql_id', None) sql_1 = '''select tablename from {0}.$_$Inception_backup_information$_$ where opid_time={1};'''.format( db_bak, x_id) cursor = connections['data_backup'].cursor() cursor.execute(sql_1) row_1 = cursor.fetchone() sql_2 = '''select rollback_statement from {0}.{1} where opid_time={2};'''.format( db_bak, row_1[0], x_id) cursor = connections['data_backup'].cursor() cursor.execute(sql_2) row_2 = cursor.fetchall() rollback(sqlid=sql_id, content=row_2[0]) return write(1) else: return render(request, 'review/500.html', context=None) except Exception, e: logging.info(e) return write(0)
def index_char_pie(request): try: user_type = getUserType(request) if user_type == 1 and request.method == "POST": sql_stat = '''select sum(deleting) del,sum(inserting) as ins,sum(updating) as upd from statistic''' cursor = connections['data_backup'].cursor() cursor.execute(sql_stat) sum_stat = cursor.fetchone() dict = collections.OrderedDict() dict['stat'] = sum_stat json_object = json.dumps(dict, cls=DecimalEncoder) return write(json_object) else: return render(request, 'review/404.html', context=None) except Exception,e: logging.info(e)
def sqlUpdateActive(request): '''审核内容更新''' try: print request.method if request.method == "POST" and request.user.is_staff == 0: sid = request.POST.get('sid', None) content = request.POST.get('text', None) bak = request.POST.get('bak', None) ct = tb_review.objects.get(id=sid) ct.content = content ct.remark_user = bak ct.flag = 0 ct.save() return write(1) else: return render(request, 'review/404.html', context=None) except Exception, e: logging.error(e)
def get_sql_history_list(request): try: sql = SqlAuditExecute() return write(sql.sql_alter_history_list(request=request)) except Exception as e: return write(e)
def get_sql_list(self, request): try: return write(SqlAuditExecute.sql_alter_list(request=request)) except Exception as e: return write(e)