python - SQL 中 GO 附近的语法不正确

Question

我在连接许多 sql 语句时遇到了以下错误。“靠近 GO 的语法不正确”和“靠近”的语法不正确-似乎当我删除尾随空格和 go 以及 go 之后的空格，然后 CTRL+Z 放回 GO 时，错误消失了吗？这很奇怪为什么？？我如何用 Python 编写代码，谢谢

')
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
END CATCH
GO

Answer 1

如评论中所述，GO 不是 SQL 语法的一部分，而是 Management Studio 中的批处理分隔符。

您可以通过两种方式绕过它，使用Subprocess 调用SqlCmd，或者在Python 中剪切脚本。 Subprocess + SqlCmd 只有在您不关心查询结果时才真正适合您，因为您需要解析控制台输出才能获得这些结果。

我过去需要从 SSMS 生成的脚本构建一个数据库，并因此创建了以下函数(更新，因为我现在有一个更好的版本，在其中留下了评论):

def partition_script(sql_script: str) -> list:
    """ Function will take the string provided as parameter and cut it on every line that contains only a "GO" string.
        Contents of the script are also checked for commented GO's, these are removed from the comment if found.
        If a GO was left in a multi-line comment, 
        the cutting step would generate invalid code missing a multi-line comment marker in each part.
    :param sql_script: str
    :return: list
    """
    # Regex for finding GO's that are the only entry in a line
    find_go = re.compile(r'^\s*GO\s*$', re.IGNORECASE | re.MULTILINE)
    # Regex to find multi-line comments
    find_comments = re.compile(r'/\*.*?\*/', flags=re.DOTALL)

    # Get a list of multi-line comments that also contain lines with only GO
    go_check = [comment for comment in find_comments.findall(sql_script) if find_go.search(comment)]
    for comment in go_check:
        # Change the 'GO' entry to '-- GO', making it invisible for the cutting step
        sql_script = sql_script.replace(comment, re.sub(find_go, '-- GO', comment))

    # Removing single line comments, uncomment if needed
    # file_content = re.sub(r'--.*$', '', file_content, flags=re.MULTILINE)

    # Returning everything besides empty strings
    return [part for part in find_go.split(sql_script) if part != '']

使用此函数，您可以像这样运行包含 GO 的脚本:

    import pymssql

    conn = pymssql.connect(server, user, password, "tempdb")
    cursor = conn.cursor()
    for part in partition_script(your_script):
        cursor.execute(part)

    conn.close()

希望对您有所帮助。