CODEWITHSUNDEEP
pythonpandassqlalchemy
Elu
Elu

Reputation: 531

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

Upvotes: 52

Views: 48729

Answers (8)

Sonic Soul
Sonic Soul

Reputation: 24949

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

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

Upvotes: 0

Deep Patel
Deep Patel

Reputation: 111

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

Upvotes: 1

user459872
user459872

Reputation: 24827

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

Upvotes: 4

jason m
jason m

Reputation: 6835

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

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

Upvotes: 2

snakecharmerb
snakecharmerb

Reputation: 55903

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.

Upvotes: 58

InnocentBystander
InnocentBystander

Reputation: 711

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

Upvotes: 15

RAIHAN BIN MOFIDUL
RAIHAN BIN MOFIDUL

Reputation: 7

Solution#01: Open cmd or terminal and Try with

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

Must restart your kernel

Upvotes: -1

Bram van Es
Bram van Es

Reputation: 441

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

Upvotes: 33

Related Questions