16. Utilizando o SGBD MySQL

16.1. Instalação do SGBD MySQL

Para utilizar o SGBD MySQL, iremos instalar o software Laragon.

16.1.1. Instalação do Laragon

O Laragon é um pacote que combina vários componentes de software:

um servidor web Apache. Iremos utilizá-lo para escrever scripts web em Python;
o SGBD MySQL;
a linguagem de script PHP, que não iremos utilizar;
um servidor Redis que implementa um cache para aplicações web. Não o utilizaremos;

O Laragon pode ser descarregado (fevereiro de 2020) no seguinte endereço:


https://laragon.org/download/

A instalação [1-5] resulta na seguinte estrutura de diretórios:

em [6] a pasta de instalação do PHP (não utilizada neste documento);

Ao iniciar o [Laragon], é apresentada a seguinte janela:

[1]: o menu principal do Laragon;
[2]: o botão [Start All] inicia o servidor web Apache e a base de dados MySQL;
[3]: o botão [WEB] exibe a página web [http://localhost];
[4]: o botão [Database] permite-lhe gerir o sistema de gestão de bases de dados MySQL utilizando a ferramenta [phpMyAdmin]. Deve instalar esta ferramenta previamente;
[5]: o botão [Terminal] abre um terminal de comandos;
[6]: o botão [Root] abre uma janela do Explorador do Windows posicionada na pasta [<laragon>/www], que é o diretório raiz do site [http://localhost]. É aqui que deve colocar as aplicações web estáticas geridas pelo servidor Apache do Laragon;

16.1.2. Criação de uma base de dados

Vamos agora mostrar-lhe como criar uma base de dados e um utilizador MySQL utilizando a ferramenta Laragon.

Uma vez iniciado, o Laragon [1] pode ser gerido a partir de um menu [2];
Em [3-5], instale a ferramenta de administração do MySQL [phpMyAdmin] caso ainda não esteja instalada;

Em [6], inicie o servidor web Apache e o sistema de gestão de bases de dados MySQL;
Em [7], o servidor Apache é iniciado;
Em [8], o sistema de gestão de bases de dados MySQL é iniciado;

Em [8-10], crie uma base de dados chamada [dbpersonnes] [11]. Vamos construir uma base de dados de pessoas;

em [11], iremos gerir a base de dados que acabámos de criar;

A operação [Bases de dados] envia um pedido Web para o URL [http://localhost/phpmyadmin] [12]. O servidor Web Apache do Laragon responde. O URL [http://localhost/phpmyadmin] é o URL do utilitário [phpMyAdmin] que instalámos anteriormente [5]. Este utilitário permite-lhe gerir bases de dados MySQL;
por predefinição, as credenciais de início de sessão do administrador da base de dados são: root [13] sem palavra-passe [14];

em [16], a base de dados que criámos anteriormente;

por enquanto, temos uma base de dados [dbpersonnes] [17] que está vazia [18];

Criamos um utilizador [admpersonnes] com a palavra-passe [nobody] que terá privilégios totais na base de dados [dbpersonnes]:

em [19], estamos posicionados na base de dados [dbpersonnes];
em [20], selecionamos o separador [Privilégios];
em [21-22], vemos que o utilizador [root] tem privilégios totais na base de dados [dbpersonnes];
em [23], criamos um novo utilizador;

em [25-26], o utilizador terá o nome de utilizador [admdbpersonnes];
em [27-29], a sua palavra-passe será [nobody];
em [30], o phpMyAdmin avisa que a palavra-passe é muito fraca (fácil de descodificar). Em produção, é preferível gerar uma palavra-passe forte utilizando [31];
em [32], especificamos que o utilizador [admdbpersonnes] deve ter privilégios totais na base de dados [dbpersonnes];
em [33], validamos as informações fornecidas;

em [35], o phpMyAdmin indica que o utilizador foi criado;
Em [36], a consulta SQL que foi executada na base de dados;
Em [37], o utilizador [admpersonnes] tem privilégios totais na base de dados [dbpersonnes];

Agora temos:

uma base de dados MySQL [dbpersonnes];
um utilizador [admpersonnes/nobody] que tem privilégios totais nesta base de dados;

16.2. Instalação do pacote [mysql-connector-python]

Iremos escrever scripts em Python para utilizar a base de dados criada anteriormente com a seguinte arquitetura:

Utiliza-se um conector para isolar o código Python do SGBD em utilização. Existem conectores para diferentes SGBDs, e todos seguem a mesma interface. Assim, quando substituímos o SGBD MySQL pelo SGBD PostgreSQL no exemplo acima, a arquitetura fica da seguinte forma:

Como todos os conectores de SGBDs seguem a mesma interface, normalmente não é necessário modificar o script Python. Na realidade, a maioria dos SGBDs utiliza SQL proprietário:

eles cumprem a norma SQL (Structured Query Language);
mas estendem-no — uma vez que não é suficiente por si só — com extensões de linguagem proprietárias;

Por isso, é comum que, ao mudar de SGBD, seja necessário fazer modificações no SQL dos scripts.

Por predefinição, o Python não oferece a capacidade de gerir uma base de dados MySQL. Para o fazer, é necessário descarregar um pacote. Existem vários disponíveis. Aqui, utilizaremos o pacote [mysql-connector-python], que é o conector oficial da Oracle, a empresa proprietária do MySQL.

O pacote será instalado numa janela [Terminal] do Pycharm:

o diretório em [2] é irrelevante para o que se segue;

No terminal, digite o comando [pip search MySQL]:

O [pip] (Package Installer for Python) é a ferramenta para instalar pacotes Python. A ferramenta [pip] liga-se ao repositório que contém pacotes Python;
[search MySQL]: recupera uma lista de pacotes que contêm o termo [MySQL] (sem distinção entre maiúsculas e minúsculas) nos seus nomes;

Os resultados do comando são os seguintes:


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>

Todos os módulos cujo nome ou descrição contém a palavra-chave MySQL foram listados. O que iremos utilizar (fevereiro de 2020) é o [mysql-connector-python], linha 17. Para o instalar, digite o comando [pip install -U mysql-connector-python] no terminal:


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

Linha 1: A opção [install -U] (U = atualização) solicita a versão mais recente dos vários pacotes associados ao pacote [mysql-connector-python];

Para ver quais os pacotes instalados no ambiente Python da nossa máquina, digite o 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
…

Linha 13: Temos o pacote [mysql-connector-python];

Para saber como utilizar o pacote [mysql-connector-python] para gerir uma base de dados MySQL, visite o site do pacote |https://dev.mysql.com/doc/connector-python/en/|. A secção seguinte apresenta uma série de exemplos.

16.3. script [mysql_01]: ligação a uma base de dados MySQL - 1

O script [mysql_01] demonstra o primeiro passo na utilização de uma base de dados. Permitir-nos-á verificar se conseguimos ligar-nos à base de dados [dbpersonnes] criada anteriormente.

#  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()

Notas

Linha 2: Importa determinadas funções e classes do módulo [mysql.connector];
Linhas 6–7: As credenciais do utilizador que se irá ligar;
linha 8: a máquina que hospeda a base de dados. O conector MySQL permite-lhe trabalhar com uma base de dados remota;
linha 9: o nome da base de dados à qual queremos ligar-nos;
linhas 11–26: o script irá ligar (linha 16) o utilizador [admpersonnes / nobody] à base de dados [dbpersonnes];
linhas 20–26: a ligação pode falhar. Por isso, é tratada dentro de um bloco try/except/finally;
linha 16: o método connect do módulo [mysq.connector] aceita vários parâmetros nomeados:
- user: o utilizador proprietário da ligação [admpersonnes];
- password: palavra-passe do utilizador [nobody];
- host: máquina do SGBD MySQL [localhost];
- database: a base de dados à qual se deve ligar. Opcional.
linha 20: se for levantada uma exceção, esta é do tipo [DatabaseError] ou [InterfaceError];
linhas 23–26: na cláusula [finally], a ligação é encerrada;

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/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]: a ligar-se a uma base de dados MySQL - 2

Neste novo script, a ligação à base de dados está encapsulada numa função:

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

Notas:

linhas 6–19: uma função [connection] que tenta ligar e, em seguida, desligar um utilizador da base de dados [dbpersonnes]. Apresenta o resultado;
linhas 29–41: programa principal – chama o método de ligação duas vezes e apresenta quaisquer exceções;

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/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]: criação de uma tabela MySQL

Agora que sabemos como estabelecer uma ligação com um SGBD MySQL, podemos começar a emitir comandos SQL através desta ligação. Para tal, vamos ligar-nos à base de dados criada [dbpersonnes] e utilizar a ligação para criar uma tabela na base de dados.

#  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()

Notas:

linha 9: a função execute_sql executa uma consulta SQL numa ligação aberta;
linha 14: as operações SQL na ligação são realizadas através de um objeto especial chamado cursor;
linha 14: obtenção de um cursor;
linha 16: execução da consulta SQL;
linhas 17–20: independentemente de haver ou não um erro, o cursor é fechado. Isto liberta os recursos a ele associados. Se ocorrer uma exceção, esta não é tratada aqui. Será propagada para o código de chamada;
linhas 33–43: criação de uma ligação à base de dados;
linha 38: definir AUTOCOMMIT=True para uma ligação significa que cada execução de consulta decorre dentro de uma transação automática. O modo predefinido é AUTOCOMMIT=False, em que o programador é responsável pela gestão das transações. Uma transação é um mecanismo que engloba a execução de múltiplas consultas, de 1 a n. Ou todas elas são bem-sucedidas, ou nenhuma delas é bem-sucedida. Assim, se as consultas d e 1 a i forem bem-sucedidas, mas a consulta i+1 falhar, então as consultas de 1 a i serão «revertidas», de modo que a base de dados retorne ao estado em que se encontrava antes da execução da consulta 1;
Aqui, existem duas consultas SQL (linhas 49, 58). Cada uma será executada dentro de uma transação. O facto de a segunda falhar não tem impacto na primeira;
linhas 45–51: a instrução SQL [drop table people] é executada. Ela elimina a tabela denominada [people]. Se a tabela não existir, poderá ser reportado um erro. Este erro é ignorado (linha 51);
linhas 53–55: o comando para criar a tabela [people]. Uma tabela pode ser vista como um conjunto de linhas e colunas. O comando de criação especifica os nomes das colunas:
- [id]: um identificador inteiro. Será único para cada pessoa. Esta será a chave primária (PRIMARY KEY). Isto significa que, dentro da tabela, esta coluna nunca tem o mesmo valor duas vezes e pode ser usada para identificar uma pessoa;
- [last_name]: uma cadeia de caracteres com até 30 caracteres;
- [last_name]: uma cadeia de caracteres com até 30 caracteres;
- [age]: um número inteiro;
- O atributo [NOT NULL] para cada uma destas colunas significa que, numa linha da tabela, nenhuma das três colunas pode estar vazia;
- o parâmetro [unique(last_name, first_name)] é chamado de restrição. Aqui, a restrição nas linhas é que a tupla (last_name, first_name) na linha deve ser única na tabela. Isto significa que podemos identificar de forma única um indivíduo na tabela cujo apelido e nome próprio sejam conhecidos;
linhas 56–60: execução da instrução SQL;
linhas 61–63: tratamento de eventuais exceções;
linhas 64–66: desligar da base de dados;

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/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ção com [phpMyAdmin]:

a base de dados [dbpersonnes] [1] tem uma tabela [personnes] [2] com a estrutura [3-4], a chave primária [5] e a restrição de unicidade [6];

16.6. script [mysql_04]: execução de um ficheiro de comandos SQL

Após ter criado previamente a tabela [personnes], preenchemos agora essa tabela e, em seguida, consultamo-la utilizando instruções SQL.

Queremos executar as instruções SQL a partir de um ficheiro de texto:

O conteúdo do ficheiro [commands.sql] é o seguinte:


# 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'

Primeiro, definimos funções que colocamos num módulo para que possamos reutilizá-las:

O script [mysql_module] é o seguinte:

#  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}"]

Notas:

Linha 29: A função [execute_file_of_commands] executa os comandos SQL contidos no ficheiro de texto denominado [sql_filename]:
consulte os comentários nas linhas 31–38 para saber o significado dos parâmetros;
Linhas 40–48: O ficheiro de texto [sql_filename] é processado;
linha 43: abertura do ficheiro;
linha 34: execução da função [execute_list_of_commands], que executa os comandos SQL que lhe são passados numa lista. Esta lista é aqui composta por todas as linhas do ficheiro de texto [file.readlines()] (linha 45);

A função [execute_list_of_commands] é a seguinte:

# ---------------------------------------------------------------------------------
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()

Notas

Linha 2: A função [execute_list_of_commands] executa os comandos SQL contidos na lista [sql_commands]:
Consulte os comentários nas linhas 4–11 para saber o significado dos parâmetros;
Linha 2: A ligação recebida é uma ligação aberta a uma base de dados;
linha 15: se pretender que todos os comandos da lista [sql_commands] sejam executados dentro de uma transação, deve trabalhar no modo AUTOCOMMIT=False. Caso contrário, trabalhará no modo AUTOCOMMIT=True, e cada comando da lista [sql_commands] será executado dentro de uma transação automática, não havendo uma transação global;
linha 19: é solicitado um cursor para executar os vários comandos SQL;
linhas 22–51: os comandos são executados um a um;
linhas 26–27: aceitamos linhas em branco e comentários na lista de comandos SQL. Neste caso, simplesmente ignoramos o comando;
linhas 30–33: executam a consulta atual;
linhas 35–45: tratam o caso de um possível erro de tempo de execução na consulta atual;
linhas 37–38: o erro é adicionado à tabela de erros;
linhas 40–41: se o registo tiver sido ativado, a mensagem de erro é apresentada;
linhas 43–45: se o código de chamada solicitou uma paragem após o primeiro erro ou solicitou a utilização de uma transação, então o programa deve parar. A matriz de erros é devolvida;
linhas 46-51: caso em que não houve erro de execução para a consulta atual;
linhas 48-49: se o rastreamento foi solicitado, a consulta executada é exibida com a etiqueta «bem-sucedida»;
linhas 50–51: exibem o resultado da consulta executada. Voltaremos à função [display_info] um pouco mais tarde;
linhas 54–65: a cláusula [finally] é executada em todos os casos, independentemente de ter ocorrido uma exceção ou não;
linhas 56–57: Fechar o cursor. Isto liberta os recursos que lhe foram atribuídos;
linhas 59-65: tratamos o caso em que o código de chamada solicitou que os comandos SQL fossem executados dentro de uma transação;
linha 60: verificamos se a lista [errors] está vazia, o que significa que não ocorreu nenhuma exceção. Neste caso, a transação é confirmada (linha 65); caso contrário, é revertida (linha 62);

A função [display_info] apresenta o resultado de uma consulta:


# ---------------------------------------------------------------------------------
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}")

Notas

Linha 1: O parâmetro da função é o cursor que acabou de executar uma instrução SQL. Dependendo de esta instrução ser uma instrução SELECT ou uma instrução de atualização (INSERT, UPDATE, DELETE), o conteúdo do cursor é diferente;
Linha 6: Se o cursor tiver o campo [description], significa que executou uma instrução SELECT, e [description] descreve os campos solicitados na instrução SELECT:
- description[i] descreve o campo número i solicitado pela instrução SELECT. É uma lista;
- description[i][0] é o nome do campo número i;
linhas 11–17: são exibidos os nomes dos campos solicitados pela instrução SELECT;
linhas 18–24: processamos o resultado da instrução SELECT;
linhas 20, 24: o resultado de um SELECT é processado sequencialmente. Este resultado é um conjunto de linhas. A linha atual é obtida através de [cursor.fetchone()] (linha 19). Obtém-se então uma tupla;
linhas 27–30: se o cursor não tiver o campo [description], então executou uma instrução de atualização INSERT, UPDATE ou DELETE. Podemos então determinar quantas linhas na tabela foram modificadas pela execução desta instrução;
linha 30: [cursor.rowcount] é este número;

O script principal [mysql-04] utiliza o módulo [mysql_module] que acabámos de descrever:

O ficheiro [config_04] configura o contexto de execução do 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"
    }

O script [mysql_04] é o seguinte:

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

Notas

linhas 1-4: configuração do script;
linha 8: importação do módulo [mysql_module] descrito acima:
linhas 12-22: o script [mysql-04] espera um parâmetro que deve ter um dos valores [true / false]. Este parâmetro indica se o ficheiro de comando SQL deve ser executado dentro de uma transação (true) ou não (false);
linha 14: os parâmetros passados pelo utilizador para o script encontram-se na lista [sys.argv];
linha 15: são necessários dois parâmetros, por exemplo [mysql-04 true]. O nome do script conta como um parâmetro;
linhas 17-18: se houver efetivamente dois parâmetros, o segundo deve ser uma cadeia de caracteres com o valor «true» ou «false»;
linhas 24–29: cálculo do texto exibido na linha 33;
linhas 39–44: executar os comandos no ficheiro [./data/commands.sql];
linhas 45–49: se ocorrer um erro durante a ligação (linha 40) ou se este não for tratado pelo script [execute_file_of_commands], o erro é apresentado e o processo é encerrado;
linhas 55–62: se a execução for bem-sucedida, é exibido o número de erros encontrados durante a execução dos comandos SQL;

Execução n.º 1

Primeiro, realizamos uma execução sem transação. Para tal, iremos criar uma configuração de execução conforme descrito na secção |configurar um contexto de execução|:

em [1-4], criamos uma configuração de execução Python;

[5]: nome da configuração de execução;
[6]: caminho para o script a ser executado;
[7]: parâmetros do script;
[8]: diretório de execução;

Esta configuração corresponde, portanto, à execução do ficheiro SQL com uma transação. Utilize o botão [Aplicar] para confirmar a configuração.

Criamos a configuração de execução [mysql mysql-04 without_transaction] da mesma forma:

Esta configuração corresponde, portanto, à execução do ficheiro SQL sem uma transação. Utilize o botão [Aplicar] para confirmar a configuração.

Primeiro, executamos a versão sem transação:

Os resultados sã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/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

Notas:

Linha 19: Podemos ver que, após o erro, a execução das instruções SQL continuou. Isto deve-se ao facto de a execução ter ocorrido sem uma transação e com o parâmetro [stop=False]. Todas as instruções SQL foram, portanto, executadas. Devemos, portanto, ter uma tabela [people] que reflita esta execução;

Verificação com o phpMyAdmin:

Execução n.º 2

Executamos agora a configuração [mysql mysql-04 with_transaction]. Os resultados sã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/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

Notas:

Linha 19: Podemos ver que, após o erro, não foram executadas mais instruções SQL. Isto deve-se ao facto de a execução ter ocorrido dentro de uma transação e, ao encontrar o primeiro erro, termos revertido a transação e interrompido a execução das instruções SQL. Isto significa que os resultados das instruções nas linhas 9, 11 e 13 foram revertidos. Devemos, portanto, ter uma tabela [people] vazia;

Verificação com o phpMyAdmin:

em [5], vemos que a tabela [people] [2] está vazia;

16.7. script [mysql_05]: utilização de consultas parametrizadas

O script [mysql_05] introduz o conceito de consultas parametrizadas:

#  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()

Notas

linhas 12–21: criamos duas listas de pessoas para incluir na base de dados [dbpeople];
linha 27: ligamos à base de dados;
linha 31: limpamos o conteúdo da tabela [people];
linhas 33-34: inserimos pessoas utilizando uma consulta parametrizada. Na linha 34, o primeiro parâmetro é a instrução SQL a ser executada. Esta instrução está incompleta. Contém marcadores de posição [%s] que serão substituídos um a um e por ordem pelos valores da lista no segundo parâmetro;
Linha 36: Inserção de pessoas, desta vez utilizando uma única instrução [cursor.executemany]. O segundo parâmetro de [executemany] é, portanto, uma lista de listas;

As vantagens das consultas parametrizadas residem em dois pontos:

elas são executadas mais rapidamente do que as consultas «codificadas», que têm de ser analisadas em cada execução. A consulta parametrizada [executemany] é analisada apenas uma vez. É então executada n vezes sem ser analisada novamente;
os parâmetros inseridos na consulta parametrizada são validados. Se contiverem caracteres reservados, como o apóstrofo, estes são «escapados» para que não interfiram na execução da instrução SQL. Para verificar isto, incluímos nomes próprios e apelidos com apóstrofos na lista (linhas 16 e 21);

Os resultados obtidos no phpMyAdmin são os seguintes:

Note-se que as cadeias de caracteres que contêm um apóstrofo — um caractere reservado em SQL — foram inseridas corretamente. A consulta parametrizada «escapou-as». Sem uma consulta parametrizada, teríamos de o fazer nós próprios;