【Python系列】Python 连接 PostgreSQL 数据库并查询数据

在现代软件开发中，数据库是存储和检索数据的核心组件。PostgreSQL（简称 PG）是一个功能强大的开源对象关系数据库系统，它以其稳定性、强大的功能和灵活性而闻名。Python 作为一种流行的编程语言，与 PostgreSQL 的结合使用非常广泛。

1. PostgreSQL 简介

PostgreSQL 是一个高度可扩展的开源数据库管理系统，它支持 SQL（结构化查询语言）并提供许多现代数据库特性，如部分 ACID（原子性、一致性、隔离性、持久性）事务、触发器、视图、事务完整性、多版本并发控制等。PostgreSQL 的可扩展性允许用户自定义数据类型、函数、操作符等。

2. psycopg2 库

psycopg2是 Python 中用于连接 PostgreSQL 数据库的适配器。它提供了一个非常接近 Python 数据库 API 规范（PEP 249）的接口，使得 Python 开发者可以方便地使用 Python 代码来操作 PostgreSQL 数据库。psycopg2支持原生的 Unicode 数据类型，并且可以处理二进制数据。

3. 安装 psycopg2

在开始之前，确保你已经安装了psycopg2库。如果还没有安装，可以通过以下命令安装：

pip install psycopg2-binary

或者，如果你使用的是 Python 3，并且系统中同时安装了 Python 2，可能需要使用pip3：

pip3 install psycopg2-binary

psycopg2-binary是psycopg2的预编译版本，它包含了二进制文件，这样可以避免编译源代码的麻烦。

4. 连接 PostgreSQL 数据库

连接 PostgreSQL 数据库需要知道数据库的地址、端口、数据库名、用户名和密码。以下是一个简单的示例，展示如何使用psycopg2连接到 PostgreSQL 数据库：

import psycopg2

# 连接数据库的参数
dbname = "your_dbname"
user = "your_username"
password = "your_password"
host = "your_host"
port = "your_port"

# 连接字符串
conn_string = f"host={host} port={port} dbname={dbname} user={user} password={password}"

# 建立连接
conn = psycopg2.connect(conn_string)

# 创建cursor对象
cursor = conn.cursor()

在这段代码中，你需要将your_dbname、your_username、your_password、your_host和your_port替换为实际的数据库连接信息。

5. 执行查询

一旦建立了数据库连接，就可以使用cursor对象来执行 SQL 查询了。以下是一个查询示例：

# 要执行的SQL查询
query = "SELECT * FROM your_table_name;"

# 执行查询
cursor.execute(query)

# 获取查询结果
results = cursor.fetchall()

# 打印结果
for row in results:
    print(row)

在这个例子中，你需要将your_table_name替换为你想要查询的表名。fetchall()方法会返回查询结果的所有行，每行是一个元组。

6. 处理查询结果

查询结果通常以元组的形式返回，每个元组代表一行数据。你可以通过索引或列名来访问元组中的值。例如：

for row in results:
    print(f"ID: {row[0]}, Name: {row[1]}")

如果你更喜欢使用列名而不是索引，可以在执行查询后使用cursor.description来获取列名，然后使用列名访问数据：

# 获取列名
columns = [desc[0] for desc in cursor.description]

# 使用列名访问数据
for row in results:
    print(f"ID: {row[columns.index('id')]}, Name: {row[columns.index('name')]}")

7. 插入、更新和删除数据

除了查询数据，psycopg2还支持插入、更新和删除操作。以下是一些基本的示例：

# 插入数据
insert_query = "INSERT INTO your_table_name (column1, column2) VALUES (%s, %s);"
cursor.execute(insert_query, (value1, value2))

# 更新数据
update_query = "UPDATE your_table_name SET column1 = %s WHERE column2 = %s;"
cursor.execute(update_query, (new_value1, condition_value2))

# 删除数据
delete_query = "DELETE FROM your_table_name WHERE column1 = %s;"
cursor.execute(delete_query, (condition_value1,))

在这些操作中，%s是一个占位符，用于传递参数化的查询值，这有助于防止 SQL 注入攻击。

8. 提交事务和关闭连接

在执行插入、更新或删除操作后，需要提交事务以确保更改被保存到数据库中：

# 提交事务
conn.commit()

完成所有数据库操作后，应该关闭cursor对象和数据库连接：

# 关闭cursor
cursor.close()

# 关闭连接
conn.close()

9. 错误处理

在操作数据库时，可能会遇到各种错误，如连接失败、执行查询错误等。使用try...except块可以捕获和处理这些异常：

try:
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    # 执行数据库操作...
except psycopg2.Error as e:
    print(f"Database error: {e}")
finally:
    if conn is not None:
        conn.close()