('HY000', 'The SQL contains 21 parameter markers, but 1 parameters were supplied')

Question

I'm trying to read a csv file and upload in SQL. Here is the code. I'm getting this error "pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 21 parameter markers, but 1 parameters were supplied')" My csv file has 21 columns. Do you know how to resolve this issue ?

import csv
import pypyodbc
import traceback
import sys
import time
import os.path
import codecs


# Create a connection to DataBase
con = pypyodbc.connect('DRIVER={SQL Server};SERVER=c1devsql01.XXXXXX.com;DATABASE=Parameters;UID=XXXXXX;PWD=XXXX@1')

cur = con.cursor()
query = "insert into Calc_Rules_Metadata_New (Calc_Set, Calc_Set_Identifier, Dependency, Data_Subset_Keys, Calc_Step, Calc_Variable, Calc_Operator, Calc_Operand, By_Variable, Where_Clause, Source_Tracking_Columns, Source_Tracking_Rows, Revision, Tag, Notes, Updated_By, Updated_On, IsDeleted, Metadata_Type, Calculation_Summary) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

filename = str(sys.argv[1]) 
basedir = 'C:/RiskClient/InputData/Metadata/Calc'
fullpath = os.path.join(basedir, filename)

with open(fullpath, 'r') as csvfile:
    next(csvfile) # Skip Header 
    reader = csv.reader(csvfile, delimiter=',')
    for row in reader:
        #for i in range(len(row)):
            #if row[i] == '':
            #    row[i] = None
        print(row)    
        cur.execute(query, row)   
    cur.commit()

Runtime Error:

['1', '1', '1b.B: Question Test 1', '', 'PFA_Unique_Identifier, Fund_Unique_Identifier; Business_Date', '1', 'Total_Borrowing', 'SUM', 'Borrowings_Data.Amount', '', "UPPER(Borrowings_Data.Commitment_Type) IN ('COMMITTED_AND_DRAWN', 'UNCOMMITTED') AND Borrowings_Data.Business_Date = &Rep_Date AND Fund_Unique_Identifier In (select Fund_Unique_Identifier from Fund_Level_Information where Applicable_PF_Sections IS NOT NULL AND PFA_Unique_Identifier = &PFA_UID AND Business_Date = &Rep_Date)", '', '', '', '', '', '', '', '', 'Test Form', '']
Traceback (most recent call last):
  File "C:\RiskClient\InputData\Metadata\Calc\CalcMetadata.py", line 47, in <module>
    cur.execute(query, row)
  File "C:\Program Files\Python3.5.2\lib\site-packages\pypyodbc-1.3.3-py3.5.egg\pypyodbc.py", line 1470, in execute
  File "C:\Program Files\Python3.5.2\lib\site-packages\pypyodbc-1.3.3-py3.5.egg\pypyodbc.py", line 1263, in _BindParams
pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 20 parameter markers, but 21 parameters were supplied')

Answer 1

You're providing a single list instead of a full set of positional arguments. When you provide those 21 ? within your query string but just the row in cur.execute(query, row), you're effectively saying to stuff that list into the first ? and haven't provided any parameters for the remaining twenty. To resolve, you need to provide *args like so:

cur.execute(query, *row)

Make sure that your row (i.e. list in this scenario) actually has 21 items in it. Otherwise, you'll still get an error about not providing the correct number of parameters.