如何在Python中向MariaDB批量插入列表形式的多组数据

问题场景 #

需要用Python向MariaDB批量插入数据，插入语句需5个参数，希望直接传入包含所有待插入数据的列表event_list，但直接传入时触发错误：

mariadb.ProgrammingError: statement (5) doesn't match the number of data elements (23305).

event_list的每条数据为包含5个元素的元组，示例：

('A2', '84', '13', '2022-12-06T10:34:12.867Z', 'e9af9383-2d7f-4963-88c9-38aa1d9c33cc')
('A9', None, None, '2022-12-06T10:34:14.436Z', '49c10cd1-3029-4367-a557-010d5eb1334a')

当前采用逐条插入的方式效率极低，需改为批量插入，且仅以列表作为参数传入。

解决方案 #

MariaDB的Python驱动原生支持批量插入，核心是构造匹配数据条数的批量插入SQL语句，再将列表作为参数传入。具体实现如下：

1. 修改批量插入的insert_data方法 #

@classmethod
def insert_data(cls, event_list):
    """批量向数据库添加事件数据

    Args:
        event_list: 待插入事件列表，每个元素为(road_name, avg_speed, flow_count, ts_event, uuid)元组
    """
    if not event_list:
        return  # 空列表直接返回，避免无效SQL执行
    
    # 构造批量插入的VALUES占位符：每条数据对应一组(?, ?, ?, ?, ?)
    placeholders = ", ".join(["(?, ?, ?, ?, ?)"] * len(event_list))
    sql = f"INSERT INTO Events(road_name, avg_speed, flow_count, ts_event, uuid) VALUES {placeholders}"
    
    try:
        # 若DB.create方法底层支持executemany批量操作，直接传入event_list
        DB.create(sql, event_list)
        # 若DB.create不支持批量，需直接操作游标：
        # conn = DB.get_connection()
        # cursor = conn.cursor()
        # cursor.executemany(sql, event_list)
        # conn.commit()
        # cursor.close()
        # conn.close()
    except mariadb.Error as e:
        print(f"批量插入失败: {e}")
        return e

2. 数据生成与调用逻辑（保持原代码即可） #

@classmethod
def add_all_events(cls):
    with open('../refactored_ndw_data.json') as json_file:
        combined_events = json.load(json_file)
    event_list = list()
    for event in tqdm(combined_events['events']):
        try:
            road_name = event['lanelocation']['road']
            avg_speed = event["avgspeed"].get("kmph")
            flow_count = event["flow"].get("count")
            ts_event = event['ts_event']
            uuid = event['measuring_point_id'].get("uuid")
        except Exception:
            continue
        event_list.append((road_name, avg_speed, flow_count, ts_event, uuid))
    EventModel.insert_data(event_list)

关键说明 #

• 错误根源：原代码仅写了一组(?, ?, ?, ?, ?)占位符，却传入了所有元组展开后的总元素（23305个），导致参数数量不匹配。批量插入需为每条数据对应一组占位符。
• None值处理：MariaDB驱动会自动将Python的None转换为SQL的NULL，无需额外处理。
• 性能优化：批量插入大幅减少数据库交互次数，若数据量极大，可分批次插入（如每1000条一批），避免单条SQL过长。

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