pandas 0.24 read_sql操作错误

问题描述

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

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.

推荐答案

根据文档第一个参数是字符串(表名)或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操作错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！