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