Update SQL Server database using stored procedure with table as paramater using PYODBC in Python

Question

I need to update a SQL Server database using a stored procedure and a table as a parameter using PYODBC. The stored procedure should be fine but I'm not sure about the syntax used in the Python script:

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)

Stored procedure:

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

    SET @Identity = SCOPE_IDENTITY()
END

Here is my user-defined type:

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

I'm not getting any error when executing the Python script.

Why are you passing a username and password ***and*** using a trusted connection? It's one or the other, not both. — Thom A, Mar 18 '20 at 19:01
@Larnu Thanks, I'll fix that issue. Just trying to get it working first then plan to take care of that. Wasn't sure about how to pass user/pass. — Shane, Mar 18 '20 at 19:04
@DavidBrowne-Microsoft - I've added my user-defined type to the post. Thanks. — Shane, Mar 18 '20 at 19:19
You need to [commit](https://stackoverflow.com/q/20199569/1422451) your changes. This is a common issue of Python DB-API questions. — Parfait, Mar 18 '20 at 19:57

score 1 · Accepted Answer · answered Mar 18 '20 at 19:44

Using Table-Valued Parameters directly requires client support which I don't think pyodbc has implemeneted. But the best way to read and write tabular data from python is with JSON.

You can send tabular results to SQL Server as JSON, and parse the doc on the server to load a TVP or regular table.

Here's a walk-through. Also, I've modified your stored procedure to return multiple rows as the result, instead of just a single SCOPE_IDENTITY value:

In SQL Server Run:

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

And verify that it's working in TSQL like this:

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

Then call it from python like this:

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)

Update SQL Server database using stored procedure with table as paramater using PYODBC in Python

1 Answers1

Linked