使用set_index触发KeyError：存在timestamp列却报错

代码片段 #

query = f''' SELECT timestamp, base FROM pricing WHERE {timestamp_range} LIMIT {max_records}'''
df: DataFrame = read_sql_query(text(query), db)
print(df.head())
print(df.columns)
df.set_index('timestamp', inplace=True)  # 此处报错

运行输出 #

timestamp  base
0 2023-02-17 10:25:54.099542    21
1 2023-02-17 10:27:54.060627    21
2 2023-02-17 10:29:53.581384    22
3 2023-02-17 10:31:54.110646    20
4 2023-02-17 10:33:53.827830    20
Index(['timestamp', 'base'], dtype='object')

完整报错堆栈 #

[2023-02-17 13:50:32,388] ERROR in app: Exception on /api/data/query [GET]
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 3802, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 165, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 5745, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 5753, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'timestamp'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/ubuntu/.local/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/ubuntu/.local/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/ubuntu/Code/grozd/main.py", line 18, in query
    result = getQuery(request.args)
  File "/home/ubuntu/Code/grozd/data.py", line 27, in getQuery
    df['timestamp'] = df['timestamp'].astype(int).floordiv(1000000).astype(int)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/pandas/core/frame.py", line 3807, in __getitem__
    indexer = self.columns.get_loc(key)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 3804, in get_loc
    raise KeyError(key) from err
KeyError: 'timestamp'

原因分析 #

从堆栈日志可以明确看到，实际触发KeyError的代码行是data.py第27行的：

df['timestamp'] = df['timestamp'].astype(int).floordiv(1000000).astype(int)

而非你贴出的df.set_index那行。问题核心在于代码执行顺序：

• 若你先调用了df.set_index('timestamp', inplace=True)，该操作会将timestamp列从普通数据列转为DataFrame的索引，此时该列不再存在于df.columns列表中，后续再通过df['timestamp']访问自然会触发KeyError。
• 你提供的代码片段可能未完整展示实际运行逻辑，比如在执行上述赋值操作前已经执行了set_index，导致原列被移除。

另外也可以排查列名是否存在隐形空格：虽然print(df.columns)显示列名为'timestamp'，但数据库返回的列名可能带有前后空格，可通过df.columns = df.columns.str.strip()清理列名后再尝试操作，但从现有信息来看，执行顺序问题的可能性更大。

内容的提问来源于stack exchange，提问作者David Min