在Python中使用PYODBC使用以表为参数的存储过程更新SQL Server数据库

Question

我需要使用存储过程更新SQL Server数据库，并使用PYODBC将表作为参数进行更新。存储过程应该很好，但我不确定Python脚本中使用的语法：

Python:

import pandas as pd
import pyodbc

# Create dataframe
data = pd.DataFrame({
    'STATENAME':[state1, state2],
    'COVID_Cases':[value1, value2],
})

data

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=mydb;'
                      'Database=mydbname;'
                      'Username=username'
                      'Password=password'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

params = ('@StateValues', data)
cursor.execute("{CALL spUpdateCases (?,?)}", params)

存储过程：

[dbo].[spUpdateCases]
    @StateValues tblTypeCOVID19 readonly,
    @Identity int out
AS
BEGIN
    INSERT INTO tblCOVID19 
        SELECT * FROM @StateValues

    SET @Identity = SCOPE_IDENTITY()
END

以下是我的用户定义类型：

CREATE TYPE [dbo].[tblTypeCOVID19] AS TABLE
                                      (
                                          [ID] [int] NOT NULL,
                                          [StateName] [varchar](50) NULL,
                                          [COVID_Cases] [int] NULL,
                                          [DateEntered] [datetime] NULL
                                      )

我在执行Python脚本时没有遇到任何错误

Answer 1

直接使用表值参数需要客户端支持，我认为pyodbc并没有实现这一点。但是从python读取和写入表格数据的最佳方法是使用JSON

您可以将表格结果作为JSON发送到SQL Server，并在服务器上解析文档以加载TVP或常规表

这是一个步行通道。此外，我还修改了存储过程以返回多行作为结果，而不是仅返回一个SCOPE\u标识值：

在SQL Server运行中：

use tempdb

go
drop table if exists tblCOVID19
drop procedure [spUpdateCases]
drop type [dbo].[tblTypeCOVID19]
go
create table tblCOVID19
(
  ID int identity not null primary key,
  StateName varchar(200), 
  COVID_Cases int, 
  DateEntered datetime default getdate()
)
go
CREATE TYPE [dbo].[tblTypeCOVID19] AS TABLE(
[ID] [int] NULL,
[StateName] [varchar](50) NULL,
[COVID_Cases] [int] NULL,
[DateEntered] [datetime] NULL
)

go
create or alter procedure [dbo].[spUpdateCases]
@StateValues tblTypeCOVID19 readonly
AS
BEGIN
  set nocount on; 

  insert into tblCOVID19 (StateName, COVID_Cases)
  output inserted.*
  select StateName, COVID_Cases 
  from @StateValues;

END

并验证它是否在TSQL中工作，如下所示：

declare @json nvarchar(max) = '[{"STATENAME":"TX","COVID_Cases":212},{"STATENAME":"OK","COVID_Cases":41}]'

declare @tvp tblTypeCOVID19

insert into @tvp(StateName, COVID_Cases)
select StateName, COVID_Cases
from openjson(@json)
with 
(
  StateName varchar(200) '$.STATENAME',
  COVID_Cases int '$.COVID_Cases'
)

exec [dbo].[spUpdateCases] @tvp

然后从python中调用它，如下所示：

import pandas as pd
import pyodbc
import json

# Create dataframe
data = pd.DataFrame({
    'STATENAME':["TX", "OK"],
    'COVID_Cases':[212, 41],
})



conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=localhost;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

jsonData = data.to_json(orient='records')
print(jsonData)

sql = """
set nocount on;
declare @tvp tblTypeCOVID19

insert into @tvp(StateName, COVID_Cases)
select StateName, COVID_Cases
from openjson(?)
with 
(
  StateName varchar(200) '$.STATENAME',
  COVID_Cases int '$.COVID_Cases'
)

exec [dbo].[spUpdateCases] @tvp
"""
cursor.execute(sql, jsonData)
results = cursor.fetchall()

print(results)