Skip to content

18. SGBD-onafhankelijke code schrijven

We hebben eerder gezien dat het in sommige gevallen mogelijk was om Python-code die geschreven was voor de SGBD MySQL eenvoudig te migreren naar code die geschreven was voor de SGBD PostgreSQL. In dit hoofdstuk laten we zien hoe we deze aanpak kunnen systematiseren. De voorgestelde architectuur ziet er als volgt uit:

Image

We willen dat de keuze van de connector, en dus van de SGBD, via de configuratie gebeurt en dat het script niet hoeft te worden herschreven. We herinneren eraan dat dit alleen mogelijk is in gevallen waarin het script geen eigen extensies van de SGBD gebruikt.

De structuur van de scripts ziet er als volgt uit:

Image

De [any_xx]-scripts zijn gebaseerd op de scripts die al zijn besproken voor de SGBD, MySQL en PostgreSQL. We gaan ze niet allemaal opnieuw behandelen. We concentreren ons op het script [any_04], dat het meest complex is. Ter herinnering: dit script voert de commando’s SQL uit uit het volgende bestand [data/commandes.sql]:


# verwijdering van de tabel [personnes]
drop table if exists personnes
# de tabel 'personen' aanmaken
create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))
# twee personen invoegen
insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)
insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)
# weergave van de tabel
select prenom, nom, age from personnes
# opzettelijke fout
xx
# toevoegen van drie personen
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)
# de tabel weergeven
select prenom, nom, age from personnes
# lijst van personen in alfabetische volgorde van de achternamen en bij gelijke achternamen in alfabetische volgorde van de voornamen
select nom,prenom from personnes order by nom asc, prenom desc
# lijst van personen met een leeftijd in het interval [20,40], in aflopende volgorde van leeftijd
# vervolgens, bij gelijke leeftijd, in alfabetische volgorde van de achternamen en bij gelijke achternamen in alfabetische volgorde van de voornamen
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# toevoeging van mevrouw Bruneau
insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)
# bijwerking van haar leeftijd
update personnes set age=47 where nom='Bruneau'
# lijst van personen met de achternaam Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# verwijdering van mevrouw Bruneau
delete from personnes where nom='Bruneau'
# lijst van personen met de achternaam Bruneau
select nom,prenom,age from personnes where nom='Bruneau'

We hebben regel 2 aangepast zodat het commando zich op dezelfde manier gedraagt voor de SGBD, MySQL en PostgreSQL als de tabel [personnes] niet bestaat.

Het script [any_04] wordt geconfigureerd door het volgende script [config.py]:


def configure():
    import os

    # absoluut pad naar de map van dit script
    script_dir = os.path.dirname(os.path.abspath(__file__))

    # configuratie van de mappen in de syspath
    absolute_dependencies = [
        # lokale mappen
        f"{script_dir}/shared",
    ]

    # syspath instellen
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # configuratie van de applicatie
    config = {
        # beheerde DBMS'en
        "sgbds": {
            "mysql": {
                # DBMS-connector
                "sgbd_connector": "mysql.connector",
                # naam van de module die de beheerfuncties van de DBMS bundelt
                "sgbd_fonctions": "any_module",
                # inloggegevens
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            },
            "postgresql": {
                # DBMS-connector
                "sgbd_connector": "psycopg2",
                # naam van de module die de beheerfuncties van het DBMS bundelt
                "sgbd_fonctions": "any_module",
                # inloggegevens
                "user": "admpersonnes",
                "password": "nobody",
                "host": "localhost",
                "database": "dbpersonnes"
            }
        },
        # opdrachtbestand SQL
        "commands_filename": f"{script_dir}/data/commandes.sql"
    }
    # syspath van de applicatie
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # de configuratie wordt doorgegeven
    return config

De wijziging zit in de regels 18-43:

  • regel 20: [sgbds] is een woordenboek met twee sleutels: [mysql] op regel 21 en [postgresql] op regel 32;
  • de waarde die aan deze sleutels is gekoppeld, is een woordenboek met de elementen die nodig zijn om verbinding te maken met een SGBD:
  • regels 21-32: de onderdelen van een verbinding met de SGBD en de MySQL;
    • regel 23: de te gebruiken Python-connector;
    • regel 25: de module met gedeelde functies;
    • regels 26-30: de inloggegevens voor de verbinding;
  • regels 32-41: dezelfde gegevens voor een verbinding met SGBD PostgreSQL;

Het script [any_04] dat het batchbestand SQL [data/commandes.sql] uitvoert, is als volgt:


# de applicatie wordt geconfigureerd
import config

config = config.configure()

# het syspath is geconfigureerd – we kunnen de imports uitvoeren
import importlib
import sys

# controle van de syntaxis van de aanroep
# argv[0] sgbd_name true / false
# er zijn 3 parameters nodig
args = sys.argv
erreur = len(args) != 3
if not erreur:
    # we halen de twee parameters op die voor ons van belang zijn
    sgbd_name = args[1].lower()
    with_transaction = args[2].lower()
    # controle van de twee parameters
    erreur = (with_transaction != "true" and with_transaction != "false") \
             or sgbd_name not in config["sgbds"].keys()
# fout?
if erreur:
    print(f"syntaxe : {args[0]} (1) sgbd_name (2) true / false")
    sys.exit()

# configuratie van de sgbd_name
sgbd_config = config["sgbds"][sgbd_name]
# connector van de sgbd_name
sgbd_connector = importlib.import_module(sgbd_config["sgbd_connector"])
# functiebibliotheek
lib = importlib.import_module(sgbd_config["sgbd_fonctions"])


# berekening van een weer te geven tekst
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

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

# uitvoering van de opdrachten SQL uit het bestand
connexion = None
try:
    # verbinding
    connexion = sgbd_connector.connect(
        host=sgbd_config['host'],
        user=sgbd_config['user'],
        password=sgbd_config['password'],
        database=sgbd_config['database'])
    # uitvoering van het opdrachtenbestand SQL
    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:
    # verbinding verbreken
    if connexion:
        connexion.close()

# aantal fouten weergeven
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# fouten weergeven
for erreur in erreurs:
    print(erreur)

Opmerkingen

  • regels 1-4: de configuratie [config] van de applicatie wordt opgehaald;
  • regels 10-21: het script wordt aangeroepen met twee parameters [sgbd_name with_transaction]:
    • [sgbd_name]: de naam van het te gebruiken SGBD;
    • [with_transaction]: True als het scriptbestand SQL binnen een transactie moet worden uitgevoerd, anders False;
  • regels 10-25: de parameters worden opgehaald en gecontroleerd;
  • regel 28: de configuratie van het gekozen SGBD;
  • regel 30: de connector van het geselecteerde SGBD wordt geïmporteerd. Hiervoor wordt de bibliotheek [importlib] (regel 7) gebruikt, waarmee een module kan worden geïmporteerd waarvan de naam in een variabele staat. Het resultaat van de bewerking [importlib.import_module] is een module. Na regel 30 verloopt alles dus alsof de uitgevoerde instructie de volgende was:
import sgbd_connector

Hierdoor kunnen we op regel 52 [sgbd_connector.connect] schrijven, waarbij we de functie [connect] van de module [sgbd_connector] gebruiken. We moeten hier in gedachten houden dat [sgbd_connector] ofwel [mysql.connector] ofwel [psycopg2] is. Deze twee modules beschikken over de functie [connect]. Op dezelfde manier kan in regel 60 [sgbd_connector.InterfaceError, sgbd_connector.DatabaseError] worden geschreven.

  • regel 32: de module met de door het script gebruikte functies wordt geïmporteerd;
  • regel 58: we voeren de functie [execute_file_of_commands] uit van de functiemodule die door het script wordt gebruikt. In vergelijking met eerdere versies heeft de signatuur van deze functie één parameter meer, namelijk de eerste. We geven de Python-connector [sgbd_connector] door aan de functie, die deze moet gebruiken;
  • afgezien van deze punten blijft het script [any_04] hetzelfde als in de vorige versies;

De functiebibliotheek [any_module] ziet er als volgt uit:


# ---------------------------------------------------------------------------------

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):
    …

    # initialisaties
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        # een cursor wordt aangevraagd
        curseur = connexion.cursor()
        # uitvoering van de sql_commands SQL die in sql_commands zijn opgenomen
        # ze worden één voor één uitgevoerd
        for command in sql_commands:
            # de spaties aan het begin en einde van het huidige commando worden verwijderd
            command = command.strip()
            # is er sprake van een leeg commando of een opmerking? Zo ja, ga dan door naar het volgende commando
            if command == '' or command[0] == "#":
                continue
            # het huidige commando wordt uitgevoerd
            error = None
            try:
                curseur.execute(command)
            except (sgbd_connector.InterfaceError, sgbd_connector.DatabaseError) as erreur:
                error = erreur
            # is er een fout opgetreden?
            …


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

    # verwerking van het bestand SQL
    try:
        # het bestand in leesmodus openen
        file = open(sql_filename, "r")
        # verwerking
        return execute_list_of_commands(sgbd_connector, connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        # er wordt een fouttabel geretourneerd
        return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
    finally:
        pass

De parameter [sgbd_connector] is op regel 31 gebruikt om het type van de opgevangen uitzonderingen te specificeren.

Het uitvoeren van het script [any_04] met de parameters [mysql false] levert de volgende resultaten op:


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
Deze cursus-tutorial, geschreven door Serge Tahé, wordt aan het publiek ter beschikking gesteld volgens de voorwaarden van de Creative Commons Naamsvermelding – Geen commercieel gebruik – Gelijk delen 3.0 ongewijzigd.