如何将psycopg2中execute批量插入代码改写为executemany版本？

原问题 #

我当前使用psycopg2的execute方法循环插入数据，代码如下，请问如何将其改写为使用executemany方法的版本？

import psycopg2
import os
from dotenv import load_dotenv
load_dotenv()


urlstocopy = return_list_urls2(1300000)



def main():
# fake values

    conn_string = "host='db-etherator-do-user--0.b.db.oean.com' port='60' dbname='etherr2' user='in' password='sadf'"
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    # convert them into list of tuples
    x = 1
    for link in urlstocopy:
        values = (x, '', '', '', '', link, '2022-10-10', '2022-10-10')
        sql = f'''insert into accounts_website(id, business_name, industry, trade, acc_notes,website,created_on,updated) values{str(values)}'''
        cursor.execute(sql)
        conn.commit()
        print(x)
        x = x+1
    conn.close()
if __name__ == "__main__":
    main()

改写方案 #

使用executemany的核心是预先构造好所有插入参数的列表，通过参数化查询批量执行插入，同时针对大数据量做分批次处理，兼顾性能与内存占用。

优化后的代码

import psycopg2
import os
from dotenv import load_dotenv
load_dotenv()

urlstocopy = return_list_urls2(1300000)

def main():
    conn_string = "host='db-etherator-do-user--0.b.db.oean.com' port='60' dbname='etherr2' user='in' password='sadf'"
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    
    insert_values = []
    batch_size = 10000  # 每批次插入的数量，可根据内存情况调整
    
    for idx, link in enumerate(urlstocopy, start=1):
        # 构造单条数据的参数元组
        values_tuple = (idx, '', '', '', '', link, '2022-10-10', '2022-10-10')
        insert_values.append(values_tuple)
        
        # 达到批次大小则执行插入并提交
        if idx % batch_size == 0:
            cursor.executemany(
                '''INSERT INTO accounts_website(id, business_name, industry, trade, acc_notes, website, created_on, updated)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s)''',
                insert_values
            )
            conn.commit()
            print(f"已完成第{idx//batch_size}批次，累计插入{idx}条数据")
            insert_values = []  # 清空当前批次列表
    
    # 处理剩余不足一个批次的数据
    if insert_values:
        cursor.executemany(
            '''INSERT INTO accounts_website(id, business_name, industry, trade, acc_notes, website, created_on, updated)
               VALUES (%s, %s, %s, %s, %s, %s, %s, %s)''',
            insert_values
        )
        conn.commit()
        print(f"插入完成，总计处理{len(urlstocopy)}条数据")
    
    conn.close()

if __name__ == "__main__":
    main()

关键要点

• 参数化查询：用%s作为占位符，避免字符串拼接带来的SQL注入风险，同时提升数据库执行效率。
• 分批次插入：针对130万条大数据量，设置合理的批次大小（如1万条），避免一次性加载所有数据导致内存溢出。
• 批量提交：原代码每次循环都执行commit，会产生大量IO开销，批量提交能显著提升插入速度。
• 简化索引维护：用enumerate自动生成从1开始的索引，替代手动维护x变量，代码更简洁可靠。

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