SQLAlchemy/Pandas读写MySQL时datetime零值与NULL无法区分问题

使用Python生态的SQLAlchemy、Pandas读取MySQL/MariaDB表数据，处理后写入同结构另一数据库时，会出现datetime/timestamp类型字段值识别异常的问题：

• MySQL中datetime/timestamp字段可存储三类值：NULL、合法日期、零日期0000-00-00 00:00:00
• 原生SQLAlchemy读取时，零日期与NULL均被解析为None
• 读取为Pandas DataFrame时，两类值均被解析为NaT
• 后续调用df.to_sql()写回数据库时，原本的零日期值会被错误写入为NULL，无法保留原始字段值

示例表结构：

create table some_table
(
    userid           int auto_increment,
    username         varchar(255)        not null,
    email            varchar(255)        not null,
    lastLogin_Date   datetime            null,
    primary key (userid)
)
    collate = utf8mb4_unicode_ci;

示例表中lastLogin_Date字段实际存储值为NULL、0000-00-00 00:00:00、NULL、NULL、NULL，但SQLAlchemy读取返回结果为[(None,), (None,), (None,), (None,), (None,)]，Pandas读取结果中该列所有值均为NaT。

原有读取代码：

engine3 = create_engine(
    'mysql+mysqlconnector://' + 'root' + ':' + 'root' + 
    '@localhost:' + '3306' + '/' + 'testdb',
    echo=False)

for chunk_dataframe in pd.read_sql(
        "SELECT * FROM table_name", engine3, chunksize=10000):
    pass

核心诉求：在数据读写过程中区分datetime/timestamp类型的NULL值与零日期0000-00-00 00:00:00，写入目标库时完全保留原始值：原值为NULL则写入NULL，原值为零日期则写入0000-00-00 00:00:00。

这个问题由两层默认配置共同导致：

• MySQL驱动（mysqlconnector、pymysql等）默认开启零日期自动转换规则，会把0000-00-00 00:00:00直接转成None，和数据库原生NULL的返回值完全一致
• Pandas的datetime64类型本身不支持无效日期值，即使驱动返回了零日期，类型解析阶段也会把它和NULL一起转成NaT，读取完成后就再也无法区分两类值

整个方案的核心逻辑是在读取阶段绕过自动类型转换，从源头保留两类值的差异，分三步实现：

1. 修改数据库连接参数，关闭零日期自动转换 #

创建SQLAlchemy引擎时添加连接参数，禁止驱动把零日期自动转为None：

from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy.types import DATETIME, VARCHAR, Integer

# 源库引擎配置
source_engine = create_engine(
    'mysql+mysqlconnector://root:root@localhost:3306/testdb',
    echo=False,
    connect_args={
        # 允许零日期格式
        "allow_zero_in_dates": True,
        # 关闭零日期自动转None的规则
        "zero_datetime_to_none": False
    }
)

# 目标库引擎配置，和源库保持一致
target_engine = create_engine(
    'mysql+mysqlconnector://root:root@localhost:3306/target_db',
    echo=False,
    connect_args={
        "allow_zero_in_dates": True,
        "zero_datetime_to_none": False,
        # 临时修改会话级sql_mode，允许写入零日期，不影响全局配置
        "init_command": "SET SESSION sql_mode = 'ALLOW_INVALID_DATES'"
    }
)

如果使用pymysql驱动，对应调整connect_args参数即可：

connect_args={
    "charset": "utf8mb4",
    "init_command": "SET SESSION sql_mode = 'ALLOW_INVALID_DATES'"
}

2. 读取时指定datetime字段按字符串解析，保留值差异 #

不要使用pd.read_sql默认的类型推断逻辑，提前识别表中所有datetime/timestamp类型字段，读取时指定这些字段按字符串类型加载，从源头避免值被转成NaT：

# 提前整理表中所有datetime/timestamp类型的字段名
datetime_cols = ["lastLogin_Date"]

all_chunks = []
for chunk in pd.read_sql(
    "SELECT * FROM some_table",
    source_engine,
    chunksize=10000,
    # 核心配置：datetime字段按字符串读取，不自动解析为datetime64类型
    dtype={col: str for col in datetime_cols}
):
    # 可在此处添加其他数据处理逻辑，注意不要将字符串格式的零日期转为空值
    all_chunks.append(chunk)

df = pd.concat(all_chunks, ignore_index=True)

读取完成后可以验证值差异：pd.NA/None对应原库的NULL，字符串"0000-00-00 00:00:00"对应原库的零日期，其余正常格式字符串对应合法日期，三类值完全可区分。

3. 写入时明确指定字段类型，避免值被自动转换 #

调用to_sql写入时明确指定字段类型映射，不要让Pandas自动推断类型导致零日期被转成NULL：

# 配置字段类型映射，datetime字段明确指定为DATETIME类型
dtype_map = {
    "userid": Integer,
    "username": VARCHAR(255),
    "email": VARCHAR(255),
    "lastLogin_Date": DATETIME
}

df.to_sql(
    name="some_table",
    con=target_engine,
    if_exists="append",
    index=False,
    dtype=dtype_map,
    method="multi"
)

注意：如果数据处理过程中需要对合法日期做计算，可以单独把非零日期、非NULL的值转成datetime类型处理，处理完成后再转回字符串，零日期和NULL值保持原样即可，不要参与日期计算。

内容的提问来源于stack exchange，提问作者Коля Акулич