Inserting multiple MySQL records using Python. ERROR: "Python 'tuple' cannot be converted to a MySQL type"

Question

Inserting multiple MySQL records using Python Error: Python 'tuple' cannot be converted to a MySQL type

ERROR CODE:

Traceback (most recent call last):
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\conversion.py", line 181, in to_mysql
    return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_tuple_to_mysql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 432, in _process_params
    res = [to_mysql(i) for i in res]
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 432, in <listcomp>
    res = [to_mysql(i) for i in res]
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\conversion.py", line 184, in to_mysql
    "MySQL type".format(type_name))
TypeError: Python 'tuple' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "python_mysql_2.py", line 22, in <module>
    my_cursor.execute(mike_placeholders,records_list)
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 557, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 437, in _process_params
    "Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type

Python Code:

#import sql.connector
import mysql.connector

#Create connection, added db we created#
connection = mysql.connector.connect(
    host='localhost', 
    user='root', 
    password='123', 
    database='testdb_1'
    ) 

#Create cursor for the connection
my_cursor = connection.cursor()

#Create SQL statement with placeholders and put in variable 
mike_placeholders="INSERT INTO users (name,email,age) VALUES (%s, %s, %s) "

#Create list (array) of records
records_list = [('Tim','[email protected]',32), ('Mary','[email protected]',40), ('Sam','[email protected]',50), ('Fred','[email protected]',22) ]

#Execute cursor, requires SQl statement variable, record variable
my_cursor.execute(mike_placeholders,records_list)

#Commit the connection to make the change on the database
connection.commit()

Answer 1

Ahhh, I used the wrong Python term.

I should have used executemany when working with a tuple.

my_cursor.executemany(mike_placeholders,records_list)

Answer 2

You can't pass a list to my_cursor.execute(), you need to iterate over the list:

for values in records_list:
    my_cursor.execute(mike_placeholders, values)

Or you could repeat the (%s, %s, %s) multiple times and do it all in a single query by flattening the list of tuples.

mike_placeholders="INSERT INTO users (name,email,age) VALUES " + ", ".join(["(%s, %s, %s)"] * len(records_list))
my_cursor.execute(mike_placeholders, sum(records_list))