使用executemany()插入datetime值失败，报sqlite3.InterfaceError错误

问题根源 #

1. 数据类型不兼容：yfinance返回的日期是pandas的Timestamp类型，sqlite3默认无法直接识别该类型，导致绑定参数失败。
2. 字段完全不匹配：插入语句指定了id、data_vendor_id等13个字段，但从yfinance获取的数据里根本没有这些字段，直接传入会导致参数数量、类型全部错位。

修复步骤 #

1. 修正字段匹配问题 #

首先明确：daily_data表的id如果是自增主键，不需要手动传入，让数据库自动生成。另外需要补充data_vendor_id、security_id、last_updated这些缺失字段：

• data_vendor_id：假设你的data_vendor表中Yahoo对应的ID是1，固定传入即可。
• security_id：根据当前ticker从securities表查询对应ID。
• last_updated：用当前系统时间填充。
• returns：yfinance不返回该字段，可设为NULL或后续自行计算。

修改主循环逻辑，补充缺失字段并对齐列名：

if __name__ == "__main__":
    tickers = obtain_tickers()
    with con:
        cur = con.cursor()
        for t in tickers:
            # 查询当前ticker对应的security_id
            cur.execute("SELECT id FROM securities WHERE ticker = ?", (t,))
            security_id = cur.fetchone()[0]
            
            yf_data = yf_request(t)
            # 补充缺失字段
            yf_data['data_vendor_id'] = 1
            yf_data['security_id'] = security_id
            yf_data['last_updated'] = datetime.datetime.now()
            yf_data['returns'] = None
            
            # 对齐数据库列名（yfinance返回的列名是大写开头，转成小写）
            yf_data.rename(columns={
                'Date':'date', 
                'Open':'open', 
                'High':'high', 
                'Low':'low', 
                'Close':'close', 
                'Volume':'volume', 
                'Dividends':'dividends', 
                'Stock Splits':'stock_split'
            }, inplace=True)
            
            insert_into_db(yf_data)

2. 处理Timestamp类型转换 #

有两种方式解决类型不兼容问题：

方式一：手动转换为datetime对象

在yf_request函数里把Timestamp转成Python标准的datetime对象：

def yf_request(ticker):
    data = yf.Ticker(ticker).history("max").reset_index()
    # 将pandas Timestamp转为datetime对象
    data['Date'] = data['Date'].dt.to_pydatetime()
    return data

方式二：注册sqlite3适配器（无需手动转换）

在连接数据库之前，注册一个适配器让sqlite3自动识别Timestamp类型：

import sqlite3
from pandas import Timestamp

# 注册Timestamp适配器
def adapt_timestamp(ts):
    return ts.isoformat()

sqlite3.register_adapter(Timestamp, adapt_timestamp)

# 之后再连接数据库
con = sqlite3.connect('sec_master.db') 

3. 修改插入函数 #

更新insert_into_db，只插入需要的字段，避免参数错位：

def insert_into_db(data):
    # 去掉自增的id字段，只保留需要插入的列
    column_str = """data_vendor_id, security_id, last_updated, date, open, high, low, close, volume, dividends, stock_split, returns""" 
    insert_str = ("?, " * 12)[:-2]
    final_str = "INSERT INTO daily_data (%s) VALUES (%s)" % (column_str, insert_str)  

    with con:
        cur = con.cursor()
        # 提取对应列的数据转为列表
        insert_rows = data[column_str.split(', ')].values.tolist()
        cur.executemany(final_str, insert_rows)

关键注意事项 #

• 确保数据库表daily_data的列类型与插入值匹配：date列设为DATETIME类型，last_updated同理。
• 如果id不是自增主键，需要补充生成唯一ID的逻辑，但通常建议设为自增主键简化操作。

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