('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

Thanks everyone. I fixed the issue by myself. Basically Run Time Error ('HY000', 'The SQL contains 21 parameter markers, but 1 parameters were supplied') comes when csv column count is not same with your insert query parameters. In this case, my cvs file had an Identity Column "Id" Mapped which is not suppose to be in csv file because this coulmn suppose to be generated by SQL Server and autoincremented. Removing that Column from csv file fixes the issue. Thanks.