使用pysqldf查询Pandas DataFrame时遇ObjectNotExecutableError错误求助

问题代码 #

from IPython.display import display
from sklearn import datasets
import data as data
import numpy as nm
import pandas as pd
import matplotlib.pyplot as plt
import pandasql as ps
from pandasql import *
from sqlalchemy import text
from sqlalchemy import sql
from sqlalchemy import create_engine
path = 'D:/train.csv'
titanic = pd.read_csv(open(path), sep=',')
print(titanic.head())
print(titanic.shape)

print (type(titanic))

globals()['titanic']

pysqldf = lambda q: sqldf(q, globals())

query = 'SELECT * FROM titanic LIMIT 3'
pysqldf(query)

报错信息 #

AttributeError  Traceback (most recent call last)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\sqlalchemy\engine\base.py:1410, in Connection.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)
Cell In[41], line 2
      1 query = 'SELECT * FROM titanic LIMIT 3'
      2 pysqldf(query)

Cell In[38], line 1, in <lambda>(q)
1 pysqldf = lambda q: sqldf(q, globals())

Object Not Executable Error: Not an executable object: 'SELECT * FROM titanic LIMIT 3'

问题原因 #

你导入了sqlalchemy的text、sql模块，这和pandasql的sqldf函数底层逻辑冲突了。pandasql原本可以直接接收字符串格式的SQL语句，但导入这些模块后，sqldf会错误地将字符串视为需要用text()包装的SQLAlchemy可执行对象，从而抛出该错误。

解决方法 #

• 移除不必要的SQLAlchemy导入：如果你的代码不需要直接使用SQLAlchemy的功能，删除以下导入语句：

  from sqlalchemy import text
  from sqlalchemy import sql
  from sqlalchemy import create_engine
  

  修改后的代码示例：

  from IPython.display import display
  from sklearn import datasets
  import data as data
  import numpy as nm
  import pandas as pd
  import matplotlib.pyplot as plt
  import pandasql as ps
  from pandasql import *
  
  path = 'D:/train.csv'
  titanic = pd.read_csv(open(path), sep=',')
  print(titanic.head())
  print(titanic.shape)
  
  print(type(titanic))
  
  globals()['titanic']
  
  pysqldf = lambda q: sqldf(q, globals())
  
  query = 'SELECT * FROM titanic LIMIT 3'
  pysqldf(query)
  

• 若需保留SQLAlchemy导入：不要直接导入text，而是通过sqlalchemy.text()调用，或者调整导入顺序，确保pandasql的导入在SQLAlchemy相关导入之后。

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