使用 Python (pyodbc) 中的 SQL Server 存储过程

问题描述：

我有一个存储过程，代码:

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)

如何使用此过程进行原始 sql 查询以创建帐户?我正在使用烧瓶和 pyodbc.

How i can make a raw sql query to create account using this procedure? I'm using flask and pyodbc.

答

来自 pyodbc 文档

要立即调用存储过程，请使用数据库识别的格式或使用 ODBC 调用转义格式.(然后 ODBC 驱动程序将为您重新格式化调用以匹配给定的数据库.)

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.)

对于 SQL Server，您将使用以下内容:

For SQL Server you would use something like this:

# SQL Server format
cursor.execute("exec sp_dosomething(123, 'abc')")

# ODBC format
cursor.execute("{call sp_dosomething(123, 'abc')}")

所以调用你的程序

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))

使用 Python (pyodbc) 中的 SQL Server 存储过程

相关推荐