PostgreSQL 高效插入大量行

在本文中，我们将介绍如何在 Psycopg2 中高效地插入大量行数据。

阅读更多：PostgreSQL 教程

Psycopg2 简介

Psycopg2 是 PostgreSQL 数据库的一个非常流行的 Python 连接库。它提供了一个简单而强大的接口，用于在 Python 程序中操作 PostgreSQL 数据库。

问题背景

当我们需要向 PostgreSQL 数据库中插入大量数据时，如何高效地进行插入是一个关键问题。如果我们使用常规的插入方法，如一条一条地执行 INSERT 语句，效率会非常低下。因此，我们需要使用一种更高效的方法来插入大量行数据。

COPY 命令

在 PostgreSQL 中，COPY 命令是一种非常高效的将大量行数据插入到表中的方法。它可以从文件、标准输入或者其他表中复制数据到目标表中。

Psycopg2 提供了一个用于执行 COPY 命令的函数 copy_from()。下面是一个示例：

import psycopg2

# 连接数据库
conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost", port="5432")
cur = conn.cursor()

# 执行 COPY 命令
with open("data.csv", "r") as f:
    cur.copy_from(f, 'mytable', sep=',')  # 从 data.csv 文件中复制数据到 mytable 表中

# 提交事务
conn.commit()

# 关闭数据库连接
cur.close()
conn.close()

在上面的示例中，我们打开一个名为 data.csv 的文件，然后使用 copy_from() 函数将其中的数据复制到名为 mytable 的表中。这种方式比一条一条地执行 INSERT 语句要快得多。

使用批量插入

除了使用 COPY 命令外，还可以使用 Psycopg2 提供的批量插入方法来提高插入大量行数据的效率。通常，将多个 INSERT 语句合并到一个事务中执行，可以减少数据库通信的开销，从而提高插入的效率。

下面是一个使用批量插入的示例：

import psycopg2

# 连接数据库
conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost", port="5432")
cur = conn.cursor()

# 执行批量插入
data = [('John', 25), ('Alice', 30), ('Bob', 35)]
cur.executemany("INSERT INTO mytable (name, age) VALUES (%s, %s)", data)

# 提交事务
conn.commit()

# 关闭数据库连接
cur.close()
conn.close()

在上面的示例中，我们使用 executemany() 函数将多个值的列表作为参数传递给 INSERT 语句，然后一次性执行这些 INSERT 语句。这样可以减少通信开销，提高效率。

使用事务

事务是 PostgreSQL 数据库中非常重要的概念。使用事务可以将多个操作作为一个逻辑单元执行，从而保持数据的一致性。

在插入大量行数据时，将插入操作包含在一个事务中可以提高效率。下面是一个使用事务的示例：

import psycopg2

# 连接数据库
conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost", port="5432")
cur = conn.cursor()

# 开始事务
cur.execute("BEGIN")

# 执行插入操作
for row in rows:
    cur.execute("INSERT INTO mytable (name, age) VALUES (%s, %s)", (row['name'], row['age']))

# 提交事务
cur.execute("COMMIT")

# 关闭数据库连接
cur.close()
conn.close()