Pandas read_sql() - AttributeError: 'Engine' object has no attribute 'cursor'

Question

I am trying to read data from MySQL query using pandas read_sql() method with python3+sqlalchemy+pymysql

I tried to follow the following tutorials -

https://pythondata.com/quick-tip-sqlalchemy-for-mysql-and-pandas/

https://www.youtube.com/watch?v=M-4EpNdlSuY

https://www.programcreek.com/python/example/101381/pandas.read_sql

Everything just looks good with the code

import pandas
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:[email protected]:3306/mydatabase')
df = pandas.read_sql("SELECT * FROM persons", con = engine)

Receiving following error -

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

When I tried to pass the 'connection' variable instead of the 'engine', like below -

import pandas
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:[email protected]:3306/mydatabase')
connection = engine.connect()
df = pandas.read_sql("SELECT * FROM persons", con = connection)

it says -

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

What am I doing wrong?

Strange. Could you try using pandas.read_sql_query() instead? What versions of pandas and sqlalchemy are installed? — codeape
– codeape, Commented Mar 24, 2019 at 9:15

Adi · Accepted Answer · 2019-03-24 12:08:10Z

15

Reference - https://github.com/pandas-dev/pandas/issues/23030#issuecomment-428140488

The problem went away when I

Saved my workbook
Restarted the PC
Reloaded

It seems like something was cached. I could use read_sql() and other pandas sql functions by passing 'engine' reference -

import pandas
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:[email protected]:3306/mydatabase')
df = pandas.read_sql("SELECT * FROM persons", con = engine)

answered Mar 24, 2019 at 12:08

Adi

5051 gold badge4 silver badges15 bronze badges

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

4 Comments

Johann Over a year ago

Thanks to your answer I tried shutting down my jupyter notebook and restart it, which actually worked. Restarting my computer was not necessary. Thanks

Mitaksh Gupta Over a year ago

Worked like a charm for me! Jupyter should definitely fix this, I ended up wasting 2-3 hours trying to debug this issue.

Lalo Over a year ago

Hours and hours debugging and this ended up being the correct answer. Most grateful.

Mix Over a year ago

As mentioned in another answer's comment, currently (in 2024) this is most likely due to version mismatch between pandas and sqlalchemy: github.com/pandas-dev/pandas/issues/57049

Vishal · Accepted Answer · 2024-04-04 23:28:32Z

9

For me the issue went away after I downgraded pandas from 2.2 to 2.1.4

answered Apr 4, 2024 at 23:28

Vishal

3213 silver badges4 bronze badges

1 Comment

Louis Maddox Over a year ago

Known bug in 2.2 github.com/pandas-dev/pandas/issues/57049

BENY · Accepted Answer · 2019-03-23 15:03:44Z

3

I think you are looking for pyodbc and sqlalchemy is another way of query db , check with https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91

import sqlalchemy as sq
engine = sq.create_engine('mysql+pymysql://root:[email protected]:3306/mydatabase')
connection = engine.connect()
metadata = sq.MetaData()
persons = sq.Table('persons', metadata, autoload=True, autoload_with=engine)

Ret = connection.execute(sq.select([persons]))
youdf=pd.DataFrame(Ret.fetchall())

answered Mar 23, 2019 at 15:03

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

Skippy le Grand Gourou Over a year ago

I got AttributeError: 'Engine' object has no attribute 'execution_options' errors after upgrading pandas and sqlalchemy from an old notebook using OP’s syntax, and querying your way worked.

Skippy le Grand Gourou · Accepted Answer · 2021-02-09 10:08:13Z

3

This may be due to a version mismatched. I had a similar error with an old notebook which used to work fine :

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

After a few tests I figured the issue appeared with pandas 1.1.0. In the release notes the minimum version for SQLAlchemy was 1.1.4. Upgrading SQLAlchemy to this version fixed the issue.

answered Feb 9, 2021 at 10:08

Skippy le Grand Gourou

7,8526 gold badges65 silver badges82 bronze badges

1 Comment

Isaías Over a year ago

For me it is a versioning error as well, not a cache/restart computer issue: reverting from pandas==2.2.1 to pandas==2.0.3 the error disappears and I can keep using an sqlalchemy.Engine as connection parameter.

snakecharmerb · Accepted Answer · 2024-06-03 08:43:26Z

1

I am using MySQL on Win10 with Python 3.10 and Alchemy 1.4

I tried a variety of options nada. THey all blew up, so here is a work around that seems to make everyone happy.

metadata_obj = MetaData()
tbl_test= Table("test", metadata_obj, autoload_with=engine)

with engine.connect() as sql_con:
    qry = select(tbl_test)
    resultset = sql_con.execute(qry)
    results_as_dict = resultset.mappings().all()
    df = pd.DataFrame(results_as_dict)
    pprint(df)

edited Jun 3, 2024 at 8:43

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

answered Dec 19, 2022 at 23:26

QuentinJS

3522 silver badges10 bronze badges

1 Comment

gil.fernandes Over a year ago

This answer works with Pandas 2.2.2 and SQL Alchemy 1.4.52.

NoobCoder · Accepted Answer · 2024-06-03 08:15:25Z

-1

Downgrading the pandas version helped resolving the issue.

answered Jun 3, 2024 at 8:15

NoobCoder

11 bronze badge

1 Comment

Friedrich Over a year ago

This answer is almost identical to this one, except that it contains even less detail. Please don't post duplicate answers. Vote for the existing answer instead (once you have enough reputation).

Collectives™ on Stack Overflow

Pandas read_sql() - AttributeError: 'Engine' object has no attribute 'cursor'

6 Answers 6

4 Comments

1 Comment

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related