如何将DataFrame的datetime索引以date类型写入PostgreSQL表

方法一：转换索引类型并指定字段映射 #

直接调整DataFrame索引的类型，同时在写入时指定PostgreSQL字段类型：

1. 将原datetime64[s]索引转换为仅保留日期部分的类型：

   # 若索引是DatetimeIndex，直接提取日期
   df.index = df.index.dt.date
   

2. 写入数据库时，通过dtype参数指定索引字段为date类型：

   from sqlalchemy import Date
   
   # 自定义字段类型映射，index_label指定索引列名（按需修改）
   dtype_map = {'trade_date': Date}
   df.to_sql(
       'quote',
       con=engine,
       index=True,
       index_label='trade_date',
       dtype=dtype_map
   )
   

   执行后，PostgreSQL中的trade_date字段会被创建为date类型，显示格式为YYYY-MM-DD，且不是text类型。

方法二：先创建表结构再写入数据 #

如果需要更精细的表结构控制，可以先手动定义表，再写入数据：

1. 用SQLAlchemy创建指定结构的表：

   from sqlalchemy import Table, Column, Date, MetaData, Float
   
   metadata = MetaData()
   # 按实际需求定义其他字段
   quote_table = Table(
       'quote',
       metadata,
       Column('trade_date', Date, primary_key=True),
       Column('close_price', Float)
   )
   # 生成表
   metadata.create_all(engine)
   

2. 转换索引后追加数据：

   df.index = df.index.dt.date
   df.to_sql(
       'quote',
       con=engine,
       index=True,
       index_label='trade_date',
       if_exists='append',
       dtype={'trade_date': Date}
   )
   

关键注意事项 #

• 避免使用df.index.astype(str)转换索引，这会导致PostgreSQL字段类型变为text，不符合需求。
• 确保索引转换后是datetime.date对象或仅含日期的DatetimeIndex，配合sqlalchemy.Date类型映射，才能让PostgreSQL识别为date类型。

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