python mysql ValueError: Could not process parameters

81
June 06, 2021, at 10:50 PM

I have some python code that adds a column to a table and then iterates through a list and adds each value to the column. It adds the column, but it does not add any of the values. It gives me the error ValueError: Could not process parameters, but when I type the same commands into the mysql console then it works just fine. Below is the relevant portion of my code and the error message. Let me know if there is anything obviously wrong. please and thankyou.

mycursor = mydb.cursor()
colname=input()
que="ALTER TABLE timestamps ADD {} TIMESTAMP(6) ".format(colname)
mycursor.execute(que)
print(que)
for i in range(len(res)):    
    val = res[i]
    print(val)
    sql = "INSERT INTO timestamps ({}) VALUES ('{}') ".format(colname,val)
    print(sql)
    mycursor.execute(sql, val)
mydb.commit()
asdf
ALTER TABLE timestamps ADD asdf TIMESTAMP(6) 
2021-05-23 02:32:20.660
INSERT INTO timestamps (asdf) VALUES ('2021-05-23 02:32:20.660') 
Traceback (most recent call last):
  File "upload_onsets.py", line 89, in <module>
    mycursor.execute(sql, val)
  File "/home/ubuntu2004/.local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 257, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/ubuntu2004/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 651, in prepare_for_mysql
    raise ValueError("Could not process parameters")
ValueError: Could not process parameters
Answer 1

Since you're providing the value in the cursor.execute(), you need to put a placeholder into the SQL, not format the value into it.

And the second argument to cursor.execute() should be a tuple, not a single value.

There's no need to assign sql inside the loop, since it doesn't change.

sql = "INSERT INTO timestamps ({}) VALUES (%s) ".format(colname)
print(sql)
for i in range(len(res)):    
    val = res[i]
    print(val)
    mycursor.execute(sql, (val,))

However, you can do this more efficiently using executemany() instead of a loop.

sql = "INSERT INTO timestamps ({}) VALUES (%s) ".format(colname)
vals = [(val,) for val in res]
mycursor.executemany(sql, vals)

This will combine all the inserts into a single query with multiple VALUES lists.

READ ALSO
No files gathered with certain extension every time while using glob.glob()?

No files gathered with certain extension every time while using glob.glob()?

I am trying to search through a list of repositories and extract the C++ filesI am searching through all the subdirectories located in the downloaded repository as well

51
SearchView with Recycler View not working in Fragment

SearchView with Recycler View not working in Fragment

I am trying to make the connection between adapter and fragment, but I get the following error when I try to filter the data for SearchView: Function invocation 'filter(

62
ObjectMapper with nested object

ObjectMapper with nested object

I have JSON response with an array but is not mapping and throwing error, how I can handle JSON like this

75