9

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:mypass@example.com: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:mypass@example.com: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?

Adi
  • 407
  • 1
  • 4
  • 13
  • Strange. Could you try using `pandas.read_sql_query()` instead? What versions of pandas and sqlalchemy are installed? – codeape Mar 24 '19 at 09:15

4 Answers4

14

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:mypass@example.com:3306/mydatabase')
df = pandas.read_sql("SELECT * FROM persons", con = engine)
Adi
  • 407
  • 1
  • 4
  • 13
  • 2
    Thanks to your answer I tried shutting down my jupyter notebook and restart it, which actually worked. Restarting my computer was not necessary. Thanks – Johann Sep 04 '19 at 15:03
  • Worked like a charm for me! Jupyter should definitely fix this, I ended up wasting 2-3 hours trying to debug this issue. – Mitaksh Gupta Feb 05 '20 at 15:10
  • Hours and hours debugging and this ended up being the correct answer. Most grateful. – Lalo Feb 27 '22 at 21:37
2

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:mypass@example.com: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())
BENY
  • 317,841
  • 20
  • 164
  • 234
  • 1
    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 Feb 09 '21 at 09:44
0

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.

Skippy le Grand Gourou
  • 6,976
  • 4
  • 60
  • 76
0

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)

`

QuentinJS
  • 162
  • 1
  • 9