如何使用Python连接PostgreSQL？psycopg2

陆砚码

2026-10-29 • 👁 0人浏览过

要使用 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

库。它提供了一个非常直接且符合DB-API 2.0规范的接口，让你能够像操作本地对象一样操作数据库。核心在于先安装

psycopg2

，然后通过

psycopg2.connect()

建立连接，接着创建游标执行SQL语句，最后别忘了提交事务并关闭连接。这套流程一旦掌握，数据库操作就变得清晰可控。解决方案要使用

psycopg2

连接PostgreSQL，你首先需要安装它。我个人偏好安装

psycopg2-binary

，因为它省去了编译C语言扩展的麻烦，对于大多数用户来说，这简直是福音。

pip install psycopg2-binary

接下来，就是编写Python代码。一个典型的连接、查询和关闭的流程会是这样：立即学习 “ 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而引发的安全事故，那种感觉就像是把家门钥匙直接挂在了大街上。数据库操作中如何处理常见错误和事务？在和数据库打交道时，错误和事务管理是两个绕不开的话题。我个人觉得，一个健壮的数据库交互代码，必须把这两点考虑进去，否则迟早会遇到数据不一致或者程序崩溃的问题。 Python 3.14.3 微软官方的 Python 扩展，是 VS Code 安装量最高的扩展（209M+）。集成 IntelliSense（通过 Pylance）、调试（通过 Python Debugger）、代码检查、格式化、重构和单元测试等功能。支持 Jupyter Notebook、虚拟环境管理和多 Python 版本切换。下载错误处理

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

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

相关文章