Skip to content

17. Utilizzo del DBMS PostgreSQL

Il DBMS PostgreSQL è disponibile gratuitamente. È un'alternativa alla versione "community" di MySQL.

Lo stiamo utilizzando qui per dimostrare che è abbastanza semplice migrare gli script Python/MySQL in script Python/PostgreSQL.

Con il DBMS MySQL, l'architettura dei nostri script era la seguente:

Image

Con il DBMS PostgreSQL, sarà la seguente:

Image

17.1. Installazione del DBMS PostgreSQL

Le distribuzioni del DBMS PostgreSQL sono disponibili all'URL [https://www.postgresql.org/download/] (maggio 2019). Mostriamo l'installazione della versione Windows a 64 bit:

Image

Image

  • In [1-4], scaricare il programma di installazione del DBMS;

Eseguire il programma di installazione scaricato:

Image

  • In [6], specificare una directory di installazione;

Image

  • In [8], l'opzione [Stack Builder] non è necessaria per ciò che stiamo facendo qui;
  • in [10], lasciare il valore predefinito;

Image

  • In [12-13], abbiamo inserito qui la password [root]. Questa sarà la password per l'amministratore del DBMS, che si chiama [postgres]. PostgreSQL lo chiama anche superutente;
  • In [15], lasciare il valore predefinito: questa è la porta di ascolto del DBMS;

Image

  • in [17], lasciare il valore predefinito;
  • In [19], il riepilogo della configurazione di installazione;

Image

Image

Su Windows, il DBMS PostgreSQL viene installato come servizio Windows che si avvia automaticamente. Nella maggior parte dei casi, ciò non è auspicabile. Modificheremo questa configurazione. Digitare [services] nella barra di ricerca di Windows [24-26]:

Image

  • in [29], puoi vedere che il servizio DBMS PostgreSQL è impostato in modalità automatica. Modificalo accedendo alle proprietà del servizio [30]:

Image

  • In [31-32], impostare il tipo di avvio su Manuale;
  • in [33], arrestare il servizio;

Se si desidera avviare manualmente il DBMS, tornare all'applicazione [Servizi], fare clic con il tasto destro del mouse sul servizio [postgresql] (34) e avviarlo (35).

17.2. Amministrazione di PostgreSQL con lo strumento [pgAdmin]

Avvia il servizio Windows per il DBMS PostgreSQL (vedi paragrafo precedente). Quindi, nello stesso modo in cui hai avviato lo strumento [Servizi], avvia lo strumento [pgAdmin], che ti consente di amministrare il DBMS PostgreSQL [1-3]:

Image

A un certo punto potrebbe essere richiesta la password del superutente. Il superutente si chiama [postgres]. Questa password viene impostata durante l'installazione del DBMS. In questo documento, durante l'installazione abbiamo assegnato la password [root] al superutente.

  • In [4], [pgAdmin] è un'applicazione web;
  • in [5], l'elenco dei server PostgreSQL rilevati da [pgAdmin], qui 1;
  • in [6], il server PostgreSQL che abbiamo avviato;
  • in [7], i database del DBMS, qui 1;
  • in [8], il database [postgresql] è gestito dal superutente [postgres];

Per prima cosa, creiamo un utente [admpersonnes] con la password [nobody]:

Image

Image

  • in [17], abbiamo inserito [nobody];

Image

  • in [21], il codice SQL che lo strumento [pgAdmin] invierà al DBMS PostgreSQL. Questo è un modo per imparare il linguaggio SQL proprietario di PostgreSQL;
  • In [22], dopo aver confermato con la procedura guidata [Salva], l'utente [admpersonnes] è stato creato;

Ora creiamo il database [dbpersonnes]:

Image

Fare clic con il tasto destro del mouse su [23], quindi su [24-25] per creare un nuovo database. Nella scheda [26], definire il nome del database [27] e il suo proprietario [admpersonnes] [28].

Image

  • In [30], il codice SQL per la creazione del database;
  • In [31], dopo aver confermato con la procedura guidata [Salva], il database [dbpersonnes] viene creato;

Utilizzeremo il database [dbpersonnes] con gli script Python.

17.3. Installazione del connettore Python per il DBMS PostgreSQL

Image

Il diagramma sopra mostra un connettore che collega gli script Python al DBMS PostgreSQL. Ne sono disponibili diversi. Installeremo il connettore [psycopg2]. Questa operazione viene eseguita in un terminale Python (indipendentemente dalla directory in cui è aperto il terminale). Il connettore viene installato utilizzando il comando [pip install psycopg2]:


(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. Conversione degli script MySQL in script PostgreSQL

Image

  • La cartella [1] contenente gli script MySQL viene duplicata (Ctrl-C / Ctrl-V), quindi i nomi dei file vengono modificati in modo da corrispondere al loro contenuto;

17.4.1. [pgres_module]

Questo modulo è una copia del modulo [mysql_module] (vedere la sezione |script [mysql-04]: esecuzione di un file di comandi SQL|). Modificare le importazioni:

Invece di:

1
2
3
4
#  imports
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

scriviamo:

1
2
3
#  imports
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

La firma della funzione [display_info] era:


def afficher_infos(curseur: MySQLCursor):

Diventa:


def afficher_infos(curseur: cursor)

La firma della funzione [execute_list_of_commands] era:


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

Diventa:


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

Altrimenti, non cambia nient'altro.

17.4.2. script [pgres_01]

Lo script [pgres_01] è una copia dello script [mysql_01] (vedere la sezione |script [mysql-01]: connessione a un database MySQL - 1|). Sono state apportate le seguenti modifiche:

Invece di:

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

Scriviamo:

1
2
#  psycopg2 module import
from psycopg2 import connect, DatabaseError, InterfaceError

Il resto rimane invariato. I risultati sono gli stessi di MySQL.

17.4.3. script [pgres_02]

Lo script [pgres_02] è una copia dello script [mysql_02] (vedere la sezione |script [mysql-02]: connessione a un database MySQL - 2|). Apportare le seguenti modifiche:

Invece di:

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

scriviamo:

1
2
#  psycopg2 module import
from psycopg2 import DatabaseError, InterfaceError, connect

I risultati non sono gli stessi dello script [mysql_02]:

1
2
3
4
5
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

Lo script [pgres_02] è il seguente:

 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
#  import module mysql.connector
from psycopg2 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="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

Sebbene le righe 36–41 avrebbero dovuto visualizzare un messaggio di errore indicando che la connessione al DBMS non è riuscita, non viene visualizzato nulla. Infatti, dopo un'ulteriore analisi, vediamo che il codice entra effettivamente nel blocco [except] nelle righe 35–37, ma la variabile [error] è impostata su [None]. Ciò si verifica con la versione 2.8.4 del connettore [psycopg2].

Possiamo aggirare questo problema scrivendo un messaggio generico ma meno preciso:

1
2
3
4
5
6
#  non-existent user login
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

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/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. script [pgres_03]

Lo script [pgres_03] è una copia dello script [mysql_03] (vedere la sezione |script [mysql-03]: creazione di una tabella MySQL|). Sono state apportate le seguenti modifiche:

Invece di:


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

scriviamo:


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

Inoltre, la firma della funzione [execute_sql], che era:


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

diventa:


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

Il resto rimane invariato. Il risultato è il seguente:


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

È possibile verificare l'esistenza della tabella [people] utilizzando lo strumento di amministrazione [pgAdmin]:

Image

17.4.5. script [pgres_04]

Lo script [pgres_04] è una copia dello script [mysql_04] (vedere la sezione |script [mysql-04]: esecuzione di un file di comandi SQL|). Utilizza il modulo [pgres_module]:

1
2
3
4
5
6
7
8
9
#  retrieve application configuration
import config_04

config = config_04.configure()

#  syspath is configured - imports can be made
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

Il resto rimane invariato.

Creiamo una configurazione [pgres pgres-04 without_transaction] come fatto nella sezione |script [mysql-04]: esecuzione di un file di comandi SQL|. Creiamo anche una configurazione [pgres pgres-04 with_transaction].

L'esecuzione della configurazione [pgres pgres-04 without_transaction] produce i seguenti 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/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
  • Riga 5: Abbiamo dovuto modificare il comando per eliminare la tabella [people]. A differenza del connettore MySQL, il connettore PostgreSQL genera un'eccezione se la tabella da eliminare non esiste. Il comando [drop table] ha una variante, [drop table if exists], che non genera un'eccezione se la tabella non esiste. L'abbiamo utilizzata qui. Questo è un esempio in cui due DBMS non si comportano allo stesso modo in situazioni simili;

La tabella [people] nello strumento [pgAdmin] è la seguente:

Image

L'esecuzione della configurazione [pgres pgres_04 with_transaction] produce i seguenti 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/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

La tabella [people] nello strumento [pgAdmin] è la seguente:

Image

Qui, il risultato differisce da quello ottenuto con MySQL. Se eseguiamo gli script nelle stesse condizioni, ovvero dopo aver eseguito lo script senza una transazione, otteniamo i seguenti risultati:

  • Con MySQL, la tabella [people] è vuota;
  • con PostgreSQL, la tabella [people] non è vuota;

La differenza sta nei diversi modi in cui questi due DBMS eseguono il rollback della transazione:

  • MySQL non esegue il rollback dei comandi [drop table] e [create table]. Ci ritroviamo con una tabella [people] vuota;
  • PostgreSQL annulla i comandi [drop table] e [create table]. La tabella viene riportata allo stato in cui si trovava prima dell'esecuzione dello script tramite una transazione;

17.4.6. script [pgres_05]

Lo script [pgres_05] è una copia dello script [mysql_05] (vedere la sezione |script [mysql-05]: utilizzo di query parametrizzate|). Lo script è modificato come segue:

Invece di:

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

scriviamo:

1
2
#  imports
from psycopg2 import connect, DatabaseError, InterfaceError

Il resto rimane invariato.

I risultati ottenuti in [pgAdmin] sono i seguenti:

Image

17.5. Conclusione

Il porting degli script MySQL in script PostgreSQL è stato relativamente semplice. Si tratta di un'eccezione. I due DBMS non supportano le stesse convenzioni di denominazione per gli oggetti SQL (database, tabelle, colonne, vincoli, tipi di dati, ecc.) e presentano estensioni SQL incompatibili... Per garantire un porting semplice, è necessario attenersi allo standard SQL in entrambi i casi senza tentare di utilizzare le estensioni proprietarie del DBMS. Ciò va a discapito delle prestazioni.

https://stahe.github.io
Questo tutorial scritto da Serge Tahé è reso disponibile al pubblico secondo i termini della Creative Commons Attribuzione – Non commerciale – Condividi allo stesso modo 3.0 Unported.