导入数据时触发ValueError: year -1 is out of range错误的技术求助

问题场景 #

使用psycopg2和pandas从PostgreSQL数据库提取数据时，触发ValueError: year -1 is out of range错误。已知待处理的时间戳数据存在负年份、空值这类无效值，现有代码的错误处理逻辑未能解决问题。

问题原因 #

现有代码先执行了SELECT MIN(dt_date)获取最小日期，但如果这个最小值本身是负年份这类Python datetime无法解析的无效值，psycopg2在拉取数据时会直接尝试将PostgreSQL的日期对象转换为Python datetime，这一步就会触发报错，根本走不到后续pandas的to_datetime(errors='coerce')处理环节。

解决方案 #

1. SQL层面提前过滤无效数据 #

直接在查询语句中筛选出Python datetime支持范围内的日期（Python datetime支持1~9999年），同时排除空值，从源头避免无效数据进入Python：

SELECT *
FROM prod_external.tran_y_register
WHERE entry_type = 1 
  AND dt_date IS NOT NULL
  AND dt_date >= '0001-01-01' 
  AND dt_date <= '9999-12-31'
ORDER BY dt_date;

2. 修正最小日期查询逻辑 #

如果需要基于最小有效日期拉取数据，先查询有效日期的最小值，而非所有数据的最小值：

SELECT MIN(dt_date)
FROM prod_external.tran_y_register
WHERE entry_type = 1
  AND dt_date IS NOT NULL
  AND dt_date >= '0001-01-01' 
  AND dt_date <= '9999-12-31';

3. SQL层将无效值转为NULL保留 #

如果不想过滤掉无效数据，而是要保留对应行并在后续处理，可以用CASE语句将无效日期转为NULL：

SELECT 
  *,
  CASE 
    WHEN dt_date IS NULL OR dt_date < '0001-01-01' OR dt_date > '9999-12-31' THEN NULL 
    ELSE dt_date 
  END AS dt_date_clean
FROM prod_external.tran_y_register
WHERE entry_type = 1
ORDER BY dt_date;

后续在pandas中处理dt_date_clean列即可。

修改后的完整代码 #

import psycopg2
import pandas as pd

# 假设conn已完成数据库连接初始化
cur = conn.cursor()

try:
    # 直接拉取有效范围内的日期数据
    cur.execute("""
        SELECT *
        FROM prod_external.tran_y_register
        WHERE entry_type = 1 
          AND dt_date IS NOT NULL
          AND dt_date >= '0001-01-01' 
          AND dt_date <= '9999-12-31'
        ORDER BY dt_date;
    """)
    
    rows = cur.fetchall()
    column_names = [desc[0] for desc in cur.description]
    df = pd.DataFrame(rows, columns=column_names)
    
    # 额外添加一层本地处理保险，防止SQL过滤有遗漏
    df['dt_date'] = pd.to_datetime(df['dt_date'], errors='coerce')
    df.dropna(subset=['dt_date'], inplace=True)
    
    print(df)

except psycopg2.Error as e:
    print("Error: Could not fetch data from the table")
    print(e)
except ValueError as e:
    # 捕获可能遗漏的日期转换错误
    print("Error: Invalid date value encountered")
    print(e)

# 关闭资源
cur.close()
conn.close()

内容的提问来源于stack exchange，提问作者Yogesh Govindan