CODEWITHSUNDEEP
pandassqlalchemy
nahata5
nahata5

Reputation: 1611

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

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'

Upvotes: 64

Views: 128622

Answers (9)

Austin Wood
Austin Wood

Reputation: 1051

Solution for 2024

I know this question is old, but a recent change to Pandas warrants a new solution

Pandas 2.2.0 appears to have modified how a SQLAlchemy Engine object operates when passed to the con argument for pd.read_sql, pd.to_sql, etc. Unsure if this is intentional.

My project worked perfectly fine with Pandas 2.1.4. When updated to 2.2.0, I receive the AttributeError due to the cursor not being present.

  • Solution: Use the connection attribute of a SQLAlchemy Connection object instead of the Engine object directly.

Peep this example:

from sqlalchemy import create_engine
import pandas as pd


# Engine for MS SQL Server using pymssql
conn_url = f"mssql+pymssql://{username}:{password}@{host}/{database}"
engine = create_engine(conn_url)

# Example query
query = 'select id from users'

# Works with pandas<2.2.0
df = pd.read_sql(
    sql=query,
    con=engine,
)

# Works with pandas==2.2.0
with engine.connect() as conn:
    df = pd.read_sql(
        sql=query,
        con=conn.connection
    )

As per comments below, this works with pandas 2.2 for .read_sql(), .read_sql_query(), etc., but does not work for .to_sql() with backends other than SQLite.

Upvotes: 95

Raymundo Escobedo
Raymundo Escobedo

Reputation: 9

I uninstalled sqlalchemy and pandas then reinstalled and it fixed the problem

Upvotes: 0

J.Sung
J.Sung

Reputation: 77

Austin is a lifesaver.

To make this work until pandas is updated and works nicely with sqlalchemy, make sure you pandas version is <2.2 and sqlalchemy<2.0

My setting to get this to stop erroring: pandas==2.1.* sqlalchemy==1.4.*

To supress sqlalchemy warning, add this: from sqlalchemy.util import deprecations deprecations.SILENCE_UBER_WARNING = True

Upvotes: 5

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

As an alternative to raw_connection, using the connection attribute on a connection seems to work with pandas>2, sqlalchemy<2.

with sql_engine.connect() as connection:
    working_df.to_sql("data", connection.connection, index=False, if_exists='append')

In this way you can still use the context manager and have the connection closed.

Upvotes: 1

Livid
Livid

Reputation: 106

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...

Upvotes: 0

nahata5
nahata5

Reputation: 1611

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')

Upvotes: 57

Raihan Shafique
Raihan Shafique

Reputation: 81

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.

Upvotes: 7

Naveen Bhupathi
Naveen Bhupathi

Reputation: 89

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.

Upvotes: 5

Mary Hill
Mary Hill

Reputation: 17

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

Upvotes: 1

Related Questions