pandas带UTC时区日期通过SQLAlchemy插入SQL Server报错求解

错误原因说明 #

1. 场景1报错：pandas向SQL写入带时区信息（tz-aware）的datetime对象时，默认要求统一为UTC时区，否则无法完成类型转换。
2. 场景2报错：SQL Server的TIMESTAMP类型为行版本标识类型，不是用于存储时间值的类型，且单表仅允许存在1个该类型字段。pandas识别到UTC时区的datetime字段时，默认映射为TIMESTAMP类型，建表时出现两个该类型字段触发报错。
3. 场景3时间错误：dt.tz_localize(None)会直接丢弃时区信息，不会做时区转换，比如原时间是2021-07-13 22:38:39 +01:00，直接丢时区后就变成了2021-07-13 22:38:39，和预期转换后的时间存在偏差。

解决方案 #

以下两种方案可选，根据自身存储需求选择：

方案1：存储带时区信息的时间（推荐，保留原始时区属性）

使用SQL Server专门存储带时区时间的DATETIMEOFFSET类型，写入时手动指定字段映射关系即可：

import pandas as pd
from sqlalchemy import create_engine
# 导入SQL Server对应字段类型
from sqlalchemy.dialects.mssql import DATETIMEOFFSET, BIGINT

AZUREUID = 'tb-test'                                    
AZUREPWD = 'test'                                
AZURESRV = 'tb-sql01.database.windows.net'   
AZUREDB = 'TB-test'                                      
TABLE = 'DataTable'                                       
DRIVER = 'ODBC Driver 17 for SQL Server'                 # 替换为你实际使用的驱动版本

connectionstring = 'mssql+pyodbc://{uid}:{password}@{server}:1433/{database}?driver={driver}'.format(
    uid=AZUREUID,
    password=AZUREPWD,
    server=AZURESRV,
    database=AZUREDB,
    driver=DRIVER.replace(' ', '+'))

engn = create_engine(connectionstring)

d = {"employee_no" : [1,2], "start_date" : ["2019-12-05 15:55:47 +00:00","2021-07-13 22:38:39 +01:00"] , "end_date" : ["2019-12-05 15:55:47 +00:00","2021-07-13 22:38:39 +01:00"] }
df = pd.DataFrame(data=d)

# 统一转换为UTC时区的带时区时间
df["start_date"] = pd.to_datetime(df["start_date"], utc=True)
df["end_date"] = pd.to_datetime(df["end_date"], utc=True)

# 写入时指定字段类型，避免映射为TIMESTAMP
df.to_sql(
    "test_table", 
    engn, 
    if_exists='replace',
    dtype={
        'index': BIGINT,
        'employee_no': BIGINT,
        'start_date': DATETIMEOFFSET,
        'end_date': DATETIMEOFFSET
    }
)

方案2：转换为目标时区后存储无时区的时间

如果不需要保留时区信息，先通过dt.tz_convert转换为你需要的时区，再移除时区信息，不会出现时间偏差：

# 接上述代码，已经完成utc转换后
# 示例转换为东八区时间，可替换为自身需要的时区
df["start_date"] = df["start_date"].dt.tz_convert('Asia/Shanghai').dt.tz_localize(None)
df["end_date"] = df["end_date"].dt.tz_convert('Asia/Shanghai').dt.tz_localize(None)

# 此时直接写入即可，会自动映射为DATETIME2类型
df.to_sql("test_table", engn, if_exists='replace')

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