read_sql_query() throws "'OptionEngine' object has no attribute 'execute'" with SQLAlchemy 2.0.0

Question

First of all, I'm a totally new guys in the dev world I'm currently taking courses in AI / Data Science and one of my work is to use a SQL Database to make prediction using Prophet, then use these predition to make a PowerBI But currently, I'm stuck with the Python code, I'm not a developer initially, so I have no clue where the problem is:

import sqlalchemy
from sqlalchemy import create_engine
import pandas as pd
from prophet import Prophet
import pymysql


engine = create_engine("mysql+pymysql://root:Password@localhost:3306/data")
query = "SELECT Cle_Produit, Date_Facturation, SUM(Quantite) AS Total_Quantite FROM ventes GROUP BY         Cle_Produit, Date_Facturation"
df = pd.read_sql_query(query, engine)

df = df.pivot(index='Date_Facturation', columns='Cle_Produit', values='Total_Quantite')
df = df.reset_index()
df.rename(columns={'Date_Facturation': 'ds', 'Total_Quantite': 'y'}, inplace=True)


m = Prophet()
m.fit(df)
future = m.make_future_dataframe(periods=365)
forecast = m.predict(future)

forecast[['ds', 'yhat']].to_csv('forecast.csv', index=False)

It returns me this message:

Importing plotly failed. Interactive plots will not work. Traceback (most recent call last): File "f:\Backup\Cours\Cours\Explo Data\app3.py", line 9, in df = pd.read_sql_query(query, engine) File "F:\Programmes\Anaconda\envs\myenv\lib\site-packages\pandas\io\sql.py", line 397, in read_sql_query return pandas_sql.read_query( File "F:\Programmes\Anaconda\envs\myenv\lib\site-packages\pandas\io\sql.py", line 1560, in read_query result = self.execute(*args) File "F:\Programmes\Anaconda\envs\myenv\lib\site-packages\pandas\io\sql.py", line 1405, in execute return self.connectable.execution_options().execute(*args, **kwargs) AttributeError: 'OptionEngine' object has no attribute 'execute'

Please, can somebody help me?

I want this python script to create a csv file with the prediction from prophet. I want Prophet to use the table ventes from the DB data, and it should use the column Cle_Produit, Quantite and Date_Facturation

snakecharmerb · Accepted Answer · 2023-03-02 20:21:55Z

59

The latest version of SQLAlchemy (2.0) has removed Engine.execute. For the time being you may need to downgrade SQLAlchemy

python -m pip install --upgrade 'sqlalchemy<2.0'

(or the equivalent conda commands if you use conda).

Or, as Gord Thompson points out in his comment, wrap the query with sqlalchemy.text.

from sqlalchemy import text

# ...

with engine.begin() as conn:
    query = text("""SELECT * FROM tbl""")
    df = pd.read_sql_query(query, conn)

Or see InnocentBystander's answer for a workaround.

edited Mar 2, 2023 at 20:21

answered Feb 1, 2023 at 11:10

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

6 Comments

Elu Over a year ago

Thanks for you help ! I tried your solution, both, but I still have the same problem. I forget to say, I work on Visual Studio Code with Anaconda Env, and I tried it also in a jupyter notebook and VSC notebook, nothing worked

snakecharmerb Over a year ago

Unfortunately I use neither VSC nor Jupyter. But probably you have to find the env in VSC and ensure that the packages are installed there (and probably using conda, not pip).

Gord Thompson Over a year ago

pandas 1.5.3 does not work for me with SQLA 2.0.0. If I try df = pd.read_sql_query("SELECT 1 AS foo", engine) I get "AttributeError: 'OptionEngine' object has no attribute 'execute'". If I try with engine.begin() as conn: followed by df = pd.read_sql_query("SELECT 1 AS foo", conn) I get "sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'SELECT 1 AS foo'"

snakecharmerb Over a year ago

@GordThompson rats, I thought 1.5.3 had the 2.0 fixes, but the release notes just say they are "working towards" compatibility. Thanks for checking

Gord Thompson Over a year ago

However, df = pd.read_sql_query(sa.text("SELECT 1 AS foo"), conn) does work.

|

Bram van Es · Accepted Answer · 2023-02-03 11:31:06Z

33

Here a similar problem is discussed.

The syntax that works for sqlAlchemy 2.0 and that is consistent with the prior api is.

    from sqlalchemy import create_engine, text as sql_text
    connection = create_engine(bla)
    query = "blo"
    df = pandas.read_sql_query(con=connection.connect(), 
                                  sql=sql_text(query))

edited Feb 3, 2023 at 11:31

answered Feb 3, 2023 at 11:30

Bram van Es

4393 silver badges5 bronze badges

3 Comments

cyril Over a year ago

Thank you very much, works also fine with geopandas.GeoDataFrame.from_postgis.

Woodly0 Over a year ago

This answer is preferable since it doesn't include downgrading of any packages. Long-term perspective.

grantr Over a year ago

seems you also have to explicitly call .connect() as well

snakecharmerb · Accepted Answer · 2023-03-02 20:18:38Z

15

For pandas read_sql_query, there are two things that are easy to get wrong. To avoid this error you need to pass a connection (not the engine) and you need to use the text function to convert the query.

import pandas as pd
from sqlalchemy import create_engine, text
engine = create_engine("mysql+mysqldb://usr:pwd@localhost/dbs")
conn = engine.connect()

query = text('SELECT * FROM table')
df = pd.read_sql_query(query, conn)

Replace "usr", "pwd", "dbs "and "table" with your own strings. You can also read the whole table with pandas read_sql_table without using text. Setup as above:

df = pd.read_sql_table('table', conn)

Some links into the sqlalchemy documentation for more about engines and text:

https://docs.sqlalchemy.org/en/20/core/engines_connections.html

https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text

edited Mar 2, 2023 at 20:18

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

answered Feb 25, 2023 at 20:34

InnocentBystander

7207 silver badges19 bronze badges

1 Comment

Paul Over a year ago

This helped me as I was not using the text method on the string based sql query

user459872 · Accepted Answer · 2023-06-01 12:30:43Z

4

This issue has now fixed in pandas 2.0.0 with the pull request(Make pandas/io/sql.py work with sqlalchemy 2.0).

edited Jun 1, 2023 at 12:30

answered May 22, 2023 at 11:10

user459872

25.9k4 gold badges51 silver badges74 bronze badges

1 Comment

mirekphd Over a year ago

... which breaks other things, including py37 support:)

jason m · Accepted Answer · 2023-03-03 15:27:21Z

2

For anyone who comes across this, the git issue for reference:

https://github.com/pandas-dev/pandas/issues/51015

answered Mar 3, 2023 at 15:27

jason m

6,86322 gold badges77 silver badges126 bronze badges

Comments

Deep Patel · Accepted Answer · 2023-08-10 06:19:54Z

1

I was also getting the same error. My sqlalchemy version is 2.0.5.post1. So I have just replaced the engine with engine.connect().

Your code:

Before:

engine = create_engine("mysql+pymysql://root:Password@localhost:3306/data")
query = "SELECT Cle_Produit, Date_Facturation, SUM(Quantite) AS Total_Quantite FROM ventes GROUP BY Cle_Produit, Date_Facturation"
df = pd.read_sql_query(query, engine)

After:

engine = create_engine("mysql+pymysql://root:Password@localhost:3306/data")
query = "SELECT Cle_Produit, Date_Facturation, SUM(Quantite) AS Total_Quantite FROM ventes GROUP BY Cle_Produit, Date_Facturation"
df = pd.read_sql_query(query, engine.connect())

answered Aug 10, 2023 at 6:19

Deep Patel

1112 bronze badges

Comments

Sonic Soul · Accepted Answer · 2023-11-22 19:04:48Z

0

in my case, switching to session.connection() fixed the issue

pd.read_sql(query, db_session.connection())

answered Nov 22, 2023 at 19:04

Sonic Soul

25.1k39 gold badges138 silver badges197 bronze badges

Comments

Tyler2P · Accepted Answer · 2023-02-04 13:55:32Z

-1

Solution#01: Open cmd or terminal and Try with

pip install --user --upgrade "sqlalchemy<2.0"

Must restart your kernel

edited Feb 4, 2023 at 13:55

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Feb 2, 2023 at 5:48

RAIHAN BIN MOFIDUL

71 bronze badge

Collectives™ on Stack Overflow

read_sql_query() throws "'OptionEngine' object has no attribute 'execute'" with SQLAlchemy 2.0.0

8 Answers 8

6 Comments

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

6 Comments

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related