sqlalchemy+pandas:错误 'OptionEngine' object has no attribute 'execute','str' object has no attribute '_execute_on_connection'

场景:使用 sqlalchemy+pandas

返回目录

1.  'OptionEngine' object has no attribute 'execute'

复制代码
import pandas as pd
from sqlalchemy import create_engine, text


engine = create_engine('mysql+pymysql://root:root1234@127.0.0.1:3308/beststock')
sql = "select max(id) as maxrid FROM tspro_stock_basic "

df = pd.read_sql(sql, engine)
print(df)
复制代码
返回目录

Error: 提示报错信息:

复制代码
Traceback (most recent call last):
  File "C:\Users\Google_he\PycharmProjects\trunk\PyStock\UnitTest\Test_Sqlalchemy.py", line 15, in <module>
    df = pd.read_sql(sql, engine)
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 591, in read_sql
    return pandas_sql.read_query(
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 1561, in read_query
    result = self.execute(*args)
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 1406, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
AttributeError: 'OptionEngine' object has no attribute 'execute'
复制代码
返回目录

排查:

查看 pd.read_sql 的 python 源码,原来,需要传递的是一个 connection,

复制代码
def read_sql(
    sql,
    con,
    index_col: str | list[str] | None = None,
    coerce_float: bool = True,
    params=None,
    parse_dates=None,
    columns: list[str] | None = None,
    chunksize: int | None = None,
) -> DataFrame | Iterator[DataFrame]:
复制代码

打印 传递的 engine 类型

print(type(engine))
// <class 'sqlalchemy.engine.base.Engine'>
// 原来对象传递错了,建立 connection,可以通过 engine.connect() 方法

print(type(engine.connect()))
<class 'sqlalchemy.engine.base.Connection'>

所以,需要改成

复制代码
import pandas as pd
from sqlalchemy import create_engine, text


engine = create_engine('mysql+pymysql://root:root1234@127.0.0.1:3308/beststock')
sql = "select max(id) as maxrid FROM tspro_stock_basic "

# df = pd.read_sql(text(sql), con=engine.connect())

df = pd.read_sql(sql, engine.connect())
print(df)
复制代码

 

返回目录

2. 'str' object has no attribute '_execute_on_connection'

改成上面的代码后,出现了下面的错误

复制代码
Traceback (most recent call last):
  File "D:\Program Files (x86)\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 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 "C:\Users\Google_he\PycharmProjects\trunk\PyStock\UnitTest\Test_Sqlalchemy.py", line 16, in <module>
    df = pd.read_sql(sql, engine.connect())
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 591, in read_sql
    return pandas_sql.read_query(
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 1561, in read_query
    result = self.execute(*args)
  File "D:\Program Files (x86)\Python39\lib\site-packages\pandas\io\sql.py", line 1406, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
  File "D:\Program Files (x86)\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'select max(id) as maxrid FROM tspro_stock_basic '
复制代码

继续查看 read_sql() 的源码,关于入参 sql 的注释及示例

sql : str or SQLAlchemy Selectable (select or text object)
        SQL query to be executed or a table name.

再次改成下面,还是错误依旧

df = pd.read_sql("select max(id) as maxrid FROM tspro_stock_basic ", engine.connect())

确定是 入参 sql 的类型有问题,再次看到上面有提到的 text object,看来还是需要转换一下

复制代码
import pandas as pd
from sqlalchemy import create_engine, text


engine = create_engine('mysql+pymysql://root:root1234@127.0.0.1:3308/beststock')
sql = "select max(id) as maxrid FROM tspro_stock_basic "

df = pd.read_sql(text(sql), con=engine.connect())
print(df)


---- output ----
  maxrid
0   5066
复制代码

 

posted on   bruce_he  阅读(7297)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· DeepSeek “源神”启动!「GitHub 热点速览」
· 上周热点回顾(2.17-2.23)
历史上的今天:
2021-02-12 Zabbix 解决中文显示时 图像“方块”问题
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

点击右上角即可分享
微信分享提示

目录导航