场景:需要批量执行一些sql文件
解决方案:使用python的subprocess包可解决,代码如下,使用前将第8行中username,password,hostname改成自己的即可
def SQLFileExec(sqlDir: str, sqlParams: dict, database: str):
failure = [] # 记录失败的sql文件名
# 遍历文件
for filename in os.listdir(sqlDir):
if filename.endswith(".sql"):
filepath = os.path.join(sqlDir, filename)
# 使用subprocess执行SQL文件并捕获输出结果
process = subprocess.Popen(["mysql", "-u", "username", "-p%s" % "password", "-h", "hostname", "-D", database], stdin=subprocess.PIPE, stdout=subprocess.PIPE)
# communicate的输入需要是字节流
output, error = process.communicate(("source " + file_path).encode("utf-8"))
if error:
print(error.decode())
failure.append(filename)
return failure