场景：使用 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

巴特西

sqlalchemy+pandas：错误 'OptionEngine' object has no attribute 'execute'，'str' object has no attribute '_execute_on_connection'

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

Error: 提示报错信息：

排查：

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

最新文章

热门文章