CODEWITHSUNDEEP
pythonmysqlpandas
Henri
Henri

Reputation: 1235

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

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.

Upvotes: 4

Views: 4645

Answers (1)

Aku
Aku

Reputation: 802

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.

Upvotes: 1

Related Questions