sqlite3/pandas日期格式异常：strftime返回None，无法生成YY-MM格式

我想在Python中通过SQL查询DataFrame，返回YY-MM格式的日期，但尝试多次都没得到正确结果。现有脚本如下：

import pandas as pd
import sqlite3
import os

desktop_path = os.path.expanduser("path")

csv_files = [ 'file1.csv','file2.csv'
]

dataframes =[]

for file in csv_files:
    file_path = os.path.join(desktop_path, file)
    df = pd.read_csv(file_path, header=0)
    dataframes.append(df)

merged_df = pd.concat(dataframes)

#Convert Date string to datetime
merged_df['Date_alternate'] = pd.to_datetime(merged_df['Date'])
print(merged_df['Date']) #prints formatted as MM/DD/YYYY, dtype: object
print("Break")
print(merged_df['Date_alternate']) #prints formatted as YYYY-MM-DD, dtype: datetime64[ns]

db_file= "path"

conn = sqlite3.connect(db_file)
merged_df.to_sql('my_table', conn, index=False)

query = "select strftime('%y-%m',my_table.Date), strftime('%Y-%m',my_table.Date_alternate),Date, Date_alternate from my_table limit 500"

query_result = pd.read_sql_query(query,conn)
print(query_result)
query_result.to_csv('path', index=False)
conn.close()

理想状态下，查询结果的前两列应该返回YY-MM格式的日期，但使用strftime的两列都返回None。Date列仍显示MM/DD/YYYY格式，Date_alternate列显示数字时间戳，示例如下：

02/19/2021 | 1613624400
02/28/2021 | 1614488400

问题根源是SQLite的strftime函数仅对TEXT类型的日期字符串或SQLite原生日期类型生效，你的数据在写入数据库时出现了类型不匹配：

• Date列是字符串，但格式为MM/DD/YYYY，SQLite无法识别为有效日期格式
• Date_alternate列的datetime64类型被pandas默认转为Unix时间戳整数，无法被strftime直接处理

以下是三种可行的解决方法：

方法1：在Python中提前格式化日期 #

直接利用pandas的dt.strftime生成目标格式列，无需在SQL中处理：

# 生成YY-MM格式的新列
merged_df['YY_MM'] = merged_df['Date_alternate'].dt.strftime('%y-%m')

# 写入数据库后直接查询该列
merged_df.to_sql('my_table', conn, index=False)
query = "SELECT YY_MM, Date, Date_alternate FROM my_table LIMIT 500"
query_result = pd.read_sql_query(query, conn)

方法2：修改数据库写入逻辑，保留日期格式 #

通过注册适配器，让pandas将datetime对象以SQLite可识别的ISO字符串格式写入：

from sqlite3 import register_adapter
import datetime

# 注册适配器，将datetime转为ISO格式字符串
def adapt_datetime(dt):
    return dt.isoformat()

register_adapter(datetime.datetime, adapt_datetime)

# 写入数据库
conn = sqlite3.connect(db_file)
merged_df.to_sql('my_table', conn, index=False)

# 此时可正常使用strftime
query = "SELECT strftime('%y-%m', Date_alternate) AS YY_MM FROM my_table LIMIT 500"
query_result = pd.read_sql_query(query, conn)

方法3：在SQL中转换时间戳为日期 #

如果不想修改写入逻辑，可在SQL中将Unix时间戳转为日期后再格式化（SQLite的datetime函数默认处理秒级时间戳）：

SELECT 
  strftime('%y-%m', datetime(Date_alternate, 'unixepoch')) AS YY_MM,
  Date,
  Date_alternate 
FROM my_table LIMIT 500

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