Reputation: 1771
I am writing a simple ETL. The source is a spreadsheet. The destination is a SQL Server table. The data are integers and strings. I would like to pass all of the data at once to SQL Server through a stored procedure rather than a row at a time. I learned about table-valued parameters, and created a type and a stored procedure.
CREATE TYPE dbo.apr_house_part_row
AS TABLE
(
-- These columns match the apr_house_part table (order, name and type)
[abc_id] [int] NOT NULL,
[def_id] [int] NOT NULL,
[ghi_nm] [nvarchar](50) NOT NULL,
[jkl_nm] [nvarchar](50) NOT NULL
)
CREATE PROCEDURE dbo.usp_load_apr_house_part(@TVP apr_house_part_row READONLY)
AS
SET NOCOUNT ON;
INSERT INTO apr_house_part
SELECT *
FROM @TVP;
I am using Python to read the spreadsheet and interact with the server. Below is an abridged version of the code that demonstrates the problem. I have tried using pyodbc and pytds. I don't show the connection code below, but I have tried with both, and confirmed that other operations, e.g. executing SELECTs, work fine.
Pyodbc and pytds seem to be raising an exception because I am passing all of the data at once. There are 2526 rows, which is where that number comes from. I show the run-time exceptions that are raised in the code below; pyodbc raises a ProgrammingError and pytds raises a ValueError. I have looked into the pyodbc message but the solutions to other questions about it don't seem to apply.
# read the spreadsheet
hp = openpyxl.load_workbook("excel_file.xlsx")
sh = hp.worksheets[0]
rows = list(sh.rows)
# pull out the header
header = [_.value for _ in rows.pop(0)]
# make a list of lists
values = list(list(col.value or '' for col in row) for row in rows)
hp.close()
# Try it with pyodbc.
conn = db.odbc() # creates and logs in with a pyodbc.Connection
res = conn.execute("exec usp_apr_house_part ?", values)
# pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2526 parameters were supplied', 'HY000')
# Try it with pytds.
tds = db.tds() # creates and logs in with a pytds.Connection
cur = tds.cursor()
res = cur.execute("exec usp_apr_house_part ?", values)
# TypeError: not all arguments converted during string formatting
I hope I am missing something "obvious". What am I missing, or where should I look for help?
Upvotes: 0
Views: 42