<think>
我们之前已经详细介绍了Python与PostgreSQL的交互，其中包含插入数据的部分。现在，我们将更详细地解释插入数据的各个方面，包括单条插入、批量插入、返回插入后的数据、防止SQL注入等，并给出相应的代码示例。
### 1. 单条插入数据
在PostgreSQL中，插入单条数据通常使用`INSERT INTO ... VALUES ...`语句。在Python中，我们使用参数化查询来安全地插入数据。
#### 代码示例：
```python
def add_employee(conn, name, position, salary):
insert_query = """
INSERT INTO employees (name, position, salary)
VALUES (%s, %s, %s)
RETURNING id;
"""
try:
with conn.cursor() as cur:
# 使用参数化查询，避免SQL注入
cur.execute(insert_query, (name, position, salary))
# 获取返回的id
employee_id = cur.fetchone()[0]
conn.commit()
print(f"添加员工成功，ID: {employee_id}")
return employee_id
except psycopg2.Error as e:
conn.rollback()
print(f"插入数据时出错: {e}")
return None
```
#### 解释：
- `INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)`：使用参数占位符`%s`，实际值通过元组传入。
- `RETURNING id`：在插入后返回自动生成的id（假设id是自增字段）。
- 使用上下文管理器`with conn.cursor() as cur`确保游标正确关闭。
- 使用`conn.commit()`提交事务，使插入操作永久生效。
- 异常处理中执行`conn.rollback()`回滚事务。
### 2. 批量插入数据
当需要插入多条数据时，使用`executemany`方法可以提高效率。
#### 代码示例：
```python
def bulk_insert_employees(conn, employees):
insert_query = """
INSERT INTO employees (name, position, salary)
VALUES (%s, %s, %s)
"""
# 将员工数据转换为元组列表
records = [(emp['name'], emp['position'], emp['salary']) for emp in employees]
try:
with conn.cursor() as cur:
cur.executemany(insert_query, records)
conn.commit()
print(f"成功插入 {len(records)} 条员工记录")
except psycopg2.Error as e:
conn.rollback()
print(f"批量插入失败: {e}")
```
#### 解释：
- `executemany`方法接收两个参数：SQL语句和由参数元组组成的列表。
- 每次执行插入一条记录，但通过一次函数调用执行所有插入，减少了网络往返次数。
- 注意：这仍然是多个独立的INSERT语句，但在一个事务中执行。
### 3. 使用COPY进行高效批量插入
对于非常大的数据集，使用PostgreSQL的COPY命令可以显著提高插入速度。
#### 代码示例：
```python
import io
def bulk_insert_with_copy(conn, employees):
# 创建一个内存中的CSV文件
csv_data = io.StringIO()
for emp in employees:
csv_data.write(f"{emp['name']},{emp['position']},{emp['salary']}\n")
csv_data.seek(0) # 将指针移回文件开头
try:
with conn.cursor() as cur:
# 使用COPY命令从内存文件加载数据
cur.copy_from(csv_data, 'employees', sep=',', columns=('name', 'position', 'salary'))
conn.commit()
print(f"通过COPY命令插入 {len(employees)} 条记录")
except psycopg2.Error as e:
conn.rollback()
print(f"COPY插入失败: {e}")
```
#### 解释：
- `copy_from`方法从一个类文件对象（如StringIO）中读取数据，并使用COPY命令加载到表中。
- 这种方式比使用多个INSERT语句快很多，特别适合导入大量数据。
- 注意：COPY命令不会触发所有触发器（如不会触发行级触发器），且需要确保数据格式正确。
### 4. 插入数据时处理冲突
当插入的数据可能导致唯一约束冲突时，可以使用`ON CONFLICT`子句来处理。
#### 代码示例（冲突时更新）：
```python
def upsert_employee(conn, id, name, position, salary):
upsert_query = """
INSERT INTO employees (id, name, position, salary)
VALUES (%s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
position = EXCLUDED.position,
salary = EXCLUDED.salary;
"""
try:
with conn.cursor() as cur:
cur.execute(upsert_query, (id, name, position, salary))
conn.commit()
print(f"员工ID {id} 已插入或更新")
except psycopg2.Error as e:
conn.rollback()
print(f"插入/更新失败: {e}")
```
#### 解释：
- `ON CONFLICT (id) DO UPDATE`：当id冲突（即已存在相同id）时，执行更新操作。
- `EXCLUDED`关键字引用要插入的行（即冲突时本应插入的行）。
- 这样实现了如果存在则更新，不存在则插入（upsert）的功能。
### 5. 插入数据时使用默认值
如果表中有设置了默认值的列，插入时可以省略这些列。
#### 代码示例：
假设employees表有一个`created_at TIMESTAMP DEFAULT current_timestamp`列。
```python
def add_employee_with_defaults(conn, name, position, salary):
insert_query = """
INSERT INTO employees (name, position, salary)
VALUES (%s, %s, %s)
RETURNING id, created_at;
"""
try:
with conn.cursor() as cur:
cur.execute(insert_query, (name, position, salary))
result = cur.fetchone()
employee_id, created_at = result[0], result[1]
conn.commit()
print(f"添加员工成功，ID: {employee_id}，创建时间: {created_at}")
return employee_id
except psycopg2.Error as e:
conn.rollback()
print(f"插入失败: {e}")
return None
```
#### 解释：
- 在插入语句中，我们只指定了`name, position, salary`三个字段，`created_at`将自动使用默认值（当前时间戳）。
- 通过`RETURNING`子句获取插入后的默认值，以便在程序中使用。
## 总结
插入数据是数据库操作的基本功能，在Python中通过`psycopg2`库可以灵活地实现各种插入需求。关键点包括：
- 使用参数化查询防止SQL注入
- 根据需要选择单条插入、批量插入或COPY命令
- 处理插入冲突（唯一约束冲突）
- 利用数据库的默认值和返回功能
§§相关问题§§:
1. 如何插入包含二进制数据（如图片）到PostgreSQL？
2. 在批量插入时，如何提高性能？
3. 如何实现插入数据时的条件判断（如仅当不存在时才插入）？
4. 插入数据后如何获取自增主键的值？
</think>
# Python与PostgreSQL插入数据详解
在Python中向PostgreSQL插入数据是数据库操作的核心功能之一。下面我将详细解释各种插入数据的方法、最佳实践和相关注意事项。
## 基础插入操作
### 1. 简单INSERT语句
```python
import psycopg2
def simple_insert(conn):
try:
with conn.cursor() as cur:
# 基础INSERT语句
cur.execute("""
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW())
""")
conn.commit()
print("插入成功")
except psycopg2.Error as e:
conn.rollback()
print(f"插入失败: {e}")
# 使用示例
conn = psycopg2.connect(database="mydb", user="postgres", password="yourpass", host="localhost", port="5432")
simple_insert(conn)
conn.close()
```
**解释**：
- 这是最基本的数据插入方式
- 直接拼接SQL字符串插入数据
- **缺点**：容易导致SQL注入攻击，不推荐在实际应用中使用
### 2. 参数化查询（推荐）
```python
def safe_insert(conn, username, email):
try:
with conn.cursor() as cur:
# 使用参数化查询防止SQL注入
cur.execute("""
INSERT INTO users (username, email, created_at)
VALUES (%s, %s, NOW())
""", (username, email))
conn.commit()
print(f"用户 {username} 插入成功")
except psycopg2.Error as e:
conn.rollback()
print(f"插入失败: {e}")
# 使用示例
safe_insert(conn, "alice", "alice@example.com")
```
**解释**：
- 使用`%s`作为占位符，实际值通过元组传入
- 自动处理特殊字符转义，防止SQL注入
- **最佳实践**：总是使用参数化查询
## 高级插入技巧
### 3. 插入多条数据
```python
def batch_insert(conn, users):
try:
with conn.cursor() as cur:
# 创建SQL模板
sql = "INSERT INTO users (username, email, created_at) VALUES (%s, %s, NOW())"
# 准备数据 [(username1, email1), (username2, email2), ...]
user_data = [(user['name'], user['email']) for user in users]
# 执行批量插入
cur.executemany(sql, user_data)
conn.commit()
print(f"批量插入 {len(users)} 条记录成功")
except psycopg2.Error as e:
conn.rollback()
print(f"批量插入失败: {e}")
# 使用示例
users_to_insert = [
{"name": "bob", "email": "bob@example.com"},
{"name": "carol", "email": "carol@example.com"},
{"name": "dave", "email": "dave@example.com"}
]
batch_insert(conn, users_to_insert)
```
**解释**：
- 使用`executemany()`方法批量插入数据
- 比循环执行单条INSERT语句性能高10-100倍
- 适用于数据导入或批量操作场景
### 4. 插入后返回生成的值
```python
def insert_and_return_id(conn, username, email):
try:
with conn.cursor() as cur:
# 插入并返回生成的ID
cur.execute("""
INSERT INTO users (username, email, created_at)
VALUES (%s, %s, NOW())
RETURNING id
""", (username, email))
# 获取返回的ID值
user_id = cur.fetchone()[0]
conn.commit()
print(f"用户插入成功，ID: {user_id}")
return user_id
except psycopg2.Error as e:
conn.rollback()
print(f"插入失败: {e}")
return None
```
**解释**：
- 使用`RETURNING`子句获取插入后生成的值
- 特别适用于获取自增主键或计算字段
- `RETURNING *`可返回整行数据
### 5. 插入时处理冲突
```python
def upsert_user(conn, user_id, username, email):
try:
with conn.cursor() as cur:
# 存在则更新，不存在则插入
cur.execute("""
INSERT INTO users (id, username, email, updated_at)
VALUES (%s, %s, %s, NOW())
ON CONFLICT (id) DO UPDATE SET
username = EXCLUDED.username,
email = EXCLUDED.email,
updated_at = NOW()
""", (user_id, username, email))
conn.commit()
print(f"用户 {username} 已插入或更新")
except psycopg2.Error as e:
conn.rollback()
print(f"操作失败: {e}")
```
**解释**：
- 使用`ON CONFLICT`子句处理唯一约束冲突
- `EXCLUDED`关键字引用要插入的行数据
- 实现"插入或更新"（upsert）功能
## 特殊数据类型插入
### 6. 插入JSON数据
```python
def insert_json(conn, user_id, preferences):
try:
with conn.cursor() as cur:
# 插入JSON数据
cur.execute("""
UPDATE users
SET preferences = %s::jsonb
WHERE id = %s
""", (json.dumps(preferences), user_id))
conn.commit()
print(f"用户 {user_id} 偏好设置已更新")
except psycopg2.Error as e:
conn.rollback()
print(f"更新失败: {e}")
# 使用示例
prefs = {
"theme": "dark",
"notifications": True,
"language": "zh-CN"
}
insert_json(conn, 1, prefs)
```
**解释**：
- PostgreSQL支持原生JSON数据类型
- 通过`::jsonb`转换确保数据类型正确
- 可直接查询JSON字段中的特定属性
### 7. 插入二进制数据
```python
def insert_image(conn, user_id, image_path):
try:
with open(image_path, 'rb') as f:
image_data = f.read()
with conn.cursor() as cur:
# 使用psycopg2.Binary处理二进制数据
cur.execute("""
UPDATE users
SET avatar = %s
WHERE id = %s
""", (psycopg2.Binary(image_data), user_id))
conn.commit()
print(f"用户 {user_id} 头像已更新")
except (psycopg2.Error, IOError) as e:
conn.rollback()
print(f"操作失败: {e}")
```
**解释**：
- 使用`psycopg2.Binary`包装二进制数据
- PostgreSQL的BYTEA类型适合存储小到中等大小的二进制对象
- 大文件建议使用大对象存储或外部存储
## 性能优化技巧
### 8. 使用COPY命令高效导入
```python
def bulk_import_csv(conn, csv_path):
try:
with conn.cursor() as cur, open(csv_path, 'r') as f:
# 使用COPY命令快速导入CSV
cur.copy_expert("""
COPY users (username, email, created_at)
FROM STDIN
WITH (FORMAT CSV, HEADER)
""", f)
conn.commit()
print(f"CSV文件 {csv_path} 导入成功")
except (psycopg2.Error, IOError) as e:
conn.rollback()
print(f"导入失败: {e}")
```
**解释**：
- COPY命令比INSERT快10-100倍
- 适用于批量导入大量数据
- 支持CSV、二进制等多种格式
### 9. 事务批处理
```python
def batch_insert_with_transaction(conn, users):
try:
with conn.cursor() as cur:
# 开始事务
for user in users:
cur.execute("""
INSERT INTO users (username, email)
VALUES (%s, %s)
""", (user['name'], user['email']))
# 一次性提交
conn.commit()
print(f"批量插入 {len(users)} 条记录成功")
except psycopg2.Error as e:
conn.rollback()
print(f"批量插入失败: {e}")
```
**解释**：
- 在单个事务中执行多条插入
- 减少事务提交次数提高性能
- 批量操作失败时可以整体回滚
## 插入数据最佳实践
1. **始终使用参数化查询** - 防止SQL注入攻击
2. **合理使用事务** - 确保数据一致性
3. **批量操作优先** - 减少数据库往返次数
4. **考虑唯一约束** - 使用ON CONFLICT处理冲突
5. **返回生成的值** - 特别是自增ID
6. **验证数据类型** - 确保插入值符合列类型
7. **处理空值** - 使用None表示SQL NULL
8. **设置连接超时** - 避免长时间阻塞
9. **使用连接池** - 高并发应用必备
10. **记录错误日志** - 便于问题排查
本文介绍了一个使用C++连接PostgreSQL数据库并插入包含Bytea类型数据的示例。该示例展示了如何通过PostgreSQL的libpq-fe.h库创建连接、转义Bytea类型的数据并执行SQL插入操作。
扫一扫
320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
