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 ""