使用 Python (pyodbc) 中的 SQL Server 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28635671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SQL Server stored procedures from Python (pyodbc)
提问by bartezr
I'm have a stored procedure, code:
我有一个存储过程，代码：
DECLARE @RC int
DECLARE @id varchar(13)
DECLARE @pw varchar(13)
DECLARE @depart varchar(32)
DECLARE @class varchar(12)
DECLARE @name varchar(12)
DECLARE @birthday varchar(10)
DECLARE @grade int
DECLARE @subgrade int
SELECT @id = 'test'
SELECT @pw = '12345'
SELECT @depart = 'none'
SELECT @class = 'GM'
SELECT @name = 'name'
SELECT @birthday = 'None'
SELECT @grade = 3
SELECT @subgrade = 2
EXEC @RC = [my_database].[dbo].[my_table] @id, @pw, @depart, @class, @name, @birthday, @grade, @subgrade
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: my_database.dbo.my_table'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
How i can make a raw sql query to create account using this procedure? I'm using flask and pyodbc.
我如何使用此过程进行原始 sql 查询以创建帐户？我正在使用烧瓶和 pyodbc。
采纳答案by dirn
From the pyodbc documentation
To call a stored procedure right now, pass the call to the execute method using either a format your database recognizes or using the ODBC call escape format. (The ODBC driver will then reformat the call for you to match the given database.)
For SQL Server you would use something like this:
要立即调用存储过程，请使用数据库识别的格式或使用ODBC 调用转义格式将调用传递给 execute 方法。（然后 ODBC 驱动程序将为您重新格式化调用以匹配给定的数据库。）
对于 SQL Server，您将使用以下内容：
# SQL Server format
cursor.execute("exec sp_dosomething(123, 'abc')")
# ODBC format
cursor.execute("{call sp_dosomething(123, 'abc')}")
So to call your procedure
所以调用你的程序
id_ = 'test'
pw = '12345'
depart = 'none'
class_ = 'GM'
name = 'name'
birthday = 'None'
grade = 3
subgrade = 2
sql = 'exec [my_database].[dbo].[my_table](?, ?, ?, ?, ?, ?, ?, ?)'
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)
cursor.execute(sql, (values))
回答by anonmyous1234
For MSSQL the correct format is this:
对于 MSSQL，正确的格式是这样的：
SQL = 'exec sp_UpdateUserGoogleAuthenticated ''?'', ''?'''
Try running the Stored Procedure in MSSQL in the SQL Query window and it will fail every time with () surrounding the ? marks. If you escape the single quotes it will allow for variables with spaces in them.
尝试在 SQL 查询窗口中的 MSSQL 中运行存储过程，每次都将失败，（）围绕 ? 分数。如果您对单引号进行转义，它将允许其中包含空格的变量。
回答by Gord Thompson
The accepted answer does not address the issue of capturing the return value from the stored procedure, which can be done like this:
接受的答案没有解决从存储过程中捕获返回值的问题，可以这样做：
id_ = 'test'
pw = '12345'
depart = 'none'
class_ = 'GM'
name = 'name'
birthday = 'None'
grade = 3
subgrade = 2
sql = """\
DECLARE @RC int;
EXEC @RC = [my_database].[dbo].[my_sp] ?, ?, ?, ?, ?, ?, ?, ?;
SELECT @RC AS rc;
"""
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)
cursor.execute(sql, values)
rc = cursor.fetchval() # pyodbc convenience method similar to cursor.fetchone()[0]
回答by Eman4real
Don't forget SET NOCOUNT ON in your stored procedure.
不要忘记存储过程中的 SET NOCOUNT ON。
回答by Viggos
Another flavour of Gord's answer is using OUTPUT and named parameters (to be defined within the Stored procedure) for clarity.
为了清楚起见，Gord 的另一种回答是使用 OUTPUT 和命名参数（在存储过程中定义）。
id_ = 'test'
pw = '12345'
depart = 'none'
class_ = 'GM'
name = 'name'
birthday = 'None'
grade = 3
subgrade = 2
sql = """\
DECLARE @RC int;
EXEC [my_database].[dbo].[my_sp] @RC OUTPUT, @id_=?, @pw=?, @depart=?, @class_=?, @name=?, @birthday=?, @grade=?, @subgrade=?;
SELECT @RC AS rc;
"""
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)
cursor.execute(sql, values)
rc = cursor.fetchval()
回答by Leonardo Pascual
With a cursor initialized by your connection, the sp can be called directly as follow
使用连接初始化的游标，可以按如下方式直接调用 sp
sql = " exec your_SP @codemp = ?, @fecha = ? "
prm = (dict['param1'], dict['param2'])
cursor.execute(qry, params)
