SQL 中 GO 附近的语法不正确 [英] Incorrect syntax near GO in SQL

问题描述

我连接了许多 sql 语句并且遇到以下错误.GO 附近的语法不正确"和附近的语法不正确"-似乎当我删除尾随空格和 go 以及 go 之后的空格，然后 CTRL+Z 放回 GO 时，这会使错误消失吗?它很奇怪为什么??我怎么能用 Python 编码呢，谢谢

I am concatenating many sql statements and am running into the following error. "Incorrect syntax near GO" and "Incorrect syntax near "- It seems that when i delete the trailing space and the go and the space after the go, and then CTRL+Z to put back the GO this makes the error go away? its pretty weird why?? How could I code it in Python, thanks

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

推荐答案

正如在评论中已经提到的，GO 不是 SQL 语法的一部分，而是 Management Studio 中的批处理分隔符.

As already mentioned in comments, GO is not part of the SQL syntax, rather a batch delimiter in Management Studio.

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

You can go around it in two ways, use Subprocess to call SqlCmd, or cut the scripts within Python. The Subprocess + SqlCmd will only really work for you if you don't care about query results as you would need to parse console output to get those.

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

I needed to build a database from SSMS generated scripts in past and created the below function as a result (updating, as I now have a better version that leaves comments in):

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 的脚本:

Using this function, you can run scripts containing GO like this:

    import pymssql

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

    conn.close()

我希望这会有所帮助.

这篇关于SQL 中 GO 附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！