sqlalchemy 使用查询mysql出错

Question

在网上查了用sqlalchemy查询mysql,都提供了比较简单的方法，但是使用就报错，请大拿指导

from sqlalchemy import create_engine
engine_ts = create_engine('mysql://user:pass@127.0.0.1:3306/test?charset=utf8&use_unicode=1')
sql = 'select * from stock_basic;
df = pd.read_sql_query(sql, engine_ts)

报错数据：

File "D:\pythonProject\Trade\mysql.py", line 17, in read_data
df = pd.read_sql_query(sql, engine_ts)
File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 397, in read_sql_query
return pandas_sql.read_query(
File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 1560, in read_query
result = self.execute(args)
File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 1405, in execute
return self.connectable.execution_options().execute(args, **kwargs)
AttributeError: 'OptionEngine' object has no attribute 'execute'

Answer 1

从别的地方找到答案了，降级到2.0以下就行了，或者改脚本

最新版本的SQLAlchemy（2.0）删除了Engine.execute。目前您可能需要降级SQLAlchemy

  python -m pip install --upgrade 'sqlalchemy<2.0'

(or如果使用conda，则为等效的conda命令）。
或者，正如Gord Thompson在他的评论中指出的，用sqlalchemy.text Package 查询。

from sqlalchemy import text
# ...
with engine.begin() as conn:
    query = text("""SELECT * FROM tbl""")
    df = pd.read_sql_query(query, conn)