Psycopg2, Postgresql, Python: 最快的批量插入方式

Question

我正在寻找一种将数百万个元组批量插入数据库的最有效方法。我正在使用Python、PostgreSQL和psycopg2。

我已经创建了一个长列表，其中包含应该插入到数据库中的元组，有时还包含几何Simplify等修饰符。

最朴素的方法是对一系列INSERT语句进行字符串格式化处理，但我读到了另外三种方法：

1. 使用pyformat绑定风格进行参数插入
2. 对元组列表使用executemany，以及
3. 将结果写入文件并使用COPY。

似乎第一种方法最高效，但我很感激您的见解和代码片段，告诉我如何正确地执行它。

- Adam Matan

Answer 1

是的，我会投票给COPY，前提是您可以将文件写入服务器硬盘（而不是运行应用程序的驱动器），因为COPY只会从服务器读取。

- Andy Shellam

---

使用psycopg2的cursor.copy_from方法，文件由客户端处理。它甚至不需要是文件系统文件：任何类似python文件的对象都可以正常工作。请参见http://initd.org/psycopg/docs/cursor.html#cursor.copy_from。 - piro

在这种情况下，看到它实际上是如何插入数据库的将会很有趣 - 我的印象是PostgreSQL的COPY只能从服务器的本地文件系统中读取，并且没有办法使用客户端批量复制文件。 - Andy Shellam

它还可以从STDIN读取数据，这意味着数据来自客户端应用程序。请参阅复制命令文档：http://www.postgresql.org/docs/8.4/static/sql-copy.html - piro

在速度方面，使用服务器端的 copy 是难以超越的。 - Avery Payne

如Haki Benita在这篇优秀的比较文章中所述，使用copy主要缺点是对于复杂数据类型的支持不够充分（如果您有此需求，更好的选择是使用pyscopg的execute_batch/execute_values...）。 - bluu

Answer 2

有一个新的psycopg2手册，其中包含了所有选项的示例。

COPY选项是最有效率的，然后是executemany，接着是使用pyformat的execute。

- piro

Answer 3

根据我的经验，executemany 并没有比自己运行多个插入语句更快， 最快的方法是自己格式化一个带有多个值的单个 INSERT 语句，也许在未来 executemany 会改进，但现在它相当慢。

我子类化了一个 list 并重载了 append 方法，因此当列表达到一定大小时，我会格式化 INSERT 语句并运行它。

- FlashDD

---

已点赞：您在https://dev59.com/vWsz5IYBdhLWcg3wDzwA中的经验已得到验证。 - Brian B

Answer 4

您可以使用新的upsert库：

$ pip install upsert

（你可能需要先 pip install decorator ）

conn = psycopg2.connect('dbname=mydatabase')
cur = conn.cursor()
upsert = Upsert(cur, 'mytable')
for (selector, setter) in myrecords:
    upsert.row(selector, setter)

selector 是一个字典对象，例如 {'name': 'Chris Smith'}，而 setter 则是一个类似于 { 'age': 28, 'state': 'WI' } 的字典。

使用此方法几乎与编写自定义的 INSERT[/UPDATE] 代码并直接使用 psycopg2 执行它一样快...但如果行已经存在，它不会出错。

- Seamus Abshere

---

现今，你应该使用内部的PostgreSQL实现（需要版本9.5+），其中包括使用INSERT [...] ON CONFLICT [...]。https://www.postgresql.org/docs/9.5/static/sql-insert.html - Rmatt

Answer 5

最新的插入多个项目的方法是使用 execute_values 助手函数（https://www.psycopg.org/docs/extras.html#fast-execution-helpers）。

from psycopg2.extras import execute_values

insert_sql = "INSERT INTO table (id, name, created) VALUES %s"
# this is optional
value_template="(%s, %s, to_timestamp(%s))"

cur = conn.cursor()

items = []
items.append((1, "name", 123123))
# append more...

execute_values(cur, insert_sql, items, value_template)
conn.commit()

- Mike Reiche

---

根据我的经验，我会补充一点，execute_values 比 cur.execute(insert_script, data) 更快 6 倍。 - Michael Halim

Answer 6

使用SQLalchemy的人可以尝试1.2版本，该版本增加了对批量插入的支持，可以在使用use_batch_mode=True初始化引擎时，使用psycopg2.extras.execute_batch()代替executemany。

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

如果有人想使用SQLalchemy，就不需要尝试不同的sqla和psycopg2以及直接SQL的组合。

- user2189731

---

execute_batch() 和 cursor.copy_from()（基于内存的文件对象）有何区别？ - Michael Goldshteyn

这是Haki Benita的一篇绝佳文章，他比较了所有可能方法的时间和内存使用情况。他的结论直接回答了你的问题：从内存中复制（copy）具有最快的速度和最小的内存占用，但对于数据类型转换的支持有限。因此，如果数据集不太大或包含复杂的数据类型，则仍然最好使用psycopg的“本地”方法（即execute_batch()）。 - bluu

Answer 7

经过一些测试，从 @Clodoaldo Neto 的答案中我了解到，unnest 经常是一个非常快速的选项。

data = [(1, 100), (2, 200), ...]  # list of tuples

cur.execute("""CREATE TABLE table1 AS
               SELECT u.id, u.var1
               FROM unnest(%s) u(id INT, var1 INT)""", (data,))

然而，当处理极大数据时，这可能会有些棘手。

- n1000

Answer 8

第一和第二个将会一起使用，而不是分开使用。第三个将会是最有效的服务器方式，因为服务器将会完成所有的艰苦工作。

- Ignacio Vazquez-Abrams

---

你能提供一些代码示例吗？我在网上找不到任何好的psycopg2资源。 - Adam Matan

Psycopg有了新手册：现在有很多例子。http://initd.org/psycopg/ - piro

太好了，谢谢。在这里找到另一个很好的例子：http://www.devx.com/opensource/Article/29071/0/page/3，可能是关于psycopg2最好的实践资源。 - Adam Matan

Answer 9

一个非常相关的问题: 使用SQLAlchemy ORM进行批量插入。

---

所有的路都通往罗马，但有些路要穿越山脉，需要乘坐渡轮，但如果你想快速到达那里，就走高速公路吧。

---

在这种情况下，高速公路要使用execute_batch()功能和psycopg2。文档说得最好： executemany()的当前实现（使用极其慷慨的低估）并不特别高效。这些函数可用于加速针对一组参数重复执行语句。通过减少服务器往返次数，性能可以比使用executemany()快几个数量级。

在我的测试中，execute_batch()大约比executemany()快两倍，并提供了配置页面大小以进行进一步调整的选项（如果您想从驱动程序中挤出最后的2-3%性能）。

如果您正在使用SQLAlchemy，则可以通过在使用create_engine()实例化引擎时将use_batch_mode=True设置为参数来轻松启用相同的功能。

- chjortlund