Python 批量插入 PostgreSQL 数据，主键存在则更新

引言

在开发过程中，经常需要将大量的数据批量插入到数据库中。在处理 PostgreSQL 数据库时，如果需要插入的数据已经存在主键冲突，就需要进行更新操作。本文将详细介绍如何使用 Python 实现批量插入 PostgreSQL 数据，以及当主键存在时如何进行更新操作。

前提条件

在开始之前，您需要完成以下准备工作：

安装并配置好 Python 环境。
安装 “psycopg2” 库，用于连接 PostgreSQL 数据库。

您可以使用以下命令在 Python 环境中安装 “psycopg2” 库：

pip install psycopg2

连接到 PostgreSQL 数据库

首先，我们需要使用 Python 连接到 PostgreSQL 数据库。在代码中，我们可以使用 “psycopg2” 库提供的 “connect()” 函数来连接到数据库。以下是一个示例：

import psycopg2

connection = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myuser",
    password="mypassword"
)

请根据您自己的实际情况修改连接字符串中的主机名、数据库名、用户名和密码。

创建表格

在开始插入数据之前，我们需要先创建一个表格来存储数据。以下是一个示例表格的创建语句：

CREATE TABLE IF NOT EXISTS mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    age INTEGER
)

在这个示例中，我们创建了一个名为 “mytable” 的表格，其中包含三个列：id、name 和 age。id 列是一个自增的主键。

批量插入数据

在开始批量插入数据之前，我们先定义一些要插入的数据。以下是一个示例数据列表：

data = [
    (1, "Alice", 25),
    (2, "Bob", 30),
    (3, "Charlie", 35),
    ...
]

在这个示例中，每个数据项都是一个元组，包含 id、name 和 age 三个值。

接下来，我们可以使用 “execute()” 方法执行插入操作。以下是一个示例代码：

cursor = connection.cursor()

for row in data:
    query = "INSERT INTO mytable (id, name, age) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE SET name = excluded.name, age = excluded.age"
    cursor.execute(query, row)

connection.commit()

在这个示例中，我们首先创建了一个游标对象，然后使用 “for” 循环遍历数据列表。在循环中，我们构建了一个插入语句，使用元组中的值替代占位符。同时，我们使用 “ON CONFLICT (id) DO UPDATE” 子句来指定当插入的数据与已存在的数据发生主键冲突时执行更新操作。在更新操作中，我们使用 “excluded” 关键字引用要插入的值，以便将其赋值给已存在的行。

最后，我们使用 “commit()” 方法提交事务，将数据变更保存到数据库中。

示例运行结果

通过上述代码，可以将数据批量插入 PostgreSQL 数据库，并在主键冲突时进行更新操作。以下是一个完整的示例运行过程：

import psycopg2

connection = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myuser",
    password="mypassword"
)

data = [
    (1, "Alice", 25),
    (2, "Bob", 30),
    (3, "Charlie", 35)
]

cursor = connection.cursor()

for row in data:
    query = "INSERT INTO mytable (id, name, age) VALUES (%s, %s, %s) ON CONFLICT (id) DO UPDATE SET name = excluded.name, age = excluded.age"
    cursor.execute(query, row)

connection.commit()

在这个示例中，我们假设已经创建了一个名为 “mytable” 的表格，并且表格中已存在 id 为 1 和 2 的数据。当我们运行上述代码时，系统将插入 id 为 3 的新数据，并将 id 为 1 和 2 的数据的 name 和 age 更新为新的值。这样，我们就实现了批量插入数据并且在主键冲突时进行更新的功能。