Psycopg2、Postgresql、Python：批量插入的最快方法

Question

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

我创建了一个长长的郁金香列表，应将其插入数据库，有时使用几何Simplify等修饰符。

最简单的方法是对 INSERT 语句列表进行字符串格式化，但我还读过其他三种方法：

1. 使用 pyformat 绑定样式 用于参数插入
2. 使用 executemany元组列表，以及
3. 使用将结果写入文件并使用COPY。

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

Answer 1

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

Answer 2

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

COPY 选项是最有效的。然后执行many。然后用pyformat执行。

Answer 3

根据我的经验 executemany 并不比自己运行许多插入更快，
最快的方法是自己格式化一个具有多个值的 INSERT ，也许将来 executemany 会改进，但目前

我子类化 list< /code> 并重载追加方法，因此当列表达到一定大小时，我格式化 INSERT 来运行它

Answer 4

插入许多项目的最新方法是使用 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()

Answer 5

您可以使用新的upsert库：（

$ pip install upsert

您可能必须pip installdecorator 首先）

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

其中 selector 是一个 dict 对象，例如 {'name': 'Chris Smith'} 和 setter > 是一个类似于 { 'age': 28, 'state': 'WI' } 的 dict

它几乎与编写自定义 INSERT[ 一样快/UPDATE] 代码并直接使用 psycopg2 运行它...如果该行已经存在，它就不会爆炸。

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

然后有人必须使用 SQLalchmey，他们不会费心尝试 sqla 和 psycopg2 的不同组合以及直接 SQL 在一起。

Answer 7

经过一些测试，取消嵌套经常似乎是一个非常快的选择，正如我从@Clodoaldo Neto的答案中了解到的类似的问题。

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,))

然而，对于极大的数据可能会很棘手。

Answer 8

第一个和第二个将一起使用，而不是单独使用。不过，第三种方法在服务器方面是最高效的，因为服务器将完成所有艰苦的工作。

Answer 9

一个非常相关的问题： Bulk insert with SQLAlchemy ORM

---

条条大路通向罗马，但其中一些要翻山越岭，需要渡轮，但如果你想快速到达那里，只需走高速公路即可。

---

在这种情况下，高速公路将使用 execute_batch() psycopg2 的功能。文档说得最好：

executemany() 的当前实现（用一种极其仁慈的轻描淡写的说法）表现不佳。这些函数可用于加速针对一组参数的语句的重复执行。通过减少服务器往返次数，性能可以比使用 executemany() 好几个数量级。

在我自己的测试中，execute_batch() 的速度大约是 executemany() 的两倍，并且提供了配置 page_size 以便进一步调整的选项（如果您想榨取驱动程序最后 2-3% 的性能）。

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