Skip to content

17. Uso do SGBD e do PostgreSQL

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

Nós o utilizamos aqui para mostrar que é bastante simples migrar de 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, ela será a seguinte:

Image

17.1. Instalação do SGBD PostgreSQL

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

Image

Image

  • no [1-4], baixe o instalador do SGBD;

Execute o instalador baixado:

Image

  • no [6], especifique uma pasta de instalação;

Image

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

Image

  • em [12-13], colocamos aqui a senha [root]. Essa será a senha do administrador do SGBD, que se chama [postgres]. O PostgreSQL também o chama de superusuário;
  • no [15], mantenha o valor padrão: essa é a porta de escuta do SGBD;

Image

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

Image

Image

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 essa configuração. Digite [services] na barra de pesquisa do Windows [24-26]:

Image

  • em [29], vemos que o serviço SGBD PostgreSQL está no modo automático. Alteramos isso acessando as propriedades do serviço [30]:

Image

  • no [31-32], defina o modo de inicialização como manual;
  • no [33], pare o serviço;

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

17.2. Gerenciar o PostgreSQL com a ferramenta [pgAdmin]

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

Image

É possível que, em determinado momento, seja solicitada a senha do superusuário. Ela é [postgres]. Você definiu essa senha durante a instalação do SGBD. Neste documento, atribuímos a senha [root] ao superusuário durante a instalação.

  • em [4], [pgAdmin] é um aplicativo web;
  • em [5], a lista de servidores PostgreSQL detectados pelo [pgAdmin], neste caso 1;
  • em [6], o servidor PostgreSQL que iniciamos;
  • em [7], os bancos de dados do SGBD, neste caso 1;
  • em [8], o banco de dados [postgresql] é gerenciado pelo superusuário [postgres];

Vamos, primeiro, criar um usuário [admpersonnes] com a senha [nobody]:

Image

Image

  • em [17], colocamos [nobody];

Image

  • em [21], o código SQL que será gerado pela ferramenta [pgAdmin] para o SGBD PostgreSQL. Essa é uma maneira de aprender a linguagem SQL, propriedade do PostgreSQL;
  • no [22], após a validação do assistente [Save], o usuário [admpersonnes] foi criado;

Agora vamos criar o banco de dados [dbpersonnes]:

Image

Clicamos com o botão direito do mouse em [23] e, em seguida, em [24-25] para criar um novo banco de dados. Na guia [26], definimos o nome do banco de dados como [27] e seu proprietário como [admpersonnes] [28].

Image

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

Vamos utilizar o banco de dados [dbpersonnes] com scripts em Python.

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

Image

No esquema acima, está representado um conector que faz a ligação entre os scripts Python e o SGBD PostgreSQL. Existem vários deles. Vamos instalar o conector [psycopg2]. Isso é feito em um terminal Python (não importa em qual pasta esse terminal esteja aberto). O conector é instalado pelo 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

Image

  • a pasta [1] dos scripts MySQL é duplicada (Ctrl-C / Ctrl-V) e, em seguida, os nomes dos arquivos são alterados de acordo com 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 arquivo 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):

E 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)

E passa a ser:


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

Fora isso, 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]: conexão a um banco de dados MySQL - 1|). Nele, são feitas 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 restante permanece inalterado. 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]: conexão a um banco 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 do 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

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):
    # conecta e, em seguida, desconecta (login, senha) do banco de dados [database] do servidor [host]
    # lança a exceção DatabaseError em caso de problema
    connexion = None
    try:
        # conexã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:
        # a conexão é encerrada caso tenha sido aberta
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# credenciais da conexão
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# conectando um usuário existente
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # exibe o erro
    print(erreur)

# conectando um usuário inexistente
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # exibe o erro
    print(erreur)

Embora as linhas 36 a 41 devessem exibir uma mensagem de erro indicando que a conexão com o SGBD falhou, nada é exibido. Na verdade, ao investigar mais a fundo, percebe-se que as linhas 35 a 37 são efetivamente processadas no [except], mas que a variável [erreur] tem o valor [None]. Isso ocorre com a versão 2.8.4 do conector [psycopg2].

É possível contornar esse problema escrevendo uma mensagem genérica, porém menos precisa:


# login de um usuário inexistente
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # exibe 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, são feitas 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 restante permanece inalterado. 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]:

Image

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 arquivo de ordens SQL|). Ele utiliza o módulo [pgres_module]:


# a configuração do aplicativo é recuperada
import config_04

config = config_04.configure()

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

O restante permanece inalterado.

Cria-se uma configuração [pgres pgres-04 without_transaction], conforme feito no parágrafo |script [mysql-04]: execução de um arquivo de ordens SQL|. Da mesma forma, cria-se 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 modificar o comando de exclusão da tabela [personnes]. Ao contrário do conector MySQL, o conector PostgreSQL gera uma exceção se a tabela a ser excluída não existir. O comando [drop table] possui uma variante, [drop table if exists], que não gera exceção caso a tabela não exista. Utilizamos essa variante aqui. Trata-se de um exemplo em que dois comandos SGBD não se comportam da mesma maneira em situações semelhantes;

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

Image

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:

Image

Aqui, o resultado é diferente daquele 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 MySQL, a tabela [personnes] está vazia;
  • com o PostgreSQL, a tabela [personnes] não está vazia;

A diferença reside nas diferentes maneiras como esses dois SGBD revertem a transação:

  • MySQL não reverte as ordens [drop table] e [create table]. Ficamos com uma tabela [personnes] vazia;
  • PostgreSQL desfaz 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]: uso de consultas parametrizadas|). O script é modificado 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 restante permanece inalterado.

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

Image

17.5. Conclusão

A migração dos scripts MySQL para os scripts PostgreSQL ocorreu com relativa 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…), possuem extensões SQL incompatíveis… Para garantir uma portabilidade simples, é preciso, em ambos os casos, seguir o padrão SQL, sem tentar usar as extensões proprietárias do SGBD. Isso, porém, ocorre em detrimento do desempenho.

https://stahe.github.io
Este tutorial do curso, escrito por Serge Tahé, é disponibilizado ao público de acordo com os termos da Creative Commons Atribuição – Sem uso comercial – Compartilhamento igual 3.0 inalterado.