在使用python的postgres中，如果主键或id相同，如何追加值

Question

我正在尝试使用python脚本向postgresql中插入大约5000万个数据。我有一个包含5000万条记录的文件。我对PostgreSQL和Python也是完全陌生的。我尝试在python中插入下面的代码，我在这里面临一个挑战。我的测试.txt包含如下所示的键值对。你知道吗

如果同一个键在文本文件中出现两次，我想用现有的值附加该值。我不知道用python怎么做。你能请人帮忙吗？你知道吗

myfile.txt文件

key1 item1,product1,model1,price1|
key2 item2,product2,model2,price2|
key3 item3,product3,model3,price3|
key4 item4,product4,model4,price4|
key2 item22,product22,model22,price22|

在本例中，key2有两个记录—在插入DB时，我必须用第一个值附加第二个值。你知道吗

表格列：

key  value
key1 item1,product1,model1,price1|
key2 item2,product2,model2,price2|item22,product22,model22,price22|
key3 item3,product3,model3,price3|
key4 item4,product4,model4,price4|

插入.py

import psycopg2

def insertToDB(fileName):
  conn = psycopg2.connect("dbname='mydb' user='testuser' host='localhost'")
  with open(fileName) as f:
     for line in f:
       k,v = line.split(' ',1)
       cursor = conn.cursor()
       query = "INSERT INTO mytable (key,value) VALUES (%s,%s);"
       data = (key,value)
       cursor.execute(query,data)
       conn.commit()

 insertfile('myfile.txt')

我有大约5000万个数据，大多数密钥可能有不同记录的重复密钥，如何处理这些数据，以及如何高效地写入数据库？你知道吗

如果有人能建议你即兴创作，那真的很有帮助吗？你知道吗

谢谢你！你知道吗

Answer 1

最简单的方法是使用sqlinsert语句的^{}子句。这会将您的简单插入更改为“upsert”（插入或更新）。你知道吗

ON CONFLICT需要PostgreSQL 9.5或更高版本，使用方式如下：

query = """INSERT INTO mytable (key,value)
           VALUES (%s,%s)
           ON CONFLICT (key)
           DO UPDATE SET value = CONCAT(users.value, %s);"""
cursor.execute(query, (key, value, value))

另一种选择是在将结果发送到数据库之前通过重构数据来连接它们。在这里，我在字典中按键收集所有行，然后在插入时，我将把所有值连接在一起。你知道吗

这样，每个键只有一个insert。你知道吗

下面是一些代码来解释这一点：

from collections import defaultdict
import psycopg2

def get_records(filename):
   records = defaultdict(list)
   with open(filename) as f:
     for line in f:
        if line.strip():
          key, value = line.split(' ',1)
          records[key].append(value)
   return records

def insert_records(records, conn):
   q = "INSERT INTO mytable (key, value) VALUES (%s, %s);"
   cursor = conn.cursor()
   for key, data in records.items():
      cursor.execute(q, (key, ''.join(data)))
      conn.commit()

conn = psycopg2.connect("dbname='mydb' user='testuser' host='localhost'")
insert_records(get_records('myfile.txt'), conn)

如果您有大量的记录，可能是因为您一次加载整个文件耗尽了内存。你知道吗

相反，您可以实现一个更简单的算法来跟踪读取的密钥。你知道吗

def insert_records(filename, conn):
   seen = set()
   cursor = conn.cursor()
   qi = "INSERT INTO mytable (key, value) VALUES (%s, %s);"
   qu = "UPDATE mytable SET value = CONCAT(value, %s) WHERE key = %s;"

   with open(filename) as f:
     for line in f:
       if line.strip():
         key, value = line.split(' ', 1)
         if key not in seen:
            # first time we see this key, do an insert
            seen.add(key)
            cursor.execute(qi, (key, value))
         else:
            # key has been processed at least once, do an update
            cursor.execute(qu, (value, key))

         conn.commit()

conn = psycopg2.connect("dbname='mydb' user='testuser' host='localhost'")
insert_records(filename, conn)