Insert pandas dataframe into MySQL table - MySQLInterfaceError: Python type list cannot be converted

Question

I want to insert a pandas dataframe into a MySQL table using the code below.

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="pw",
    database='db')

cols = "`,`".join([str(i) for i in df.columns.tolist()])
c = mydb.cursor(buffered=True)

for i,row in df.iterrows():
    sql = "INSERT INTO `table` (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
   

    c.execute(sql, tuple(row)) # I've also tried (tuple(row,)) with the same result
   
    mydb.commit()

When I run this I get the following:


---------------------------------------------------------------------------
MySQLInterfaceError                       Traceback (most recent call last)
<ipython-input-195-e2067aefba0c> in <module>
     11     sql = "INSERT INTO `bas` (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
     12     print('row:',tuple(row))
---> 13     c.execute(str(sql), tuple(row))
     14 
     15     # the connection is not autocommitted by default, so we must commit to save our changes

c:\programdata\miniconda3\lib\site-packages\mysql\connector\cursor_cext.py in execute(self, operation, params, multi)
    255 
    256         if params:
--> 257             prepared = self._cnx.prepare_for_mysql(params)
    258             if isinstance(prepared, dict):
    259                 for key, value in prepared.items():

c:\programdata\miniconda3\lib\site-packages\mysql\connector\connection_cext.py in prepare_for_mysql(self, params)
    663                 ]
    664             else:
--> 665                 result = self._cmysql.convert_to_mysql(*params)
    666         elif isinstance(params, dict):
    667             result = {}

MySQLInterfaceError: Python type list cannot be converted

I find this a bit confusing since I don't insert any list into c.execute() - just a str (sql) and a tuple(row). This isn't a reproducible example, but if anyone has any clue what I could do to solve this I would be grateful.

Please post the error message as a string, rather than as an image, as people using a screenreader won't be able to read the text in the image. Also, have you tried adding print(row) before executing the cursor: could it be that one of the values within row is a list? — Luke Woodward
– Luke Woodward, Commented Nov 21, 2021 at 13:24
Yes, you were right. One column in my dataframe where stores as a list. Thanks! — Henri
– Henri, Commented Nov 21, 2021 at 20:29
Have you looked at the .to_sql method? It handles inserting a pandas df into an SQL database. pandas.pydata.org/docs/reference/api/… — cerd
– cerd, Commented Nov 21, 2021 at 20:34

Aku · Accepted Answer · 2021-12-10 08:39:29Z

maybe I can help you out. As far as I know

MySQLInterfaceError: Python type list cannot be converted

means that you failed to convert your list to str... I would suggest to switch for the pymysql library! Its pretty easy to use and I am quite happy with it. There you can append variables or DataFrames very easy.

For a Dataframe named "df":

sql = """INSERT INTO dummy_table(dummy1, dummy2, dummy3) VALUES(%s,%s,%s)"""
for row in df.values.tolist():
    curs.execute(sql, tuple(row))
conn.commit()
conn.close()

For single variables (value1 etc.) you want to append, you just make a tuple:

sql = """INSERT INTO dummy_table(dummy1, dummy2, dummy3) VALUES(%s,%s,%s)"""
data = (value1, value2, value3)
curs.execute(sql, data)
conn.commit()
conn.close()

Note that you will of course have to create a table before you try and insert ;) also dummy1 etc. are the names of your table-columns you specified when creating the table.

Collectives™ on Stack Overflow

Insert pandas dataframe into MySQL table - MySQLInterfaceError: Python type list cannot be converted

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related