如何优化300万条记录的python postgresql查询

Question

我正在使用python脚本在postgresql中插入或更新大约300万到400万个数据。请参阅下面的代码。如果密钥的新密钥已存在，则要求插入；如果密钥已经存在，则需要使用新值更新密钥。但是下面的代码是做了太多的往返连接到数据库，它需要大约35-45分钟才能在数据库中插入300万条记录，这是非常缓慢的。如何避免往返连接，以更快的方式插入或更新？在

任何帮助都将不胜感激。在

提前谢谢你的帮助。在

输入文件.txt-此文件大约有300万到400万行itesm

productKey1 printer1,printerModel1,printerPrice1,printerDesc1|
productKey2 sacnner2,scannerModel2,scannerPrice2,scannerDesc2|
productKey3 mobile3,mobileModel3,mobilePrice3,mobileDesc3|
productKey4 tv4,tvModel4,tvPrice4,tvDescription4|
productKey2 sacnner22,scannerModel22,scannerPrice22,scannerDesc22|

插入.py

^{pr2}$

Answer 1

执行成批准备好的语句。http://initd.org/psycopg/docs/extras.html#fast-execution-helpers

import psycopg2, psycopg2.extras
def insertProduct(filename, conn):

    data = []
    with open(filename) as f:
        for line in f:
            line = line.strip()
            if line:
                key, value = line.split(' ', 1)
                data.append((key, value))

    cursor = conn.cursor()
    cursor.execute("""
        prepare upsert (text, text) as
        with i as (
            insert into productTable (key, value)
            select $1, $2
            where not exists (select 1 from productTable where key = $1)
            returning *
        )
        update productTable p
        set value = concat (p.value, $2)
        where p.key = $1 and not exists (select 1 from i)
    """)
    psycopg2.extras.execute_batch(cursor, "execute upsert (%s, %s)", data, page_size=500)
    cursor.execute("deallocate upsert")
    conn.commit()          

conn = psycopg2.connect(database='cpn')
insertProduct('InputFile.txt', conn)