PyODBC:如何在事务中复制批处理分隔符(GO)的行为

Question

背景

我维护了一个Python应用程序，它自动将SQL模式迁移(添加/删除表和列，调整数据等)应用到我们的数据库(SQL2016)。每次迁移都是通过事务中的PyODBC执行的，这样在出现问题时可以回滚。有时，迁移需要一个或多个批处理语句(GO)才能正确执行。由于GO实际上不是T-SQL命令，而是SSMS中的一个特殊关键字，所以我一直在GO上拆分每个SQL迁移，并在同一事务中分别执行每个SQL片段。

import pyodbc
import re
conn_args = {
'driver': '{ODBC Driver 17 for SQL Server}',
'hostname': 'MyServer',
'port': 1298,
'server': r'MyServerMyInstance',
'database': 'MyDatabase',
'user': 'MyUser',
'password': '********',
'autocommit': False,
}
connection = pyodbc.connect(**conn_args)
cursor = connection.cursor()
sql = '''
ALTER TABLE MyTable ADD NewForeignKeyID INT NULL FOREIGN KEY REFERENCES MyParentTable(ID)
GO
UPDATE MyTable
SET NewForeignKeyID = 1
'''
sql_fragments = re.split(r'^s*GO;?s*$', sql, flags=re.IGNORECASE|re.MULTILINE)
for sql_frag in sql_fragments:
cursor.execute(sql_frag)
# Wait for the command to complete.  This is necessary for some database system commands
# (backup, restore, etc).  Probably not necessary for schema migrations, but included
# for completeness.
while cursor.nextset():
pass
connection.commit()

问题

SQL语句批处理没有像我预期的那样执行。当在SSMS中执行上述模式迁移时，迁移成功。在Python中执行时，第一批(添加外键)执行得很好，但第二批(设置外键值)失败了，因为它不知道新的外键。

('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'NewForeignKeyID'. (207) (SQLExecDirectW)")

目标

在PyODBC中的单个事务中执行SQL语句批的层次结构(即每个语句批取决于前一个批)。

我尝试过的

• 在PyODBC文档中搜索有关PyODBC如何支持或不支持批处理语句/GO命令的信息。找不到引用。

• 搜索StackOverflow&谷歌了解如何在PyODBC中批处理语句。

• 在SQL片段执行之间引入一个小睡眠，以防出现某种竞争条件。似乎不太可能成为解决方案，也没有改变行为。

• 我曾考虑过将每一批语句分离成一个单独的事务，在执行下一批语句之前提交该事务，但这将降低/消除我们自动回滚失败的模式迁移的能力。

• 编辑：我刚刚发现了这个问题，这正是我想要做的。然而，在测试(在SSMS中)建议使用EXEC的答案时，我发现第二个EXEC命令(设置值)失败了，因为它不知道新的外键我不擅长测试，而且它确实成功了。此解决方案可能有效，但并不理想，因为EXEC与参数不兼容。此外，如果在片段之间使用变量，这将不起作用。

  BEGIN TRAN
  EXEC('ALTER TABLE MyTable ADD NewForeignKeyID INT NULL FOREIGN KEY REFERENCES MyParentTable(ID)')
  EXEC('UPDATE MyTable SET NewForeignKeyID = 1')
  ROLLBACK TRAN
  Invalid column name 'FK_TestID'.
  

Answer 1

如果您正在从文本文件(例如SSMS中脚本对象生成的文本文件)中读取SQL语句，那么您可以使用Python的subprocess模块以该文件作为输入(-i)来运行sqlcmd实用程序。最简单的形式看起来像

server = "localhost"
port = 49242
uid = "scott"
pwd = "tiger^5HHH"
database = "myDb"
script_file = r"C:__tmpbatch_test.sql"
"""contents of the above file:
DROP TABLE IF EXISTS so69020084;
CREATE TABLE so69020084 (src varchar(10), var_value varchar(10));
INSERT INTO so69020084 (src, var_value) VALUES ('1st batch', 'foo');
GO
INSERT INTO so69020084 (src, var_value) VALUES ('2nd batch', 'bar');
GO
"""
import subprocess
cmd = [
"sqlcmd",
"-S", f"{server},{port}",
"-U", uid,
"-P", pwd,
"-d", database,
"-i", script_file,
]
subprocess.run(cmd)