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 softwares:
- um servidor web Apache. Vamos usá-lo para escrever scripts web em Python;
- o SGBD MySQL;
- a linguagem de script PHP, que não utilizaremos;
- um servidor Redis que implementa um cache para aplicativos web. Não o utilizaremos;
O Laragon pode ser baixado (fevereiro de 2020) no seguinte endereço:
- 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 executar o [Laragon], é exibida 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] exibe a página da web [http://localhost];
- [4]: o botão [Database] permite gerenciar o SGBD e o MySQL com a ferramenta [phpMyAdmin]. É necessário instalá-la 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]. É nesse local que devem ser colocadas as aplicações web estáticas gerenciadas pelo servidor Apache do Laragon;
16.1.2. Criação de um banco de dados
Mostraremos agora como criar um banco de dados e um usuário 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 um banco de dados denominado [dbpersonnes] [11]. Vamos construir um banco de dados de pessoas;
- em [11], vamos gerenciar o banco de dados que acabamos de criar;
- A operação [Bases de données] envia uma solicitação da web para 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 instalamos anteriormente, o [5]. Este utilitário permite gerenciar os bancos de dados MySQL;
- por padrão, as credenciais de login do administrador do banco de dados são: root [13] sem senha [14];
- em [16], o banco de dados que criamos anteriormente;
- no momento, temos uma base de dados [dbpersonnes], [17], que está vazia, e [18];
Criamos um usuário [admpersonnes] com a senha [nobody], que terá todos os direitos sobre o banco de dados [dbpersonnes]:
- em [19], estamos posicionados no banco de dados [dbpersonnes];
- em [20], selecionamos a aba [Privileges];
- em [21-22], vemos que o usuário [root] possui todos os direitos sobre o banco de dados [dbpersonnes];
- em [23], cria-se um novo usuário;
- em [25-26], o usuário terá o identificador [admdbpersonnes];
- em [27-29], sua senha será [nobody];
- em [30], phpMyAdmin indica que a senha é muito fraca (fácil de ser quebrada). Em produção, é preferível gerar uma senha forte com [31];
- em [32], indica-se que o usuário [admdbpersonnes] deve ter todos os direitos sobre o banco de dados [dbpersonnes];
- em [33], validam-se as informações fornecidas;
- em [35], phpMyAdmin indica que o usuário foi criado;
- em [36], a ordem SQL que foi emitida no banco de dados;
- em [37], o usuário [admpersonnes] possui todos os direitos sobre o banco de dados [dbpersonnes];
Agora temos:
- um banco de dados MySQL [dbpersonnes];
- um usuário [admpersonnes/nobody] que possui todos os direitos sobre essa base de dados;
16.2. Instalação do pacote [mysql-connector-python]
Vamos escrever scripts em Python para explorar o banco de dados criado anteriormente com a seguinte arquitetura:
Um conector serve para isolar o código Python do SGBD em uso. Existem conectores para diferentes modelos do SGBD, e todos eles seguem a mesma interface. Assim, quando, no exemplo acima, substituímos 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, normalmente não é necessário modificar o script em Python. Na prática, a maioria dos SGBD possui um SQL proprietário:
- eles seguem o padrão SQL (Structured Query Language);
- mas a ampliam, pois ela não é suficiente, com extensões proprietárias da linguagem;
Além disso, é comum que, ao alterar um SGBD, seja necessário fazer modificações no SQL nos scripts.
Por padrão, o Python não oferece a possibilidade de gerenciar um banco de dados MySQL. Para isso, é necessário baixar um pacote. Existem vários disponíveis. Aqui, utilizaremos o pacote [mysql-connector-python], que é o conector oficial da Oracle, empresa proprietária do MySQL.
A instalação do pacote será feita em uma janela do PyCharm:
- a pasta em [2] não tem importância para o que se seguirá;
No terminal, digite o comando [pip search MySQL]:
- [pip] (Package Installer for Python) é a ferramenta de instalação de pacotes Python. A ferramenta [pip] se conecta 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 está em maiúsculas ou minúsculas) em 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>
Foram listados todos os módulos cujo nome ou descrição contém a palavra-chave MySQL. O que vamos usar (fevereiro de 2020) é o [mysql-connector-python], linha 17. Para instalá-lo, digite 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 diversos pacotes associados ao pacote [mysql-connector-python];
Para saber quais pacotes estão 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: o pacote [mysql-connector-python] está presente;
Para saber como usar o pacote [mysql-connector-python] para gerenciar um banco de dados MySQL, acesse o site do pacote |https://dev.mysql.com/doc/connector-python/en/|. A seguir, apresentamos uma série de exemplos.
16.3. script [mysql_01]: conexão com um banco de dados MySQL - 1
O script [mysql_01] apresenta a primeira etapa do uso de um banco de dados. Ele nos permitirá verificar se conseguimos nos conectar ao banco de dados [dbpersonnes] criado anteriormente.
# importação do módulo mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
# conexão a um banco de dados MySql [dbpersonnes]
# a identidade do usuário é (admpersonnes, nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# vamos lá
connexion = None
try:
print("Connexion au SGBD MySQL en cours...")
# conectando
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:
# exibindo o erro
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# fechamos a conexão, caso ela 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 usuário que vai se conectar;
- linha 8: a máquina que hospeda o banco de dados. De fato, o conector MySQL permite trabalhar com um banco de dados remoto;
- linha 9: o nome do banco de dados ao qual se deseja conectar;
- linhas 11-26: o script conectará (linha 16) o usuário [admpersonnes / nobody] ao banco de dados [dbpersonnes];
- linhas 20-26: a conexão pode falhar. Por isso, ela é feita dentro de um bloco try / except / finally;
- linha 16: o método connect do módulo [mysq.connector] aceita diferentes parâmetros nomeados:
- user: usuário proprietário da conexão [admpersonnes];
- password: senha do usuário [nobody];
- host: máquina do SGBD, MySQL, [localhost];
- database: o banco de dados ao qual se conecta. Opcional.
- linha 20: se uma exceção for lançada, ela será do tipo [DatabaseError] ou [InterfaceError];
- linhas 23-26: na cláusula [finally], encerra-se a conexão;
Resultados
16.4. script [mysql_02]: conexão a um banco de dados MySQL - 2
Neste novo script, a conexão com o banco de dados é isolada em uma 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):
# 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
# dados de login
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# login de um usuário existente
try:
connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# é exibido o erro
print(erreur)
# login de um usuário inexistente
try:
connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# exibe o erro
print(erreur)
Notas:
- linhas 6-19: uma função [connexion] que tenta conectar e, em seguida, desconectar um usuário do banco de dados [dbpersonnes]. Exibe o resultado;
- linhas 29-41: programa principal – chama duas vezes o método connexion e exibe eventuais exceções;
Resultados
16.5. script [mysql_03]: criação de uma tabela MySQL
Agora que sabemos como criar uma conexão com um SGBD MySQL, começamos a emitir comandos SQL nessa conexão. Para isso, vamos nos conectar ao banco de dados criado [dbpersonnes] e usar a conexão para criar uma tabela nesse banco 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 conexão
curseur = None
try:
# solicita um cursor
curseur = connexion.cursor()
# executa a consulta de atualização na conexão
curseur.execute(update)
finally:
# fecha o cursor, caso ele tenha sido obtido
if curseur:
curseur.close()
# ---------------------------------------------- main
# credenciais da conexão
# identidade do usuário
ID = "admpersonnes"
PWD = "nobody"
# o servidor do SGBD
HOST = "localhost"
# identidade do banco de dados
DATABASE = "dbpersonnes"
# vamos passo a passo
try:
# conexão
connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
# modo AUTOCOMMIT
connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
# exibindo o erro
print(f"L'erreur suivante s'est produite : {erreur}")
# saindo
sys.exit()
# exclusão da tabela “pessoas”, caso exista
# se ela não existir, ocorrerá um erro — ele é 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 em uma conexão aberta;
- linha 14: as operações SQL na conexão são realizadas por meio de um objeto específico chamado cursor;
- linha 14: obtenção de um cursor;
- linha 16: execução da consulta SQL;
- linhas 17-20: haja ou não erro, o cursor é fechado. Isso libera os recursos a ele associados. Se houver exceção, ela não é tratada aqui. Ela será repassada para o código chamador;
- linhas 33-43: criação de uma conexão com o banco de dados;
- linha 38: o modo AUTOCOMMIT=True para uma conexão significa que cada execução de uma consulta ocorre em uma transação automática. O modo padrão é AUTOCOMMIT=False, em que cabe ao desenvolvedor a responsabilidade de gerenciar as transações. Uma transação é um mecanismo que abrange 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, as consultas de 1 a i serão “revertidas” para que o banco de dados retorne ao estado em que se encontrava antes da execução da consulta 1;
- aqui, há duas consultas SQL (linhas 49, 58). Cada uma delas será executada em uma transação. O fato de a segunda falhar não tem nenhum impacto sobre a primeira;
- linhas 45-51: a ordem SQL [drop table personnes] é executada. Ela exclui a tabela denominada [personnes]. Se esta não existir, pode ser gerado um erro. Este é ignorado (linha 51);
- linhas 53-55: a instrução de criação da 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. Ele será único para cada pessoa. Essa será a chave primária (PRIMARY KEY). Isso significa que, na tabela, essa coluna não apresenta o mesmo valor duas vezes e pode ser usada para identificar uma pessoa;
- [nom]: uma sequência de, no máximo, 30 caracteres;
- [prenom]: uma sequência de, no máximo, 30 caracteres;
- [age]: um número inteiro;
- o atributo [NOT NULL] para cada uma dessas colunas significa que, em uma linha da tabela, nenhuma das três colunas pode estar vazia;
- o parâmetro [unique(nom,prenom)] é chamado de restrição. Aqui, a restrição sobre as linhas é que o tuplo (sobrenome, nome) da linha deve ser único na tabela. Isso significa que é possível identificar de forma única na tabela um indivíduo cujo sobrenome e nome sejam conhecidos;
- linhas 56-60: execução da ordem SQL;
- linhas 61-63: tratamento de uma eventual exceção;
- linhas 64-66: desconectamos do banco de dados;
Resultados
Verificação com [phpMyAdmin]:
- O banco de dados [dbpersonnes] [1] possui uma tabela [personnes] [2], que tem a estrutura [3-4], a chave primária [5] e a restrição de exclusividade [6];
16.6. script [mysql_04]: execução de um arquivo de comandos SQL
Após termos criado anteriormente a tabela [personnes], agora a preenchemos e a processamos por meio das ordens SQL.
Desejamos executar as ordens SQL a partir de um arquivo de texto:
O conteúdo do arquivo [commandes.sql] é o seguinte:
# exclusã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)
# exibiçã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)
# exibição da tabela
select prenom, nom, age from personnes
# lista de pessoas em ordem alfabética pelos sobrenomes e, em caso de sobrenomes iguais, em ordem alfabética pelos nomes próprios
select nom,prenom from personnes order by nom asc, prenom desc
# lista de pessoas com idade no intervalo [20,40], em ordem decrescente de idade
# e, em caso de idades iguais, por ordem alfabética dos sobrenomes e, em caso de sobrenomes 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 idade dela
update personnes set age=47 where nom='Bruneau'
# lista das pessoas com o sobrenome Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# exclusão da Sra. Bruneau
delete from personnes where nom='Bruneau'
# lista de pessoas com o sobrenome Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
Primeiramente, definimos funções que instalamos em um módulo para poder reutilizá-las:
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 conexão aberta [connexion]
# executa nessa conexão os comandos SQL contidos na lista [sql_commands]
# este arquivo contém comandos SQL a serem executados, um por linha
# se acompanhamento=True, então cada execução de um comando SQL é acompanhada por uma mensagem indicando se foi bem-sucedida ou falhou
# se “parar”=True, a função para ao encontrar o primeiro erro; caso contrário, ela executa todos os comandos SQL
# se with_transaction=True, então qualquer erro cancela todas as ordens SQL executadas anteriormente
# se with_transaction=False, então um erro não tem nenhum impacto sobre os comandos SQL executados anteriormente
# a função retorna 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 conexão aberta [connexion]
# executa nessa conexão os comandos SQL contidos no arquivo de texto sql_filename
# esse arquivo é um arquivo de comandos SQL a ser executado, um por linha
# se acompanhamento=True, então cada execução de um comando SQL é acompanhada por uma mensagem indicando se foi bem-sucedida ou falhou
# se “parar”=True, a função para ao encontrar o primeiro erro; caso contrário, ela executa todos os comandos SQL
# se with_transaction=True, então qualquer erro cancela todas as ordens SQL executadas anteriormente
# se with_transaction=False, então um erro não tem nenhum impacto sobre os comandos SQL executados anteriormente
# a função retorna uma lista [erreur1, erreur2, ...]
# processamento do arquivo SQL
try:
# abertura do arquivo para leitura
file = open(sql_filename, "r")
# processamento
return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
except BaseException as erreur:
# retorna uma matriz de erros
return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
Observações:
- linha 29: a função [execute_file_of_commands] executa os comandos SQL contidos no arquivo de texto denominado [sql_filename]:
- consulte os comentários nas linhas 31 a 38 para conhecer o significado dos parâmetros;
- linhas 40 a 48: o arquivo de texto [sql_filename] é processado;
- linha 43: abertura do arquivo;
- linha 34: execução da função [execute_list_of_commands], que executa os comandos SQL que lhe são passados em uma lista. Essa lista é constituída, neste caso, pela lista de todas as linhas do arquivo 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 conexão aberta [connexion]
# executa nesta conexão os comandos SQL contidos na lista [sql_commands]
# este arquivo é um arquivo de comandos SQL a ser executado, um por linha
# se acompanhamento=True, então cada execução de um comando SQL é acompanhada por uma mensagem indicando se foi bem-sucedida ou falhou
# se “parar”=True, a função para ao encontrar o primeiro erro; caso contrário, ela executa todos os comandos SQL
# se with_transaction=True, então qualquer erro cancela todas as ordens SQL executadas anteriormente
# se with_transaction=False, então um erro não tem nenhum impacto sobre os comandos SQL executados anteriormente
# a função retorna 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
# elas são executadas uma a uma
for command in sql_commands:
# eliminam-se os espaços em branco no início e no final 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)
# exibição na tela ou não?
if suivi:
print(msg)
# interromper?
if with_transaction or arrêt:
# exibimos a lista de erros
return erreurs
else:
# sem erro
if suivi:
print(f"[{command}] : Exécution réussie")
# exibe-se o resultado do comando
afficher_infos(curseur)
# retorna a tabela de erros
return erreurs
finally:
# fecha o cursor
if curseur:
curseur.close()
# confirma/cancela a transação, se houver
if with_transaction:
if erreurs:
# cancelamento
connexion.rollback()
else:
# confirmaçã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 conexão recebida é uma conexão aberta com um banco de dados;
- linha 15: se desejar que todas as ordens da lista [sql_commands] sejam executadas dentro de uma transação, é necessário trabalhar no modo AUTOCOMMIT=False. Caso contrário, trabalharemos no modo AUTOCOMMIT=True e, nesse caso, cada um dos comandos da lista [sqlCommands] será executado dentro de uma transação automática, sem que haja uma transação global;
- linha 19: solicita-se um cursor para executar os diversos comandos SQL;
- linhas 22-51: os comandos são executados um por um;
- linhas 26-27: aceitam-se as linhas em branco e os comentários na lista de comandos SQL. Nesse caso, o comando é simplesmente ignorado;
- linhas 30-33: execução da consulta atual;
- linhas 35-45: trata-se o caso de um possível erro na execução da consulta atual;
- linhas 37-38: o erro é adicionado à tabela de erros;
- linhas 40-41: se um acompanhamento tiver sido solicitado, a mensagem de erro é exibida;
- linhas 43-45: se o código chamador solicitou uma interrupção após o primeiro erro ou se solicitou o uso de uma transação, então é necessário interromper. Retorna-se a tabela de erros;
- linhas 46-51: caso não tenha ocorrido erro na execução da consulta atual;
- linhas 48-49: se um acompanhamento tiver sido solicitado, exibe-se a consulta executada com a indicaçã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, independentemente de ter ocorrido uma exceção ou não;
- linhas 56-57: fechamento do cursor. Isso libera os recursos alocados a ele;
- linhas 59-65: trata-se do caso em que o código chamador solicitou que os comandos SQL fossem executados em uma 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] exibe o resultado de uma consulta:
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
print(type(curseur))
# exibe o resultado de um comando SQL
# era 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 consulta SELECT
# são exibidos 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)
# próxima linha da lista de seleção
ligne = curseur.fetchone()
# linha separadora
print("*" * (len(titre) - 1))
else:
# o cursor não possui campo [description] — portanto, ele executou um comando SQL
# de atualização (inserir, excluir, 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 ele executou um SELECT, e [description] descreve os campos solicitados no SELECT:
- description[i] descreve o campo nº i solicitado pelo SELECT. Trata-se de uma lista;
- description[i][0] é o nome do campo nº i;
- linhas 11-17: exibe-se o nome dos campos solicitados pelo SELECT;
- linhas 18-24: processa-se o resultado do SELECT;
- linhas 20, 24: o resultado de um SELECT é processado sequencialmente. Esse resultado é um conjunto de linhas. A linha atual é obtida por [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 ele executou uma ordem de atualização INSERT, UPDATE, DELETE. Assim, é possível saber quantas linhas da tabela foram modificadas pela execução dessa ordem;
- linha 30: [curseur.rowcount] é esse número;
O script principal [mysql-04] utiliza o módulo [mysql_module] que acabamos de descrever:
O arquivo [config_04] configura o contexto de execução do script [mysql_04]:
def configure():
import os
# caminho absoluto da pasta do arquivo 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)
# aplicamos a configuração
return {
# arquivo de comandos SQL
"commands_filename": f"{script_dir}/data/commandes.sql",
# credenciais de conexão com o banco de dados
"host": "localhost",
"database": "dbpersonnes",
"user": "admpersonnes",
"password": "nobody"
}
O script [mysql_04] é o seguinte:
# recuperando a configuração do aplicativo
import config_04
config = config_04.configure()
# o syspath está configurado — já é possível fazer 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"
# registros na tela
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")
# execução das ordens SQL do arquivo
connexion = None
try:
# conexão ao banco de dados
connexion = connect(host=config['host'], user=config['user'], password=config['password'],
database=config['database'])
# execução do arquivo 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:
# fechamento da conexã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]. Esse parâmetro indica se o arquivo de comandos SQL deve ser executado dentro de uma transação (true) ou não (false);
- linha 14: os parâmetros passados pelo usuário ao script estão 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 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 arquivo [./data/commandes.sql];
- linhas 45-49: se ocorrer um erro na conexã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, exibe-se o número de erros encontrados na execução dos comandos SQL;
Execução nº 1
Primeiro, realizamos uma execução sem transação. Para isso, vamos criar uma configuração de execução, conforme feito no parágrafo |configuração de um contexto de execução|:
- em [1-4], criamos uma configuração de execução em Python;
- [5]: nome da configuração de execução;
- [6]: caminho do script a ser executado;
- [7]: parâmetros do script;
- [8]: pasta de execução;
Essa configuração corresponde, portanto, à execução do arquivo SQL com uma transação. Use o botão [Apply] para confirmar a configuração.
Da mesma forma, criamos a configuração de execução [mysql mysql-04 without_transaction]:
Essa configuração corresponde, portanto, à execução do arquivo SQL sem transação. Use o botão [Apply] para validar a configuração.
Primeiro, executamos 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
Observações:
- linha 19: observa-se que, após o erro, a execução das ordens SQL continuou, pois a execução ocorreu sem transação e com o parâmetro [arrêt=False]. Todas as ordens SQL foram, portanto, executadas. Deveríamos, portanto, ter uma tabela [personnes] refletindo essa execução;
Verificação com phpMyAdmin:
Execução nº 2
Agora executamos 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
Observações:
- linha 19: percebe-se que, após o erro, não há mais execução das ordens SQL, pois a execução ocorreu em uma transação e, ao encontrar o primeiro erro, revertemos a transação e interrompemos a execução das ordens SQL. Isso significa que o resultado das ordens das linhas 9, 11 e 13 foi revertido. Portanto, deveríamos ter uma tabela [personnes] vazia;
Verificações com phpMyAdmin:
- em [5], verifica-se que as tabelas [personnes] e [2] estão vazias;
16.7. script [mysql_05]: uso de consultas parametrizadas
O script [mysql_05] introduz o conceito de consultas parametrizadas:
# importações
from mysql.connector import connect, DatabaseError, InterfaceError
# identidade do usuário
ID = "admpersonnes"
PWD = "nobody"
# máquina host do SGBD
HOST = "localhost"
# identidade do banco de dados
BASE = "dbpersonnes"
# lista de pessoas (sobrenome, nome, 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:
# login
connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
# cursor
curseur = connexion.cursor()
# exclusão de registros 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 lote de uma lista de pessoas
curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
# confirmação da transação
connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
# exibição de erro
print(f"L'erreur suivante s'est produite : {erreur}")
# cancelamento da transação
if connexion:
connexion.rollback()
finally:
# encerramento da conexão
if connexion:
connexion.close()
Notas
- linhas 12-21: criam-se duas listas de pessoas a serem incluídas no banco de dados [dbpersonnes];
- linha 27: conexão com o banco de dados;
- linha 31: exclusão do conteúdo da tabela [personnes];
- linhas 33-34: inserção de pessoas por meio de uma consulta configurada. Na linha 34, o primeiro parâmetro é a ordem SQL a ser executada. Ela está incompleta. Contém parâmetros [%s] que serão substituídos, um por um e na 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] é, portanto, uma lista de listas;
A vantagem das consultas parametrizadas reside em dois pontos:
- elas são executadas mais rapidamente do que consultas “fixas”, que precisam ser analisadas a cada execução. A consulta parametrizada [executemany] é analisada apenas uma vez. Depois, ela é executada n vezes sem ser analisada novamente;
- os parâmetros inseridos 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 esse ponto que incluímos nomes e sobrenomes 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 com apóstrofo — um caractere reservado no SQL — foram inseridas corretamente. A consulta parametrizada as “protegeu”. Sem a consulta parametrizada, teríamos que fazer esse trabalho manualmente;