Python操作SQLite3报Incorrect number of bindings supplied错误

问题描述 #

• 场景：使用Python内置sqlite3模块向表中批量插入接口返回的航班数据时，持续触发参数绑定数量不匹配错误
• 已尝试操作：先后将传参格式调整为(dataset)、(dataset,)、[dataset]三种形式，均无法解决问题
• 完整复现代码：

import requests
import json
import datetime
import sqlite3

# 获取接口数据
url = 'https://opensky-network.org//api/flights/departure?airport=EDDF&begin=1517227200&end=1517230800'
content = requests.get(url).content
dataset = json.loads(content)

# 创建数据表
try:
    db = sqlite3.connect('Flights')
    cursor = db.cursor()
    cursor.execute('''create table flights(icao24 VARCHAR(50), firstSeen VARCHAR(50), estDepartureAirport VARCHAR(50), lastSeen VARCHAR(50), estArrivalAirport VARCHAR(50), callsign VARCHAR(50), estDepartureAirportHorizDistance VARCHAR(50), estDepartureAirportVertDistance VARCHAR(50), estArrivalAirportHorizDistance VARCHAR(50), estArrivalAirportVertDistance VARCHAR(50), departureAirportCandidatesCount VARCHAR(50), arrivalAirportCandidatesCount VARCHAR(50))''')
except Exception as E:
    print('Error:', E)
else:
    print ('Table created')

# 插入数据（问题代码段）
try:
    cursor.executemany('insert into flights(icao24, firstSeen, estDepartureAirport, lastSeen, estArrivalAirport, callsign, estDepartureAirportHorizDistance, estDepartureAirportVertDistance, estArrivalAirportHorizDistance, estArrivalAirportVertDistance, departureAirportCandidatesCount, arrivalAirportCandidatesCount) values (?,?,?,?,?,?,?,?,?,?,?,?)', (dataset,))
except Exception as E:
    print('Error:', E)
else:
    db.commit()
    print ('Data inserted')

报错根因 #

• cursor.executemany()对传入参数的格式有明确要求：第二个参数必须是嵌套可迭代对象，外层序列的每个元素对应一条待插入的记录，单个元素的长度必须和SQL语句中占位符的数量完全一致。
• 接口返回的dataset本身已经是存储多条航班记录的列表，但列表内的元素是字典结构，不是sqlite3 ?占位符要求的顺序元组/列表。
• 给dataset额外套()、[]包装的操作，相当于把整个dataset当成单条记录传给方法，单条记录的长度和12个占位符完全不匹配，必然触发报错。

修复方法 #

方法1：按位置参数格式组装数据 #

遍历接口返回的字典列表，按照表字段的顺序把每条记录的值提取为元组，组成新的列表直接传入executemany即可，不需要额外套层包装：

# 修正后的插入逻辑
try:
    insert_rows = []
    # 按建表字段顺序提取每条记录的值
    for item in dataset:
        row = (
            item['icao24'],
            item['firstSeen'],
            item['estDepartureAirport'],
            item['lastSeen'],
            item['estArrivalAirport'],
            item['callsign'],
            item['estDepartureAirportHorizDistance'],
            item['estDepartureAirportVertDistance'],
            item['estArrivalAirportHorizDistance'],
            item['estArrivalAirportVertDistance'],
            item['departureAirportCandidatesCount'],
            item['arrivalAirportCandidatesCount']
        )
        insert_rows.append(row)
    # 直接传入组装好的行列表
    cursor.executemany('insert into flights(icao24, firstSeen, estDepartureAirport, lastSeen, estArrivalAirport, callsign, estDepartureAirportHorizDistance, estDepartureAirportVertDistance, estArrivalAirportHorizDistance, estArrivalAirportVertDistance, departureAirportCandidatesCount, arrivalAirportCandidatesCount) values (?,?,?,?,?,?,?,?,?,?,?,?)', insert_rows)
except Exception as E:
    print('Error:', E)
else:
    db.commit()
    print ('Data inserted')

方法2：使用命名占位符直接传字典列表 #

如果不想手动按顺序提取字段，可以把SQL里的?占位符换成:字段名格式的命名占位符，sqlite3会自动匹配字典里的对应键值，不需要手动转元组：

try:
    # 占位符改为和字典key一致的命名格式
    cursor.executemany('''insert into flights(icao24, firstSeen, estDepartureAirport, lastSeen, estArrivalAirport, callsign, estDepartureAirportHorizDistance, estDepartureAirportVertDistance, estArrivalAirportHorizDistance, estArrivalAirportVertDistance, departureAirportCandidatesCount, arrivalAirportCandidatesCount) values 
    (:icao24, :firstSeen, :estDepartureAirport, :lastSeen, :estArrivalAirport, :callsign, :estDepartureAirportHorizDistance, :estDepartureAirportVertDistance, :estArrivalAirportHorizDistance, :estArrivalAirportVertDistance, :departureAirportCandidatesCount, :arrivalAirportCandidatesCount)
    ''', dataset)
except Exception as E:
    print('Error:', E)
else:
    db.commit()
    print ('Data inserted')

注意：数据库操作完成后记得主动关闭连接，避免连接资源泄漏。

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