16. Utilizzo del DBMS MySQL

16.1. Installazione del DBMS MySQL

Per utilizzare il DBMS MySQL, installeremo il software Laragon.

16.1.1. Installazione di Laragon

Laragon è un pacchetto che combina diversi componenti software:

un server web Apache. Lo useremo per scrivere script web in Python;
il DBMS MySQL;
il linguaggio di scripting PHP, che non useremo;
un server Redis che implementa una cache per le applicazioni web. Non lo useremo;

Laragon può essere scaricato (febbraio 2020) al seguente indirizzo:


https://laragon.org/download/

L'installazione [1-5] produce la seguente struttura di directory:

in [6] la cartella di installazione di PHP (non utilizzata in questo documento);

L'avvio di [Laragon] visualizza la seguente finestra:

[1]: il menu principale di Laragon;
[2]: il pulsante [Start All] avvia il server web Apache e il database MySQL;
[3]: il pulsante [WEB] visualizza la pagina web [http://localhost];
[4]: il pulsante [Database] consente di gestire il sistema di gestione del database MySQL utilizzando lo strumento [phpMyAdmin]. È necessario installare questo strumento in precedenza;
[5]: il pulsante [Terminal] apre una finestra di comando;
[6]: il pulsante [Root] apre una finestra di Esplora risorse posizionata sulla cartella [<laragon>/www], che è la directory principale del sito web [http://localhost]. È qui che dovresti collocare le applicazioni web statiche gestite dal server Apache di Laragon;

16.1.2. Creazione di un database

Ora vi mostreremo come creare un database e un utente MySQL utilizzando lo strumento Laragon.

Una volta avviato, Laragon [1] può essere gestito da un menu [2];
In [3-5], installa lo strumento di amministrazione MySQL [phpMyAdmin] se non è già stato installato;

In [6], avviate il server web Apache e il sistema di gestione del database MySQL;
In [7], il server Apache viene avviato;
In [8], viene avviato il sistema di gestione del database MySQL;

In [8-10], creare un database denominato [dbpersonnes] [11]. Creeremo un database di persone;

in [11], gestiremo il database appena creato;

L'operazione [Databases] invia una richiesta web all'URL [http://localhost/phpmyadmin] [12]. Il server web Apache di Laragon risponde. L'URL [http://localhost/phpmyadmin] è l'URL dell'utilità [phpMyAdmin] che abbiamo installato in precedenza [5]. Questa utilità consente di gestire i database MySQL;
per impostazione predefinita, le credenziali di accesso dell'amministratore del database sono: root [13] senza password [14];

in [16], il database che abbiamo creato in precedenza;

per ora, abbiamo un database [dbpersonnes] [17] che è vuoto [18];

Creiamo un utente [admpersonnes] con la password [nobody] che avrà privilegi completi sul database [dbpersonnes]:

in [19], ci troviamo sul database [dbpersonnes];
in [20], selezioniamo la scheda [Privilegi];
in [21-22], vediamo che l'utente [root] ha privilegi completi sul database [dbpersonnes];
in [23], creiamo un nuovo utente;

in [25-26], l'utente avrà il nome utente [admdbpersonnes];
in [27-29], la sua password sarà [nobody];
in [30], phpMyAdmin avverte che la password è molto debole (facile da violare). In produzione, è preferibile generare una password forte utilizzando [31];
in [32], specifichiamo che l'utente [admdbpersonnes] deve avere privilegi completi sul database [dbpersonnes];
in [33], convalidiamo le informazioni fornite;

in [35], phpMyAdmin indica che l'utente è stato creato;
In [36], la query SQL che è stata eseguita sul database;
In [37], l'utente [admpersonnes] ha privilegi completi sul database [dbpersonnes];

Ora abbiamo:

un database MySQL [dbpersonnes];
un utente [admpersonnes/nobody] che ha privilegi completi su questo database;

16.2. Installazione del pacchetto [mysql-connector-python]

Scriveremo degli script Python per utilizzare il database creato in precedenza con la seguente architettura:

Si utilizza un connettore per separare il codice Python dal DBMS in uso. Esistono connettori per diversi DBMS, e tutti seguono la stessa interfaccia. Pertanto, quando nell'esempio precedente si sostituisce il DBMS MySQL con il DBMS PostgreSQL, l'architettura diventa la seguente:

Poiché tutti i connettori DBMS aderiscono alla stessa interfaccia, lo script Python normalmente non necessita di modifiche. In realtà, la maggior parte dei DBMS utilizza un SQL proprietario:

sono conformi allo standard SQL (Structured Query Language);
ma lo estendono — poiché da solo non è sufficiente — con estensioni linguistiche proprietarie;

Pertanto, è comune che, quando si cambia DBMS, sia necessario apportare modifiche SQL agli script.

Per impostazione predefinita, Python non offre la possibilità di gestire un database MySQL. Per farlo, è necessario scaricare un pacchetto. Ce ne sono diversi disponibili. Qui useremo il pacchetto [mysql-connector-python], che è il connettore ufficiale di Oracle, la società proprietaria di MySQL.

Il pacchetto verrà installato in una finestra [Terminale] di Pycharm:

la directory in [2] è irrilevante per quanto segue;

Nel terminale, digitare il comando [pip search MySQL]:

[pip] (Package Installer for Python) è lo strumento per l'installazione dei pacchetti Python. Lo strumento [pip] si connette al repository contenente i pacchetti Python;
[search MySQL]: recupera un elenco di pacchetti contenenti il termine [MySQL] (senza distinzione tra maiuscole e minuscole) nei loro nomi;

I risultati del comando sono i seguenti:


mysql (0.0.2)                                                   - Virtual package for MySQL-python
jx-mysql (3.49.20042)                                           - jx-mysql - JSON Expressions for MySQL
weibo-mysql (0.1)                                               - insert mysql
bits-mysql (1.0.3)                                              - BITS MySQL
MySQL-python (1.2.5)                                            - Python interface to MySQL
deployfish-mysql (0.2.13)                                       - Deployfish MySQL plugin
mtstat-mysql (0.7.3.3)                                          - MySQL Plugins for mtstat
bottle-mysql (0.3.1)                                            - MySQL integration for Bottle.
WintxDriver-MySQL (2.0.0-1)                                     - MySQL support for Wintx
py-mysql (1.0)                                                  - Operating Mysql for Python.
mysql-utilities (1.4.3)                                         - MySQL Utilities 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
….                                        - Tool to move slices of data from one MySQL store to another
mysql-tracer (2.0.2)                                            - A MySQL client to run queries, write execution reports and export results
mysql-utils (0.0.2)                                             - A simple MySQL library including a set of utility APIs for Python database programming
mysql-connector-repackaged (0.3.1)                              - MySQL driver written in Python
dffml-source-mysql (0.0.5)                                      - DFFML Source for MySQL Protocol
mysql-connector-python (8.0.19)                                 - MySQL driver written in Python
  INSTALLED: 8.0.19 (latest)
prometheus-mysql-exporter (0.2.0)                               - MySQL query Prometheus exporter
backwork-backup-mysql (0.3.0)                                   - Backwork plug-in for MySQL backups.
django-mysql-manager (0.1.4)                                    - django-mysql-manager is a Django based management interface for MySQL users and databases.
….                                              - mysql operate
 
C:\Data\st-2020\dev\python\cours-2020\v-01>

Sono stati elencati tutti i moduli il cui nome o descrizione contiene la parola chiave MySQL. Quello che useremo (febbraio 2020) è [mysql-connector-python], riga 17. Per installarlo, digita il comando [pip install -U mysql-connector-python] nel terminale:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip install -U mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.19-py2.py3-none-any.whl (355 kB)
Requirement already satisfied, skipping upgrade: protobuf==3.6.1 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied, skipping upgrade: dnspython==1.16.0 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (1.16.0)
Requirement already satisfied, skipping upgrade: six>=1.9 in c:\users\serge\appdata\roaming\python\python38\site-packages (from protobuf==3.6.1->mysql-connector-python) (1.14.0)
Requirement already satisfied, skipping upgrade: setuptools in c:\myprograms\python38\lib\site-packages (from protobuf==3.6.1->mysql-connector-python) (41.2.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.19

Riga 1: L'opzione [install -U] (U=aggiornamento) richiede l'ultima versione dei vari pacchetti associati al pacchetto [mysql-connector-python];

Per vedere quali pacchetti sono installati nell'ambiente Python del nostro computer, digitare il comando [pip list]:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip list
Package                Version
---------------------- ----------
asgiref                3.2.3
astroid                2.3.3
atomicwrites           1.3.0
attrs                  19.3.0
certifi                2019.11.28
…
MarkupSafe             1.1.1
mccabe                 0.6.1
more-itertools         8.1.0
mysql-connector-python 8.0.19
mysqlclient            1.4.6
packaging              20.0
pip                    20.0.1
pipenv                 2018.11.26
…

Riga 13: Abbiamo il pacchetto [mysql-connector-python];

Per imparare a utilizzare il pacchetto [mysql-connector-python] per gestire un database MySQL, visita il sito web del pacchetto |https://dev.mysql.com/doc/connector-python/en/|. La sezione seguente presenta una serie di esempi.

16.3. script [mysql_01]: connessione a un database MySQL - 1

Lo script [mysql_01] illustra il primo passo nell'utilizzo di un database. Ci consentirà di verificare che sia possibile connettersi al database [dbpersonnes] creato in precedenza.

#  import module mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

#  connection to a MySql [dbpersonnes] database
#  user identity is (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

#  here we go
connexion = None
try:
    print("Connexion au SGBD MySQL en cours...")
    #  connection
    connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
    #  follow-up
    print(
        f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    #  close the connection if it has been opened
    if connexion:
        connexion.close()

Note

Riga 2: Importa alcune funzioni e classi dal modulo [mysql.connector];
Righe 6–7: le credenziali dell'utente che si connetterà;
riga 8: la macchina che ospita il database. Il connettore MySQL consente di lavorare con un database remoto;
riga 9: il nome del database a cui vogliamo collegarci;
righe 11–26: lo script connetterà (riga 16) l'utente [admpersonnes / nobody] al database [dbpersonnes];
righe 20–26: la connessione potrebbe non riuscire. Pertanto, viene gestita all'interno di un blocco try/except/finally;
riga 16: il metodo connect del modulo [mysq.connector] accetta vari parametri denominati:
- user: l'utente proprietario della connessione [admpersonnes];
- password: password dell'utente [nobody];
- host: macchina DBMS MySQL [localhost];
- database: il database a cui connettersi. Opzionale.
riga 20: se viene generata un'eccezione, è di tipo [DatabaseError] o [InterfaceError];
righe 23–26: nella clausola [finally], la connessione viene chiusa;

Risultati

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/mysql/mysql_01.py
Connexion au SGBD MySQL en cours...
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody

Process finished with exit code 0

16.4. script [mysql_02]: connessione a un database MySQL - 2

In questo nuovo script, la connessione al database è incapsulata in una funzione:

#  import module mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    #  connects then disconnects (login,pwd) from the [database] server [host]
    #  throws DatabaseError exception if problem occurs
    connexion = None
    try:
        #  connection
        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:
        #  close the connection if it has been opened
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


#  ---------------------------------------------- main
#  login credentials
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

#  existing user login
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

#  non-existent user login
try:
    connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

Note:

righe 6–19: una funzione [connection] che tenta di connettere e poi disconnettere un utente dal database [dbpersonnes]. Visualizza il risultato;
righe 29–41: programma principale – chiama due volte il metodo di connessione e visualizza eventuali eccezioni;

Risultati

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/mysql/mysql_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

1045 (28000): Access denied for user 'xx'@'localhost' (using password: YES)

Process finished with exit code 0

16.5. script [mysql_03]: creazione di una tabella MySQL

Ora che sappiamo come stabilire una connessione con un DBMS MySQL, possiamo iniziare a eseguire comandi SQL tramite questa connessione. Per farlo, ci collegheremo al database creato [dbpersonnes] e useremo la connessione per creare una tabella nel database.

#  imports
import sys

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


# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
    #  executes an update request on the
    curseur = None
    try:
        #  a cursor is requested
        curseur = connexion.cursor()
        #  executes the update request on the
        curseur.execute(update)
    finally:
        #  close cursor if obtained
        if curseur:
            curseur.close()


#  ---------------------------------------------- main
#  login credentials
#  user identity
ID = "admpersonnes"
PWD = "nobody"
#  the sgbd host machine
HOST = "localhost"
#  base identity
DATABASE = "dbpersonnes"

#  step by step
try:
    #  connection
    connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
    #  mode AUTOCOMMIT
    connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  we leave
    sys.exit()

#  delete the people table if it exists
#  if it doesn't exist, an error will occur - we ignore it
requête = "drop table personnes"
try:
    execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
    pass

#  create people table
requête = "create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, " \
          "unique(nom,prenom)) "
try:
    #  request execution
    execute_sql(connexion, requête)
    #  display
    print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    #  we disconnect
    connexion.close()

Note:

riga 9: la funzione execute_sql esegue una query SQL su una connessione aperta;
riga 14: le operazioni SQL sulla connessione vengono eseguite tramite un oggetto speciale chiamato cursore;
riga 14: ottenimento di un cursore;
riga 16: esecuzione della query SQL;
righe 17–20: indipendentemente dal fatto che si verifichi o meno un errore, il cursore viene chiuso. Ciò libera le risorse ad esso associate. Se si verifica un'eccezione, questa non viene gestita qui. Verrà propagata al codice chiamante;
righe 33–43: creazione di una connessione al database;
riga 38: impostare AUTOCOMMIT=True per una connessione significa che ogni esecuzione di query avviene all'interno di una transazione automatica. La modalità predefinita è AUTOCOMMIT=False, in cui lo sviluppatore è responsabile della gestione delle transazioni. Una transazione è un meccanismo che comprende l'esecuzione di più query, da 1 a n. O tutte hanno esito positivo, oppure nessuna di esse ha esito positivo. Pertanto, se le query d e 1 a i hanno esito positivo ma la query i+1 fallisce, allora le query da 1 a i saranno "rollbackate" in modo che il database torni allo stato in cui si trovava prima dell'esecuzione della query 1;
Qui ci sono due query SQL (righe 49, 58). Ciascuna verrà eseguita all'interno di una transazione. Il fatto che la seconda fallisca non ha alcun impatto sulla prima;
righe 45–51: viene eseguita l'istruzione SQL [drop table people]. Essa elimina la tabella denominata [people]. Se la tabella non esiste, potrebbe essere segnalato un errore. Questo errore viene ignorato (riga 51);
righe 53–55: il comando per creare la tabella [people]. Una tabella può essere vista come un insieme di righe e colonne. Il comando di creazione specifica i nomi delle colonne:
- [id]: un identificatore intero. Sarà unico per ogni persona. Questo sarà la chiave primaria (PRIMARY KEY). Ciò significa che all'interno della tabella, questa colonna non avrà mai lo stesso valore due volte e potrà essere utilizzata per identificare una persona;
- [last_name]: una stringa di massimo 30 caratteri;
- [last_name]: una stringa di massimo 30 caratteri;
- [age]: un numero intero;
- L'attributo [NOT NULL] per ciascuna di queste colonne significa che in una riga della tabella nessuna delle tre colonne può essere vuota;
- il parametro [unique(last_name, first_name)] è chiamato vincolo. In questo caso, il vincolo sulle righe è che la tupla (last_name, first_name) nella riga deve essere unica nella tabella. Ciò significa che possiamo identificare in modo univoco un individuo nella tabella di cui sono noti il cognome e il nome;
righe 56–60: esecuzione dell'istruzione SQL;
righe 61–63: gestione di eventuali eccezioni;
righe 64–66: disconnessione dal database;

Risultati

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/mysql/mysql_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

Verifica con [phpMyAdmin]:

il database [dbpersonnes] [1] contiene una tabella [personnes] [2] con la struttura [3-4], la chiave primaria [5] e il vincolo di unicità [6];

16.6. script [mysql_04]: esecuzione di un file di comandi SQL

Dopo aver precedentemente creato la tabella [personnes], ora la popoliamo e poi la interroghiamo utilizzando istruzioni SQL.

Vogliamo eseguire le istruzioni SQL da un file di testo:

Il contenuto del file [commands.sql] è il seguente:


# suppression de la table [personnes]
drop table personnes
# création de la table personnes
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# insertion de deux personnes
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# affichage de la table
select prenom, nom, age from personnes
# erreur volontaire
xx
# insertion de trois personnes
insert into personnes(prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes(prenom, nom, age) values ('Geraldine','Colou',26)
insert into personnes(prenom, nom, age) values ('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(prenom, nom, age) values('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'

Per prima cosa, definiamo delle funzioni che inseriamo in un modulo in modo da poterle riutilizzare:

Lo script [mysql_module] è il seguente:

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


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    #  displays the result of an sql command
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the [sql_commands] list on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    ….


# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the sql_filename text file on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    #  use of the SQL file
    try:
        #  open file for reading
        file = open(sql_filename, "r")
        #  operation
        return execute_list_of_commands(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}"]

Note:

Riga 29: La funzione [execute_file_of_commands] esegue i comandi SQL contenuti nel file di testo denominato [sql_filename]:
vedere i commenti alle righe 31–38 per il significato dei parametri;
Righe 40–48: Il file di testo [sql_filename] viene elaborato;
riga 43: apertura del file;
riga 34: esecuzione della funzione [execute_list_of_commands], che esegue i comandi SQL passati in un elenco. Questo elenco è qui composto da tutte le righe del file di testo [file.readlines()] (riga 45);

La funzione [execute_list_of_commands] è la seguente:

# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the [sql_commands] list on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    #  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 (InterfaceError, DatabaseError) as erreur:
                error = erreur
            #  was there a mistake?
            if error:
                #  one more mistake
                msg = f"{command} : Erreur ({error})"
                erreurs.append(msg)
                #  screen tracking or not?
                if suivi:
                    print(msg)
                #  shall we stop?
                if with_transaction or arrêt:
                    #  return the error list
                    return erreurs
            else:
                #  no error
                if suivi:
                    print(f"[{command}] : Exécution réussie")
                #  displays the result of the command
                afficher_infos(curseur)
        #  return the error table
        return erreurs
    finally:
        #  closing the cursor
        if curseur:
            curseur.close()
        #  validate / cancel the transaction if it exists
        if with_transaction:
            if erreurs:
                #  cancellation
                connexion.rollback()
            else:
                #  validation
                connexion.commit()

Note

Riga 2: La funzione [execute_list_of_commands] esegue i comandi SQL contenuti nell'elenco [sql_commands]:
Vedere i commenti alle righe 4–11 per il significato dei parametri;
Riga 2: La connessione ricevuta è una connessione aperta a un database;
riga 15: se si desidera che tutti i comandi presenti nell'elenco [sql_commands] vengano eseguiti all'interno di una transazione, è necessario operare in modalità AUTOCOMMIT=False. In caso contrario, si opererà in modalità AUTOCOMMIT=True e ogni comando dell'elenco [sql_commands] verrà eseguito all'interno di una transazione automatica, senza che vi sia una transazione globale;
riga 19: viene richiesto un cursore per eseguire i vari comandi SQL;
righe 22–51: i comandi vengono eseguiti uno per uno;
righe 26–27: accettiamo righe vuote e commenti nell'elenco dei comandi SQL. In questo caso, ignoriamo semplicemente il comando;
righe 30–33: eseguono la query corrente;
righe 35–45: gestiscono il caso di un possibile errore di runtime nella query corrente;
righe 37–38: l'errore viene aggiunto alla tabella degli errori;
righe 40–41: se la registrazione è stata abilitata, viene visualizzato il messaggio di errore;
righe 43–45: se il codice chiamante ha richiesto un arresto dopo il primo errore o ha richiesto l'uso di una transazione, allora il programma deve arrestarsi. Viene restituito l'array degli errori;
righe 46-51: caso in cui non si sia verificato alcun errore di esecuzione per la query corrente;
righe 48-49: se è stato richiesto il tracciamento, la query eseguita viene visualizzata con l'etichetta 'successful';
righe 50-51: visualizza il risultato della query eseguita. Torneremo alla funzione [display_info] un po' più avanti;
righe 54–65: la clausola [finally] viene eseguita in tutti i casi, indipendentemente dal fatto che si sia verificata un'eccezione o meno;
righe 56–57: chiudiamo il cursore. Questo libera le risorse ad esso allocate;
righe 59-65: gestiamo il caso in cui il codice chiamante abbia richiesto che i comandi SQL fossero eseguiti all'interno di una transazione;
riga 60: verifichiamo se l'elenco [errors] è vuoto, il che significa che non si è verificata alcuna eccezione. In questo caso, la transazione viene confermata (riga 65); altrimenti, viene annullata (riga 62);

La funzione [display_info] visualizza il risultato di una query:


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    print(type(curseur))
    # affiche le résultat d'une command sql
    # s'agissait-il d'un select ?
    if curseur.description:
        # le curseur a une description - donc il a exécuté un select
        # description[i] est la description de la colonne n° i du select
        # description[i][0] est le nom de la colonne n° i du select
        # on affiche les noms des champs
        titre = ""
        for i in range(len(curseur.description)):
            titre += curseur.description[i][0] + ", "
        # on affiche la liste des champs sans la virgule de fin
        print(titre[0:len(titre) - 1])
        # ligne séparatrice
        print("*" * (len(titre) - 1))
        # ligne courante du select
        ligne = curseur.fetchone()
        while ligne:
            # on l'affiche
            print(ligne)
            # ligne suivante du select
            ligne = curseur.fetchone()
        # ligne séparatrice
        print("*" * (len(titre) - 1))
    else:
        # le curseur n'a pas de champ [description] - il a donc exécuté un ordre SQL
        # de mise à jour (insert, delete, update)
        print(f"nombre de lignes modifiées : {curseur.rowcount}")

Note

Riga 1: Il parametro della funzione è il cursore che ha appena eseguito un'istruzione SQL. A seconda che si tratti di un'istruzione SELECT o di un'istruzione di aggiornamento (INSERT, UPDATE, DELETE), il contenuto del cursore è diverso;
Riga 6: Se il cursore contiene il campo [description], significa che ha eseguito un'istruzione SELECT e [description] descrive i campi richiesti nell'istruzione SELECT:
- description[i] descrive il campo numero i richiesto dal SELECT. Si tratta di un elenco;
- description[i][0] è il nome del campo numero i;
righe 11–17: vengono visualizzati i nomi dei campi richiesti dal SELECT;
righe 18–24: elaboriamo il risultato del SELECT;
righe 20, 24: il risultato di un SELECT viene elaborato in modo sequenziale. Questo risultato è un insieme di righe. La riga corrente viene ottenuta tramite [cursor.fetchone()] (riga 19). Si ottiene quindi una tupla;
righe 27–30: se il cursore non ha il campo [description], allora ha eseguito un'istruzione di aggiornamento INSERT, UPDATE o DELETE. Possiamo quindi determinare quante righe nella tabella sono state modificate dall'esecuzione di questa istruzione;
riga 30: [cursor.rowcount] è questo numero;

Lo script principale [mysql-04] utilizza il modulo [mysql_module] appena descritto:

Il file [config_04] configura il contesto di esecuzione dello script [mysql_04]:

def configure():
    import os

    #  absolute path of the configuration file 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)

    #  return the config
    return {
        #  order file SQL
        "commands_filename": f"{script_dir}/data/commandes.sql",
        #  database connection identifiers
        "host": "localhost",
        "database": "dbpersonnes",
        "user": "admpersonnes",
        "password": "nobody"
    }

Lo script [mysql_04] è il seguente:

#  retrieve application configuration
import config_04

config = config_04.configure()

#  syspath is configured - imports can be made
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError

#  ---------------------------------------------- main
#  check call syntax
#  argv[0] true / false
args = sys.argv
erreur = len(args) != 2
if not erreur:
    with_transaction = args[1].lower()
    erreur = with_transaction != "true" and with_transaction != "false"
#  mistake?
if erreur:
    print(f"syntaxe : {args[0]} true / false")
    sys.exit()

#  text calculation
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

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

#  execution of SQL orders in the file
connexion = None
try:
    #  connection to comics
    connexion = connect(host=config['host'], user=config['user'], password=config['password'],
                        database=config['database'])
    #  execution of SQL command file
    erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
                                       with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
    #  error display
    print(f"L'erreur fatale suivante s'est produite : {erreur}")
    #  we stop
    sys.exit()
finally:
    #  close the connection if it has been opened
    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)

Note

righe 1-4: configurazione dello script;
riga 8: importazione del modulo [mysql_module] descritto sopra:
righe 12-22: lo script [mysql-04] richiede un parametro che deve assumere uno dei valori [true / false]. Questo parametro indica se il file di comandi SQL deve essere eseguito all'interno di una transazione (true) o meno (false);
riga 14: i parametri passati dall'utente allo script si trovano nell'elenco [sys.argv];
riga 15: sono richiesti due parametri, ad esempio [mysql-04 true]. Il nome dello script conta come un parametro;
righe 17-18: se ci sono effettivamente due parametri, il secondo deve essere una stringa con valore 'true' o 'false';
righe 24–29: calcolo del testo visualizzato alla riga 33;
righe 39–44: esecuzione dei comandi nel file [./data/commands.sql];
righe 45–49: se si verifica un errore durante la connessione (riga 40) o se non viene gestito dallo script [execute_file_of_commands], l'errore viene visualizzato e il processo viene terminato;
righe 55–62: se l'esecuzione ha esito positivo, viene visualizzato il numero di errori riscontrati durante l'esecuzione dei comandi SQL;

Esecuzione n. 1

Per prima cosa, eseguiamo un'esecuzione senza transazione. A tal fine, creeremo una configurazione di esecuzione come descritto nella sezione |configurazione di un contesto di esecuzione|:

in [1-4], creiamo una configurazione di esecuzione Python;

[5]: nome della configurazione di esecuzione;
[6]: percorso dello script da eseguire;
[7]: parametri dello script;
[8]: directory di esecuzione;

Questa configurazione corrisponde quindi all'esecuzione del file SQL con una transazione. Utilizzare il pulsante [Applica] per confermare la configurazione.

Creiamo la configurazione di esecuzione [mysql mysql-04 without_transaction] allo stesso modo:

Questa configurazione corrisponde quindi all'esecuzione del file SQL senza transazione. Utilizza il pulsante [Applica] per confermare la configurazione.

Eseguiamo prima la versione senza transazione:

I risultati sono i seguenti:


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/mysql/mysql_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table 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

Note:

Riga 19: Possiamo notare che, dopo l'errore, l'esecuzione delle istruzioni SQL è proseguita. Ciò è dovuto al fatto che l'esecuzione è avvenuta senza una transazione e con il parametro [stop=False]. Tutte le istruzioni SQL sono state quindi eseguite. Dovremmo quindi avere una tabella [people] che riflette questa esecuzione;

Verifica con phpMyAdmin:

Esecuzione n. 2

Ora eseguiamo la configurazione [mysql mysql-04 with_transaction]. I risultati sono i seguenti:


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/mysql/mysql_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table 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)
--------------------------------------------------------------------
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

Note:

Riga 19: Possiamo notare che, dopo l'errore, non vengono eseguite ulteriori istruzioni SQL. Questo perché l'esecuzione è avvenuta all'interno di una transazione e, al verificarsi del primo errore, abbiamo eseguito il rollback della transazione e interrotto l'esecuzione delle istruzioni SQL. Ciò significa che i risultati delle istruzioni alle righe 9, 11 e 13 sono stati annullati. Dovremmo quindi avere una tabella [people] vuota;

Verifica con phpMyAdmin:

in [5], vediamo che la tabella [people] [2] è vuota;

16.7. script [mysql_05]: uso delle query parametrizzate

Lo script [mysql_05] introduce il concetto di query parametrizzate:

#  imports
from mysql.connector import connect, DatabaseError, InterfaceError

#  user identity
ID = "admpersonnes"
PWD = "nobody"
#  the sgbd host machine
HOST = "localhost"
#  base identity
BASE = "dbpersonnes"

#  list of people (last name, first name, age)
personnes = []
for i in range(5):
    personnes.append((i, f"n0{i}", f"p0{i}", i + 10))
personnes.append((40, "d'Aboot", "Y'éna", 18))
#  other list of persons
autresPersonnes = []
for i in range(5):
    autresPersonnes.append((i + 100, f"n1{i}", f"p1{i}", i + 20))
autresPersonnes.append((200, "d'Aboot", "F'ilhem", 34))

#  access to SGBD
connexion = None
try:
    #  connection
    connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
    #  cursor
    curseur = connexion.cursor()
    #  delete existing registrations
    curseur.execute("delete from personnes")
    #  person-by-person insertions with a prepared query
    for personne in personnes:
        curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
    #  bulk insertion of a list of people
    curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
    #  transaction validation
    connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
    #  error display
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  cancel transaction
    if connexion:
        connexion.rollback()
finally:
    #  locking connection
    if connexion:
        connexion.close()

Note

righe 12–21: creiamo due elenchi di persone da includere nel database [dbpeople];
riga 27: ci connettiamo al database;
riga 31: cancelliamo il contenuto della tabella [people];
righe 33-34: inseriamo le persone utilizzando una query parametrizzata. Alla riga 34, il primo parametro è l'istruzione SQL da eseguire. Questa istruzione è incompleta. Contiene dei segnaposto [%s] che verranno sostituiti uno per uno e in ordine dai valori presenti nell'elenco del secondo parametro;
Riga 36: inserimento di persone, questa volta utilizzando un'unica istruzione [cursor.executemany]. Il secondo parametro di [executemany] è quindi un elenco di elenchi;

I vantaggi delle query parametrizzate risiedono in due punti:

vengono eseguite più velocemente delle query "hard-coded", che devono essere analizzate ad ogni esecuzione. La query parametrizzata [executemany] viene analizzata una sola volta. Viene quindi eseguita n volte senza essere analizzata nuovamente;
i parametri inseriti nella query parametrizzata vengono convalidati. Se contengono caratteri riservati, come l'apostrofo, questi vengono "escapati" in modo da non interferire con l'esecuzione dell'istruzione SQL. Per verificarlo, abbiamo incluso nomi e cognomi con apostrofi nell'elenco (righe 16 e 21);

I risultati ottenuti in phpMyAdmin sono i seguenti:

Si noti che le stringhe contenenti un apostrofo — un carattere riservato in SQL — sono state inserite correttamente. La query parametrizzata le ha "escapate". Senza una query parametrizzata, avremmo dovuto farlo noi stessi;