nluckn nluckn - 3 months ago 40
Python Question

pandas dataframe to mariadb database with to_sql and sqlalchemy

I want to use the pandas function to_sql to write a dataframe into a MariaDB database. My Python code inside PyCharm looks as follows:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

myd = pd.read_csv('/[path]/[filename].csv')

engine = create_engine('mysql+mysqlconnector://[user]:[pw]@[dbname]')

myd.to_sql(name='[tablename]', con=engine, if_exists='replace', index=False)

When executing the last line in the console I get the following error:

Error on sql SELECT name FROM sqlite_master WHERE type='table' AND name='[tablename]';
Traceback (most recent call last):
File "/usr/lib/python3.4/", line 90, in runcode
exec(code, self.locals)
File "<input>", line 1, in <module>
File "/usr/lib/python3/dist-packages/pandas/core/", line 1261, in to_sql
self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)
File "/usr/lib/python3/dist-packages/pandas/io/", line 207, in write_frame
exists = table_exists(name, con, flavor)
File "/usr/lib/python3/dist-packages/pandas/io/", line 275, in table_exists
return len(tquery(query, con)) > 0
File "/usr/lib/python3/dist-packages/pandas/io/", line 90, in tquery
cur = execute(sql, con, cur=cur)
File "/usr/lib/python3/dist-packages/pandas/io/", line 44, in execute
cur = con.cursor()
AttributeError: 'Engine' object has no attribute 'cursor'

Here someone had the same error at one point. However, it had disappeared before someone solved the problem. Do you know what is wrong?


Passing sqlalchemy engines is only supported starting from pandas 0.14.0

To use to_sql with older pandas version, you need to pass the raw connection (engine.raw_connection()) and flavor='mysql' to to_sql:

myd.to_sql(name='[tablename]', con=engine.raw_connection(), flavor='mysql', if_exists='replace', index=False)

However, I recommend to upgrade your pandas version (passing raw connections is deprecated and will not be supported anymore in newer pandas versions, then only sqlalchemy engines/connections will be supported)