使用Python读取MySQL数据时遇AttributeError错误求助

问题场景 #

使用Python从MySQL数据库提取数据时，执行以下代码：

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine("mysql+pymysql://root:@localhost:3306/haider")
df = pd.read_sql_table("ahmed", engine)

出现如下错误：

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In [46], line 1
----> 1 df = pd.read_sql_table("ahmed", engine)

File E:\python\lib\site-packages\pandas\io\sql.py:286, in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize)
    282     raise ValueError(f"Table {table_name} not found")
    284 # error: Item "SQLiteDatabase" of "Union[SQLDatabase, SQLiteDatabase]"
    285 # has no attribute "read_table"
--> 286 table = pandas_sql.read_table(  # type: ignore[union-attr]
    287     table_name,
    288     index_col=index_col,
    289     coerce_float=coerce_float,
    290     parse_dates=parse_dates,
    291     columns=columns,
    292     chunksize=chunksize,
    293 )
    295 if table is not None:
    296     return table

File E:\python\lib\site-packages\pandas\io\sql.py:1460, in SQLDatabase.read_table(self, table_name, index_col, coerce_float, parse_dates, columns, schema, chunksize)
   1417 """
   1418 Read SQL database table into a DataFrame.
   1419 
   (...)
   1457 
   1458 """
   1459 table = SQLTable(table_name, self, index=index_col, schema=schema)
--> 1460 return table.read(
   1461     coerce_float=coerce_float,
   1462     parse_dates=parse_dates,
   1463     columns=columns,
   1464     chunksize=chunksize,
   1465 )

File E:\python\lib\site-packages\pandas\io\sql.py:1003, in SQLTable.read(self, coerce_float, parse_dates, columns, chunksize)
   1001 else:
   1002     sql_select = select(self.table)
--> 1003 result = self.pd_sql.execute(sql_select)
   1004 column_names = result.keys()
   1006 if chunksize is not None:

File E:\python\lib\site-packages\pandas\io\sql.py:1405, in SQLDatabase.execute(self, *args, **kwargs)
   1403 def execute(self, *args, **kwargs):
   1404     """Simple passthrough to SQLAlchemy connectable"""
--> 1405     return self.connectable.execution_options().execute(*args, **kwargs)

AttributeError: 'OptionEngine' object has no attribute 'execute'

注：数据库无密码，已安装PyMySQL。

解决方案 #

该错误源于SQLAlchemy 2.x版本变更了执行API，而旧版本pandas尚未适配。可通过以下任意一种方式解决：

• 降级SQLAlchemy到1.x兼容版本
  执行命令：

  pip install sqlalchemy==1.4.49
  

  安装完成后重新运行原代码即可。

• 改用read_sql_query替代read_sql_table
  直接编写SELECT查询语句，代码修改为：

  import pandas as pd
  import sqlalchemy
  
  engine = sqlalchemy.create_engine("mysql+pymysql://root:@localhost:3306/haider")
  df = pd.read_sql_query("SELECT * FROM ahmed", engine)
  

• 适配SQLAlchemy 2.x的连接方式
  通过手动获取连接对象传递给read_sql_table，代码修改为：

  import pandas as pd
  import sqlalchemy
  
  engine = sqlalchemy.create_engine("mysql+pymysql://root:@localhost:3306/haider")
  with engine.connect() as conn:
      df = pd.read_sql_table("ahmed", conn)
  

内容的提问来源于stack exchange，提问作者Ch M Haider Ali gujjer