Issue with pyodbc when running the update SQL?

  database, pyodbc, python

I am trying to use pyodbc to upsurt in SQL server. I have a list of columns and values which i want to update/insert. I have tried all the possible options i can think of but can’t fix the issue. My values are free text in some cases and can contain "'" as well.

This is how my code looks:

def countList(lst1, lst2): 
    return [sub[item] for item in range(len(lst2)) 
                      for sub in [lst1, lst2]] 

header_string = ', '.join('[{0}]'.format(w) for w in incident_header)
list_for_update = (countList(incident_header, incident_values)) # 
##incident_values Example ###  ['123', 'true', '', '', '', '']
## list_for_update Example ###  ['number', '123', 'made_sla', 'true', 'closed_at', '', 'resolved_at', '', 'work_start', '', 'work_end', '']

k = len(incident_header)  # 6 - For above example
var_string = ', '.join('?' * len(incident_values))
my_string = "[?] = ?"
list_for_update_string = ",".join([my_string]*k)


query_string = "update LIVE_DATA set %s where number='%s';IF @@ROWCOUNT=0
    INSERT INTO ICS_KAFKA_INCIDENT_LIVE_DATA (%s) values (%s);" % (list_for_update_string,number,header_string,var_string)
    
## This is how my SQL is gettting Formed: update ICS_KAFKA_INCIDENT_LIVE_DATA set [?] = ?,[?] = ?,[?] = ?,[?] = ?,[?] = ?,[?] = ? where number='123';IF @@ROWCOUNT=0    INSERT INTO ICS_KAFKA_INCIDENT_LIVE_DATA ([number], [made_sla], [closed_at], [resolved_at], [work_start], [work_end]) values (?, ?, ?, ?, ?, ?);

final_list = list_for_update + incident_values

cursor.execute(query_string,final_list)

I am getting the following Error:

<ipython-input-106-62e3e93b4c08> in insert_and_update(number, incident_header, incident_values)
     33     print(check_sql_string(query_string,final_list))
     34 
---> 35     cursor.execute(query_string,final_list)
     36     connStr.commit()
     37 

ProgrammingError: ('The SQL contains 12 parameter markers, but 18 parameters were supplied', 'HY000')

Source: Python Questions

LEAVE A COMMENT