17. 使用 SGBD PostgreSQL

SGBD PostgreSQL 是开源的。它是 MySQL “社区版”的替代方案。

我们在此使用它来展示，将 Python / MySQL 脚本迁移到 Python / PostgreSQL 脚本是相当简单的。

在 SGBD 和 MySQL 版本中，我们的脚本架构如下：

在 SGBD 和 PostgreSQL 中，架构将如下所示：

17.1. 安装 SGBD PostgreSQL

SGBD PostgreSQL 的发行版可在 URL [https://www.postgresql.org/download/]（2019年5月）中获取。以下展示 64 位 Windows 版本的安装过程：

• 在 [1-4] 中，需下载 SGBD 的安装程序；

运行下载的安装程序：

• 在 [6] 中，指定一个安装文件夹；

• 在 [8] 中，选项 [Stack Builder] 对于我们此处的操作没有用；
• 在 [10] 中，保留系统默认的值；

• 在 [12-13] 处，我们已在此设置了密码 [root]。 这将是 SGBD 的管理员密码，该管理员名为 [postgres]。PostgreSQL 也称其为超级用户；
• 在 [15] 处，保留默认值：这是 SGBD 的监听端口；

• 在 [17] 中，保留默认值；
• 在 [19] 中，显示安装配置摘要；

在 Windows 系统中，SGBD PostgreSQL 被安装为自动启动的 Windows 服务。大多数情况下，这并非理想状态。 我们将修改此配置。在 Windows 搜索栏中输入 [services] [24-26]：

• 为 [29]，可以看到 SGBD 和 PostgreSQL 服务处于自动模式。通过访问 [30] 服务的属性来更改此设置：

• 在 [31-32] 中，将启动方式设为手动；
• 对于 [33]，停止该服务；

当您需要手动启动 SGBD 时，请返回 [services] 应用程序，右键单击 [postgresql] 服务 (34) 并启动它 (35)。

17.2. 使用工具 [pgAdmin] 管理 PostgreSQL

启动 SGBD 的 Windows 服务 PostgreSQL（参见前一段）。然后，与启动工具 [services] 的方法相同， 启动工具 [pgadmin]，该工具用于管理 SGBD、PostgreSQL 和 [1-3]：

在某些情况下，系统可能会要求您输入超级用户的密码。该密码名为 [postgres]。 您在安装 SGBD 时已设置了该密码。在本文档中，我们在安装过程中为超级用户设置的密码是 [root]。

• 在 [4] 中，[pgAdmin] 是一个 Web 应用程序；
• 在 [5] 中，[pgAdmin] 检测到的 PostgreSQL 服务器列表，此处为 1；
• 在 [6] 中，是我们启动的 PostgreSQL 服务器；
• 在 [7] 中，SGBD 的数据库，此处为 1；
• 在 [8] 中，[postgresql] 数据库由超级用户 [postgres] 管理；

首先创建用户 [admpersonnes]，密码为 [nobody]：

• 在 [17] 中，我们设置了 [nobody]；

• 在[21]中，[pgAdmin]工具将向SGBD和PostgreSQL生成代码SQL。 这是学习 SQL 专有语言的一种方式，该语言属于 PostgreSQL；
• 在[22]中，经过[Save]向导的验证，已创建用户[admpersonnes]；

现在我们创建数据库 [dbpersonnes]：

右键单击 [23]，然后选择 [24-25] 以创建新的数据库。 在 [26] 选项卡中，将数据库名称设置为 [27]，所有者设置为 [admpersonnes] [28]。

• 在 [30] 中，数据库创建代码 SQL；
• 在 [31] 中，经过向导 [Save] 的验证后，数据库 [dbpersonnes] 已创建；

我们将使用 Python 脚本对 [dbpersonnes] 数据库进行操作。

17.3. 安装 SGBD 的 Python 连接器 PostgreSQL

上图展示了一个连接器，用于连接 Python 脚本与 SGBD PostgreSQL。此类连接器有多个。 我们将安装 [psycopg2] 连接器。此操作需在 Python 终端中进行（终端所在的文件夹无关紧要）。通过以下命令安装该连接器：

(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\troiscouches\v01\tests>pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
     || 1.1 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5

17.4. 将 MySQL 脚本迁移至 PostgreSQL 脚本

• 将 MySQL 脚本中的 [1] 文件夹复制（Ctrl-C / Ctrl-V），然后更改文件名但不更改其内容；

17.4.1. 模块 [pgres_module]

该模块是模块 [mysql_module] 的副本（参见段落 |脚本 [mysql-04]：执行命令文件 SQL|）。修改导入语句：

原代码：

# 导入
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

改为：

# 导入
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

函数 [afficher_infos] 的签名原为：

def afficher_infos(curseur: MySQLCursor):

现在变为：

def afficher_infos(curseur: cursor)

函数 [execute_list_of_commands] 的签名原为：

def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True)

现在变为：

def execute_list_of_commands(connexion: connection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):

除此之外，其他内容保持不变。

17.4.2. 脚本 [pgres_01]

脚本 [pgres_01] 是脚本 [mysql_01] 的副本（参见段落 |脚本 [mysql-01]：连接到数据库 MySQL - 1|）。其中进行了以下修改：

将：

# 导入模块 mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

改为：

# 导入 psycopg2 模块
from psycopg2 import connect, DatabaseError, InterfaceError

其余部分保持不变。结果与使用 MySQL 时相同。

17.4.3. 脚本 [pgres_02]

脚本 [pgres_02] 是脚本 [mysql_02] 的副本（参见段落 |脚本 [mysql-02]：连接到数据库 MySQL - 2|）。其中进行了以下修改：

将：

# 导入模块 mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

改为：

# 导入 psycopg2 模块
from psycopg2 import DatabaseError, InterfaceError, connect

结果与脚本 [mysql_02] 的结果不同：

C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

Process finished with exit code 0

脚本 [pgres_02] 如下：

# 导入模块 mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # 连接并断开（用户名、密码）[database] 数据库与 [host] 服务器的连接
    # 若出现问题则抛出异常 DatabaseError
    connexion = None
    try:
        # 连接
        connexion = connect(host=host, user=login, password=pwd, database=database)
        print(
            f"Connexion réussie à la base database={database}, host={host} sous l'identité user={login}, passwd={pwd}")
    finally:
        # 如果连接已建立，则关闭该连接
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# 连接凭据
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# 现有用户的登录
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # 显示错误
    print(erreur)

# 登录不存在的用户
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # 显示错误
    print(erreur)

虽然第36-41行本应显示一条错误消息，指出连接到SGBD失败，但实际上并未显示任何内容。 实际上，经深入排查发现，虽然第35-37行确实调用了[except]，但变量[erreur]的值却是[None]。 此问题出现在 [psycopg2] 连接器的 2.8.4 版本中。

可以通过编写一条通用但精度较低的消息来规避此问题：

# 登录不存在的用户
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # 显示错误
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

此时结果如下：

C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_02.py
Connexion réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion réussie

Erreur de connexion à la base [dbpersonnes] par l'utilisateur [xx/yy]

Process finished with exit code 0

17.4.4. 脚本 [pgres_03]

脚本 [pgres_03] 是脚本 [mysql_03] 的副本（参见段落 |脚本 [mysql-03]：创建表 MySQL|）。其中进行了以下修改：

将：

from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection

改为：

from psycopg2 import DatabaseError, InterfaceError, connect
from psycopg2.extensions import connection

此外，函数 [execute_sql] 的签名原为：

def execute_sql(connexion: MySQLConnection, update: str):

变为：

def execute_sql(connexion: connection, update: str):

其余部分保持不变。结果如下：

C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_03.py
create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, unique(nom,prenom))  : requête réussie

Process finished with exit code 0

可通过管理工具 [pgAdmin] 验证表 [personnes] 是否存在：

17.4.5. 脚本 [pgres_04]

脚本 [pgres_04] 是脚本 [mysql_04] 的副本（参见段落 |脚本 [mysql-04]：执行命令文件 SQL|）。 它使用模块 [pgres_module]：

# 正在检索应用程序配置
import config_04

config = config_04.configure()

# 已配置 syspath - 可以进行导入
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

其余部分保持不变。

创建配置 [pgres pgres-04 without_transaction]，操作方法与段落 |脚本 [mysql-04]：执行命令文件 SQL| 中所述相同。同样地，创建配置 [pgres pgres-04 with_transaction]。

执行配置 [pgres pgres-04 without_transaction] 得到以下结果：

C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : -1
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
[insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom, prenom,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
nom, prenom, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)] : Exécution réussie
nombre de lignes modifiées : 1
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from personnes where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
*****************
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0

• 第 5 行：必须修改表 [personnes] 的删除命令。与 MySQL 的连接器不同，PostgreSQL 的连接器会在待删除表不存在时抛出异常。 命令 [drop table] 有一个变体 [drop table if exists]，该变体在表不存在时不会抛出异常。我们在此使用了该变体。这是一个典型的例子，说明两个 SGBD 在类似情况下表现不一致；

工具 [pgAdmin] 中的表 [personnes] 如下所示：

执行配置 [pgres pgres_04 with_transaction] 后，结果如下：

C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : -1
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0

工具 [pgAdmin] 中的表 [personnes] 如下所示：

此处的结果与使用 MySQL 获得的结果不同。如果在相同条件下执行脚本，即在执行无事务脚本之后，结果如下：

• 使用 MySQL 时，表 [personnes] 为空；
• 使用 PostgreSQL 时，表 [personnes] 并非如此；

差异在于这两个 SGBD 撤销事务的方式不同：

• MySQL 不会回滚 [drop table] 和 [create table] 事务。最终 [personnes] 表为空；
• PostgreSQL 撤销了 [drop table] 和 [create table] 命令。该表恢复到执行带事务脚本之前的状态；

17.4.6. 脚本 [pgres_05]

脚本 [pgres_05] 是脚本 [mysql_05] 的副本（参见段落 |脚本 [mysql-05]：参数化查询的使用|）。该脚本修改如下：

原代码：

# 导入
from mysql.connector import connect, DatabaseError, InterfaceError

改为：

# 导入
from psycopg2 import connect, DatabaseError, InterfaceError

其余部分保持不变。

在 [pgAdmin] 中获得的结果如下：

17.5. 结论

将脚本 MySQL 移植到 PostgreSQL 脚本的过程相当顺利。这属于特例。 两个 SGBD 版本不支持 SQL 对象（数据库、表、列、约束、数据类型等）相同的命名规则，且扩展名 SQL 不兼容…… 为了确保移植的简便性，这两种情况都必须遵循 SQL 标准，而不要尝试使用 SGBD 的专有扩展。但这会以牺牲性能为代价。