首页 > 文章 > python教程

Python连接PostgreSQL：psycopg2使用教程

时间：2025-07-15 12:25:22 213浏览收藏

目前golang学习网上已经有很多关于文章的文章了，自己在初次阅读这些文章中，也见识到了很多学习思路；那么本文《Python连接PostgreSQL方法：psycopg2教程》，也希望能帮助到大家，如果阅读完后真的对你学习文章有帮助，欢迎动动手指，评论留言并分享~

要使用Python连接PostgreSQL数据库，最常用且稳健的方式是使用psycopg2库。1. 首先安装psycopg2或更便捷的psycopg2-binary；2. 使用psycopg2.connect()方法建立连接，传入host、database、user、password和port等参数；3. 创建游标对象执行SQL语句；4. 操作完成后提交事务并关闭连接。为保障安全，应避免将数据库连接参数硬编码在代码中，推荐使用环境变量、.env配置文件（配合python-dotenv）或配置管理服务（如Vault、AWS Secrets Manager）。错误处理方面，需通过try...except...finally结构捕获psycopg2.Error及其子类异常，并在出错时回滚事务。事务管理上，必须显式调用connection.commit()提交更改或connection.rollback()撤销操作。高级用法包括：使用参数化查询防止SQL注入；选择DictCursor或NamedTupleCursor提升结果可读性；使用连接池（如psycopg2.pool）优化高并发性能；异步场景下考虑asyncpg替代psycopg2。这些实践能显著增强代码安全性、稳定性和性能。

如何使用Python连接PostgreSQL？psycopg2

连接Python与PostgreSQL数据库，最常用也最稳健的选择就是psycopg2库。它提供了一个非常直接且符合DB-API 2.0规范的接口，让你能够像操作本地对象一样操作数据库。核心在于先安装psycopg2，然后通过psycopg2.connect()建立连接，接着创建游标执行SQL语句，最后别忘了提交事务并关闭连接。这套流程一旦掌握，数据库操作就变得清晰可控。

解决方案

要使用psycopg2连接PostgreSQL，你首先需要安装它。我个人偏好安装psycopg2-binary，因为它省去了编译C语言扩展的麻烦，对于大多数用户来说，这简直是福音。

pip install psycopg2-binary

接下来，就是编写Python代码。一个典型的连接、查询和关闭的流程会是这样：

import psycopg2
from psycopg2 import Error

# 数据库连接参数
# 在实际项目中，这些参数绝不应该硬编码，后面我会聊聊如何安全管理
DB_HOST = "localhost"
DB_NAME = "your_database_name"
DB_USER = "your_username"
DB_PASSWORD = "your_password"
DB_PORT = "5432" # PostgreSQL默认端口

connection = None # 初始化连接变量
cursor = None     # 初始化游标变量

try:
    # 建立数据库连接
    connection = psycopg2.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        port=DB_PORT
    )

    # 创建一个游标对象，用于执行SQL命令
    # 默认游标返回的是元组，如果需要字典形式的结果，可以使用 psycopg2.extras.DictCursor
    cursor = connection.cursor()

    # 示例1：创建一个表（如果不存在）
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE
    );
    """
    cursor.execute(create_table_query)
    connection.commit() # 提交事务，使更改生效
    print("表 'users' 已创建或已存在。")

    # 示例2：插入数据
    # 注意：这里使用参数化查询（%s），这是防止SQL注入的关键！
    insert_query = "INSERT INTO users (name, email) VALUES (%s, %s);"
    user_data = ("张三", "zhangsan@example.com")
    cursor.execute(insert_query, user_data)
    connection.commit()
    print("数据插入成功。")

    # 示例3：查询数据
    select_query = "SELECT id, name, email FROM users WHERE name = %s;"
    cursor.execute(select_query, ("张三",))

    # 获取所有查询结果
    records = cursor.fetchall()
    print("\n查询结果：")
    for row in records:
        print(f"ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}")

except (Exception, Error) as error:
    print(f"连接或操作数据库时发生错误: {error}")
    if connection:
        connection.rollback() # 发生错误时回滚事务

finally:
    # 无论成功或失败，都确保关闭游标和连接
    if cursor:
        cursor.close()
    if connection:
        connection.close()
    print("PostgreSQL连接已关闭。")

PostgreSQL连接参数如何安全配置与管理？

说实话，把数据库的账号密码直接写在代码里，那简直是给自己挖坑。在实际开发中，尤其是在团队协作和部署到生产环境时，连接参数的安全管理是头等大事。我通常会考虑几种方案，它们各有优劣。

最常见的参数包括：host（数据库服务器地址，如localhost或IP）、database（要连接的数据库名）、user（用户名）、password（密码）以及port（端口，PostgreSQL默认是5432）。

至于安全配置，我强烈推荐以下几种方式，优先级从高到低：

环境变量 (Environment Variables): 这是最推荐的方式。将数据库凭证设置为操作系统的环境变量，你的代码运行时直接读取这些变量。例如：export DB_USER=myuser。代码中通过os.environ.get('DB_USER')来获取。这样做的好处是代码库里完全不包含敏感信息，且在不同环境（开发、测试、生产）部署时，只需修改环境变量即可，无需改动代码。我个人觉得，虽然初始设置有点麻烦，但长期来看，它最干净。
配置文件 (.env files with python-dotenv): 对于本地开发环境，或者不方便设置系统环境变量的场景，使用.env文件配合python-dotenv库是个不错的折衷方案。你创建一个.env文件（记得把它添加到.gitignore里，绝不能提交到版本控制！），里面写上DB_USER=myuser，然后Python代码里用dotenv_values()或load_dotenv()来加载。
```
# .env 文件示例
DB_HOST=localhost
DB_NAME=mydb
DB_USER=myuser
DB_PASSWORD=mypassword
DB_PORT=5432
```
```
# Python代码
from dotenv import load_dotenv
import os

load_dotenv() # 加载 .env 文件中的环境变量

DB_HOST = os.getenv("DB_HOST")
DB_NAME = os.getenv("DB_NAME")
# ... 其他参数
```
这种方式兼顾了便捷性和安全性，很适合项目初期。
配置管理服务 (如Vault, AWS Secrets Manager): 对于大型企业级应用，或者需要更高级别安全策略的场景，会引入专门的秘密管理服务。这些服务能够动态生成、轮换凭证，并提供细粒度的访问控制。虽然集成起来更复杂，但安全性达到了最高级别。这通常是架构师和运维团队会考虑的。

无论哪种方式，核心原则都是：敏感信息绝不能硬编码在代码中，更不能提交到版本控制系统。 我见过太多因为不小心把凭证推到GitHub而引发的安全事故，那种感觉就像是把家门钥匙直接挂在了大街上。

数据库操作中如何处理常见错误和事务？

在和数据库打交道时，错误和事务管理是两个绕不开的话题。我个人觉得，一个健壮的数据库交互代码，必须把这两点考虑进去，否则迟早会遇到数据不一致或者程序崩溃的问题。

错误处理

psycopg2在执行SQL操作时，如果遇到问题，会抛出psycopg2.Error或其子类的异常。最常见的错误类型包括：

psycopg2.IntegrityError: 当你尝试插入重复的主键、违反唯一约束或外键约束时。比如，你试图插入一个邮箱地址已经存在的用户。
psycopg2.ProgrammingError: SQL语法错误、表或列不存在等。
psycopg2.OperationalError: 连接问题，如数据库服务器宕机、网络不通等。

我的经验是，使用try...except...finally结构是处理数据库错误的黄金法则。

try:
    # 数据库操作，比如插入一条可能违反唯一约束的数据
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s);", ("李四", "zhangsan@example.com"))
    connection.commit()
except psycopg2.IntegrityError as e:
    print(f"数据完整性错误：{e}")
    if connection:
        connection.rollback() # 发生完整性错误时，回滚当前事务
except psycopg2.ProgrammingError as e:
    print(f"SQL编程错误：{e}")
    if connection:
        connection.rollback()
except psycopg2.Error as e: # 捕获所有psycopg2相关的错误
    print(f"数据库操作通用错误：{e}")
    if connection:
        connection.rollback()
except Exception as e: # 捕获其他任何Python异常
    print(f"未知错误：{e}")
    if connection:
        connection.rollback()
finally:
    # 确保资源被释放
    if cursor:
        cursor.close()
    if connection:
        connection.close()

关键在于，一旦发生错误，特别是影响数据状态的错误（如插入、更新、删除），你几乎总是需要调用connection.rollback()。这能撤销当前事务中所有未提交的更改，保证数据库回到操作前的状态，避免数据不一致。

事务管理

事务（Transaction）是数据库操作中一个非常重要的概念，它确保了一组操作要么全部成功，要么全部失败，从而维护数据的完整性和一致性。PostgreSQL默认是自动提交模式，但psycopg2默认是手动提交，这意味着你需要显式地调用connection.commit()来保存更改。

connection.commit(): 当你执行了INSERT、UPDATE、DELETE或CREATE TABLE等修改数据库状态的SQL语句后，必须调用commit()才能将这些更改永久保存到数据库中。如果没有调用，即使代码执行成功，这些更改也只存在于当前会话的内存中，一旦连接关闭，所有更改都会丢失。我刚开始用的时候就犯过这个错，查了半天数据没进去，结果发现是忘了commit。
connection.rollback(): 当一组操作中的任何一个失败，或者你决定放弃当前事务中的所有更改时，调用rollback()可以撤销所有自上次commit()以来或自连接建立以来的所有操作。这对于错误恢复至关重要。

一个典型的事务流程：

开始事务（psycopg2连接后默认就处于一个事务中）。
执行一系列SQL操作（比如先插入订单，再更新库存）。
如果所有操作都成功，调用connection.commit()。
如果任何一个操作失败，或者捕获到异常，调用connection.rollback()。

try:
    # 假设这是一个转账操作：从A扣钱，给B加钱
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = %s;", (1,))
    # 模拟一个错误，比如B用户不存在，或者网络断了
    # raise Exception("模拟网络错误")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = %s;", (2,))

    connection.commit() # 两步都成功，才提交
    print("转账成功！")
except Exception as e:
    print(f"转账失败：{e}")
    connection.rollback() # 任何一步失败，都回滚
    print("事务已回滚。")

理解并正确使用commit()和rollback()是编写可靠数据库应用程序的基石。

除了基本的CRUD，psycopg2还有哪些高级用法或注意事项？

psycopg2不仅仅是提供CRUD（创建、读取、更新、删除）操作的基础，它还有一些高级特性和最佳实践，能够让你的代码更健壮、更高效。我个人在项目中会特别关注以下几点：

SQL注入防护：参数化查询 (%s) 这是最重要的！永远不要直接将用户输入或任何动态值拼接到SQL字符串中。psycopg2提供了参数化查询机制，使用%s作为占位符，然后将参数作为execute()方法的第二个参数（一个元组或列表）传递。psycopg2会自动为你处理转义，有效防止SQL注入攻击。
```
# 错误示范：存在SQL注入风险
# user_input = "'; DROP TABLE users; --"
# cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# 正确且安全的做法：使用参数化查询
user_name = "Alice"
cursor.execute("SELECT * FROM users WHERE name = %s;", (user_name,))
```
这个习惯，必须养成。我见过太多因为忽视这一点而导致的安全漏洞，后果不堪设想。
游标类型：DictCursor与NamedTupleCursor 默认的cursor对象在fetchall()或fetchone()时返回的是元组。如果你想以字典形式访问结果（比如row['column_name']而不是row[0]），可以使用psycopg2.extras.DictCursor。
```
from psycopg2.extras import DictCursor

# ... 连接代码 ...
cursor = connection.cursor(cursor_factory=DictCursor)
cursor.execute("SELECT id, name, email FROM users;")
records = cursor.fetchall()
for row in records:
    print(f"ID: {row['id']}, 姓名: {row['name']}, 邮箱: {row['email']}")
```
对于更严格的类型检查和IDE自动补全，NamedTupleCursor也是一个不错的选择。选择哪种取决于你的项目习惯和需求，但它们都比纯元组更具可读性。

连接池 (psycopg2.pool) 频繁地建立和关闭数据库连接会带来性能开销。在Web应用或高并发场景下，使用连接池是标准做法。连接池预先创建并维护一定数量的数据库连接，当应用程序需要连接时，直接从池中获取一个可用的连接，用完后再归还到池中，而不是关闭。 psycopg2.pool模块提供了SimpleConnectionPool和ThreadedConnectionPool等。

from psycopg2.pool import SimpleConnectionPool

# 创建连接池，最少1个连接，最多20个连接
connection_pool = SimpleConnectionPool(1, 20,
                                       host=DB_HOST,
                                       database=DB_NAME,
                                       user=DB_USER,
                                       password=DB_PASSWORD,
                                       port=DB_PORT)

conn = None
try:
    conn = connection_pool.getconn() # 从池中获取一个连接
    cursor = conn.cursor()
    cursor.execute("SELECT version();")
    print(cursor.fetchone())
except Exception as e:
    print(f"连接池操作错误: {e}")
finally:
    if conn:
        connection_pool.putconn(conn) # 将连接归还到池中
    # 在程序结束时，记得关闭连接池
    # connection_pool.closeall()

我个人觉得，对于任何稍微有点规模的应用，连接池都是必须的。它能显著提升性能和资源利用率。

异步操作 (AsyncPG) 虽然psycopg2本身是同步的，但如果你正在构建一个基于asyncio的异步Python应用，直接使用psycopg2会阻塞事件循环。在这种情况下，通常会选择asyncpg这个库，它是专门为异步PostgreSQL操作设计的，性能非常出色。虽然它不是psycopg2的一部分，但在讨论psycopg2的高级场景时，提到它非常有必要，因为它解决了psycopg2在异步环境下的痛点。

这些高级用法和注意事项，往往是在你从“能用”到“好用”甚至“生产可用”的路上，需要逐步掌握和实践的。一开始可能觉得复杂，但一旦用起来，你会发现它们能解决很多实际问题，让你的代码更健壮、更高效。

本篇关于《Python连接PostgreSQL：psycopg2使用教程》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！