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

Anwarvic · Accepted Answer · 2020-04-30 10:44:25Z

12

Ahhh, I used the wrong Python term.

I should have used executemany when working with a tuple.

my_cursor.executemany(mike_placeholders,records_list)

edited Apr 30, 2020 at 10:44

Anwarvic

13.2k5 gold badges59 silver badges72 bronze badges

answered Jan 21, 2019 at 20:11

JavaMon

3191 gold badge5 silver badges15 bronze badges

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

Comments

Barmar · Accepted Answer · 2019-01-21 20:04:07Z

5

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

answered Jan 21, 2019 at 20:04

Barmar

789k57 gold badges554 silver badges669 bronze badges

2 Comments

Ozan Sen Over a year ago

@Barmar You are fantastic. This solution worked for me after searching the solution for hours. I upvoted it.

Barmar Over a year ago

The answers that use executemany() are better.

Pawara Siriwardhane · Accepted Answer · 2021-10-29 16:11:19Z

3

use my_cursor.executemany(mike_placeholders,records_list)

If you have multiple elements which are saved in a list or tuple then use,

cursor.executemany(query,list) or cursor.executemany(query,tuple)

edited Oct 29, 2021 at 16:11

Pawara Siriwardhane

2,05512 gold badges32 silver badges44 bronze badges

answered Oct 29, 2021 at 6:49

user17277559

311 bronze badge

Comments

alphadmon · Accepted Answer · 2021-12-10 10:43:31Z

0

You must use a for loop and INSERT item by item

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

edited Dec 10, 2021 at 10:43

alphadmon

4465 silver badges22 bronze badges

answered Sep 1, 2021 at 18:53

dhia chemingui

1

1 Comment

Community Over a year ago

Please provide additional details in your answer. As it's currently written, it's hard to understand your solution.

Collectives™ on Stack Overflow

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

Python Code:

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Python Code:

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related