使用COPY将CSV数据传输到PostgreSQL以获得更好的性能，而不是“write to file”？

Question

我在一个包含2500行的CSV文件中有一个数据集。文件的结构（简化）如下：

id_run; run_name; receptor1; receptor2; receptor3_value; [...]; receptor50_value

文件的每个接收器都已在一个表中，并且具有唯一的id

我需要用以下格式将每一行上载到表中：

id_run; id_receptor; receptor_value
1; 1; 2.5
1; 2; 3.2
1; 3, 2.1
[...]
2500, 1, 2.4
2500, 2, 3.0
2500, 3, 1.1

实际上，我把需要上传的所有数据都写在一个.txt文件中，我使用postgreSQL中的COPY命令将文件传输到目标表。在

对于2500次运行（CSV文件中的2500行）和50个接收器，我的Python程序在要上载的文本文件中生成大约110000条记录。在

我将删除目标表的外键，并在上载后恢复它们。在

使用此方法，生成文本文件实际上需要大约8秒，将文件复制到表中需要1秒。在

有没有一种方法、方法、库或其他任何东西可以用来加速上传数据的准备，这样90%的时间都不是用来写文本文件的？在

编辑：

这是我的（更新）代码。我正在用一个大容量的文件来写。它看起来更快（上传了11万行在3.8秒）。在

^{pr2}$

编辑2：

使用cStringIO库，我用一个类似文件的对象代替了临时文本文件的创建，但是速度的提高非常小。在

代码更改：

outf = cStringIO.StringIO()
for rec_i, rec in enumerate(rec_uids):
    outf.write('%s %s %s\n' % (sc_uid, rec, rec_values[line_i][rec_i]))

cur.copy_from(outf, 'receptor_results')

Answer 1

是的，您可以做一些事情来加快提前将数据写入文件的速度：不用麻烦！在

您已经将数据放入内存，所以这不是问题。因此，与其将这些行写入字符串列表，不如将它们写入一个稍微不同的对象StringIO实例。然后数据可以保存在内存中，并且作为psycopg2的copy_from函数的参数。在

filelike = StringIO.StringIO('\n'.join(['1\tA', '2\tB', '3\tC']))
cursor.copy_from(filelike, 'your-table-name')

请注意，StringIO必须包含换行符、字段分隔符等-就像文件中所包含的那样。在

Answer 2

I'm writing all the data I need to upload in a .txt file and I'm using the COPY command from postgreSQL to transfer the file to the destination table.

对于你所有的数据来说，这是一个沉重而不必要的往返。因为内存中已经有它，所以应该直接将其转换为多行插入：

INSERT INTO table(col1, col2) VALUES (val1, val2), (val3, val4), ...

也就是说，将数据连接到这样的查询中并按原样执行。在

在您的例子中，您可能会根据您的需求生成并执行50个这样的插入，每个插入包含2500行。在

它将是性能最好的解决方案；）