17. Utilização do SGBD PostgreSQL

O SGBD PostgreSQL está disponível gratuitamente. É uma alternativa à versão «community» do MySQL.

Utilizamo-lo aqui para demonstrar que é bastante simples migrar scripts Python / MySQL para scripts Python / PostgreSQL.

Com o SGBD e o MySQL, a arquitetura dos nossos scripts era a seguinte:

Com o SGBD e o PostgreSQL, será a seguinte:

17.1. Instalação do SGBD PostgreSQL

As distribuições do SGBD e do PostgreSQL estão disponíveis no URL e no [https://www.postgresql.org/download/] (maio de 2019). Apresentamos a instalação da versão para Windows de 64 bits:

no [1-4], descarrega-se o instalador do SGBD;

Executa-se o instalador descarregado:

em [6], indique uma pasta de instalação;

no [8], a opção [Stack Builder] é desnecessária para o que pretendemos fazer aqui;
em [10], mantenha o valor que lhe for apresentado;

em [12-13], colocámos aqui a palavra-passe [root]. Esta será a palavra-passe do administrador do SGBD, que se chama [postgres]. O PostgreSQL também lhe chama «superutilizador»;
no [15], mantenha o valor por predefinição: trata-se da porta de escuta do SGBD;

em [17], mantenha o valor predefinido;
em [19], o resumo da configuração da instalação;

No Windows, o SGBD PostgreSQL é instalado como um serviço do Windows iniciado automaticamente. Na maioria das vezes, isso não é desejável. Vamos alterar esta configuração. Digite [services] na barra de pesquisa do Windows [24-26]:

em [29], verifica-se que o serviço SGBD PostgreSQL está no modo automático. Alteramos isto acedendo às propriedades do serviço [30]:

no [31-32], defina o arranque para o modo manual;
no [33], pare o serviço;

Quando quiser iniciar manualmente o SGBD, volte à aplicação [services], clique com o botão direito do rato no serviço [postgresql] (34) e inicie-o (35).

17.2. Gerir o PostgreSQL com a ferramenta [pgAdmin]

Inicie o serviço do Windows SGBD PostgreSQL (parágrafo anterior). Em seguida, da mesma forma que iniciou a ferramenta [services], inicie a ferramenta [pgadmin], que permite administrar o SGBD, o PostgreSQL e o [1-3]:

É possível que, a dada altura, lhe seja solicitada a palavra-passe do superutilizador. Esta é [postgres]. Definiste essa palavra-passe durante a instalação do SGBD. Neste documento, atribuímos a palavra-passe [root] ao superutilizador durante a instalação.

em [4], [pgAdmin] é uma aplicação web;
em [5], a lista de servidores PostgreSQL detetados pelo [pgAdmin], neste caso 1;
em [6], o servidor PostgreSQL que lançámos;
em [7], as bases de dados do SGBD, neste caso 1;
em [8], a base de dados [postgresql] é gerida pelo superutilizador [postgres];

Vamos, em primeiro lugar, criar um utilizador [admpersonnes] com a palavra-passe [nobody]:

em [17], foi inserido [nobody];

em [21], o código SQL que a ferramenta [pgAdmin] irá emitir para o SGBD PostgreSQL. Esta é uma forma de aprender a linguagem SQL, propriedade do PostgreSQL;
no [22], após a validação do assistente [Save], foi criado o utilizador [admpersonnes];

Agora, vamos criar a base de dados [dbpersonnes]:

Clicamos com o botão direito do rato em [23] e, em seguida, em [24-25] para criar uma nova base de dados. No separador [26], definimos o nome da base de dados [27] e o seu proprietário [admpersonnes] [28].

em [30], o código SQL de criação da base de dados;
em [31], após a validação do assistente [Save], é criada a base de dados [dbpersonnes];

Vamos explorar a base de dados [dbpersonnes] com scripts Python.

17.3. Instalação do conector Python do SGBD PostgreSQL

No esquema acima está representado um conector que faz a ligação entre os scripts Python e o SGBD PostgreSQL. Existem vários. Vamos instalar o conector [psycopg2]. Isto é feito num terminal Python (independentemente da pasta em que esse terminal estiver aberto). O conector é instalado através do 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. Migração dos scripts MySQL para os scripts PostgreSQL

A pasta [1] dos scripts MySQL é duplicada (Ctrl-C / Ctrl-V) e, em seguida, os nomes dos ficheiros são alterados de acordo com o seu conteúdo;

17.4.1. módulo [pgres_module]

Este módulo é uma cópia do módulo [mysql_module] (ver parágrafo |script [mysql-04]: execução de um ficheiro de comandos SQL|). Alteram-se as importações:

Em vez de:


# importações
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

escreve-se:


# importações
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

A assinatura da função [afficher_infos] era:


def afficher_infos(curseur: MySQLCursor):

Passa a ser:


def afficher_infos(curseur: cursor)

A assinatura da função [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)

Passa a ser:


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

De resto, nada mais muda.

17.4.2. script [pgres_01]

O script [pgres_01] é uma cópia do script [mysql_01] (ver parágrafo |script [mysql-01]: ligação a uma base de dados MySQL - 1|). Nele, efetuam-se as seguintes alterações:

Em vez de:


# importação do módulo mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

escreve-se:


# importação do módulo psycopg2
from psycopg2 import connect, DatabaseError, InterfaceError

O resto não se altera. Os resultados são os mesmos que com o MySQL.

17.4.3. script [pgres_02]

O script [pgres_02] é uma cópia do script [mysql_02] (ver parágrafo |script [mysql-02]: ligação a uma base de dados MySQL - 2|). Nele, são feitas as seguintes alterações:

Em vez de:


# importação do módulo mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

escreve-se:


# importação do módulo psycopg2
from psycopg2 import DatabaseError, InterfaceError, connect

Os resultados não são os mesmos que os do script [mysql_02]:

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

O script [pgres_02] é o seguinte:


# importação do módulo mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # liga-se e, em seguida, desliga-se (login, palavra-passe) da base de dados [database] do servidor [host]
    # lança a exceção DatabaseError em caso de problema
    connexion = None
    try:
        # ligação
        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:
        # encerra-se a ligação se esta tiver sido aberta
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# dados de identificação da ligação
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# início de sessão de um utilizador existente
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # é apresentado o erro
    print(erreur)

# início de sessão de um utilizador inexistente
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # é apresentado o erro
    print(erreur)

Embora as linhas 36-41 devessem ter apresentado uma mensagem de erro a indicar que a ligação ao SGBD tinha falhado, nada é apresentado. Na verdade, ao analisar a questão mais a fundo, verifica-se que as linhas 35 a 37 passam efetivamente pelo [except], mas que a variável [erreur] tem o valor [None]. Isto acontece com a versão 2.8.4 do conector [psycopg2].

É possível contornar este problema escrevendo uma mensagem genérica, mas menos precisa:


# início de sessão de um utilizador inexistente
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # é apresentado o erro
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

Os resultados são então os seguintes:


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]

O script [pgres_03] é uma cópia do script [mysql_03] (ver parágrafo |script [mysql-03]: criação de uma tabela MySQL|). Nele, efetuam-se as seguintes alterações:

Em vez de:


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

escreve-se:


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

Além disso, a assinatura da função [execute_sql], que era:


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

passa a ser:


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

O resto não se altera. O resultado é o seguinte:


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

É possível verificar a existência da tabela [personnes] com a ferramenta de administração [pgAdmin]:

17.4.5. script [pgres_04]

O script [pgres_04] é uma cópia do script [mysql_04] (ver parágrafo |script [mysql-04]: execução de um ficheiro de ordens SQL|). Utiliza o módulo [pgres_module]:


# a configuração da aplicação é recuperada
import config_04

config = config_04.configure()

# o syspath está configurado — é possível efetuar as importações
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

O resto permanece inalterado.

Cria-se uma configuração [pgres pgres-04 without_transaction], tal como foi feito no parágrafo |script [mysql-04]: execução de um ficheiro de ordens SQL|. Cria-se igualmente uma configuração [pgres pgres-04 with_transaction].

A execução da configuração [pgres pgres-04 without_transaction] produz os seguintes resultados:


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

linha 5: foi necessário alterar o comando de eliminação da tabela [personnes]. Ao contrário do conector MySQL, o conector PostgreSQL lança uma exceção se a tabela a eliminar não existir. O comando [drop table] tem uma variante, [drop table if exists], que não lança uma exceção se a tabela não existir. Foi essa que utilizámos aqui. Trata-se de um exemplo em que dois SGBD não se comportam da mesma forma em situações análogas;

A tabela [personnes] na ferramenta [pgAdmin] é a seguinte:

A execução da configuração [pgres pgres_04 with_transaction] produz os seguintes resultados:


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

A tabela [personnes] na ferramenta [pgAdmin] é a seguinte:

Aqui, o resultado é diferente do obtido com MySQL. Se executarmos os scripts nas mesmas condições, ou seja, após a execução do script sem transação, obtemos os seguintes resultados:

com o MySQL, a tabela [personnes] fica vazia;
com o PostgreSQL, a tabela [personnes] não está vazia;

A diferença reside nas formas diferentes como estes dois SGBD anulam a transação:

MySQL não anula as ordens [drop table] e [create table]. Ficamos com uma tabela [personnes] vazia;
PostgreSQL anula as ordens [drop table] e [create table]. A tabela volta ao estado em que se encontrava antes da execução do script com transação;

17.4.6. script [pgres_05]

O script [pgres_05] é uma cópia do script [mysql_05] (ver parágrafo |script [mysql-05]: utilização de consultas parametrizadas|). O script é alterado da seguinte forma:

Em vez de:


# importações
from mysql.connector import connect, DatabaseError, InterfaceError

escreve-se:


# importações
from psycopg2 import connect, DatabaseError, InterfaceError

O resto permanece inalterado.

Os resultados obtidos em [pgAdmin] são os seguintes:

17.5. Conclusão

A migração dos scripts MySQL para os scripts PostgreSQL decorreu com bastante facilidade. Trata-se de uma exceção. Os dois SGBD não suportam as mesmas regras de nomenclatura dos objetos SQL (bases de dados, tabelas, colunas, restrições, tipos de dados…), têm extensões SQL incompatíveis… Para garantir uma migração simples, é necessário, em ambos os casos, ater-se à norma SQL, sem tentar utilizar as extensões proprietárias dos SGBD. Isto faz-se, então, em detrimento do desempenho.