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

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

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.

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.

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

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