英文:
AttributeError: 'str' object has no attribute '_execute_on_connection'
问题
我在以下代码中遇到了问题：
from pandasql import sqldf
import pandas as pd
df = pd.DataFrame({'column1': [1, 2, 3], 'column2': [4, 5, 6]})
query = "SELECT * FROM df WHERE column1 > 1"
new_dataframe = sqldf(query)
print(new_dataframe)
当我运行代码时，出现以下错误：
Traceback (most recent call last):
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1410 in execute
meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ~\AppData\Local\Programs\Spyder\pkgs\spyder_kernels\py3compat.py:356 in compat_exec
exec(code, globals, locals)
File c:\users\yv663dz\downloads\untitled1.py:18
new_dataframe = sqldf(query)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:156 in sqldf
return PandaSQL(db_uri)(query, env)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:61 in __call__
result = read_sql(query, conn)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:592 in read_sql
return pandas_sql.read_query(
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1557 in read_query
result = self execute(*args)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1402 in execute
return self connectable.execution_options().execute(*args, **kwargs)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1412 in execute
raise exc.ObjectNotExecutableError(statement) from err
ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df WHERE column1 > 1'
我已经安装了最新版本的pandas、pandasql和sqlalchemy，使用Spyder作为IDE。能有人帮帮我吗？
英文:
I have a problem with following code:
from pandasql import sqldf
import pandas as pd
df = pd.DataFrame({'column1': [1, 2, 3], 'column2': [4, 5, 6]})
query = "SELECT * FROM df WHERE column1 > 1"
new_dataframe = sqldf(query)
print(new_dataframe)
When I submit, I have this error:
Traceback (most recent call last):
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1410 in execute
meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ~\AppData\Local\Programs\Spyder\pkgs\spyder_kernels\py3compat.py:356 in compat_exec
exec(code, globals, locals)
File c:\users\yv663dz\downloads\untitled1.py:18
new_dataframe = sqldf(query)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:156 in sqldf
return PandaSQL(db_uri)(query, env)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:61 in __call__
result = read_sql(query, conn)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:592 in read_sql
return pandas_sql.read_query(
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1557 in read_query
result = self.execute(*args)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1402 in execute
return self.connectable.execution_options().execute(*args, **kwargs)
File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1412 in execute
raise exc.ObjectNotExecutableError(statement) from err
ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df WHERE column1 > 1'
I installed the latest versions of pandas, pandasql and sqlalchemy and I use Spyder as IDE. Could someone help me please?
答案1
得分: 31
SQLAlchemy 2.0（发布于2023年01月26日）要求将原始 SQL 查询用 sqlalchemy.text 进行包装。
解决这个错误消息的通用方法是将查询文本传递给 sqlalchemy.text()
from sqlalchemy import text
...
query = text("SELECT * FROM some_table WHERE column1 > 1")
然而在这种情况下，OP 使用的是pandasql，它期望一个字符串。似乎没有简单的方法使 pandasql 与 SQLAlchemy >= 2.0 兼容，而且该软件包似乎未得到维护，因此唯一的解决方法是找到已修复问题的分支（有一些），自行分叉项目并修复问题，或使用 Python 包管理器降级你的 SQLAlchemy 安装。例如，如果你使用 pip：
python3 -m pip install --upgrade 'sqlalchemy<2.0'
英文:
SQLAlchemy 2.0 (released 2023-01-26) requires that raw SQL queries be wrapped by sqlalchemy.text.
The general solution for this error message is to pass the query text to sqlalchemy.text()
from sqlalchemy import text
...
query = text("SELECT * FROM some_table WHERE column1 > 1")
However in this case the OP is using pandasql, which expects a string. There does not seem to be a straightforward way to make pandasql compatible with SQLAlchemy >= 2.0, and the package seems to be unmaintained, so the only solutions are to find a fork that has fixed the problem (there are some), fork the project yourself and fix it, or downgrade your SQLAlchemy installation using your Python package manager. For example, if you use pip:
python3 -m pip install --upgrade 'sqlalchemy<2.0'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论