转载自品略图书馆 http://www.pinlue.com/article/2019/10/1814/199717600110.html
背景
接下来的版本各个系统的sql脚本都会放在系统对应的git目录下,开发不再重复提交sql至数据库审核平台。也就是说各套环境的脚本需要运维自己审核后执行,暂时不通过原先审核平台半自动化的方式处理了。
因为时间较短,修改整个审核平台的工作量非常,而且需要更全面的知识储备,所以临时先采用python脚本的方式去实现审核和执行。
inception部署
可参照网上文档进行操作,没有难度。
调用inception时报错
如果使用默认的配置,可能会遇到以下报错:
错误一:
invalid literal for int() with base 10: "Inception2"
这个错误主要是因为connections.py在连接到Inception服务参数解析类型转换的时候有问题,修改下connections.py文件
#781行def _request_authentication(self):# https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse# if int(self.server_version.split(".", 1)[0]) >= 5:# self.client_flag |= CLIENT.MULTI_RESULTSif self.server_version.split(".", 1)[0] >= "Inception2": self.client_flag |= CLIENT.MULTI_RESULTSelif int(self.server_version.split(".", 1)[0]) >= 5: self.client_flag |= CLIENT.MULTI_RESULTS
错误二:
python返回:Must start as begin statement
pymysql模块会自动向inception发送SHOW WARNINGS语句,而恰好这个warnings被inception捕捉到了,从而导致inception返回一个"Must start as begin statement"错误。
这个问题只能去cursors.py文件件里注释掉self._show_warnings()这行代码并替换成pass,但是这个方法会导致所有调用该pymysql模块的程序不能show warnings。
#352行if not self._defer_warnings: #self._show_warnings() pass
编写调用脚本
import pymysqlimport sys, osimport timeoperation = "--enable-check"# operation = "--enable-execute;--enable-ignore-warnings;--enable-force"connstr_target = {"host": "XXX", "port": 3306, "user": "root", "password": "XXX", "db": "inception_test", "charset": "utf8"}connstr_inception = {"host": "XXX", "port": 6669, "user": "root", "password": "", "db": "", "charset": "utf8"}prefix_format = "/*--user={};--password={};--host={};{};--port={};*/ ".format(connstr_target["user"], connstr_target["password"], connstr_target["host"], operation, connstr_target["port"]) \ + "\n" \ + "inception_magic_start;"suffix_format = "inception_magic_commit;"sql_files = []files = os.listdir(os.path.dirname(os.path.abspath(__file__)))for file in files: if os.path.splitext(file)[1] == ".sql": sql_files.append(file)for file in sql_files: fd = open(file, "r", encoding="utf-8") sqlFile = fd.read() fd.close() sqlFile1=sqlFile.replace("commit;","") sql_demo1 = sqlFile1 #if sqlCommands[-1] == "": #sqlCommands.pop(-1) try: sql_demo1_with_format = prefix_format + "\n" + sql_demo1 + "\n" + suffix_format conn_inception = pymysql.connect(host=connstr_inception["host"], port=connstr_inception["port"], user=connstr_inception["user"], password=connstr_inception["password"], charset=connstr_inception["charset"]) cur = conn_inception.cursor() cur.execute(sql_demo1_with_format) result = cur.fetchall() #num_fields = len(cur.description) #field_names = [i[0] for i in cur.description] #print(field_names) for row in result: if row[2] != 0: num_fields = len(cur.description) field_names = [i[0] for i in cur.description] print("请检查sql脚本:"+file) #print(field_names) #print(row[0], "|", row[1], "|", row[2], "|", row[3], "|", row[4], "|", row[5], "|", row[6], "|", row[7], "|",row[8], "|", row[9], "|", row[10]) print("SQL错误信息:"+row[4]) print("SQL具体语句:"+row[5]) else: print("脚本"+file+"审核通过") cur.close() conn_inception.close() except Exception as err: print(err) finally: print("****************")
总结
上述脚本还需要继续完善,除了审核外,还需要git clone、sql文件筛选和执行sql文件的脚本。关于易用性的话,先不考虑做web页面,写一个简单guide脚本去实现各脚本间的调用。