Python如何连接PostgreSQL？-杰瑞科技汇

核心概念

在 Python 中操作 PostgreSQL，通常需要一个“桥梁”或“适配器”，它是一个 Python 库，能够让你用 Python 代码来执行 SQL 命令并与 PostgreSQL 数据库进行通信，目前最主流、最推荐的库是 psycopg。

我们将重点介绍 psycopg 的两个主要版本：

psycopg2: 经典版，非常稳定，被广泛使用，安装时是 psycopg2-binary。
psycopg (或 psycopg3): 新一代版本，完全重写，性能更好，API 更现代化，类型提示更完善。这是目前推荐的选择。

第一步：安装 `psycopg` 库

你需要安装 psycopg 库，打开你的终端或命令行工具,运行以下命令：

# 推荐安装最新版 psycopg (psycopg3)
pip install psycopg

如果你因为某些原因（需要兼容旧项目）必须使用 psycopg2，可以安装 psycopg2-binary，注意，binary 版本包含了编译好的库，安装更简单,但性能可能略逊于从源码编译的版本。

# 安装 psycopg2 (旧版)
pip install psycopg2-binary

第二步：连接到 PostgreSQL 数据库

在使用任何数据库操作之前，你必须先建立一个连接,连接需要一些基本信息：

dbname: 数据库名称
user: 用户名
password: 密码
host: 数据库服务器地址 (通常是 localhost 或 IP 地址)
port: 数据库端口号 (默认是 5432)

使用 `psycopg` (推荐) 连接

psycopg 使用一个连接字符串（connection string）来简化连接过程。

import psycopg
# 建立连接
# 使用 with 语句可以确保连接在使用完毕后自动关闭
try:
    # 替换为你的实际数据库连接信息
    conninfo = "dbname=testdb user=postgres password=yourpassword host=localhost port=5432"
    with psycopg.connect(conninfo) as conn:
        print("成功连接到 PostgreSQL 数据库！")
        # 在这里执行数据库操作...
        # conn 对象就是你的连接
except psycopg.OperationalError as e:
    print(f"连接失败: {e}")

使用 `psycopg2` 连接

psycopg2 的连接方式稍有不同，它通常使用 connect() 函数并直接传递参数。

import psycopg2
try:
    # 替换为你的实际数据库信息
    conn = psycopg2.connect(
        dbname="testdb",
        user="postgres",
        password="yourpassword",
        host="localhost",
        port="5432"
    )
    print("成功连接到 PostgreSQL 数据库！")
    # 在这里执行数据库操作...
    # conn 对象就是你的连接
except psycopg2.OperationalError as e:
    print(f"连接失败: {e}")

第三步：执行 SQL 语句

连接建立后，你需要一个游标来执行 SQL 语句，游标就像一个指针,指向你查询结果集中的某一行。

创建表

import psycopg
# ... (连接代码同上) ...
with psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432") as conn:
    # 获取一个游标
    with conn.cursor() as cur:
        # 执行 CREATE TABLE 语句
        # 使用 %s 作为占位符，防止 SQL 注入！
        cur.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                email VARCHAR(50) UNIQUE NOT NULL,
                age INT
            );
        """)
        # 提交事务，让更改永久生效
        conn.commit()
        print("表 'users' 创建成功！")

重要提示: 永远不要使用 Python 的字符串格式化（如 f"..." 或）来拼接 SQL 查询，这极易导致 SQL 注入攻击。psycopg 和 psycopg2 都使用 %s 作为占位符,它们会安全地处理参数化查询。

插入数据

插入数据同样使用参数化查询。

import psycopg
# ... (连接代码同上) ...
with psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432") as conn:
    with conn.cursor() as cur:
        # 插入单条数据
        insert_query = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s);"
        user_data = ("Alice", "alice@example.com", 30)
        cur.execute(insert_query, user_data)
        # 插入多条数据 (executemany)
        users_to_insert = [
            ("Bob", "bob@example.com", 25),
            ("Charlie", "charlie@example.com", 35)
        ]
        cur.executemany(insert_query, users_to_insert)
        conn.commit()
        print(f"成功插入了 {cur.rowcount} 条数据。")

查询数据

查询数据后,你需要获取结果。

import psycopg
# ... (连接代码同上) ...
with psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432") as conn:
    with conn.cursor() as cur:
        # 查询所有用户
        cur.execute("SELECT id, name, email, age FROM users;")
        # 获取所有结果 (返回一个列表，每个元素是一个元组)
        all_users = cur.fetchall()
        print("所有用户:")
        for user in all_users:
            print(user)
        # 查询年龄大于 30 的用户
        cur.execute("SELECT name, email FROM users WHERE age > %s;", (30,))
        # 获取第一条结果 (返回一个元组)
        first_user_over_30 = cur.fetchone()
        print("\n年龄大于 30 的第一个用户:")
        print(first_user_over_30)
        # 逐行获取结果 (适用于大数据量，节省内存)
        # cur.execute("SELECT name, email FROM users;")
        # for row in cur:
        #     print(row)

更新和删除数据

更新和删除与插入类似，都需要 WHERE 子句来指定操作的目标,并且要非常小心。

import psycopg
# ... (连接代码同上) ...
with psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432") as conn:
    with conn.cursor() as cur:
        # 更新数据
        update_query = "UPDATE users SET age = age + 1 WHERE name = %s;"
        cur.execute(update_query, ("Alice",))
        conn.commit()
        print(f"更新了 {cur.rowcount} 条记录。")
        # 删除数据
        delete_query = "DELETE FROM users WHERE age < %s;"
        cur.execute(delete_query, (28,))
        conn.commit()
        print(f"删除了 {cur.rowcount} 条记录。")

第四步：使用 `psycopg` 的高级特性 (推荐)

psycopg3 提供了一些非常方便的特性，让数据处理变得更加 Pythonic。

使用 `Row` 对象获取字典式结果

默认情况下，fetchall() 返回的是元组。psycopg 可以让你轻松获取字典,这样可以通过列名访问数据。

import psycopg
# ... (连接代码同上) ...
with psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432") as conn:
    # 创建一个返回字典的游标
    with conn.cursor(row_factory=psycopg.rows.dict_row) as cur:
        cur.execute("SELECT id, name, email, age FROM users;")
        # 现在每行都是一个字典
        for user in cur:
            print(f"ID: {user['id']}, 姓名: {user['name']}, 邮箱: {user['email']}")

自动提交事务

psycopg 的 with 语句会自动处理事务的提交和回滚，非常方便，如果你想在某些情况下手动控制,可以这样：

import psycopg
conn = psycopg.connect("dbname=testdb user=postgres password=yourpassword host=localhost port=5432")
try:
    with conn.transaction(): # 在一个事务块内执行
        with conn.cursor() as cur:
            cur.execute("INSERT INTO users (name, email) VALUES (%s, %s);", ("David", "david@example.com"))
            # 如果这里发生错误，整个事务会自动回滚
            # conn.commit() # 不需要手动调用
    print("事务成功提交。")
except Exception as e:
    print(f"发生错误，事务已回滚: {e}")
finally:
    conn.close() # 记得关闭连接

第五步：使用 ORM (对象关系映射)

对于大型项目，直接写 SQL 可能会变得繁琐且容易出错，ORM 库允许你用 Python 类来表示数据库表,用对象来操作数据。

最流行的 Python ORM 是 SQLAlchemy。

使用 SQLAlchemy + `psycopg`

安装 SQLAlchemy:
```
pip install sqlalchemy
```
基本用法:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 1. 定义模型 (Python 类映射到数据库表)
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String, unique=True)
    age = Column(Integer)
    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.email}')>"
# 2. 创建数据库引擎 (psycopg 是驱动)
# 'postgresql+psycopg://' 是 SQLAlchemy 的 URL 格式
engine = create_engine('postgresql+psycopg://postgres:yourpassword@localhost:5432/testdb')
# 3. 创建表 (如果不存在)
Base.metadata.create_all(engine)
# 4. 创建 Session
Session = sessionmaker(bind=engine)
session = Session()
# 5. 使用 ORM 进行增删改查
# 创建
new_user = User(name="Eve", email="eve@example.com", age=28)
session.add(new_user)
# 查询
users = session.query(User).filter(User.age > 25).all()
print("年龄大于 25 的用户:")
for user in users:
    print(user)
# 更新
eve_user = session.query(User).filter_by(email="eve@example.com").first()
if eve_user:
    eve_user.age = 29
# 删除
# user_to_delete = session.query(User).filter_by(name="David").first()
# if user_to_delete:
#     session.delete(user_to_delete)
# 6. 提交所有更改
session.commit()
# 7. 关闭 session
session.close()

总结与最佳实践

选择库:
- 新项目: 优先选择 psycopg (psycopg3)，它更现代、性能更好。
- 旧项目维护: 如果项目已经在用 psycopg2，可以继续使用，但计划未来迁移到 psycopg。
- 复杂应用: 考虑使用 SQLAlchemy 这样的 ORM,它能将数据库操作与业务逻辑解耦。
连接管理:
- 始终使用 with 语句来管理连接和游标,确保它们被正确关闭。
- 在 Web 应用等场景中，使用连接池来管理数据库连接，而不是频繁地创建和销毁连接。psycopg 和 SQLAlchemy 都内置了连接池支持。
安全性:
- 永远不要拼接 SQL 字符串！ 始终使用参数化查询（%s 占位符）。
事务管理:
- 理解事务的概念，一组相关的操作应该在一个事务中完成，要么全部成功,要么全部失败。
- psycopg 的 with 语句会自动提交。psycopg2 需要你手动调用 conn.commit()。

希望这份详细的指南能帮助你在 Python 中顺利地使用 PostgreSQL！

Python如何连接PostgreSQL？

核心概念

第一步：安装 `psycopg` 库

第二步：连接到 PostgreSQL 数据库

使用 `psycopg` (推荐) 连接

使用 `psycopg2` 连接

第三步：执行 SQL 语句

创建表

插入数据

查询数据

更新和删除数据

第四步：使用 `psycopg` 的高级特性 (推荐)

使用 `Row` 对象获取字典式结果

自动提交事务

第五步：使用 ORM (对象关系映射)

使用 SQLAlchemy + `psycopg`

总结与最佳实践

99ANYc3cd6

add binary python

Centos系统安装教程步骤详解？

Java字符串用法有哪些核心要点？

foreach 数组 java

java调用sqlserver

Java Socket编程实例具体怎么实现？

Python contourf如何设置边缘线？

Visual Studio教程PDF哪里找？新手如何学？

数据结构与算法分析Java语言描述(第2版)有何核心更新？

Python geocoding API如何实现地址与坐标互转？

SQL Server 2008视频教程哪里学？

Dreamweaver网页设计项目教程

Java Exception Error，如何快速定位与解决？

Photoshop CS3教程下载哪里找？

Java异常捕获时，父类异常能否先于子类被catch？

Adobe Acrobat XI教程，从入门到精通怎么学？

Python如何连接PostgreSQL？

核心概念

第一步：安装 psycopg 库

第二步：连接到 PostgreSQL 数据库

使用 psycopg (推荐) 连接

使用 psycopg2 连接

第三步：执行 SQL 语句

创建表

插入数据

查询数据

更新和删除数据

第四步：使用 psycopg 的高级特性 (推荐)

使用 Row 对象获取字典式结果

自动提交事务

第五步：使用 ORM (对象关系映射)

使用 SQLAlchemy + psycopg

总结与最佳实践

相关推荐

Java Socket编程实例具体怎么实现？

第一步：安装 `psycopg` 库

使用 `psycopg` (推荐) 连接

使用 `psycopg2` 连接

第四步：使用 `psycopg` 的高级特性 (推荐)

使用 `Row` 对象获取字典式结果

使用 SQLAlchemy + `psycopg`