Pandas 0.24 read_sql 操作错误 [英] Pandas 0.24 read_sql operational errors

问题描述

我刚刚从 0.23.4 (Python 2.7.12) 升级到 Pandas 0.24.0，我的许多 pd.read_sql 查询都中断了.看起来像是与 MySQL 相关的东西，但奇怪的是这些错误仅在更新我的 Pandas 版本后才会发生.任何想法发生了什么?

I just upgraded to Pandas 0.24.0 from 0.23.4 (Python 2.7.12), and many of my pd.read_sql queries are breaking. It looks like something related to MySQL, but it's strange that these errors only occur after updating my pandas version. Any ideas what's going on?

这是我的 MySQL 表:

Here's my MySQL table:

CREATE TABLE `xlations_topic_update_status` (
  `run_ts` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这是我的查询:

import pandas as pd
from sqlalchemy import create_engine
db_engine = create_engine('mysql+mysqldb://<><>/product_analytics', echo=False)
pd.read_sql('select max(run_ts) from product_analytics.xlations_topic_update_status', con = db_engine).values[0][0]

这里是错误:

OperationalError: (_mysql_exceptions.OperationalError) (1059, "Identifier name 'select max(run_ts) from product_analytics.xlations_topic_update_status;' is too long") [SQL: 'DESCRIBE `select max(run_ts) from product_analytics.xlations_topic_update_status;`']

对于其他更复杂的查询，我也得到了这个，但不会在这里发布.

I've also gotten this for other more complex queries, but won't post them here.

推荐答案

根据 documentation 第一个参数是字符串(表名)或 SQLAlchemy Selectable(select 或 text 对象).换句话说 pd.read_sql() 委托给 pd.read_sql_table() 并将整个查询字符串视为表标识符.

According to documentation the first argument is either a string (a table name) or SQLAlchemy Selectable (select or text object). In other words pd.read_sql() is delegating to pd.read_sql_table() and treating the entire query string as a table identifier.

将查询字符串包裹在 text() 先构造:

Wrap your query string in a text() construct first:

stmt = text('select max(run_ts) from product_analytics.xlations_topic_update_status')
pd.read_sql(stmt, con = db_engine).values[0][0]

这样pd.read_sql() 将委托给 pd.read_sql_query() 代替.另一种选择是直接调用它.

This way pd.read_sql() will delegate to pd.read_sql_query() instead. Another option is to call it directly.

这篇关于Pandas 0.24 read_sql 操作错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！