用多个用“;"分隔的语句执行SQL文件.使用pyodbc [英] Execute SQL file with multiple statements separated by ";" using pyodbc
问题描述
我目前正在编写一个脚本,以使用Python运行多个SQL文件,这是您提到替代方法之前的一些背景知识;这是为了使脚本自动化,而Python是我在Windows 2008服务器上拥有的唯一工具.我有一个适用于一组的脚本,但问题是,另一组有两个语句,而不是用;"分隔这是我的代码:
I am currently writing a script to run multiple SQL files using Python, a little background before you mention alternative methods; this is to automate the scripts and Python is the only tools I have on our windows 2008 server. I have a script that works for one set but the issue is when the other set has two statements instead of one seperated by a ';' here is my code:
import os
import pyodbc
print ("Connecting via ODBC")
conn = pyodbc.connect('DSN=dsn', autocommit=True)
print ("Connected!\n")
inputdir = 'C:\\path'
cursor = conn.cursor()
for script in os.listdir(inputdir):
with open(inputdir+'\\' + script,'r') as inserts:
sqlScript = inserts.readlines()
sql = (" ".join(sqlScript))
cursor.execute(sql)
print (script)
conn.close()
print ('Run Complete!')
因此,此代码可显示整个文件,但只执行;"之前的一条语句.
So this code works to show the entire file but it only executes one statement before ";".
任何帮助都会很棒!
谢谢.
推荐答案
pyodbc连接器(或pymysql)中的API不允许在SQL调用中使用多个语句.这是引擎解析的问题;一个API需要完全了解要传递的SQL,以便传递多个语句,然后在返回时处理多个结果.
The API in the pyodbc connector (or pymysql) doesn't allow multiple statements in a SQL call. This is an issue of engine parsing; an API would need to completely understand the SQL that it's passing in order for multiple statements to be passed, and then multiple results handled upon return.
对脚本进行如下稍作修改后,应该可以使用单独的连接器分别发送每个语句:
A slight modification to your script like the one below should allow you to send each of your statements individually with separate connectors:
import os
import pyodbc
print ("Connecting via ODBC")
conn = pyodbc.connect('DSN=dsn', autocommit=True)
print ("Connected!\n")
inputdir = 'C:\\path'
for script in os.listdir(inputdir):
with open(inputdir+'\\' + script,'r') as inserts:
sqlScript = inserts.readlines()
for statement in sqlScript.split(';'):
with conn.cursor() as cur:
cur.execute(statement)
print(script)
conn.close()
with conn.cursor() as cur:
打开并关闭每个语句的游标,并在每次调用完成后适当退出.
The with conn.cursor() as cur:
opens a closes a cursor for each statement, exiting appropriately after each call is completed.
这篇关于用多个用“;"分隔的语句执行SQL文件.使用pyodbc的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!