Python psycopg2使用SimpleConnectionPool数据库连接池以及execute_batch批量插入数据

有关快速插入大量数据到数据库的一个比较好的博文如下：Fastest Way to Load Data Into PostgreSQL Using Python 其中文末还有提到几种不同方式的对比，效率十分的震撼，可以看看

1. 连接池和批量插入示例代码如下

import psycopg2
import psycopg2.extras
from psycopg2.pool import SimpleConnectionPool
from contextlib import contextmanager

# 连接池初始化
postgresql_conn_pool = SimpleConnectionPool(5, 200,
        host=DATABASE_HOST,
        port=DATABASE_PORT,
        user=DATABASE_USERNAME,
        password=DATABASE_PASSWORD,
        database=DATABASE_NAME)

@contextmanager
def get_cursor():
    con = postgresql_conn_pool.getconn()
    # 默认就自动提交
    con.autocommit = True
    try:
        yield con.cursor()
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        self.postgresql_conn_pool.putconn(con)

def save_data(dict_list):
    """
    dict_list = [{"name":"lucy", "address":"shanghai"},
                 {"name":"mike", "address":"beijing"}]
    """
    with get_cursor() as cursor:
        # 批量插入
        psycopg2.extras.execute_batch(cursor, """
                                    INSERT INTO user(name,address) 
                                    VALUES
                                    (%(name)s, %(address)s)
                                """, dict_list)
    

2.使用时需要注意的问题

• 不需要再使用executemany了，该方法的性能没有execute_batch好，还有更快的则是copy from
• SimpleConnectionPool只支持单线程，如果是多线程使用线程池，请使用psycopg2.pool.ThreadedConnectionPool，参考官方链接如下psycopg2.pool - Connections pooling

3. 为什么批量就是快？

从我自己测试时，插入数据来说话，业务场景是，一条数据要保存到三张表里面，具体代码实现就不展示了。
相关数据如下：

• 每秒钟74条数据
• 批量，500条一次耗时大约在0.33s左右，即6.7s后才执行一次存储，(6.7 + 0.33) / 500 = 0.014s一条
• 不批量，一次insert单条耗时约0.048s，500 x 0.048 = 24s

那毫无疑问，批量就是快

4. 批量时的n条数据作为一个批次，n取何值效率最高？

假设如下：

• 每秒钟p条数据
• 批量情况下，n条数据耗时a秒
• 不批量，一次insert单条耗时约q秒

请问n值，如何效率最好？总而言之
n = 200, a = 0.126799, a/n = 0.00063
n = 500, a = 0.33, a/n = 0.00066
n = 1000, a=0.35, a/n = 0.00035