Skip to content

18. 编写数据库管理系统无关的代码

我们之前看到，在某些情况下，可以轻松地将为 MySQL 数据库管理系统编写的 Python 代码迁移到为 PostgreSQL 数据库管理系统编写的代码。在本章中，我们将展示如何将这种方法系统化。建议的架构如下：

Image

我们希望通过配置来选择连接器（从而选择 DBMS），而无需重写脚本。请注意，这仅在脚本未使用专有 DBMS 扩展的情况下才可行。

脚本的目录结构如下：

Image

[any_xx] 脚本基于之前针对 MySQL 和 PostgreSQL 数据库管理系统介绍过的脚本。我们不会逐一详述，而是重点讲解其中最复杂的 [any_04] 脚本。请注意，该脚本执行来自 [data/commandes.sql] 文件中的 SQL 命令：


# suppression de la table [personnes]
drop table if exists personnes
# création de la table personnes
create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))
# insertion de deux personnes
insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)
insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)
# affichage de la table
select prenom, nom, age from personnes
# erreur volontaire
xx
# insertion de trois personnes
insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)
insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)
insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)
# affichage de la table
select prenom, nom, age from personnes
# liste des personnes par ordre alphabétique des noms et à nom égal par ordre alphabétique des prénoms
select nom,prenom from personnes order by nom asc, prenom desc
# liste des personnes ayant un âge dans l'intervalle [20,40] par ordre décroissant de l'âge
# puis à âge égal par ordre alphabétique des noms et à nom égal par ordre alphabétique des prénoms
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# insertion de mme Bruneau
insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)
# mise à jour de son âge
update personnes set age=47 where nom='Bruneau'
# liste des personnes ayant Bruneau pour nom
select nom,prenom,age from personnes where nom='Bruneau'
# suppression de Mme Bruneau
delete from personnes where nom='Bruneau'
# liste des personnes ayant Bruneau pour nom
select nom,prenom,age from personnes where nom='Bruneau'

我们修改了第 2 行，以便当 [people] 表不存在时，该命令在 MySQL 和 PostgreSQL 数据库管理系统中表现一致。

[any_04]脚本由以下[config.py]脚本进行配置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
def configure():
    import os

    #  absolute path of this script's folder
    script_dir = os.path.dirname(os.path.abspath(__file__))

    #  syspath folder configuration
    absolute_dependencies = [
        #  local files
        f"{script_dir}/shared",
    ]

    #  syspath mounting
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  application configuration
    config = {
        #  managed sgbd
        "sgbds": {
            "mysql": {
                #  sgbd connector
                "sgbd_connector": "mysql.connector",
                #  name of the module containing sgbd management functions
                "sgbd_fonctions": "any_module",
                #  login credentials
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            },
            "postgresql": {
                #  sgbd connector
                "sgbd_connector": "psycopg2",
                #  name of the module containing sgbd management functions
                "sgbd_fonctions": "any_module",
                #  login credentials
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            }
        },
        #  command file SQL
        "commands_filename": f"{script_dir}/data/commandes.sql"
    }
    #  application syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  return the config
    return config

新更改位于第 18–43 行：

  • 第 20 行：[sgbds] 是一个包含两个键的字典：第 21 行的 [mysql] 和第 32 行的 [postgresql]；
  • 与这些键关联的值是一个字典，其中包含连接数据库管理系统（DBMS）所需的元素：
  • 第 21–32 行：连接 MySQL 数据库管理系统所需的元素；
    • 第 23 行：要使用的 Python 连接器；
    • 第 25 行：包含共享函数的模块；
    • 第 26–30 行：连接凭据；
  • 第 32–41 行：用于连接 PostgreSQL 数据库管理系统（DBMS）的相同参数；

执行 SQL 命令文件 [data/commandes.sql] 的脚本 [any_04] 如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
#  configure the application
import config

config = config.configure()

#  syspath is configured - imports can be made
import importlib
import sys

#  check call syntax
#  argv[0] sgbd_name true / false
#  3 parameters are required
args = sys.argv
erreur = len(args) != 3
if not erreur:
    #  we retrieve the two parameters of interest
    sgbd_name = args[1].lower()
    with_transaction = args[2].lower()
    #  check both parameters
    erreur = (with_transaction != "true" and with_transaction != "false") \
             or sgbd_name not in config["sgbds"].keys()
#  mistake?
if erreur:
    print(f"syntaxe : {args[0]} (1) sgbd_name (2) true / false")
    sys.exit()

#  sgbd_name configuration
sgbd_config = config["sgbds"][sgbd_name]
#  sgbd_name connector
sgbd_connector = importlib.import_module(sgbd_config["sgbd_connector"])
#  function library
lib = importlib.import_module(sgbd_config["sgbd_fonctions"])


#  calculation of text to be displayed
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

#  display
commands_filename=config['commands_filename']
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {commands_filename} {texte}")
print("--------------------------------------------------------------------")

#  execution of SQL orders in the file
connexion = None
try:
    #  connection
    connexion = sgbd_connector.connect(
        host=sgbd_config['host'],
        user=sgbd_config['user'],
        password=sgbd_config['password'],
        database=sgbd_config['database'])
    #  execution of SQL command file
    erreurs = lib.execute_file_of_commands(sgbd_connector, connexion, commands_filename, suivi=True, arrêt=False,
                                           with_transaction=with_transaction)
except (sgbd_connector.InterfaceError, sgbd_connector.DatabaseError) as erreur:
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    #  closing the connection
    if connexion:
        connexion.close()

#  display number of errors
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
#  error display
for erreur in erreurs:
    print(erreur)

注释

  • 第 1-4 行：获取应用程序配置 [config]；
  • 第 10-21 行：脚本通过两个参数 [db_name with_transaction] 调用：
    • [db_name]：要使用的数据库管理系统名称；
    • [with_transaction]：若需在事务内执行 SQL 脚本则设为 True，否则设为 False；
  • 第 10–25 行：获取并验证参数；
  • 第 28 行：配置所选的数据库管理系统；
  • 第 30 行：导入所选 DBMS 的连接器。为此使用了 [importlib] 库（第 7 行），该库允许导入一个名称存储在变量中的模块。[importlib.import_module] 操作的结果是一个模块。因此，在第 30 行之后，后续流程就如同执行了以下语句一样： 
import sgbd_connector

这使得我们可以在第 52 行编写 [sgbd_connector.connect]，其中我们使用了 [sgbd_connector] 模块的 [connect] 函数。这里需要记住的是，[sgbd_connector] 可以是 [mysql.connector] 也可以是 [psycopg2]。这两个模块都包含 [connect] 函数。 同样地，在第 60 行，我们可以写 [sgbd_connector.InterfaceError, sgbd_connector.DatabaseError]。

  • 第 32 行：我们导入包含脚本所用函数的模块；
  • 第 58 行：调用来自包含脚本所用函数的模块中的 [execute_file_of_commands] 函数。与之前的版本相比，该函数的签名多了一个参数——即第一个参数。我们将 Python 连接器 [sgbd_connector] 传递给该函数供其使用；
  • 除上述几点外，[any_04]脚本与之前版本保持一致；

[any_module] 函数库如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# ---------------------------------------------------------------------------------

def afficher_infos(curseur):
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(sgbd_connector, connexion, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    …

    #  initializations
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        #  a cursor is requested
        curseur = connexion.cursor()
        #  execution of sql_commands SQL contained in sql_commands
        #  they are executed one by one
        for command in sql_commands:
            #  eliminates blanks at the beginning and end of the current command
            command = command.strip()
            #  is there an empty command or a comment? If so, move on to the next command
            if command == '' or command[0] == "#":
                continue
            #  execute current command
            error = None
            try:
                curseur.execute(command)
            except (sgbd_connector.InterfaceError, sgbd_connector.DatabaseError) as erreur:
                error = erreur
            #  was there a mistake?
            …


# ---------------------------------------------------------------------------------
def execute_file_of_commands(sgbd_connector, connexion, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    …

    #  use of the SQL file
    try:
        #  open file for reading
        file = open(sql_filename, "r")
        #  operation
        return execute_list_of_commands(sgbd_connector, connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        #  an error table is returned
        return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
    finally:
        pass

第 31 行使用了 [sgbd_connector] 参数来指定拦截的异常类型。

使用参数 [mysql false] 运行 [any_04] 脚本将产生以下结果：


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/anysgbd/any_04.py mysql false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\anysgbd/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : 0
[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 : 0
[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 (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
[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 (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
 
Process finished with exit code 0
https://stahe.github.io
本教程由 Serge Tahé 编写，根据 知识共享 署名-非商业性使用- 相同方式共享 3.0 未本地化.