Unable to parse List type into mysql using python

Question

I am trying to insert data from records those are having lists or tuples in the data.I have tried with the following code:

Code:

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,emails,ranksheld) VALUES (%s, %s, %s) "

    #Create list (array) of records
    records_list = [('Tim',['[email protected]', '[email protected]'],[2,3]), ('Mary',['[email protected]'],[40, 45, 52]), ('Sam',['[email protected]'],None), ('Fred',['[email protected]'],[4]) ]

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

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

When I tried to execute this i got following error:

InterfaceError: Failed executing the operation; Python type list cannot be converted

Can anyone help?

Try to use tuple instead of list. So, records_list should be(('Tim',['[email protected]', '[email protected]'],[2,3]), ('Mary',['[email protected]'],[40, 45, 52]), ('Sam',['[email protected]'],None), ('Fred',['[email protected]'],[4]) ) — Anwarvic
– Anwarvic, Commented Apr 30, 2020 at 10:41
It appears the problem is with the emails and ranksheld columns where you are passing for each of these lists instead of single values. — Booboo
– Booboo, Commented Apr 30, 2020 at 10:45
Does the emails column for example contain multiple emails separated by some separator such as a comma? — Booboo
– Booboo, Commented Apr 30, 2020 at 10:50

evalkaz · Accepted Answer · 2020-05-03 13:07:55Z

1

The issue is that you have arrays in tuples. If you want to insert multiple rows with the same name/email, you have to add additional tuples in your array. Example:

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,emails,ranksheld) VALUES (%s, %s, %s) "

    #Create list (array) of records
    records_list = [('Tim','[email protected]',2), ('Tim', '[email protected]',3), ('Mary','[email protected]',40), ('Mary','[email protected]', 45), ('Mary','[email protected]', 52), ('Sam','[email protected]',None), ('Fred','[email protected]',4) ]

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

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

edited May 3, 2020 at 13:07

answered Apr 30, 2020 at 10:44

evalkaz

1912 bronze badges

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

2 Comments

Booboo Over a year ago

The prepared statement takes 3 parameters and you are passing 4 in the first case. I think for example the emails column based on its name is meant to hold multiple values even though this makes it not event first normal form.

evalkaz Over a year ago

Yeah, that was mistake on my part. Even though if emails is supposed to hold multiple values, the data is not configured correctly. From SQL query (%s, %s, %s) it expects 3 parameters so if you want to add multiple emails -> concatenate them.

Collectives™ on Stack Overflow

Unable to parse List type into mysql using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related