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

Question

我正在尝试使用 python 脚本将大约 5000 万条数据插入到 postgresql 中。我有包含 5000 万条记录的文件。我也是 PostgreSQL 和 Python 的新手。我尝试在 python 中插入下面的代码，我在这里面临一个挑战。我的 test.txt 包含如下所示的键值对。

如果同一个键在文本文件中出现两次，我想用现有的值附加值。我不确定如何在 python 中做到这一点。你能请人帮忙吗？

我的文件.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 有两条记录 - 在插入数据库时​​，我必须将第二个值附加到第一个值。

表格列:

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

最简单的方法是使用 ON CONFLICT SQL 插入语句的子句。这会将您的简单插入更改为“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))

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

这样一来，每个键只有一个插入物。

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

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)

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

相反，您可以实现一个更简单的算法来跟踪读取的 key 。

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)