Receiving 'Invalid column name' when connecting to SQL Server using pyodbc

Question

I am trying to connect to a SQL Server database to insert a file. The file will be coming from an API in JSON format, but as that is not finished yet, I have created a sample record in the meantime.

{
    "group_id": "03505da3-b23d-466f-85f0-187f3afcbd2c", 
    "certificate_number": "IS 759831", 
    "original_registration_date": "2022-04-08", 
    "effective_date": "2022-04-08", 
    "last_revision_date": "2022-04-08", 
    "exp_date": "2025-04-07"
}

I import this JSON as JSON_SAMPLE into my 'main.py' python script, which I shall copy below:

import json
import pyodbc
from _secrets import SERVER, DATABASE, UID, PWD

JSON_SAMPLE = 'static/sample.json'

with open(JSON_SAMPLE, 'r', encoding='utf-8') as f:
    _str = f.read()
    strp_str = _str.replace('\\', "")
    data = json.loads(strp_str)

cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};' + f'SERVER={SERVER};DATABASE={DATABASE};UID={UID};PWD={PWD}')

cursor = cnxn.cursor()

cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

columns = list(data.keys())
rows = list(data.values())
QUERY = ', '.join(columns)

cursor.execute("INSERT INTO dbo.certificates(?) VALUES (?, ?, ?, ?, ?, ?)", columns, rows[0], rows[1], rows[2], rows[3], rows[4], rows[5])

AND THE ERROR MESSAGE IS...

Traceback (most recent call last):
File "/workspaces/codespaces-blank/main.py", line 26, in cursor.execute("INSERT INTO dbo.certificates(?) VALUES (?, ?, ?, ?, ?, ?)", columns, rows[0], rows[1], rows[2], rows[3], rows[4], rows[5]) #pylint: disable=line-too-long pyodbc.ProgrammingError: ("A TVP's rows must all be the same size.", 'HY000')

If it helps understand what happened, the result of printing the argument for cursor.execute() is:

INSERT INTO dbo.certificates(?) VALUES (?, ?, ?, ?, ?, ?) ['group_id', 'certificate_number', 'original_registration_date', 'effective_date', 'last_revision_date', 'exp_date'] 03505da3-b23d-466f-85f0-187f3afcbd2c IS 759831 2022-04-08 2022-04-08 2022-04-08 2025-04-07

What did I try:

I have reproduced this error on a Github Codespace. I have only done this process once before, so I am a newbie at this, but I have consulted the documentation here 'https://github.com/mkleehammer/pyodbc/wiki/Getting-started', checked Google for a good while and also shared the problem with a couple of colleagues that have a little programming experience and we couldn't pinpoint the problem.

I joined the list of column names into a string in an effort to troubleshoot, as the examples in the documentation are typed in the same format - without quotation marks:

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

What I was expecting: The command to execute without an error message, and a new record to be created in my SQL SERVER database.

So what is triggering this error message? It seems to be saying that every column name is invalid but I can't see how. So, what am I missing? Any help would be hugely appreciated! And with that, any constructive advice about how this question is written would be great, as this is my first Stack Overflow question.

Receiving 'Invalid column name' when connecting to SQL Server using pyodbc

0 Answers0