Error when updating a database table with pymssql and a python dictionary

Question

I am trying to write data in a dictionary back into a SQL Server database table with pymssql.

But I am getting this error:

ValueError: more placeholders in sql than params available

Here is my code:

cursor = conn.cursor()
for key in dictW:
    x = dictW[key]
    sql = 'UPDATE tablename SET col = %s WHERE %s = @url '
    cursor.executemany(sql, (key, x))
conn.commit()
conn.close()

What am I doing wrong here?

Answer 1

You are attempting to execute your queries one by one but are using executemany(). You should consider using a simple execute() instead:

cursor = conn.cursor()
for key in dictW:
    x = dictW[key]
    sql = 'UPDATE tablename SET col = %s WHERE %s = @url '
    cursor.execute(sql, (key, x))
conn.commit()
conn.close()

If you want to use executemany(), you should make a list of tuples like this:

cursor = conn.cursor()
params = [(k, v) for k, v in dictW.items()]
sql = 'UPDATE tablename SET col = %s WHERE %s = @url '
cursor.executemany(sql, params)
conn.commit()
conn.close()