pandasql.sqldf(); ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df '

Question

I need to pandasql library for doing some queries on my pandas dataframes. I have run the below code on a simple sample and got the following Error. Strangely, can't consider it as a query! I tried to reinstall, and upgrade pandasql, pandas, pip, and some others, but I couldn't find what is the problem. I am using Jupiter notebook and the compiler is python3.8 on Ubuntu 20.04.5 LTS.

code:

import pandas as pd
import pandasql as ps
import numpy as np



df = pd.DataFrame([[1234, 'Customer A', '123 Street', np.nan],
               [1234, 'Customer A', np.nan, '333 Street'],
               [1233, 'Customer B', '444 Street', '333 Street'],
              [1233, 'Customer B', '444 Street', '666 Street']], columns=
['ID', 'Customer', 'Billing Address', 'Shipping Address'])

q1 = """SELECT * FROM df """

ps.sqldf(q1)

Result:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, parameters, execution_options)
   1409         try:
-> 1410             meth = statement._execute_on_connection
   1411         except AttributeError as err:

AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

ObjectNotExecutableError                  Traceback (most recent call last)
<ipython-input-21-8b921e84766f> in <module>
     13 q1 = """SELECT * FROM df """
     14 
---> 15 ps.sqldf(q1)

~/.local/lib/python3.8/site-packages/pandasql/sqldf.py in sqldf(query, env, db_uri)
    154     >>> sqldf("select avg(x) from df;", locals())
    155     """
--> 156     return PandaSQL(db_uri)(query, env)

~/.local/lib/python3.8/site-packages/pandasql/sqldf.py in __call__(self, query, env)
     59 
     60             try:
---> 61                 result = read_sql(query, conn)
     62             except DatabaseError as ex:
     63                 raise PandaSQLException(ex)

~/.local/lib/python3.8/site-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    588         )
    589     else:
--> 590         return pandas_sql.read_query(
    591             sql,
    592             index_col=index_col,

~/.local/lib/python3.8/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1558         args = _convert_params(sql, params)
   1559 
-> 1560         result = self.execute(*args)
   1561         columns = result.keys()
   1562 

~/.local/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1403     def execute(self, *args, **kwargs):
   1404         """Simple passthrough to SQLAlchemy connectable"""
-> 1405         return self.connectable.execution_options().execute(*args, **kwargs)
   1406 
   1407     def read_table(

~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, parameters, execution_options)
   1410             meth = statement._execute_on_connection
   1411         except AttributeError as err:
-> 1412             raise exc.ObjectNotExecutableError(statement) from err
   1413         else:
   1414             return meth(

ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df '

tdy · Accepted Answer · 2023-02-01 11:54:15Z

10

This might be because of breaking changes in sqlalchemy 2.0. A SQL query must now be wrapped in a sqlalchemy.text() function to make it executable.

It would be helpful to know your current sqlalchemy version to know if this is the issue you face.

edited Feb 1, 2023 at 11:54

tdy

42k42 gold badges124 silver badges125 bronze badges

answered Jan 30, 2023 at 14:50

Lucasje19

1177 bronze badges

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

3 Comments

Amin Khodamoradi Over a year ago

Name: SQLAlchemy Version: 2.0.0 Requires: greenlet, typing-extensions Required-by: pandasql

Amin Khodamoradi Over a year ago

Do you mean below code? ps.sqldf( sqlalchemy.text( q1 )) It doesn't work. And if you mean using t = text("SELECT * FROM users WHERE id=:user_id") result = connection.execute(t, user_id=12) , so it means put away pandasql.

Johannes Schöck Over a year ago

For me the required change was from pd.read_sql(query, con=db.connection) to pd.read_sql(text(query), con=db.connection) (with from sqlalchemy import text)

Minura Punchihewa · Accepted Answer · 2023-02-04 18:59:53Z

9

I was able to get this to work after downgrading SQLAlchemy to version 1.4.46,

pip install sqlalchemy==1.4.46

answered Feb 4, 2023 at 18:59

Minura Punchihewa

2,1154 gold badges32 silver badges72 bronze badges

Collectives™ on Stack Overflow

pandasql.sqldf(); ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df '

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related