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

Question

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

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。

Answer 1

来自pyodbc文档

要立即调用存储过程，请使用数据库识别的格式或ODBC调用转义格式将调用传递给execute方法。（ODBC驱动程序将重新格式化调用，以便与给定的数据库匹配。）

对于SQL Server，您可以使用以下内容：

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

Answer 2

接受的答案没有解决从存储过程中捕获返回值的问题，可以这样做：

id_ = 'test' 
pw = '12345' 
depart = 'none' 
class_ = 'GM' 
name = 'name' 
birthday = 'None' 
grade = 3 
subgrade = 2 
sql = """
SET NOCOUNT ON;
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]

Answer 3

不要忘记在存储过程中设置NOCOUNT ON。

Answer 4

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

Answer 5

在到处搜索这个解决方案后，我找不到简化版本。所有的结果似乎都过于复杂了，这本应该很容易做到。以下是我的解决方案。

 import pyodbc
 import pandas as pd
 import datetime as d

  conn = pyodbc.connect('Driver=;'
                  'Server=;'
                  'Database=;'
                  'UID=;'
                  'PWD=;')

     # define parameters to be passed in and out
     quarter_date = d.date(year=2020, month=10, day=1)
     SQL = r'exec TERRITORIES_SP @quarterStart = ' + "'" + str(quarter_date) + "'"
     print(SQL)
     try:
         cursor = conn.cursor()
          cursor.execute(SQL)
          cursor.close()
          conn.commit()
    finally:
          conn.close()

Answer 6

通过连接初始化光标，可以直接调用sp，如下所示

sql = " exec your_SP @codemp = ?, @fecha = ? "
prm = (dict['param1'], dict['param2'])
cursor.execute(qry, params)

Answer 7

对于MSSQL，正确的格式是：

SQL = 'exec sp_UpdateUserGoogleAuthenticated ''?'', ''?'''

尝试在"SQL查询"窗口中的MSSQL中运行存储过程，每次都会失败，并且（）围绕？标记。如果您转义单引号，它将允许变量中包含空格。