Pandas to_sql to sqlite returns 'Engine' object has no attribute 'cursor'

Question

I have a working_df in pandas I'd like to output to sqlite database.

from sqlalchemy import create_engine

sql_engine = create_engine('sqlite:///test.db', echo=False)
working_df.to_sql('data', sql_engine,index=False, if_exists='append')

returns: AttributeError: 'Engine' object has no attribute 'cursor'

Any thoughts?

Pandas version '0.18.1'

Edit: Added full trace

AttributeError                            Traceback (most recent call last)
<ipython-input-41-4f64fc939721> in <module>()
----> 1 working_df.to_sql('data', engine, index=False, if_exists='append')

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
   1163         sql.to_sql(self, name, con, flavor=flavor, schema=schema,
   1164                    if_exists=if_exists, index=index, index_label=index_label,
-> 1165                    chunksize=chunksize, dtype=dtype)
   1166 
   1167     def to_pickle(self, path):

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
    569     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    570                       index_label=index_label, schema=schema,
--> 571                       chunksize=chunksize, dtype=dtype)
    572 
    573 

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1659                             if_exists=if_exists, index_label=index_label,
   1660                             dtype=dtype)
-> 1661         table.create()
   1662         table.insert(chunksize)
   1663 

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in create(self)
    688 
    689     def create(self):
--> 690         if self.exists():
    691             if self.if_exists == 'fail':
    692                 raise ValueError("Table '%s' already exists." % self.name)

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in exists(self)
    676 
    677     def exists(self):
--> 678         return self.pd_sql.has_table(self.name, self.schema)
    679 
    680     def sql_schema(self):

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in has_table(self, name, schema)
   1674         query = flavor_map.get(self.flavor)
   1675 
-> 1676         return len(self.execute(query, [name, ]).fetchall()) > 0
   1677 
   1678     def get_table(self, table_name, schema=None):

/Users/tom/anaconda/envs/data_science/lib/python3.5/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1557             cur = self.con
   1558         else:
-> 1559             cur = self.con.cursor()
   1560         try:
   1561             if kwargs:

AttributeError: 'Engine' object has no attribute 'cursor'

Are you sure that is is picking up the correct pandas version? Can you show the output of `pd.__version__` in the same session as where you get that error? As you get typically this error if you have an older pandas version (< 0.14) — joris, Jul 12 '16 at 15:19
`pd.__version__` returns `'0.18.1'`, I had seen it was an older pandas issue which is why it was strange — nahata5, Jul 12 '16 at 15:21
going on the older issue http://stackoverflow.com/questions/30631325/writing-to-mysql-database-with-pandas-using-sqlalchemy-to-sql, I did a `engine.raw_connection()` which when applied to the `to_sql`, it seemed to work — nahata5, Jul 12 '16 at 15:22
For some reason, it thinks that it is not a sqlalchemy engine (so falls back to sqlite connection). Can you show the output of `pd.io.sql._is_sqlalchemy_connectable(sql_engine)`? — joris, Jul 12 '16 at 18:07
And `isinstance(sql_engine, sqlalchemy.engine.Connectable)`? — joris, Jul 12 '16 at 18:10
so I think you pointed me in the right direction, I had started up a jupyter notebook and only conda installed sqlalchemy in a second instance of the same env after it mentioned it wasn't installed in this env, so it was never initialized. I restarted the whole session and redid it, and it works w/o the raw_connection. Thanks @joris — nahata5, Jul 12 '16 at 18:14
Ah, glad it is solved! maybe update your answer for if people run into the same situation — joris, Jul 12 '16 at 18:25

score 42 · Accepted Answer · edited Apr 21 '22 at 15:02

42

adding in a raw_connection() worked for me

from sqlalchemy import create_engine

sql_engine = create_engine('sqlite:///test.db', echo=False)
connection = sql_engine.raw_connection()
working_df.to_sql('data', connection, index=False, if_exists='append')

I had conda install sqlalchemy during my notebook session, so while it was accessible, since I already initiated pandas, it appeared as if there was no sqlalchemy. Restarting the session allowed me to remove the raw_connection().

from sqlalchemy import create_engine

sql_engine = create_engine('sqlite:///test.db', echo=False)
connection = sql_engine
working_df.to_sql('data', connection, index=False, if_exists='append')

edited Apr 21 '22 at 15:02

PajLe

791
1
7
21

answered Jul 12 '16 at 15:26

nahata5

1,163
1
10
20

30

I was using Jupyter. After `pip install sqlalchemy`, I just needed to restart the Jupyter kernel for pandas to be able to use sqlalchemy. – jjmontes Apr 11 '17 at 11:57
I was in a REPL and installed SQLAlchemy in another terminal tab without restarting the REPL. The overall takeaway seems to be that SQLAlchemy needs to be available when Pandas is loaded for this to work. – Dave Cameron Oct 30 '17 at 19:26
1

Exactly true, @jjmontes. Resart before trying anything. – Axis Nov 28 '17 at 11:03
For me, no restart issue, this change from `connect()` to `raw_connection()` made it. On top of that, `with` statement on the connection was also not possible, I have to manually `conn.close()` it in the end, see [Python Error: AttributeError: __enter__ [without the markup: \_\_enter\_\_])](https://stackoverflow.com/questions/51427729/python-error-attributeerror-enter). I thought sqlalchemy was a standard? – questionto42 Jan 27 '22 at 11:03

score 7 · Answer 2 · answered Mar 03 '20 at 04:34

7

The following code was giving me the same error while I was working to push data to MS Sql Server:

engine_string = 'mssql+pyodbc://'+username_dsdb+':'+password_dsdb+'@'+server_dsdb+':1433/'+database_dsdb+'?driver=ODBC Driver 13 for SQL Server'
engine = sqlalchemy.create_engine(engine_string,deprecate_large_types=True)

conn_dsdb = engine.connect()

df_year_week_contract_wise.to_sql("KPIEngine_FACT_Historical", conn_dsdb, index=False, if_exists='append')

conn_dsdb.close()

But, then I restarted the session and it worked fine.

answered Mar 03 '20 at 04:34

Raihan Shafique

71
1
2

1

For me similarly i needed to restart session (for jupyter restart kernel) . I think main problem is on the same python session previously there was a connection (likely with pyodbc) but it hanged and was not closed properly (happens not using with context). – Gorkem Aug 26 '21 at 09:31
Have you tried turning it off and on again :) – questionto42 Feb 09 '22 at 21:18

score 5 · Answer 3 · edited Oct 15 '18 at 12:13

5

from sqlalchemy import create_engine
sql_engine = create_engine('sqlite:///test.db', echo=False)
working_df.to_sql('data', sql_engine,index=False, if_exists='append')

Instead of above code, can you try to use connect method of the engine object instead of engine object itself as below.,

sql_engine = create_engine('sqlite:///test.db', echo=False)
conn = sql_engine.connect()
working_df.to_sql('data', conn,index=False, if_exists='append')

Try above steps and let me know if you still facing the issue.

edited Oct 15 '18 at 12:13

amarnath

785
3
19
23

answered Oct 15 '18 at 11:19

Naveen Bhupathi

81
1
6

3

I'm having the same issue of this post. I tried your suggestion but the error now is `AttributeError: 'Connection' object has no attribute 'cursor'`. – Lucas Aimaretto Jan 02 '20 at 16:00
1

@LucasAimaretto Neither connection nor engine do not have cursor method. I assume the cursor purpose for you is to fetch the results stored. You can use execute method of connection for this purpose. You can refer [link](https://docs.sqlalchemy.org/en/13/core/connections.html). – Naveen Bhupathi Jun 24 '20 at 06:00

score 1 · Answer 4 · edited May 23 '17 at 11:47

This is an old question but I had the same error this week and it turned out that there was a problem with MySQLdb on my Mac. As a test I tried to ‘import MySQLdb as mysql’ and discovered an ImportError: “Library not loaded: libmysqlclient.18.dylib”

The solution in my case was to add a symbolic link to the missing library as suggested in Python mysqldb: Library not loaded: libmysqlclient.18.dylib

score 0 · Answer 5 · answered Jul 01 '22 at 06:10

0

From my experience, it's because just pip install the sqlalchemy and didn't restart the jupyter.

So what just need to do is restart the kernel...

answered Jul 01 '22 at 06:10

Livid

86
3

Pandas to_sql to sqlite returns 'Engine' object has no attribute 'cursor'

5 Answers5

Linked