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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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)

pandas dataframe to mariadb database with to_sql and sqlalchemy

Sign up