16. Utilização do SGBD MySQL
16.1. Instalação do SGBD e do MySQL
Para utilizar o SGBD e o MySQL, vamos instalar o software Laragon.
16.1.1. Instalação do Laragon
O Laragon é um pacote que reúne vários programas:
- 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) na seguinte morada:
- A instalação [1-5] gera a seguinte estrutura de diretórios:
- em [6], a pasta de instalação de 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 o SGBD MySQL;
- [3]: o botão [WEB] apresenta a página web [http://localhost];
- [4]: o botão [Database] permite gerir o SGBD e o MySQL com a ferramenta [phpMyAdmin]. É necessário instalar esta ferramenta previamente;
- [5]: o botão [Terminal] abre um terminal de comandos;
- [6]: o botão [Root] abre o Explorador do Windows com a pasta [<laragon>/www] selecionada, que é a raiz do site [http://localhost]. É aqui que devem ser colocadas 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 como criar uma base de dados, bem como um utilizador MySQL, com a ferramenta Laragon.
- Uma vez iniciado, o Laragon [1] pode ser administrado a partir de um menu [2];
- em [3-5], instala-se a ferramenta [phpMyAdmin] de administração do MySQL, caso ainda não tenha sido instalada;
- em [6], inicia-se o servidor web Apache, bem como o SGBD e o MySQL;
- no [7], o servidor Apache é iniciado;
- em [8], o SGBD e o MySQL são iniciados;
- em [8-10], cria-se uma base de dados a que se dá o nome de [dbpersonnes] [11]. Vamos construir uma base de dados de pessoas;
- em [11], vamos gerir a base de dados que acabámos de criar;
- A operação [Bases de données] envia um pedido Web para o URL, [http://localhost/phpmyadmin] e [12]. É o servidor Web Apache do Laragon que responde. O URL [http://localhost/phpmyadmin] é o URL do utilitário [phpMyAdmin] que instalámos anteriormente, o [5]. Este utilitário permite gerir as bases de dados MySQL;
- por predefinição, as credenciais de ligaçã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, e [18];
Criamos um utilizador [admpersonnes] com a palavra-passe [nobody], que terá todos os direitos sobre a base de dados [dbpersonnes]:
- em [19], estamos posicionados na base de dados [dbpersonnes];
- em [20], selecionamos o separador [Privileges];
- em [21-22], verifica-se que o utilizador [root] tem todos os direitos sobre a base de dados [dbpersonnes];
- em [23], cria-se um novo utilizador;
- em [25-26], o utilizador terá o identificador [admdbpersonnes];
- em [27-29], a sua palavra-passe será [nobody];
- em [30], phpMyAdmin indica que a palavra-passe é muito fraca (fácil de descodificar). Em produção, é preferível gerar uma palavra-passe forte com [31];
- em [32], indica-se que o utilizador [admdbpersonnes] deve ter todos os direitos sobre a base de dados [dbpersonnes];
- em [33], validam-se os dados fornecidos;
- em [35], phpMyAdmin indica que o utilizador foi criado;
- em [36], a ordem SQL que foi emitida com base nisso;
- em [37], o utilizador [admpersonnes] tem todos os direitos sobre a base de dados [dbpersonnes];
Agora temos:
- uma base de dados MySQL [dbpersonnes];
- um utilizador [admpersonnes/nobody] que tem todos os direitos sobre esta base de dados;
16.2. Instalação do pacote [mysql-connector-python]
Vamos escrever scripts em Python para explorar a base de dados criada anteriormente com a seguinte arquitetura:
Um conector serve para isolar o código Python do SGBD em funcionamento. Existem conectores para diferentes SGBD e estes seguem a mesma interface. Assim, quando, no exemplo acima, se substitui o SGBD e o MySQL pelo SGBD e pelo PostgreSQL, a arquitetura passa a ser a seguinte:
Como todos os conectores do SGBD seguem a mesma interface, o script Python normalmente não precisa de ser alterado. Na prática, a maioria dos SGBD tem um SQL proprietário:
- seguem a norma SQL (Structured Query Language);
- mas alargam-na, uma vez que esta não é suficiente, com extensões proprietárias da linguagem;
Por isso, é frequente que, aquando de uma alteração no SGBD, seja necessário efetuar alterações no SQL nos scripts.
Por si só, o Python não oferece a possibilidade de gerir uma base de dados MySQL. Para tal, é necessário descarregar um pacote. Existem vários. Vamos utilizar aqui o pacote [mysql-connector-python], que é o conector oficial da Oracle, a empresa proprietária do MySQL.
A instalação do pacote será efetuada numa janela do PyCharm:
- a pasta em [2] não tem importância para o que se segue;
No terminal, introduz-se o comando [pip search MySQL]:
- [pip] (Package Installer for Python) é a ferramenta de instalação de pacotes Python. A ferramenta [pip] liga-se ao repositório que contém os pacotes Python;
- [search MySQL]: solicita a lista de pacotes que contenham o termo [MySQL] (não importa se em maiúsculas ou minúsculas) no seu nome;
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>
Foram listados todos os módulos cujo nome ou descrição contém a palavra-chave MySQL. O que iremos utilizar (fevereiro de 2020) é o [mysql-connector-python], linha 17. Para o instalar, introduzimos no terminal o comando [pip install -U mysql-connector-python]:
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=upgrade) solicita a versão mais recente dos vários pacotes associados ao pacote [mysql-connector-python];
Para saber quais são os pacotes instalados no ambiente Python da nossa máquina, digitamos 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, de facto, o pacote [mysql-connector-python];
Para saber como utilizar o pacote [mysql-connector-python] para gerir uma base de dados MySQL, acedemos ao site do pacote |https://dev.mysql.com/doc/connector-python/en/|. A seguir, apresentamos uma série de exemplos.
16.3. script [mysql_01]: ligação a uma base de dados MySQL - 1
O script [mysql_01] apresenta o primeiro passo para a utilização de uma base de dados. Permitir-nos-á verificar se conseguimos ligar-nos à base de dados [dbpersonnes] criada anteriormente.
# importação do módulo mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
# ligação a uma base de dados MySql [dbpersonnes]
# a identidade do utilizador é (admpersonnes, nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# vamos lá
connexion = None
try:
print("Connexion au SGBD MySQL en cours...")
# ligação
connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
# acompanhamento
print(
f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
# exibe-se o erro
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# encerra-se a ligação, caso tenha sido aberta
if connexion:
connexion.close()
Notas
- linha 2: importamos algumas funções e classes do módulo [mysql.connector];
- linhas 6-7: os dados de identificação do utilizador que se vai ligar;
- linha 8: o servidor que aloja a base de dados. Com efeito, o conector MySQL permite trabalhar com uma base de dados remota;
- linha 9: o nome da base de dados à qual se pretende ligar;
- linhas 11-26: o script vai ligar (linha 16) o utilizador [admpersonnes / nobody] à base de dados [dbpersonnes];
- linhas 20-26: a ligação pode falhar. Por isso, é realizada num bloco «try / except / finally»;
- linha 16: o método connect do módulo [mysq.connector] aceita vários parâmetros nomeados:
- user: 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 estabelece a ligação. Opcional.
- linha 20: se for lançada uma exceção, esta é do tipo [DatabaseError] ou [InterfaceError];
- linhas 23-26: na cláusula [finally], encerra-se a ligação;
Resultados
16.4. script [mysql_02]: ligação a uma base de dados MySQL - 2
Neste novo script, a ligação à base de dados é isolada numa função:
# importação do módulo mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect
# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
# liga e, em seguida, desliga (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:
# a ligação é encerrada se tiver sido aberta
if connexion:
connexion.close()
print("Déconnexion réussie\n")
# ---------------------------------------------- main
# dados de início de sessã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 um erro
print(erreur)
# início de sessão de um utilizador inexistente
try:
connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# é exibido o erro
print(erreur)
Notas:
- linhas 6-19: uma função [connexion] que tenta ligar e, em seguida, desligar um utilizador da base de dados [dbpersonnes]. Apresenta o resultado;
- linhas 29-41: programa principal – chama duas vezes o método connexion e apresenta eventuais exceções;
Resultados
16.5. script [mysql_03]: criação de uma tabela MySQL
Agora que sabemos como estabelecer uma ligação com um SGBD MySQL, começamos a emitir comandos SQL nesta ligação. Para tal, vamos ligar-nos à base de dados criada [dbpersonnes] e utilizar a ligação para criar uma tabela nessa base de dados.
# importações
import sys
from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection
# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
# executa uma consulta de atualização na ligação
curseur = None
try:
# solicita-se um cursor
curseur = connexion.cursor()
# executa a consulta de atualização na ligação
curseur.execute(update)
finally:
# fecha o cursor, caso tenha sido obtido
if curseur:
curseur.close()
# ---------------------------------------------- main
# credenciais da ligação
# a identidade do utilizador
ID = "admpersonnes"
PWD = "nobody"
# o computador anfitrião do SGBD
HOST = "localhost"
# identidade da base de dados
DATABASE = "dbpersonnes"
# vamos avançar passo a passo
try:
# ligação
connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
# modo AUTOCOMMIT
connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
# exibir o erro
print(f"L'erreur suivante s'est produite : {erreur}")
# sair
sys.exit()
# eliminação da tabela «pessoas», caso exista
# se não existir, ocorrerá um erro — este é ignorado
requête = "drop table personnes"
try:
execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
pass
# criação da tabela «pessoas»
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:
# execução da consulta
execute_sql(connexion, requête)
# exibição
print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
# exibe-se o erro
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# desconexão
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 específico denominado cursor;
- linha 14: obtenção de um cursor;
- linha 16: execução da consulta SQL;
- linhas 17-20: quer ocorra um erro ou não, o cursor é fechado. Isto liberta os recursos que lhe estão associados. Se ocorrer uma exceção, esta não é tratada aqui. Será encaminhada para o código chamador;
- linhas 33-43: criação de uma ligação à base de dados;
- linha 38: o modo AUTOCOMMIT=True para uma ligação significa que cada execução de uma consulta ocorre numa transação automática. O modo predefinido é AUTOCOMMIT=False, em que cabe ao programador a responsabilidade de gerir as transações. Uma transação é um mecanismo que engloba a execução de várias consultas, de 1 a n. Ou todas elas são bem-sucedidas, ou nenhuma é bem-sucedida. Assim, se as consultas de 1 a i forem bem-sucedidas, mas a consulta i+1 falhar, então as consultas de 1 a i serão «revertidas» para que a base de dados recupere o estado em que se encontrava antes da execução da consulta 1;
- aqui, existem duas consultas SQL (linhas 49, 58). Cada uma delas será executada numa transação. O facto de a segunda falhar não tem qualquer impacto na primeira;
- linhas 45-51: a ordem SQL [drop table personnes] é executada. Esta elimina a tabela denominada [personnes]. Se esta não existir, pode ser sinalizado um erro. Este é ignorado (linha 51);
- linhas 53-55: a instrução para criar a tabela [personnes]. Uma tabela pode ser vista como um conjunto de linhas e colunas. A instrução 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, na tabela, esta coluna não apresenta duas vezes o mesmo valor e que pode ser utilizada para identificar uma pessoa;
- [nom]: uma cadeia de, no máximo, 30 caracteres;
- [prenom]: uma cadeia de, no máximo, 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(nom,prenom)] denomina-se restrição. Neste caso, a restrição aplicável às linhas é que o tuplo (apelido, nome próprio) da linha deve ser único na tabela. Isto significa que é possível identificar de forma única na tabela um indivíduo cujo apelido e nome próprio sejam conhecidos;
- linhas 56-60: execução da ordem SQL;
- linhas 61-63: gestão de uma eventual exceção;
- linhas 64-66: desligamento da base de dados;
Resultados
Verificação com [phpMyAdmin]:
- A base de dados [dbpersonnes] [1] possui 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
Depois de termos criado anteriormente a tabela [personnes], vamos agora preenchê-la e, em seguida, processá-la utilizando as ordens SQL.
Pretendemos executar as ordens SQL a partir de um ficheiro de texto:
O conteúdo do ficheiro [commandes.sql] é o seguinte:
# eliminação da tabela [personnes]
drop table personnes
# criação da tabela «pessoas»
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# inserção de duas pessoas
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# visualização da tabela
select prenom, nom, age from personnes
# erro intencional
xx
# inserção de três pessoas
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)
# visualização da tabela
select prenom, nom, age from personnes
# lista de pessoas por ordem alfabética dos apelidos e, em caso de igualdade de apelidos, por ordem alfabética dos nomes próprios
select nom,prenom from personnes order by nom asc, prenom desc
# lista de pessoas com idade no intervalo [20,40], por ordem decrescente de idade
# e, em caso de idades iguais, por ordem alfabética dos apelidos e, em caso de apelidos iguais, por ordem alfabética dos nomes próprios
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# inclusão da Sra. Bruneau
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# atualização da sua idade
update personnes set age=47 where nom='Bruneau'
# lista das pessoas com o apelido Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# eliminação da Sra. Bruneau
delete from personnes where nom='Bruneau'
# lista de pessoas com o apelido Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
Primeiro, definimos funções que instalamos num módulo para que possam ser reutilizadas:
O script [mysql_module] é o seguinte:
# importações
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
# exibe o resultado de um comando SQL
…
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# utiliza a ligação aberta [connexion]
# executa nesta ligação os comandos SQL contidos na lista [sql_commands]
# este ficheiro é um ficheiro de comandos SQL a executar à razão de um por linha
# se acompanhamento=True, então cada execução de um comando SQL é acompanhada por uma mensagem indicando o seu sucesso ou falha
# se «paragem»=True, a função pára ao primeiro erro encontrado; caso contrário, executa todos os comandos SQL
# se with_transaction=True, então qualquer erro anula o conjunto de comandos SQL executados anteriormente
# se with_transaction=False, então um erro não tem qualquer impacto nas ordens SQL executadas anteriormente
# a função devolve uma lista [erreur1, erreur2, ...]
….
# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# utiliza a ligação aberta [connexion]
# executa nesta ligação os comandos SQL contidos no ficheiro de texto sql_filename
# este ficheiro é um ficheiro de comandos SQL a executar à razão de um por linha
# se «seguimento=True», então cada execução de um comando SQL é acompanhada por uma mensagem indicando o seu sucesso ou falha
# se «paragem»=True, a função pára ao primeiro erro encontrado; caso contrário, executa todos os comandos SQL
# se with_transaction=True, então qualquer erro anula o conjunto de comandos SQL executados anteriormente
# se with_transaction=False, então um erro não tem qualquer impacto nas ordens SQL executadas anteriormente
# a função devolve uma lista [erreur1, erreur2, ...]
# análise do ficheiro SQL
try:
# abertura do ficheiro em modo de leitura
file = open(sql_filename, "r")
# processamento
return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
except BaseException as erreur:
# é devolvida uma matriz de erros
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 das linhas 31-38 para conhecer o significado dos parâmetros;
- linhas 40-48: processa-se o ficheiro de texto [sql_filename];
- 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 constituída pela lista de 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):
# utiliza a ligação aberta [connexion]
# executa nesta ligação os comandos SQL contidos na lista [sql_commands]
# este ficheiro é um ficheiro de comandos SQL a executar à razão de um por linha
# se acompanhamento=True, então cada execução de um comando SQL é acompanhada por uma mensagem indicando o seu sucesso ou falha
# se «paragem»=True, a função pára ao primeiro erro encontrado; caso contrário, executa todos os comandos SQL
# se with_transaction=True, então qualquer erro anula o conjunto de comandos SQL executados anteriormente
# se with_transaction=False, então um erro não tem qualquer impacto nas ordens SQL executadas anteriormente
# a função devolve uma lista [erreur1, erreur2, ...]
# inicializações
curseur = None
connexion.autocommit = not with_transaction
erreurs = []
try:
# é necessário um cursor
curseur = connexion.cursor()
# execução dos sql_commands SQL contidos em sql_commands
# são executados um a um
for command in sql_commands:
# eliminam-se os espaços em branco no início e no fim do comando atual
command = command.strip()
# trata-se de um comando vazio ou de um comentário? Se sim, passa-se para o comando seguinte
if command == '' or command[0] == "#":
continue
# execução do comando atual
error = None
try:
curseur.execute(command)
except (InterfaceError, DatabaseError) as erreur:
error = erreur
# Ocorreu algum erro?
if error:
# mais um erro
msg = f"{command} : Erreur ({error})"
erreurs.append(msg)
# Acompanhamento no ecrã ou não?
if suivi:
print(msg)
# paramos?
if with_transaction or arrêt:
# Apresentar a lista de erros
return erreurs
else:
# sem erros
if suivi:
print(f"[{command}] : Exécution réussie")
# exibir o resultado do comando
afficher_infos(curseur)
# apresenta a tabela de erros
return erreurs
finally:
# fecha o cursor
if curseur:
curseur.close()
# confirma/cancela a transação, caso exista
if with_transaction:
if erreurs:
# anulação
connexion.rollback()
else:
# validação
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 das linhas 4 a 11 para conhecer o significado dos parâmetros;
- linha 2: a ligação recebida é uma ligação aberta a uma base de dados;
- linha 15: se se pretender que o conjunto de comandos da lista [sql_commands] seja executado no âmbito de uma transação, é necessário trabalhar no modo AUTOCOMMIT=False. Caso contrário, trabalhar-se-á no modo AUTOCOMMIT=True e, nesse caso, cada um dos comandos da lista [sqlCommands] será executado no âmbito de uma transação automática, não havendo uma transação global;
- linha 19: solicita-se um cursor para executar os diferentes comandos SQL;
- linhas 22-51: executam-se os comandos um a um;
- linhas 26-27: aceitam-se as linhas em branco, bem como os comentários, na lista de comandos SQL. Neste caso, o comando é simplesmente ignorado;
- linhas 30-33: execução da consulta atual;
- linhas 35-45: trata-se o caso de um eventual erro na execução da consulta atual;
- linhas 37-38: o erro é adicionado à tabela de erros;
- linhas 40-41: se tiver sido solicitado um acompanhamento, a mensagem de erro é apresentada;
- linhas 43-45: se o código chamador tiver solicitado uma interrupção após o primeiro erro ou se tiver solicitado a utilização de uma transação, então é necessário interromper. Devolve-se a tabela de erros;
- linhas 46-51: caso em que não tenha ocorrido qualquer erro na execução da consulta atual;
- linhas 48-49: se tiver sido solicitado um acompanhamento, exibe-se a consulta executada com a menção «bem-sucedida»;
- linhas 50-51: exibe-se o resultado da consulta executada. Voltaremos à função [afficher_infos] um pouco mais adiante;
- linhas 54-65: a cláusula [finally] é executada em todos os casos, quer tenha ocorrido uma exceção ou não;
- linhas 56-57: encerramento do cursor. Isto liberta os recursos que lhe foram atribuídos;
- linhas 59-65: trata-se do caso em que o código chamador solicitou que os comandos SQL fossem executados numa transação;
- linha 60: verifica-se se a lista [erreurs] está vazia, o que significa que não ocorreu nenhuma exceção. Nesse caso, a transação é validada (linha 65); caso contrário, é cancelada (linha 62);
A função [afficher_infos] apresenta o resultado de uma consulta:
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
print(type(curseur))
# exibe o resultado de um comando SQL
# tratava-se de um SELECT?
if curseur.description:
# o cursor tem uma descrição — portanto, executou um SELECT
# descrição[i] é a descrição da coluna n.º i do SELECT
# descriçãoQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX é o nome da coluna n.º i da instrução SELECT
# são apresentados os nomes dos campos
titre = ""
for i in range(len(curseur.description)):
titre += curseur.description[i][0] + ", "
# exibe-se a lista de campos sem a vírgula final
print(titre[0:len(titre) - 1])
# linha separadora
print("*" * (len(titre) - 1))
# linha atual da seleção
ligne = curseur.fetchone()
while ligne:
# é exibido
print(ligne)
# linha seguinte do menu de seleção
ligne = curseur.fetchone()
# linha separadora
print("*" * (len(titre) - 1))
else:
# o cursor não tem nenhum campo [description] - por isso, executou um comando SQL
# de atualização (inserir, eliminar, atualizar)
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 ordem SQL. Dependendo de essa ordem ser uma SELECT ou uma ordem de atualização INSERT, UPDATE, DELETE, o conteúdo do cursor não é o mesmo;
- linha 6: se o cursor tiver o campo [description], então executou um SELECT e o [description] descreve os campos solicitados no SELECT:
- description[i] descreve o campo n.º i solicitado pelo SELECT. Trata-se de uma lista;
- descrição[i][0] é o nome do campo n.º i;
- linhas 11-17: são apresentados os nomes dos campos solicitados pelo SELECT;
- linhas 18-24: processa-se o resultado do SELECT;
- linhas 20, 24: o resultado de um SELECT é processado sequencialmente. Este resultado é um conjunto de linhas. A linha atual é obtida através do [curseur.fetchone()] (linha 19). Obtém-se então um tuplo;
- linhas 27-30: se o cursor não tiver o campo [description], então executou uma ordem de atualização INSERT, UPDATE, DELETE. É então possível saber quantas linhas da tabela foram alteradas pela execução desta ordem;
- linha 30: [curseur.rowcount] é esse 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
# caminho absoluto da pasta do ficheiro de configuração
script_dir = os.path.dirname(os.path.abspath(__file__))
# configuração das pastas do syspath
absolute_dependencies = [
# pastas locais
f"{script_dir}/shared",
]
# definição do syspath
from myutils import set_syspath
set_syspath(absolute_dependencies)
# aplicar a configuração
return {
# ficheiro de comandos SQL
"commands_filename": f"{script_dir}/data/commandes.sql",
# credenciais de ligação à base de dados
"host": "localhost",
"database": "dbpersonnes",
"user": "admpersonnes",
"password": "nobody"
}
O script [mysql_04] é o seguinte:
# recuperar a configuração da aplicação
import config_04
config = config_04.configure()
# o syspath está configurado — já é possível efetuar as importações
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError
# ---------------------------------------------- main
# verificação da sintaxe da chamada
# argv[0] verdadeiro / falso
args = sys.argv
erreur = len(args) != 2
if not erreur:
with_transaction = args[1].lower()
erreur = with_transaction != "true" and with_transaction != "false"
# erro?
if erreur:
print(f"syntaxe : {args[0]} true / false")
sys.exit()
# cálculo de um texto
with_transaction = with_transaction == "true"
if with_transaction:
texte = "avec transaction"
else:
texte = "sans transaction"
# registos no ecrã
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")
# execução das ordens SQL do ficheiro
connexion = None
try:
# ligação à base de dados
connexion = connect(host=config['host'], user=config['user'], password=config['password'],
database=config['database'])
# execução do ficheiro de comandos SQL
erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
# exibição do erro
print(f"L'erreur fatale suivante s'est produite : {erreur}")
# encerramento
sys.exit()
finally:
# encerramento da ligação, caso tenha sido aberta
if connexion:
connexion.close()
# exibição do número de erros
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# exibição dos erros
for erreur in erreurs:
print(erreur)
Notas
- linhas 1-4: configuração do script;
- linha 8: importação do módulo [mysql_module] descrito anteriormente:
- 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 comandos SQL deve ser executado no âmbito de uma transação (true) ou não (false);
- linha 14: os parâmetros passados pelo utilizador ao 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 de um texto exibido na linha 33;
- linhas 39-44: executam-se os comandos do ficheiro [./data/commandes.sql];
- linhas 45-49: se ocorrer um erro na ligação (linha 40) ou um erro não tratado pelo script [execute_file_of_commands], exibe-se o erro e interrompe-se todo o processo;
- linhas 55-62: em caso de execução bem-sucedida, é apresentado o número de erros encontrados na execução dos comandos SQL;
Execução n.º 1
Primeiro, faz-se uma execução sem transação. Para tal, vai-se criar uma configuração de execução tal como foi feito no parágrafo |configuração de um contexto de execução|:
- em [1-4], cria-se uma configuração de execução em Python;
- [5]: nome da configuração de execução;
- [6]: caminho do script a executar;
- [7]: parâmetros do script;
- [8]: pasta de execução;
Esta configuração corresponde, portanto, à execução do ficheiro SQL com uma transação. Utilize o botão [Apply] para validar a configuração.
Da mesma forma, criamos a configuração de execução [mysql mysql-04 without_transaction]:
Esta configuração corresponde, portanto, a uma execução do ficheiro SQL sem transação. Utilize o botão [Apply] para validar a configuração.
Executamos primeiro a versão sem transação:
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/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: verifica-se que, após o erro, a execução das ordens SQL prosseguiu, uma vez que a execução foi realizada sem transação e com o parâmetro [arrêt=False]. Todas as ordens SQL foram, portanto, executadas. Deveríamos, assim, ter uma tabela [personnes] que reflita esta execução;
Verificação com 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: verifica-se que, após o erro, não há mais execução das ordens SQL, uma vez que a execução foi realizada numa transação e, ao ocorrer o primeiro erro, anulámos a transação e interrompemos a execução das ordens SQL. Isto significa que o resultado das ordens das linhas 9, 11 e 13 foi anulado. Por conseguinte, deveríamos ter uma tabela [personnes] vazia;
Verificações com a tabela phpMyAdmin:
- em [5], verifica-se que a tabela [personnes] e [2] estão vazias;
16.7. script [mysql_05]: utilização de consultas parametrizadas
O script [mysql_05] introduz o conceito de consultas parametrizadas:
# importações
from mysql.connector import connect, DatabaseError, InterfaceError
# identidade do utilizador
ID = "admpersonnes"
PWD = "nobody"
# máquina anfitriã do SGBD
HOST = "localhost"
# identidade da base de dados
BASE = "dbpersonnes"
# lista de pessoas (apelido, nome próprio, idade)
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))
# outra lista de pessoas
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))
# acesso ao SGBD
connexion = None
try:
# início de sessão
connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
# cursor
curseur = connexion.cursor()
# eliminação de registos existentes
curseur.execute("delete from personnes")
# inserções individuais com uma consulta preparada
for personne in personnes:
curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
# inserção em bloco de uma lista de pessoas
curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
# validação da transação
connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
# exibição de erro
print(f"L'erreur suivante s'est produite : {erreur}")
# anulação da transação
if connexion:
connexion.rollback()
finally:
# encerramento da sessão
if connexion:
connexion.close()
Notas
- linhas 12-21: criam-se duas listas de pessoas a incluir na base de dados [dbpersonnes];
- linha 27: ligação à base de dados;
- linha 31: eliminação do conteúdo da tabela [personnes];
- linhas 33-34: inserção de pessoas através de uma consulta parametrizada. Na linha 34, o primeiro parâmetro é a ordem SQL a executar. Esta está incompleta. Contém parâmetros [%s] que serão substituídos um a um e por ordem pelos valores da lista do segundo parâmetro;
- linha 36: inserção de pessoas, desta vez com uma única instrução [curseur.executemany]. O segundo parâmetro de [executemany] é, então, uma lista de listas;
A vantagem das consultas parametrizadas reside em dois aspetos:
- são executadas mais rapidamente do que as consultas «fixas», que têm de ser analisadas em cada execução. A consulta parametrizada [executemany] é analisada apenas uma vez. Posteriormente, é executada n vezes sem ser analisada novamente;
- os parâmetros introduzidos na consulta parametrizada são verificados. Se contiverem caracteres reservados, como o apóstrofo, por exemplo, estes são «protegidos» para que não interfiram na execução da ordem SQL. Foi para verificar este ponto que incluímos nomes e apelidos com apóstrofos na lista (linhas 16 e 21);
Os resultados obtidos em phpMyAdmin são os seguintes:
- note-se que as cadeias de caracteres com apóstrofo, um carácter reservado no SQL, foram inseridas corretamente. A consulta parametrizada «protegeu-as». Sem uma consulta parametrizada, teríamos de fazer esse trabalho nós próprios;