通过pyodbc在MS SQL中创建存储过程

Question

我正试图通过python代码在mssql中的master中创建一个存储过程。以下是我的代码：

import pyodbc
conn = pyodbc.connect("driver={SQL Server};server=localhost; database=master; trusted_connection=true",
autocommit=True) 
cursor = conn.cursor()
sqlcommand = """
USE master
GO
CREATE PROCEDURE sp_myCustomSystemProc
AS
BEGIN
PRINT 'myCustomCode'
END
GO
EXEC sp_ms_marksystemobject 'sp_myCustomSystemProc'
"""     
cursor.execute(sqlcommand)
cursor.commit()
conn.commit()

运行完这个python代码后，我得到了这个错误：

Traceback(最后一次调用(：

File "auto_complete.py", line 27, in <module>
cursor.execute(sqlcommand)  
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'GO'. (102) (SQLExecDirectW); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. (111); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'GO'. (102); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near 'sp_myCustomSystemProc'. (102)")

有人能帮我解决这个问题吗？

Answer 1

由于连接字符串已经指定了master数据库(即database=master;(，因此只需删除

USE master
GO

来自您的查询。