Psycopg2,Postgresql,Python:批量插入的最快方法 [英] Psycopg2, Postgresql, Python: Fastest way to bulk-insert
问题描述
我正在寻找将数百万个元组批量插入数据库的最有效方法.我正在使用Python,PostgreSQL和 psycopg2 .
I'm looking for the most efficient way to bulk-insert some millions of tuples into a database. I'm using Python, PostgreSQL and psycopg2.
我已经创建了很长一段时间的流氓列表,应该将其插入数据库中,有时还会使用诸如几何Simplify
之类的修饰符.
I have created a long list of tulpes that should be inserted to the database, sometimes with modifiers like geometric Simplify
.
最简单的方法是对INSERT
语句列表进行字符串格式化,但是我还了解了三种其他方法:
The naive way to do it would be string-formatting a list of INSERT
statements, but there are three other methods I've read about:
- 使用
pyformat
绑定样式参数插入 - 在元组列表中使用
executemany
,并且 - 使用将结果写入文件并使用
COPY
.
- Using
pyformat
binding style for parametric insertion - Using
executemany
on the list of tuples, and - Using writing the results to a file and using
COPY
.
第一种方法似乎是最有效的,但是我很感谢您的见解和代码片段,告诉我如何正确进行操作.
It seems that the first way is the most efficient, but I would appreciate your insights and code snippets telling me how to do it right.
推荐答案
是的,如果您可以将文件写入 server 的硬盘驱动器(而不是驱动器应用程序正在运行),因为COPY只会读取服务器.
Yeah, I would vote for COPY, providing you can write a file to the server's hard drive (not the drive the app is running on) as COPY will only read off the server.
这篇关于Psycopg2,Postgresql,Python:批量插入的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!