Skip to content

18. Writing DBMS-Independent Code

We saw earlier that in some cases it was possible to easily migrate Python code written for the MySQL DBMS to code written for the PostgreSQL DBMS. In this chapter, we show how to systematize this approach. The proposed architecture is as follows:

Image

We want the choice of connector—and therefore of the DBMS—to be made via configuration and not require rewriting the script. Note that this is only possible in cases where the script does not use proprietary DBMS extensions.

The script directory structure will be as follows:

Image

The [any_xx] scripts are based on the scripts already covered for the MySQL and PostgreSQL DBMSs. We will not go over all of them. We will focus on the [any_04] script, which is the most complex. Note that this script executes the SQL commands from the following [data/commandes.sql] file:


# 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'

We modified line 2 so that the command behaves the same way for both MySQL and PostgreSQL DBMSs if the [people] table does not exist.

The [any_04] script is configured by the following [config.py] script:

 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

The new changes are in lines 18–43:

  • line 20: [sgbds] is a dictionary with two keys: [mysql] on line 21 and [postgresql] on line 32;
  • the value associated with these keys is a dictionary containing the elements needed to connect to a DBMS:
  • lines 21–32: the elements for connecting to the MySQL DBMS;
    • line 23: the Python connector to use;
    • line 25: the module containing shared functions;
    • lines 26–30: the connection credentials;
  • lines 32–41: the same elements for a connection to the PostgreSQL DBMS;

The script [any_04] that executes the SQL command file [data/commandes.sql] is as follows:

 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)

Comments

  • lines 1-4: retrieve the application configuration [config];
  • lines 10-21: the script is called with two parameters [db_name with_transaction]:
    • [db_name]: the name of the database management system to use;
    • [with_transaction]: True if you want to execute the SQL script within a transaction, False otherwise;
  • lines 10–25: the parameters are retrieved and verified;
  • line 28: configuration of the selected DBMS;
  • line 30: the connector for the selected DBMS is imported. To do this, the [importlib] library (line 7) is used, which allows importing a module whose name is stored in a variable. The result of the [importlib.import_module] operation is a module. Thus, after line 30, everything proceeds as if the executed statement had been: 
import sgbd_connector

This allows us to write [sgbd_connector.connect] on line 52, where we use the [connect] function of the [sgbd_connector] module. It is important to remember here that [sgbd_connector] is either [mysql.connector] or [psycopg2]. Both of these modules have the [connect] function. Similarly, on line 60, we can write [sgbd_connector.InterfaceError, sgbd_connector.DatabaseError].

  • Line 32: We import the module containing the functions used by the script;
  • Line 58: The [execute_file_of_commands] function from the module containing the functions used by the script is called. Compared to previous versions, this function’s signature has one additional parameter—the first one. We pass the Python connector [sgbd_connector] to the function for it to use;
  • Apart from these points, the [any_04] script remains unchanged from previous versions;

The [any_module] function library is as follows:

 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

The [sgbd_connector] parameter was used on line 31 to specify the type of intercepted exceptions.

Running the [any_04] script with the parameters [mysql false] yields the following results:


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
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.