2

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?

Community
  • 1
  • 1
nluckn
  • 171
  • 1
  • 1
  • 16
  • 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 Jul 01 '16 at 12:46
  • That gives 0.13.1 - I need to update pandas, right? – nluckn Jul 01 '16 at 12:51
  • 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 Jul 01 '16 at 12:54

1 Answers1

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)

joris
  • 133,120
  • 36
  • 247
  • 202