为实现Python对SQL脚本的自动化调用或批量执行,读取脚本内容自然是第一步,也是关键所在
规范化的SQL脚本是我们的最爱,代码处理也最为简单,如网文《20行Python代码执行SQL文件》、《Python执行SQL脚本》等
但是!
SQL脚本文件内容如果包含人为的、手写、不规范的、多种备注方式的 情况,该怎么处理呢?
以MySQL为例,备注内容的书写方式就有好几种,如下:
# 备注方式1
-- 备注方式2
/*
备注方式3
*/
select * from dual; # 备注方式4
select * from dual; -- 备注方式5
参考资料
如下方法实现MySQL脚本文件的读取,包含了对备注内容的处理,入参path 是脚本文件的绝对路径,出参为 SQL语句的字符串列表
如有纰漏,敬请@
def readSqlFile(path):
f = open(path, "r", encoding="UTF-8")
lines = f.readlines()
sqlList = []
thisSql = ""
mulNote = False
for line in lines:
string = str(line).strip()
if string == "":
continue
# part1 multi-line comment
if mulNote:
if string.startswith("*/"):
mulNote = False
continue
if string.startswith("/*"):
mulNote = True
continue
if string.startswith("#") or string.startswith("--"):
continue
strIn1 = False
strIn2 = False
for i in range(len(string)):
c = string[i]
# part2 string in sql
if "'" == c:
if not strIn1:
strIn1 = True
else:
strIn1 = False
continue
if '"' == c:
if not strIn2:
strIn2 = True
else:
strIn2 = False
continue
if strIn1 is True and strIn2:
continue
# part3 end of sql
if ";" == c:
string = string[0:(i + 1)]
break
# part4 comment behind of the sql
if "#" == c:
string = string[0:i]
break
if "-" == c and i <= len(string) - 2 \
and "-" == string[i + 1]:
string = string[0:i]
break
# part5 join multi-line for one sql
thisSql += " " + string
# part6 end of sql
if string.endswith(";"):
sqlList.append(copy.deepcopy(thisSql))
thisSql = ""
return sqlList