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
– roganjosh, Commented Feb 4, 2019 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
– Shawn, Commented Aug 4, 2020 at 21:47

Wes Hardaker · Accepted Answer · 2021-07-05 20:11:17Z

55

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 5, 2021 at 20:11

Wes Hardaker

22.3k2 gold badges42 silver badges70 bronze badges

answered Feb 4, 2019 at 15:03

Lucas Hort

8547 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ciasto piekarz Over a year ago

insertion is a statement so variable name query is incorrect.

Slid3r Over a year ago

Thank you so much for this answer. None of the usual sources of reference docs mention this at all.

snakecharmerb Over a year ago

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.

Budi Mulyo · Accepted Answer · 2020-12-21 04:32:07Z

0

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.

answered Dec 21, 2020 at 4:32

Budi Mulyo

3841 gold badge5 silver badges23 bronze badges

Comments

akshay_sushir · Accepted Answer · 2022-08-10 07:27:53Z

-1

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

answered Aug 10, 2022 at 7:27

akshay_sushir

1,96116 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL Connector could not process parameters

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related