def review(request, record_id): s_sql = """select a.submit_sql,b.dbname,c.host,c.port from t_sql_review_record a,t_db_basic_info b, t_db_conn_info c where a.db_id=b.id and a.conn_id=c.id and a.id = {0}""" s = SQL() rows = s.execute_and_fetchall(s_sql.format(record_id)) if rows: submit_sql = rows[0][0] dbname = rows[0][1] instance_ip = rows[0][2] instance_port = rows[0][3] else: return HttpResponse('Error ...', status=500) exec_sql = Inception.get_enable_check_sql(submit_sql, instance_ip, instance_port, dbname) try: with Inception.get_inception_conn as cur: ret = cur.execute(exec_sql) num_fields = len(cur.description) field_names = [i[0] for i in cur.description] result = cur.fetchall() # 判断结果中是否有error level 为 2 的,如果有,则不做操作,如果没有则将sql_review_record 记录的 is_checked 设为1 flag = 'success' for res in result: if res[2] == 2: flag = 'failed' if flag == 'success': with get_mysql_conn() as conn: with conn as cur: cur.execute( "update t_sql_review_record set is_checked=1 where id = {}" .format(record_id)) #第一行为use database,丢弃 if len(result) > 1: result = result[1:] data = { 'field_names': field_names, 'result': result, 'sub_module': '2_1', 'flag': flag, 'record_id': record_id, 'sql': submit_sql } return render(request, 'sql_review/result.html', data) except: return HttpResponse('Mysql Error ...', status=500)
def sql_review_before_execute(request, record_id): s = SQL() submit_sql, dbname, instance_ip, instance_port = s.get_db_info_from_record_id( record_id) exec_sql = "/*--user=%s;--password=%s; --enable-check;--host=%s;--disable-remote-backup;--port=%s;*/\ inception_magic_start;\ use %s;" % ('root', 'test123', instance_ip, int(instance_port), dbname) if str(submit_sql).strip()[-1:] != ";": exec_sql = exec_sql + str(submit_sql).strip() + ";" else: exec_sql = exec_sql + str(submit_sql).strip() exec_sql = exec_sql + "inception_magic_commit;" try: conn = pymysql.connect(host=INCEPTION_IP, user='', passwd='', db='', port=INCEPTION_PORT, charset='utf8') cur = conn.cursor() ret = cur.execute(exec_sql) num_fields = len(cur.description) field_names = [i[0] for i in cur.description] result = cur.fetchall() cur.close() conn.close() # 第一行为use database,丢弃 if len(result) > 1: result = result[1:] data = { 'field_names': field_names, 'result': result, 'sub_module': '2_1', 'record_id': record_id, 'sql': submit_sql } return render(request, 'sql_review/review_before_execute_result.html', data) except pymysql.Error as e: return HttpResponse('Mysql Error {}: {}'.format(e.args[0], e.args[1]), status=500)
def getTableNameList(request): clusterName = request.POST.get('cluster_name') db_name = request.POST.get('db_name', 'xxxxxx') is_master = request.POST.get('is_master') result = {'status': 0, 'msg': 'ok', 'data': []} sql_table = "select table_name from information_schema.tables where table_schema='{0}'".format(db_name) slave_info = s.execute_and_fetchall("select host,port from sqltools_db_conninfo where type='R' and dbname='{0}'".format(db_name)) if slave_info: slave_host = slave_info[0][0] slave_port = slave_info[0][1] ss = SQL(slave_host, slave_port) with get_mysql_conn(slave_host, slave_port) as conn: with conn as cur: cur.execute(sql_table) tables = cur.fetchall() listTb = [row[0] for row in tables] result['data'] = listTb else: result['data'] = ['无法找到对应表'] return HttpResponse(json.dumps(result), content_type='application/json')
def sql_execute(request, record_id, ignore_flag): s = SQL() submit_sql, dbname, instance_ip, instance_port = s.get_db_info_from_record_id( record_id) if ignore_flag == 'ignore': exec_sql = "/*--user=%s;--password=%s; --enable-execute;--host=%s;--enable-remote-backup;--enable-ignore-warnings;--port=%s;*/\ inception_magic_start;\ use %s;" % ('root', 'test123', instance_ip, int(instance_port), dbname) else: exec_sql = "/*--user=%s;--password=%s; --enable-execute;--host=%s;--enable-remote-backup;--port=%s;*/\ inception_magic_start;\ use %s;" % ('root', 'test123', instance_ip, int(instance_port), dbname) if str(submit_sql).strip()[-1:] != ";": exec_sql = exec_sql + str(submit_sql).strip() + ";" else: exec_sql = exec_sql + str(submit_sql).strip() exec_sql = exec_sql + "inception_magic_commit;" try: conn = pymysql.connect(host=INCEPTION_IP, user='', passwd='', db='', port=INCEPTION_PORT, charset='utf8') cur = conn.cursor() ret = cur.execute(exec_sql) field_names = [i[0] for i in cur.description] result = cur.fetchall() cur.close() conn.close() # 判断结果中是否有执行成功的状态,如果有则将备份信息存入表中,等待给以后做回滚 for res in result: if res[1] == 'EXECUTED' and (res[2] == 0 or res[2] == 1): sql_backup_instance = SqlBackupRecord() sql_backup_instance.review_record_id = record_id sql_backup_instance.backup_db_name = res[8] sql_backup_instance.sequence = res[7] sql_backup_instance.sql_sha1 = res[10] sql_backup_instance.save() # 判断结果中是否有error level 为 2 的,如果有,则不做操作,如果没有则将 sql_review_record 记录的 is_executed 设为1 # 判断结果中是否有error level 为 1 的,如果有,并且忽略标记不为'ignore',则不做操作,如果有,且忽略标记为'ignore',则操作和上述一样 flag = 'success' for res in result: if res[2] == 2 or (ignore_flag != 'ignore' and res[2] == 1): flag = 'failed' if flag == 'success': is_executed = 1 s.execute_and_return_status( "update t_sql_review_record set is_executed=1 where id = {0}". format(record_id)) # 第一行为use database,丢弃 if len(result) > 1: result = result[1:] data = { 'field_names': field_names, 'result': result, 'sub_module': '2_4', 'record_id': record_id, 'sql': submit_sql, 'flag': flag } return render(request, 'sql_review/execute_result.html', data) except pymysql.Error as e: return HttpResponse('Mysql Error {}: {}'.format(e.args[0], e.args[1]), status=500)
from mysql_platform.settings import INCEPTION_IP, INCEPTION_PORT, BACKUP_HOST_IP, BACKUP_HOST_PORT, BACKUP_PASSWORD from mysql_platform.settings import BACKUP_USER from statistics.models import MysqlInstance, MysqlInstanceGroup from sql_review.models import SqlReviewRecord, SqlBackupRecord, SpecificationContentForSql, SpecificationTypeForSql from sql_review.forms import SqlReviewRecordForm from users.models import UserProfile, MessageRecord from pure_pagination import Paginator, EmptyPage, PageNotAnInteger from utils.log import my_logger from mysql_platform.mysql_function import SQL from .Inceptions import Inception from users.endecrypt import endeCrypt s = SQL() # 或者读写用户 en = endeCrypt() review_user, review_password = en.get_ro_user_pass() admin_user, admin_password = en.get_rw_user_pass() # 连接mysql配置 conn_args = dict(host='127.0.0.1', user='******', password='******', db='platform', port=3306, charset="utf8mb4") def dictFetchall(cursor):
import json from django.shortcuts import render, redirect, reverse from django.http.response import HttpResponse from django.contrib.auth import authenticate, login, logout from django.contrib.auth.backends import ModelBackend from django.contrib.auth.hashers import make_password from django.db.models import Q from django.views import View from django.contrib.auth.decorators import login_required from django.core import serializers from pure_pagination import Paginator, EmptyPage, PageNotAnInteger from mysql_platform.mysql_function import SQL s = SQL('10.10.50.145', 3306, 'mysqltool_rw', 'f60sq8f5wz', 'yhops_db') t_depandency_api = "http://dependency-t1.yonghuivip.com/api/database/customMigrate" u_depandency_api = "http://flyway-test.yonghuivip.com/dependency-center-rest-api/database/customMigrate" flyway_user = "******" flyway_pass = "******" def dictFetchall(cursor): desc = cursor.description return [ dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall() ] @login_required()
def get_rows_from_real(self, sql): result = SQL.execute_and_fetchall(sql) if result: return result[0][0] else: return ""