使用pandasql查询DataFrame时遭遇ObjectNotExecutableError求助

在Ubuntu 20.04.5 LTS系统的Jupyter Notebook（Python3.8环境）中，使用pandasql对pandas DataFrame执行SQL查询时，出现ObjectNotExecutableError，提示查询语句不是可执行对象。已尝试重装、升级pandasql、pandas、pip等相关库，但问题仍未解决。

代码示例：

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)

报错信息：

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

方法1：降级SQLAlchemy到1.x兼容版本 #

执行以下命令安装适配的旧版本：

pip install sqlalchemy==1.4.49

（可选择1.4.x系列任意稳定版本）

方法2：修改pandasql源码适配SQLAlchemy 2.0+ #

找到pandasql安装目录下的sqldf.py文件（示例路径：~/.local/lib/python3.8/site-packages/pandasql/sqldf.py），修改第61行的代码：
原代码：

result = read_sql(query, conn)

修改为：

from sqlalchemy import text
result = read_sql(text(query), conn)

保存文件后重启Jupyter Notebook即可生效。

方法3：使用替代库 #

若不想修改源码或降级，可改用兼容SQLAlchemy新版本的替代库，比如duckdb，用法示例：

import duckdb
duckdb.query("SELECT * FROM df").df()

内容的提问来源于stack exchange，提问作者Amin Khodamoradi