pandas dataframe to mariadb database with to_sql and sqlalchemy

Question

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]@127.0.0.1/[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/code.py", line 90, in runcode
     exec(code, self.locals)   
   File "<input>", line 1, in <module>   
   File "/usr/lib/python3/dist-packages/pandas/core/frame.py", line 1261, in to_sql
     self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 207, in write_frame
     exists = table_exists(name, con, flavor)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 275, in table_exists
     return len(tquery(query, con)) > 0   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 90, in tquery
     cur = execute(sql, con, cur=cur)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", 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?

Normally, you can get this error if you are using an old pandas version (before it supported sqlalchemy engines). Can you show the output of pd.__version__? — joris
– joris, Commented Jul 1, 2016 at 12:46
Indeed, minimum version is 0.14 for this. Or otherwise you can pass the raw connection (engine.raw_connection()) and flavor='mysql' (but this is not supported anymore in newer versions). — joris
– joris, Commented Jul 1, 2016 at 12:54

joris · Accepted Answer · 2016-07-01 12:56:04Z

4

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)

answered Jul 1, 2016 at 12:56

joris

140k37 gold badges257 silver badges207 bronze badges

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

Collectives™ on Stack Overflow

pandas dataframe to mariadb database with to_sql and sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related