MySQL Connector could not process parameters

Question

I'm trying to loop through an array and insert each element into a table. As far as I can see my syntax is correct and I took this code straight from Microsoft Azure's documentation.

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()
data = ['1','2','3','4','5']


for x in data:
   cursor.execute("INSERT INTO test (serial) VALUES (%s)",(x))
   print("Inserted",cursor.rowcount,"row(s) of data.")

conn.commit()
cursor.close()
conn.close()
print("Done.")

When I run this is gets to cursor.execute(...) and then fails. Here is the stack trace.

Traceback (most recent call last): File "test.py", line 29, in cursor.execute("INSERT INTO test (serial) VALUES (%s)",("test")) File "C:\Users\AlexJ\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 248, in execute prepared = self._cnx.prepare_for_mysql(params) File "C:\Users\AlexJ\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 538, in prepare_for_mysql raise ValueError("Could not process parameters") ValueError: Could not process parameters

I'm pretty sure MySQL requires `%s` to be enclosed in single quotes. Additionally, you will then get a binding issue because the string will be unpacked. Change `("test")` to `("test",)`, noting the comma — roganjosh, Feb 04 '19 at 14:57
Why do you need to trailing comma in the tuple? When I try to execute a query without it, it fails. If I add the trailing comma it works. I don't understand how this has any effect. When I type the following in the Python interpreter, it looks like they are the same: `>>> (1, 2) == (1, 2,) True` — Shawn, Aug 04 '20 at 21:47

score 51 · Accepted Answer · edited Jul 05 '21 at 20:11

51

Try this:

for x in data:
    value = "test"
    query = "INSERT INTO test (serial) VALUES (%s)"
    cursor.execute(query,(value,))
    print("Inserted",cursor.rowcount,"row(s) of data.")

Since you are using mysql module, cursor.execute requires a sql query and a tuple as parameters

edited Jul 05 '21 at 20:11

Wes Hardaker

21,735
2
38
69

answered Feb 04 '19 at 15:03

Lucas Hort

812
7
9

insertion is a statement so variable name `query` is incorrect. – Ciasto piekarz Dec 01 '19 at 21:06
Thank you so much for this answer. None of the usual sources of reference docs mention this at all. – Slid3r Jan 15 '21 at 09:10
Technically `cursor.execute` requires a _sequence_ or mapping as the second argument, so a list would work as well, but it's conventional to use a tuple, and a list of tuples when calling `cursor.executemany`. – snakecharmerb Aug 10 '22 at 11:01

score 0 · Answer 2 · answered Dec 21 '20 at 04:32

Nice answer from @lucas, but maybe this help other, cz i think more cleaner

sql = "INSERT INTO your_db (your_table) VALUES (%s)"
val = [("data could be array")]
cursor = cnx.cursor()
cursor.execute(sql, val)
print("Inserted",cursor.rowcount,"row(s) of data.")
cnx.commit()
cnx.close()

Cz this is useful for my purpose, to input multiple data.

score -1 · Answer 3 · answered Aug 10 '22 at 07:27

I'm facing same issue but instead of array, I'm looping through a set and insert each item into mysql db and got this error mysql.connector.errors.ProgrammingError: Could not process parameters: str(Data_Tokens), it must be of type list, tuple or dict.

The uniqueTokenSet includes string data type, but as error shows that it must be list, tuple or dict. By converting item to list of tuple [(item)] work for me.

uniqueTokenSet = set()

for item in uniqueTokenSet:
    tokenSql = "insert into tokens(token) values (%s)"
    data = [(item)]
    mycursor.execute(tokenSql, data)

print('data inserted')
mydb.commit()

MySQL Connector could not process parameters

3 Answers3

Linked

Related