如何使用 Psycopg2：Python 的 PostgreSQL 适配器

作为后端开发，少不了与关系型数据库打交道，PostgreSQL 数据库我想大家应该会比较熟悉，也是我们常用的关系型数据库之一，这里介绍一款兼容PostgreSQL的免费云数据库MemFireDB，云数据库秉持拿来即用的原则，可以省去我们在本地或者服务器上去搭建数据库的时间，MemfireDB目前作为免费使用阶段，还是比较稳定的，推荐大家尝试试用一下。

Python 是最流行的编程语言之一，广泛用于数据分析、可视化和数据科学。本指南将引导您了解如何通过 Psycopg2（最流行的 PostgreSQL 适配器之一）集成 PostgreSQL 和 Python 代码。

---

使用 Python 连接到 PostgreSQL 数据库

PostgreSQL 适配器充当桥梁，使您能够直接从应用程序和编程语言直接与 PostgreSQL 数据库进行交互。

对于 Python 的特殊情况，以下是一些最流行的 PostgreSQL 适配器：

asyncpg

asyncpg 是一种独特的数据库适配器，以其卓越的性能和异步处理能力而闻名。asyncpg 专为 Python 的 asyncio 框架设计，可与 PostgreSQL 数据库进行非阻塞、异步通信。这确保了应用程序的响应速度和可扩展性，尤其是在高负载情况下。

在处理并发数据库连接方面的优点使 asyncpg 有别于其他适配器，成为开发人员构建高性能、I/O 密集型应用程序的首选。

asyncpg 支持 PostgreSQL 9.2 及更高版本，专为 Python 3.5 及更高版本设计。

SQLAlchemy

SQLAlchemy 不仅是一个适配器，更是一个适用于 Python 应用程序的综合 SQL 工具包和对象关系映射（ORM）系统。它抽象了数据库通信的复杂性，允许开发人员使用 Pythonic 表达式与数据库交互。

SQLAlchemy 的 ORM 使开发人员能够将 Python 对象映射到数据库表，从而促进更高层次的面向对象的数据库交互。这种功能丰富的适配器非常适合那些需要大量工具来简化数据库交互的初高级开发人员。

SQLAlchemy 可用于各种数据库，包括 PostgreSQL。

Psycopg2

Psycopg2 因其全面的功能、健壮性和可扩展性而成为一种流行的数据库适配器，在与PostgreSQL 进行交互的 Python 开发人员中深受喜爱。它是通过 C 扩展实现的，这有助于提高其性能效率。

Psycopg2 支持一系列 PostgreSQL 功能，包括服务器端游标、异步通知和 COPY 命令。此外，它还具有线程安全和连接池功能。它之所以被广泛采用，是因为它的可靠性以及与各种版本的 PostgreSQL 和 Python 的兼容性，使其成为开发各种应用程序的普遍选择。

psycopg2 支持 PostgreSQL 7.4 及以上版本和 Python 2.5 至 最新3.12 版本。（目前 Psycopg2 从2.9 至最新版本仅支持 pyhon3.6 及以上版本）

---

Psycopg2的优势

Psycopg2无缝集成了 Python 和 PostgreSQL，让这两种技术的协同工作变得更加简单。它提供了一组 Python 模块，让你能建立与 PostgreSQL 数据库的连接、执行 SQL 查询并轻松检索数据。

以下是它的主要优势：

• 连接稳定。Psycopg2 擅长在 Python 应用程序和 PostgreSQL 数据库之间建立和维护稳定的连接。它能保证可靠、不间断的数据传输通道，提高数据交换和通信的效率。

• 高效的 SQL 执行能力。它善于处理各种任务，包括数据检索、记录修改和执行复杂操作，确保最佳性能和准确性。

• 实时数据同步。Psycopg2 可帮助您开发实时应用程序，因为它能确保 Python 代码与 PostgreSQL 数据库保持同步。这一功能有助于创建反应灵敏、数据驱动的应用程序，从而有效地适应动态数据变化。

• 稳健性。Psycopg2 具有公认的稳定性和可靠性，是核心应用的理想选择。该库可处理各种 PostgreSQL 功能、复杂数据类型和大型对象，确保高风险项目所需的精确性和可靠性。

• 兼容性。Psycopg2 可与不同的 Python 版本和 PostgreSQL 版本无缝集成，为您的项目提供兼容性和多功能性。这可确保您的 Python 代码在各种环境中都能正常运行。

• 活跃的社区。Psycopg2 受益于持续的开发和支持性社区。

---

Psycopg2的使用场景

数据分析与报表

数据分析师和科学家经常使用 Psycopg2 对 PostgreSQL 数据库进行交互。例如，想象一下营销公司的数据分析师使用 Psycopg2 从 PostgreSQL 数据库中检索客户数据的情景。有了这些数据，他们就能做出有洞察力的报告，从而分析趋势并做出数据驱动的决策，以此来加强营销策略。

Web 开发

在网站开发中，Psycopg2 对于建立动态的、数据库驱动的网站来说非常重要。在电子商务网站中，Psycopg2 可用于管理存储在 PostgreSQL 数据库中的产品库存、客户订单和用户账户。这样就能确保为客户提供流畅的购物体验，并为企业提供高效的库存管理。

商业应用

各行各业的企业都将 Psycopg2 用于核心应用中。例如，金融机构可以使用 Psycopg2 来维护安全、强大的客户交易和账户数据库。这确保了数据的完整性、可靠性和对金融数据的快速访问。

物联网和实时应用

在物联网领域，Psycopg2 在捕捉和存储实时传感器数据方面发挥着至关重要的作用。试想一下，一个智慧城市项目可以依靠 Psycopg2 收集和分析来自交通摄像头和空气质量监测器等各种传感器的数据，这些数据可用于优化交通流量、改善空气质量和加强整体城市管理。

科学研究

科学家和研究人员利用 Psycopg2 来存储和分析科学数据。例如，在一个涉及气候数据的研究项目中，Psycopg2 可用于在 Psycopg2 数据库中存储温度和天气数据。然后，研究人员就可以进行复杂的数据分析并生成气候模型，从而更好地了解气候模式。
 

---

Psycopg2的安装说明

在开始安装之前，请确保您当前已具备以下条件：

• 系统中已安装 Python（Python 3.6 或更高版本）。

• 访问 PostgreSQL 数据库。

安装步骤

使用 Python 的软件包管理器 pip 进行安装 Psycopg2。打开终端或命令提示符，运行以下命令：

pip install psycopg2

您还可以使用此命令安装 Psycopg2 的最新版本，包括必要的二进制依赖项：

pip install psycopg2-binary

为确保正确安装 Psycopg2，请执行以下检查：

• 打开 Python 解释器或创建 Python 脚本并导入 Psycopg2。如果没有导入错误，说明 Psycopg2 已正确安装。

• 可以用以下代码验证已安装的 Psycopg2 版本：

print(psycopg2.__version__)

解决常见安装问题

虽然 Psycopg2 的安装一般都很简单，但当遇到一些安装问题时，请做对自己当前环境做如下检查：

• Psycopg2 依赖于 PostgreSQL 的 C 库。如果在安装过程中遇到缺少库的错误，请确保系统中已安装 PostgreSQL 相关依赖库。

• 如果使用虚拟环境进行 Python 开发，请确保在运行安装命令时激活了相应的虚拟环境。

• 确认你的 Python 版本符合 Psycopg2 的要求。如果遇到兼容性问题，请考虑升级到兼容的 Python 版本。

---

使用 Psycopg2 连接 PostgreSQL 数据库

上述安装完毕后，接下来我们进行连接 PostgreSQL 数据库！

下面是一个使用 Psycopg2 建立数据库连接的示例：

import psycopg2
# Defining database connection parameters
db_params = {
    "host": "your_database_host",
    "database": "your_database_name",
    "user": "your_database_user",
    "password": "your_database_password",
    "port": "your_database_port"
}
try:
    # Establishing a connection to the database
    connection = psycopg2.connect(**db_params)
    # Creating a cursor object to interact with the database
    cursor = connection.cursor()
    # Performing database operations here...
except (Exception, psycopg2.Error) as error:
    print(f"Error connecting to the database: {error}")

finally:
    if connection:
        cursor.close()
        connection.close()
        print("Database connection closed.")

在这段代码中，我们首先导入 psycopg2 模块，该模块提供了从 Python 与 PostgreSQL 数据库交互所需的功能。db_params 字典包含以下建立数据库连接所需的参数：

• host：指定数据库服务器的主机名或 IP 地址。

• database：指定要连接的数据库名称。

• user：指定用于身份验证的用户名。

• password：指定用于身份验证的密码。

• 端口：指定要连接的端口号。PostgreSQL 的默认端口号为 5432。

代码被包裹在一个 try 代码块中，用于处理数据库连接过程中可能出现的异常或错误。在 try 代码块中，psycopg2.connect(**db_params) 用于建立与数据库的连接。**db_params 语法将字典中定义的连接参数传递给 connect 函数。

成功建立连接后，将使用 connection.cursor() 创建一个游标对象。游标用于执行 SQL 查询和与数据库交互。except 块会捕获连接过程中可能出现的任何异常或错误，如果出现问题，它会打印错误信息。finally 程序块确保游标和连接被正确关闭。

---

使用 Psycopg2 执行SQL

在本节中，我们假设已经建立了数据库连接，因此将重点展示一些可以使用 Psycopg2 运行的SQL示例。

使用 Psycopg2 执行 SELECT

下面的代码展示了如何使用 Psycopg2 执行 SELECT 查询：

# Defining the SELECT query
select_query = "SELECT column1, column2 FROM your_table_name WHERE condition;"

# Executing the SELECT query
cursor.execute(select_query)

# Fetching and printing the results
result = cursor.fetchall()
for row in result:
    print(row)

通过这段代码，我们可以实现以下功能：

• 我们定义了 SELECT 查询，并包含了所需列和条件。

• 执行方法用于执行查询。

• 使用 cursor.fetchall() 获取结果，然后循环打印记录。

使用 psycopg2 执行 INSERT

# Defining the INSERT query
insert_query = "INSERT INTO your_table_name (column1, column2) VALUES (value1, value2);"

# Executing the INSERT query
cursor.execute(insert_query)

# Committing the transaction to save changes
connection.commit()

使用 Psycopg2 执行 UPDATE 和 DELETE

# Defining the UPDATE query
update_query = "UPDATE your_table_name SET column1 = new_value WHERE condition;"

# Executing the UPDATE query
cursor.execute(update_query)

# Committing the transaction to save changes
connection.commit()

# Defining the DELETE query
delete_query = "DELETE FROM your_table_name WHERE condition;"

# Executing the DELETE query
cursor.execute(delete_query)

# Committing the transaction to save changes
connection.commit()

---

常见的Psycopg2错误及其解决方案

接下来，我们来探讨一下在使用Psycopg2与PostgreSQL数据库进行交互时最常遇到的错误以及解决方案。

psycopg2.OperationalError

import psycopg2

try:
    connection = psycopg2.connect(
        dbname="yourdbname",
        user="youruser",
        password="yourpassword",
        host="yourhost",
        port="yourport"
    )
    # Additional database operations here

except psycopg2.OperationalError as error:
    print(f"OperationalError: {error}")

psycopg2.OperationalError 是常见错误之一，产生错误的主要原因有以下几种：

• 连接失败。可能与 PostgreSQL 服务器的连接有关。请确保服务器正常运行并可从客户端机器访问。

• 数据库信息或用户账户密码错误。数据库名称或端口或用户账号密码不正确也会导致 OperationalError。请确保数据库的连接信息正确无误。

• 网络问题。网络问题（如主机、端口不正确或网络无法访问）也会引发此错误。请确保网络配置正确且服务器可连接。

• 权限不足。用户可能不具备连接指定数据库所需的权限。确保用户拥有必要的权限。

• 服务器超载或停机。PostgreSQL 服务器可能宕机或遇到过载问题--确认服务器健康状况良好并可正常运行。

psycopg2.ProgrammingError

import psycopg2

try:
    connection = psycopg2.connect(
        dbname="yourdbname",
        user="youruser",
        password="yourpassword",
        host="yourhost",
        port="yourport"
    )
    cursor = connection.cursor()
    # Replace the next line with your actual SQL query
    cursor.execute("YOUR SQL QUERY HERE")
    connection.commit()

except psycopg2.ProgrammingError as error:
    print(f"ProgrammingError: {error}")

这种错误通常是正在执行的 SQL 查询的结构或语法出现异常的信号。这些是最常见的原因：

• SQL 语法错误。SQL 语法中可能存在错字、运算符放错或其他错误。

• 无效的表/列名称。您可能引用了不存在的表或列。

• 数据类型不正确。如果数据类型不匹配或试图在列中插入不正确的数据类型，也会出现此错误。

• 权限问题。最后，您可能试图在没有必要权限的情况下访问或操作数据库对象。

psycopg2.IntegrityError

import psycopg2

try:
    connection = psycopg2.connect(
        dbname="yourdbname",
        user="youruser",
        password="yourpassword",
        host="yourhost",
        port="yourport"
    )
    cursor = connection.cursor()
    # Replace the next line with your actual SQL query
    cursor.execute("YOUR SQL QUERY HERE")
    connection.commit()

except psycopg2.IntegrityError as error:
    print(f"IntegrityError: {error}")
    connection.rollback()

当尝试的操作威胁到数据库的完整性约束时，就会出现此错误，例如

• 违反唯一性约束（即试图在受约束只能有唯一值的列中插入重复值）。

• 违反外键约束（即试图在外键列中插入一个不存在于引用的主键列中的值）。

• 违反校验约束（即插入不满足列校验约束的数据）。

• 违反非空约束（即试图在定义为 NOT NULL 的列中插入空值）。

psycopg2.DataError

import psycopg2

try:
    connection = psycopg2.connect(
        dbname="yourdbname",
        user="youruser",
        password="yourpassword",
        host="yourhost",
        port="yourport"
    )
    cursor = connection.cursor()
    # Replace the next line with your actual SQL query
    cursor.execute("YOUR SQL QUERY HERE")
    connection.commit()

except psycopg2.DataError as error:
    print(f"DataError: {error}")
    connection.rollback()

该错误与数据值有关，特别是当插入或操作的数据类型或格式与数据库列的预期数据类型不兼容时。要解决这个问题，请检查正在插入或更新的数据，确保其类型、格式和值与列的规格兼容。

---

Psycopg2 使用建议

Logging errors

通过在 Psycopg2 工作流程中加入日志机制，可以更轻松地跟踪和分析复杂问题。对于与数据库交互的应用程序来说，这是一种很好的做法。

例如下面的示例代码：

import psycopg2
import logging

# Configuring logging
logging.basicConfig(filename='database_errors.log', level=logging.ERROR, 
                    format='%(asctime)s:%(levelname)s:%(message)s')

try:
    connection = psycopg2.connect(
        dbname="yourdbname",
        user="youruser",
        password="yourpassword",
        host="yourhost",
        port="yourport"
    )
    cursor = connection.cursor()
    cursor.execute("YOUR SQL QUERY HERE")
    connection.commit()

except (Exception, psycopg2.Error) as error:
    # Rolling back the transaction in case of error
    connection.rollback()
    # Logging the error
    logging.error(f"Error: {error}")
    print(f"Error: {error}")

finally:
    # Closing the cursor and connection
    if connection:
        cursor.close()
        connection.close()

该日志配置通过格式化参数进行了丰富，其中包括了时间戳、严重性级别和错误信息。这样就能更全面地记录错误，确保在发生错误时回滚事务，避免部分数据提交导致不一致。

关闭游标和连接等数据库资源对防止资源泄漏也很重要，这可以在finally代码块中进行处理。

始终检查数据库连接

在执行查询前，请检查数据库连接是否仍处于打开状态，以避免出现与连接关闭有关的错误：

import psycopg2

# Assuming 'connection' is the established database connection

try:
    # Checking if the connection is still open
    if connection.closed == 0:
        # Database operations here
    else:
        print("Database connection is closed.")
except (Exception, psycopg2.Error) as error:
    print(f"Error: {error}")

对输入进行验证以防止 SQL 注入攻击

SQL 注入攻击是一种安全漏洞，当攻击者能够在查询中插入恶意 SQL 代码时就会发生。当应用程序允许在 SQL 中包含用户输入而不进行适当验证或转义时，就会发生这种情况。

当数据库执行这些恶意输入时，可能会导致未经授权的访问、数据被盗、损坏或其他不利影响。在使用 Psycopg2 时，需要防止这种情况的发生。

在本代码段中，对用户提供的输入进行了排查和验证，以防止 SQL 注入攻击。使用 Psycopg2 的 adapt 函数创建了 safe_input 变量，确保用户输入经过正确转义，可以安全地用于 SQL 查询：

import psycopg2

# User-provided input
user_input = "'; DROP TABLE users --"

try:
    # Sanitizing and validating user input
    safe_input = psycopg2.extensions.adapt(user_input).getquoted()
    
    # Using safe_input in our query
    query = f"SELECT * FROM users WHERE username = {safe_input};"

    # Executing the query
    cursor.execute(query)

except (Exception, psycopg2.Error) as error:
    print(f"Error: {error}")