如何跳过Pandas to_sql插入SQL时的无效日期行避免程序失败？

遇到这种因无效日期导致的插入失败，最高效的方式是提前预处理DataFrame，过滤掉不符合SQL Server datetime类型要求的行，而不是让程序崩溃后再处理。下面是具体的解决步骤和代码示例：

第一步：理解问题根源 #

SQL Server的datetime类型有严格的范围限制：1753-01-01 00:00:00 到 9999-12-31 23:59:59。你遇到的'1900-01-01T00:00:00Z'虽然日期数值在范围内，但可能是带Z的UTC字符串格式导致SQL Server解析失败，或是API返回的无效占位符值。

第二步：预处理DataFrame，过滤无效行 #

我们可以先将日期字段转换为Pandas的datetime类型，把无法解析的无效值转为NaT（Not a Time），然后过滤掉这些行。

import pandas as pd

# 1. 假设你已经从API获取到JSON数据并转为DataFrame，命名为df
# df = pd.read_json(api_response)

# 2. 列出所有需要处理的日期字段（根据你的表结构）
date_fields = [
    'ConfidentialDate', 'CreatedDate', 'CurrentStatusDate', 
    'DeletedDate', 'LicenseDate', 'OnProductionDate', 
    'SpudDate', 'UpdatedDate'
]

# 3. 将日期字段转为datetime类型，无效值强制转为NaT
df[date_fields] = df[date_fields].apply(
    pd.to_datetime, 
    errors='coerce',  # 无法解析的转为NaT
    utc=True          # 处理带Z的UTC格式
)

# 4. 过滤掉有问题的行：这里我们只过滤CurrentStatusDate字段的无效值（根据你的错误来源）
df_clean = df[~df['CurrentStatusDate'].isna()]

# 如果需要过滤所有日期字段都有效的行，用下面的代码：
# df_clean = df.dropna(subset=date_fields)

第三步：插入清理后的数据到SQL Server #

现在用to_sql插入清理后的DataFrame，就不会再触发范围错误了：

from sqlalchemy import create_engine

# 替换为你的数据库连接信息
engine = create_engine(
    'mssql+pyodbc://<用户名>:<密码>@<服务器名>/<数据库名>?driver=ODBC+Driver+11+for+SQL+Server'
)

# 写入数据库，if_exists可选'replace'或'append'
df_clean.to_sql(
    'TEMP_well_origin', 
    engine, 
    if_exists='append', 
    index=False,
    method='multi'  # 批量插入，提升效率
)

备选方案：逐行插入并跳过错误行 #

如果不确定哪些字段有问题，或者不想提前过滤，可以用try-except块捕获错误，逐行插入并跳过失败的行（适合数据量较小的场景）：

from sqlalchemy.exc import DataError

with engine.connect() as conn:
    trans = conn.begin()
    try:
        # 先尝试批量插入
        df.to_sql('TEMP_well_origin', conn, if_exists='append', index=False)
        trans.commit()
    except DataError:
        trans.rollback()
        print("批量插入失败，尝试逐行插入并跳过错误行...")
        for idx, row in df.iterrows():
            try:
                row.to_frame().T.to_sql('TEMP_well_origin', conn, if_exists='append', index=False)
            except DataError:
                print(f"跳过行 {idx}：日期格式无效")
                continue
        conn.commit()

关键提示 #

• 尽量使用预处理过滤的方式，逐行插入效率极低，不适合大数据量。
• 始终将日期字段转为Pandas的datetime类型后再插入，避免直接传递字符串导致SQL Server解析错误。

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