PostgreSQL | Database

基本概念

PostgreSQL 是一个功能强大的开源对象关系数据库管理系统，可以用于存储和管理各种类型的数据，包括结构化数据、半结构化数据和无结构化数据。与 MySQL 类似，PostgreSQL 也被开发人员广泛地应用于各种 Web 项目中。

PostgreSQL，最初被称为 Postgres，起源于 1986 年。它是由加州大学伯克利分校的教授 Michael Stonebraker 领导的一支团队开发的一个研究项目。最早的版本是基于 Ingres 数据库系统的一个改进版本，因此起初被称为 Postgres（Post Ingres）。这个项目的目标是探索新的数据库技术和概念，以改进关系型数据库系统的性能和功能。

在接下来的几年中，Postgres 项目不断演化和改进，其添加了许多新的功能和特性。1996 年，Postgres 项目发布了一个重要的版本，这个版本包括了一个新的查询语言解析器和一个全新的存储系统，这些变化使得 Postgres 更加灵活和强大。

2007 年，Postgres 项目正式更名为 PostgreSQL，以更好地反映其关系型数据库管理系统的性质。此时的 PostgreSQL 已经是一个功能丰富且强大的数据库系统，拥有广泛的社区支持和用户基础。

随着时间的推移，PostgreSQL 不断发展壮大，如今，它已经成为了一款非常受欢迎的开源数据库系统。由于其傲人的稳定性、可扩展性和可定制性，PostgreSQL 已经成为许多企业和组织的首选数据库解决方案之一。

与 MySQL 类似，Python 与 PostgreSQL 的结合也非常紧密。下面，就让我们详细地了解一下 PostgreSQL 在 Python 中的具体使用。

在 Python 中使用 PostgreSQL

作为开发人员，我们其实有多种方式在 Python 中操作 PostgreSQL 数据库，例如：

psycopg2 库
psycopg2是一个非常流行的 Python 库，用于连接和操作 PostgreSQL 数据库。它提供了对 PostgreSQL 数据库的高性能访问，支持数据库连接、执行 SQL 查询、事务管理等功能。psycopg2还支持自定义数据类型的转换，使得 Python 开发人员能够轻松地与 PostgreSQL 数据库交互。
SQLAlchemy
SQLAlchemy是一个强大的 ORM（对象关系映射）库，它支持多种数据库，其中就包括 PostgreSQL。使用SQLAlchemy，开发人员可以在 Python 中定义数据模型并将其映射到 PostgreSQL 数据库表，从而实现面向对象的数据库操作。
Django 和 Flask
Django和Flask是两个流行的 Python web 框架，它们都有内置的数据库支持，并且可以轻松地与 PostgreSQL 集成。开发人员可以使用这些框架来开发各种强大的 web 应用程序，并使用 PostgreSQL 作为后端数据库存储数据。

本节将以psycopg2库为主，探究 PostgreSQL 在 Python 中的应用。

PostgreSQL 在 Python 中的使用步骤

与 MySQL 类似，如果希望在 Python 中操作 PostgreSQL 数据库，我们一般参考如下的步骤：

安装

我们可以使用pip命令来安装psycopg2库：

pip install psycopg2

安装完成后，我们就可以在 Python 程序中导入psycopg2库了。

import psycopg2

建立数据库连接

操作 PostgreSQL 数据库的第一步，就是建立连接。

import psycopg2
 
connection = psycopg2.connect(
    host="localhost",
    port="5432",
    database="mydatabase",
    user="myuser",
    password="mypassword"
)

我们需要将上例中的"localhost"、"myuser"、"mypassword"和"mydatabase"替换为实际的连接信息。

返回值connection对象表示与 PostgreSQL 服务器的连接。我们可以在此连接上执行后续的数据库操作。

使用游标执行 SQL 查询

在数据库成功连接后，我们就可以使用游标执行 SQL 查询，并与数据库进行交互。

cursor = connection.cursor()
cursor.execute("SELECT column1, column2 FROM mytable")
 
rows = cursor.fetchall()
for row in rows:
    print(row[0], row[1])

在这里，我们执行了一个SELECT查询并获取游标返回的结果集。

关闭游标，关闭连接

与 MySQL 类似，我们在完成数据操作后，需要关闭游标、关闭数据库连接。

cursor.close()
connection.close()

下面让我们详细探究一下psycopg2库中各个方法的使用详情。

语句详解

`connect()`方法

psycopg2模块中的connect()方法可以帮助开发人员建立与 PostgreSQL 数据库的连接。

import psycopg2
 
# 连接到PostgreSQL数据库
connection = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_username",
    password="your_password",
    port="your_port"
)

其中，

host：用于指定 PostgreSQL 数据库服务器所在的计算机的主机名或 IP 地址。如果数据库在同一台计算机上运行，我们可以使用localhost或127.0.0.1作为值。
database：用于指定我们希望连接的 PostgreSQL 服务器中的数据库名称。
user：用于提供连接到 PostgreSQL 数据库的用户名。
password：用于指定与用户名关联的密码。需要注意的是，除非我们已使用 SSL 加密连接，否则此密码以明文形式发送。
port（可选）：如果我们的 PostgreSQL 数据库服务器在非默认端口上运行，那么我们就需要在此参数中指定端口。PostgreSQL 的默认端口是5432。

`cursor()`方法

在psycopg2模块中，cursor()方法主要用来创建一个游标对象，开发人员可以通过此游标对象来执行后续的 SQL 语句。

cursor()方法的基本语法如下所示：

# 创建游标对象
cursor = connection.cursor(name, cursor_factory)

其中，

name：字符串，用来指定游标的名称。在某些情况下--比如处理服务器端游标或使用FETCH命令时，这可能很有用。
cursor_factory：用来指定自定义游标工厂类，而不是使用默认的Cursor类。如果我们需要自定义游标的行为，可以使用该参数。注意：自定义游标工厂类必须继承自psycopg2.extensions.cursor类。

举个例子：

import psycopg2
 
connection = psycopg2.connect(
	database="mydb",
	user="myuser",
	password="mypassword",
	host="localhost",
	port="5432")
 
# 创建一个具名游标
cursor = connection.cursor(name="mycursor")
 
# 使用自定义游标工厂创建一个游标
class MyCursor(psycopg2.extensions.cursor):
    # 自定义游标实现...
    pass
 
cursor2 = connection.cursor(cursor_factory=MyCursor)
 
# 使用这些游标执行SQL语句
cursor.execute("SELECT * FROM mytable")
cursor2.execute("SELECT * FROM myothertable")
 
# 关闭这些游标和连接
cursor.close()
cursor2.close()
connection.close()

通常情况下，我们无需为cursor()方法指定参数。

`execute()`方法

psycopg2模块中的execute()方法用于在 PostgreSQL 数据库中执行 SQL 命令。其语法如下所示。

cursor.execute(sql, vars)

其中，

sql：必选参数，用于指定需要执行的 SQL 命令。它既可以是一个简单的 SQL 语句，也可以是一个预处理的 SQL 语句。SQL 可以包含以%s表示的占位符，这些占位符稍后可以用vars参数中提供的实际值来替换。
vars：可选参数，用于提供将替换 SQL 语句中占位符的实际值。这些值可以是单个值、元组或字典。如果使用字典，则占位符应与字典中的键对应。

举个例子：

import psycopg2
 
# 建立与PostgreSQL数据库的连接
connection = psycopg2.connect(
	host="localhost",
	database="mydatabase",
	user="myuser",
	password="mypassword")
 
# 创建一个游标对象
cursor = connection.cursor()
 
# 执行一个带有占位符的预处理的SQL语句
cursor.execute(
	"SELECT * FROM employees WHERE age > %s AND salary > %s",
	(30, 50000))
 
# 从结果集中获取所有行
results = cursor.fetchall()
 
# 根据需要处理结果
for row in results:
    print(row)
 
# 关闭游标和数据库连接
cursor.close()
connection.close()

在上面的示例中，我们调用execute()方法来执行一个带有两个占位符（%s）的预处理的 SQL 语句。在执行过程中，实际值(30, 50000)会替换 SQL 语句中的占位符。

`fetchone()` 方法和 `fetchall()` 方法

psycopg2模块中的fetchone()方法用于从查询结果集里面检索单个行。它将查询结果集中的下一行作为元组获取，并将游标位置向前移动一行；如果没有更多的行可用，则返回None。其语法如下所示：

cursor.fetchone()

相对应的，fetchall()方法用于从查询结果集中检索出所有行。它将查询结果集中的所有剩余行作为元组的列表获取。其语法如下：

cursor.fetchall()

让我们来看一个例子。

import psycopg2
 
# 连接到数据库
connection = psycopg2.connect(
	database="mydb",
	user="myuser",
	password="mypassword")
 
# 创建一个游标
cursor = connection.cursor()
 
# 执行查询
cursor.execute("SELECT * FROM users")
 
# 获取单个行
one_row = cursor.fetchone()
 
# 获取所有行
rows = cursor.fetchall()
 
# 处理行数据
for row in rows:
    print(row)
 
# 关闭游标和连接
cursor.close()
connection.close()

需要特别注意的是，我们应当谨慎使用fetchall()方法，因为它会一次性将所有行都获取到内存中。如果结果集非常大，这可能会消耗大量的内存。在这种情况下，最好使用循环中的fetchone()来逐个获取行数据，或者使用服务器端游标进行分页。

总结

综上所述，开发人员可以在 Python 中很容易地操作 PostgreSQL 数据库。通过使用类似psycopg2的库，开发人员可以很方便地与 PostgreSQL 数据库进行交互，执行各种 SQL 查询、插入、更新或者删除数据的操作。

除此之外，开发人员还可以使用更高级的 ORM 库来操作 PostgreSQL 数据库。这些内容，我们将在后续章节中进行讲解。

17.3 MySQL 17.5 SQLAlchemy