16. Uso de SGBD MySQL
16.1. Instalación de SGBD MySQL
Para disponer de SGBD MySQL, vamos a instalar el software Laragon.
16.1.1. Instalación de Laragon
Laragon es un paquete que reúne varios programas:
- un servidor web Apache. Lo utilizaremos para escribir scripts web en Python;
- el SGBD MySQL;
- el lenguaje de scripting PHP, que no utilizaremos;
- un servidor Redis que implementa una caché para aplicaciones web. No lo utilizaremos;
Laragon se puede descargar (febrero de 2020) en la siguiente dirección:
- La instalación [1-5] da lugar al siguiente árbol de directorios:
- en [6], la carpeta de instalación de PHP (no utilizada en este documento);
Al iniciar [Laragon], aparece la siguiente ventana:
- [1]: el menú principal de Laragon;
- [2]: el botón [Start All] inicia el servidor web Apache y el SGBD MySQL;
- [3]: el botón [WEB] muestra la página web [http://localhost];
- [4]: el botón [Database] permite gestionar el SGBD MySQL con la herramienta [phpMyAdmin]. Es necesario instalarlo previamente;
- [5]: el botón [Terminal] abre un terminal de comandos;
- [6]: el botón [Root] abre un explorador de Windows situado en la carpeta [<laragon>/www], que es la raíz del sitio web [http://localhost]. Ahí es donde hay que colocar las aplicaciones web estáticas gestionadas por el servidor Apache de Laragon;
16.1.2. Creación de una base de datos
A continuación, mostramos cómo crear una base de datos y un usuario MySQL con la herramienta Laragon.
- Una vez iniciado, Laragon [1] se puede administrar desde un menú [2];
- en [3-5], se instala la herramienta [phpMyAdmin] de administración de MySQL si aún no se ha instalado;
- en [6], se inicia el servidor web Apache, así como SGBD y MySQL;
- en [7], se inicia el servidor Apache;
- en [8], se inicia SGBD MySQL;
- En [8-10], se crea una base de datos a la que se le da el nombre de [dbpersonnes] [11]. Vamos a crear una base de datos de personas;
- en [11], vamos a gestionar la base de datos que acabamos de crear;
- la operación [Bases de données] envía una solicitud web a URL, [http://localhost/phpmyadmin] y [12]. Es el servidor web Apache de Laragon el que responde. URL [http://localhost/phpmyadmin] es URL de la utilidad [phpMyAdmin] que hemos instalado anteriormente [5]. Esta utilidad permite gestionar las bases de datos MySQL;
- por defecto, las credenciales de conexión del administrador de la base de datos son: root [13] sin contraseña [14];
- en [16], la base de datos que hemos creado anteriormente;
- por ahora tenemos una base [dbpersonnes] [17] que está vacía [18];
Creamos un usuario [admpersonnes] con la contraseña [nobody] que tendrá todos los derechos sobre la base de datos [dbpersonnes]:
- en [19], nos situamos en la base [dbpersonnes];
- en [20], seleccionamos la pestaña [Privileges];
- en [21-22], vemos que el usuario [root] tiene todos los derechos sobre la base [dbpersonnes];
- en [23], se crea un nuevo usuario;
- en [25-26], el usuario tendrá el identificador [admdbpersonnes];
- en [27-29], su contraseña será [nobody];
- en [30], phpMyAdmin indica que la contraseña es muy débil (fácil de descifrar). En producción, es preferible generar una contraseña segura con [31];
- en [32], se indica que el usuario [admdbpersonnes] debe tener todos los derechos sobre la base [dbpersonnes];
- en [33], se valida la información proporcionada;
- en [35], phpMyAdmin indica que se ha creado el usuario;
- en [36], la orden SQL que se ha emitido en la base;
- en [37], el usuario [admpersonnes] tiene todos los derechos sobre la base de datos [dbpersonnes];
Ahora tenemos:
- una base de datos MySQL [dbpersonnes];
- un usuario [admpersonnes/nobody] que tiene todos los derechos sobre esta base de datos;
16.2. Instalación del paquete [mysql-connector-python]
Vamos a escribir scripts en Python para explotar la base de datos creada anteriormente con la siguiente arquitectura:
Un conector sirve para aislar el código Python del SGBD utilizado. Existen conectores para diferentes SGBD y estos siguen la misma interfaz. Por lo tanto, cuando en el ejemplo anterior se sustituye el SGBD MySQL por el SGBD PostgreSQL, la arquitectura queda así:
Dado que todos los conectores de SGBD respetan la misma interfaz, normalmente no es necesario modificar el script de Python. En la práctica, la mayoría de los SGBD tienen un SQL propietario:
- cumplen con el estándar SQL (Structured Query Language);
- pero la amplían, ya que no es suficiente, con extensiones del lenguaje propias;
Por lo tanto, es frecuente que, al cambiar de SGBD, haya que realizar modificaciones de SQL en los scripts.
De forma nativa, Python no ofrece la posibilidad de gestionar una base de datos MySQL. Para ello, es necesario descargar un paquete. Existen varios. Aquí utilizaremos el paquete [mysql-connector-python], que es el conector oficial de Oracle, la empresa propietaria de MySQL.
La instalación del paquete se realizará en una ventana de Pycharm:
- la carpeta en [2] no tiene importancia para lo que viene a continuación;
En el terminal, escribimos el comando [pip search MySQL]:
- [pip] (Package Installer for Python) es la herramienta de instalación de paquetes de Python. La herramienta [pip] se conecta al repositorio que contiene los paquetes de Python;
- [search MySQL]: solicita la lista de paquetes que contengan el término [MySQL] (no importa si se escribe en mayúsculas o minúsculas) en su nombre;
Los resultados del comando son los siguientes:
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>
Se han enumerado todos los módulos cuyo nombre o descripción contienen la palabra clave MySQL. El que utilizaremos (feb. 2020) es [mysql-connector-python], línea 17. Para instalarlo, escribimos en el terminal el 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
- línea 1: el comando option [install -U] (U=actualización) solicita el version más reciente de los distintos paquetes asociados al paquete [mysql-connector-python];
Para conocer los paquetes instalados en el entorno Python de nuestra máquina, escribimos el 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
…
- línea 13: efectivamente, tenemos el paquete [mysql-connector-python];
Para saber cómo utilizar el paquete [mysql-connector-python] para gestionar una base de datos MySQL, visitaremos la página web del paquete |https://dev.mysql.com/doc/connector-python/en/|. A continuación se presentan una serie de ejemplos.
16.3. Script [mysql_01]: conexión a una base de datos MySQL - 1
El script [mysql_01] presenta el primer paso para utilizar una base de datos. Nos permitirá comprobar que podemos conectarnos a la base [dbpersonnes] creada anteriormente.
# importación del módulo mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
# conexión a una base de datos MySql [dbpersonnes]
# la identidad del usuario es (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# Allá vamos
connexion = None
try:
print("Connexion au SGBD MySQL en cours...")
# conexión
connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
# seguimiento
print(
f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# se cierra la conexión si se ha abierto
if connexion:
connexion.close()
Notas
- línea 2: se importan ciertas funciones y clases del módulo [mysql.connector];
- líneas 6-7: las credenciales del usuario que se va a conectar;
- línea 8: el servidor que aloja la base de datos. De hecho, el conector MySQL permite trabajar con una base de datos remota;
- línea 9: el nombre de la base de datos a la que queremos conectarnos;
- líneas 11-26: el script conectará (línea 16) al usuario [admpersonnes / nobody] a la base de datos [dbpersonnes];
- líneas 20-26: la conexión puede fallar. Por eso se realiza en un try / except / finally;
- línea 16: el método connect del módulo [mysq.connector] admite diferentes parámetros con nombre:
- user: usuario propietario de la conexión [admpersonnes];
- password: contraseña del usuario [nobody];
- host: máquina de SGBD MySQL [localhost];
- database: la base de datos a la que se conecta. Opcional.
- línea 20: si se lanza una excepción, es de tipo [DatabaseError] o [InterfaceError];
- líneas 23-26: en la cláusula [finally], se cierra la conexión;
Resultados
16.4. script [mysql_02]: conexión a una base de datos MySQL - 2
En este nuevo script, la conexión a la base de datos se aísla en una función:
# importación del módulo mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect
# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
# se conecta y luego se desconecta (login, contraseña) de la base de datos [database] del servidor [host]
# lanza la excepción DatabaseError si hay algún problema
connexion = None
try:
# conexión
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:
# se cierra la conexión si se ha abierto
if connexion:
connexion.close()
print("Déconnexion réussie\n")
# ---------------------------------------------- main
# datos de conexión
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# conexión de un usuario existente
try:
connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(erreur)
# inicio de sesión de un usuario inexistente
try:
connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(erreur)
Notas:
- líneas 6-19: una función [connexion] que intenta conectar y luego desconectar a un usuario de la base de datos [dbpersonnes]. Muestra el resultado;
- líneas 29-41: programa principal: llama dos veces al método connexion y muestra las posibles excepciones;
Resultados
16.5. script [mysql_03]: creación de una tabla MySQL
Ahora que sabemos cómo crear una conexión con un SGBD MySQL, comenzamos a emitir órdenes SQL en esta conexión. Para ello, nos conectaremos a la base de datos creada [dbpersonnes] y utilizaremos la conexión para crear una tabla en la base de datos.
# importaciones
import sys
from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection
# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
# ejecuta una consulta de actualización en la conexión
curseur = None
try:
# se solicita un cursor
curseur = connexion.cursor()
# ejecuta la consulta de actualización en la conexión
curseur.execute(update)
finally:
# cierre del cursor si se ha obtenido
if curseur:
curseur.close()
# ---------------------------------------------- main
# credenciales de la conexión
# la identidad del usuario
ID = "admpersonnes"
PWD = "nobody"
# la máquina host del SGBD
HOST = "localhost"
# identidad de la base
DATABASE = "dbpersonnes"
# vamos paso a paso
try:
# conexión
connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
# modo AUTOCOMMIT
connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(f"L'erreur suivante s'est produite : {erreur}")
# salimos
sys.exit()
# eliminación de la tabla personas si existe
# si no existe, se producirá un error; se ignora
requête = "drop table personnes"
try:
execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
pass
# creación de la tabla personas
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:
# ejecución de la consulta
execute_sql(connexion, requête)
# visualización
print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# se desconecta
connexion.close()
Notas:
- línea 9: la función execute_sql ejecuta una consulta SQL en una conexión abierta;
- línea 14: las operaciones SQL en la conexión se realizan a través de un objeto específico denominado cursor;
- línea 14: obtención de un cursor;
- línea 16: ejecución de la consulta SQL;
- líneas 17-20: independientemente de si hay un error o no, el cursor se cierra. Esto libera los recursos asociados a él. Si se produce una excepción, esta no se gestiona aquí. Se transmitirá al código que la invocó;
- líneas 33-43: creación de una conexión con la base de datos;
- línea 38: el modo AUTOCOMMIT=True para una conexión significa que cada ejecución de una consulta se realiza en una transacción automática. El modo por defecto es AUTOCOMMIT=False, en el que es el desarrollador quien tiene la responsabilidad de gestionar las transacciones. Una transacción es un mecanismo que engloba la ejecución de varias consultas, de la 1 a la n. O bien todas tienen éxito, o bien ninguna lo tiene. Así, si las consultas de la 1 a la i tienen éxito, pero la consulta i+1 falla, entonces las consultas de la 1 a la i se «desharán» para que la base de datos recupere el estado que tenía antes de la ejecución de la consulta 1;
- aquí hay dos consultas SQL (líneas 49, 58). Cada una de ellas se ejecutará en una transacción. El hecho de que la segunda falle no tiene ningún impacto en la primera;
- líneas 45-51: se ejecuta la orden SQL [drop table personnes]. Esta elimina la tabla denominada [personnes]. Si esta no existe, puede producirse un error. Este se ignora (línea 51);
- líneas 53-55: la orden de creación de la tabla [personnes]. Una tabla puede verse como un conjunto de filas y columnas. La orden de creación especifica el nombre de las columnas:
- [id]: un identificador entero. Será único para cada persona. Será la clave primaria (PRIMARY KEY). Esto significa que, en la tabla, esta columna no tiene dos veces el mismo valor y que puede utilizarse para identificar a una persona;
- [nom]: una cadena de hasta 30 caracteres;
- [prenom]: una cadena de hasta 30 caracteres;
- [age]: un número entero;
- el atributo [NOT NULL] para cada una de estas columnas significa que, en una fila de la tabla, ninguna de las tres columnas puede estar vacía;
- el parámetro [unique(nom,prenom)] se denomina restricción. En este caso, la restricción sobre las filas es que la tupla (apellido, nombre) de la fila debe ser única en la tabla. Esto significa que se puede identificar de forma única en la tabla a una persona de la que se conocen el apellido y el nombre;
- líneas 56-60: ejecución de la orden SQL;
- líneas 61-63: gestión de posibles excepciones;
- líneas 64-66: se desconecta de la base de datos;
Resultados
Verificación con [phpMyAdmin]:
- la base de datos [dbpersonnes] [1] tiene una tabla [personnes] [2] que tiene la estructura [3-4], la clave primaria [5] y la restricción de unicidad [6];
16.6. script [mysql_04]: ejecución de un archivo de comandos SQL
Tras haber creado previamente la tabla [personnes], ahora la rellenamos y la procesamos mediante las órdenes SQL.
Queremos ejecutar las órdenes SQL de un archivo de texto:
El contenido del archivo [commandes.sql] es el siguiente:
# 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'
En primer lugar, definimos unas funciones que instalamos en un módulo para poder reutilizarlas:
El script [mysql_module] es el siguiente:
# importaciones
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
# muestra el resultado de un command sql
…
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# utiliza la conexión abierta [connexion]
# ejecuta en esta conexión los comandos SQL contenidos en la lista [sql_commands]
# este archivo es un archivo de comandos SQL que se ejecutarán a razón de uno por línea
# si seguimiento=True, entonces cada ejecución de un comando SQL se muestra en pantalla indicando si ha tenido éxito o ha fallado
# si stop=True, la función se detiene ante el primer error encontrado; de lo contrario, ejecuta todos los comandos sql
# si with_transaction=True, entonces cualquier error anula todas las órdenes SQL ejecutadas anteriormente
# si with_transaction=False, entonces un error no tiene ningún impacto en los comandos SQL ejecutados anteriormente
# la función devuelve una 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 la conexión abierta [connexion]
# ejecuta en esta conexión los comandos SQL contenidos en el archivo de texto sql_filename
# este archivo es un archivo de comandos SQL que se ejecutan a razón de uno por línea
# si seguimiento=True, entonces cada ejecución de un comando SQL se muestra en pantalla indicando si ha tenido éxito o ha fallado
# si stop=True, la función se detiene ante el primer error encontrado; de lo contrario, ejecuta todos los comandos sql
# si with_transaction=True, entonces cualquier error anula todas las órdenes SQL ejecutadas anteriormente
# si with_transaction=False, entonces un error no tiene ningún impacto en los comandos SQL ejecutados anteriormente
# la función devuelve una lista [erreur1, erreur2, ...]
# procesamiento del archivo SQL
try:
# apertura del archivo en modo lectura
file = open(sql_filename, "r")
# procesamiento
return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
except BaseException as erreur:
# se devuelve una tabla de errores
return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
Notas:
- línea 29: la función [execute_file_of_commands] ejecuta las órdenes SQL contenidas en el archivo de texto denominado [sql_filename]:
- consulte los comentarios de las líneas 31-38 para conocer el significado de los parámetros;
- líneas 40-48: se procesa el archivo de texto [sql_filename];
- línea 43: apertura del archivo;
- línea 34: ejecución de la función [execute_list_of_commands], que ejecuta los comandos SQL que se le pasan en una lista. Esta lista está formada aquí por la lista de todas las líneas del archivo de texto [file.readlines()] (línea 45);
La función [execute_list_of_commands] es la siguiente:
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# utiliza la conexión abierta [connexion]
# ejecuta en esta conexión los comandos SQL contenidos en la lista [sql_commands]
# este archivo es un archivo de comandos SQL que se ejecutarán a razón de uno por línea
# si seguimiento=True, entonces cada ejecución de un comando SQL se muestra en pantalla indicando si ha tenido éxito o ha fallado
# si stop=True, la función se detiene ante el primer error encontrado; de lo contrario, ejecuta todos los comandos sql
# si with_transaction=True, entonces cualquier error anula todas las órdenes SQL ejecutadas anteriormente
# si with_transaction=False, entonces un error no tiene ningún impacto en los comandos SQL ejecutados anteriormente
# la función devuelve una lista [erreur1, erreur2, ...]
# inicializaciones
curseur = None
connexion.autocommit = not with_transaction
erreurs = []
try:
# se solicita un cursor
curseur = connexion.cursor()
# ejecución de sql_commands SQL contenidas en sql_commands
# se ejecutan una a una
for command in sql_commands:
# se eliminan los espacios al principio y al final del comando actual
command = command.strip()
# ¿Tenemos un comando vacío o un comentario? Si es así, pasamos al siguiente comando
if command == '' or command[0] == "#":
continue
# ejecución del comando actual
error = None
try:
curseur.execute(command)
except (InterfaceError, DatabaseError) as erreur:
error = erreur
# ¿Ha habido algún error?
if error:
# otro error
msg = f"{command} : Erreur ({error})"
erreurs.append(msg)
# ¿Seguimiento en pantalla o no?
if suivi:
print(msg)
# ¿se detiene?
if with_transaction or arrêt:
# ¿Mostramos la lista de errores?
return erreurs
else:
# sin errores
if suivi:
print(f"[{command}] : Exécution réussie")
# se muestra el resultado de la command
afficher_infos(curseur)
# se muestra la tabla de errores
return erreurs
finally:
# cierre del cursor
if curseur:
curseur.close()
# se valida/cancela la transacción si existe
if with_transaction:
if erreurs:
# anulación
connexion.rollback()
else:
# validación
connexion.commit()
Notas
- línea 2: la función [execute_list_of_commands] ejecuta las órdenes SQL contenidas en la lista [sql_commands]:
- consulte los comentarios de las líneas 4-11 para conocer el significado de los parámetros;
- línea 2: la conexión recibida es una conexión abierta a una base de datos;
- línea 15: si se desea que el conjunto de comandos de la lista [sql_commands] se ejecute dentro de una transacción, hay que trabajar en modo AUTOCOMMIT=False. De lo contrario, se trabajará en modo AUTOCOMMIT=True y, en ese caso, cada uno de los comandos de la lista [sqlCommands] se ejecutará dentro de una transacción automática y no habrá una transacción global;
- línea 19: se solicita un cursor para ejecutar los diferentes comandos SQL;
- líneas 22-51: se ejecutan los comandos uno por uno;
- líneas 26-27: se aceptan las líneas en blanco y los comentarios en la lista de comandos SQL. En este caso, simplemente se ignora el comando;
- líneas 30-33: ejecución de la consulta actual;
- líneas 35-45: se gestiona el caso de un posible error de ejecución de la consulta actual;
- líneas 37-38: el error se añade a la tabla de errores;
- líneas 40-41: si se ha solicitado un seguimiento, se muestra el mensaje de error;
- líneas 43-45: si el código llamante ha solicitado una interrupción tras el primer error o si ha solicitado el uso de una transacción, entonces hay que detenerse. Se devuelve la tabla de errores;
- líneas 46-51: caso en el que no se ha producido ningún error en la ejecución de la consulta actual;
- líneas 48-49: si se ha solicitado un seguimiento, se muestra la consulta ejecutada con la indicación «correcta»;
- líneas 50-51: se muestra el resultado de la consulta ejecutada. Volveremos sobre la función [afficher_infos] un poco más adelante;
- líneas 54-65: la cláusula [finally] se ejecuta en todos los casos, haya habido una excepción o no;
- líneas 56-57: cierre del cursor. Esto libera los recursos asignados al mismo;
- líneas 59-65: se trata el caso en el que el código llamante ha solicitado que los comandos SQL se ejecuten en una transacción;
- línea 60: se comprueba si la lista [erreurs] está vacía, lo que significa que no se ha producido ninguna excepción. En ese caso, la transacción se valida (línea 65); de lo contrario, se cancela (línea 62);
La función [afficher_infos] muestra el resultado de una 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
- línea 1: el parámetro de la función es el cursor que acaba de ejecutar una orden SQL. Dependiendo de si esta orden es una SELECT o una orden de actualización INSERT, UPDATE, DELETE, el contenido del cursor no es el mismo;
- línea 6: si el cursor tiene el campo [description], entonces ha ejecutado un SELECT y [description] describe los campos solicitados en el SELECT:
- description[i] describe el campo n.º i solicitado por el SELECT. Es una lista;
- descripción[i][0] es el nombre del campo n.º i;
- líneas 11-17: se muestran los nombres de los campos solicitados por el SELECT;
- líneas 18-24: se procesa el resultado del SELECT;
- líneas 20, 24: el resultado de un SELECT se procesa secuencialmente. Este resultado es un conjunto de líneas. La línea actual se obtiene mediante [curseur.fetchone()] (línea 19). Se obtiene entonces una tupla;
- líneas 27-30: si el cursor no tiene el campo [description], entonces ha ejecutado una orden de actualización INSERT, UPDATE, DELETE. Así se puede saber cuántas líneas de la tabla se han modificado al ejecutar esta orden;
- línea 30: [curseur.rowcount] es ese número;
El script principal [mysql-04] utiliza el módulo [mysql_module] que acabamos de describir:
El archivo [config_04] configura el contexto de ejecución del script [mysql_04]:
def configure():
import os
# ruta absoluta de la carpeta del archivo de configuración
script_dir = os.path.dirname(os.path.abspath(__file__))
# configuración de las carpetas del syspath
absolute_dependencies = [
# carpetas locales
f"{script_dir}/shared",
]
# fijación de syspath
from myutils import set_syspath
set_syspath(absolute_dependencies)
# se establece el config
return {
# archivo de comandos SQL
"commands_filename": f"{script_dir}/data/commandes.sql",
# credenciales de conexión a la base de datos
"host": "localhost",
"database": "dbpersonnes",
"user": "admpersonnes",
"password": "nobody"
}
El script [mysql_04] es el siguiente:
# se recupera la configuración de la aplicación
import config_04
config = config_04.configure()
# se configura la ruta del sistema (syspath); ya se pueden realizar las importaciones
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError
# ---------------------------------------------- main
# verificación de la sintaxis de la llamada
# 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"
# ¿error?
if erreur:
print(f"syntaxe : {args[0]} true / false")
sys.exit()
# cálculo de un texto
with_transaction = with_transaction == "true"
if with_transaction:
texte = "avec transaction"
else:
texte = "sans transaction"
# registros de pantalla
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")
# ejecución de las órdenes SQL del archivo
connexion = None
try:
# conexión a la base de datos
connexion = connect(host=config['host'], user=config['user'], password=config['password'],
database=config['database'])
# ejecución del archivo 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:
# visualización del error
print(f"L'erreur fatale suivante s'est produite : {erreur}")
# se detiene
sys.exit()
finally:
# cierre de la conexión si se ha abierto
if connexion:
connexion.close()
# visualización del número de errores
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# visualización de los errores
for erreur in erreurs:
print(erreur)
Notas
- líneas 1-4: configuración del script;
- línea 8: importación del módulo [mysql_module] descrito anteriormente:
- líneas 12-22: el script [mysql-04] espera un parámetro que debe tener uno de los valores [true / false]. Este parámetro indica si el archivo de comandos SQL debe ejecutarse dentro de una transacción (true) o no (false);
- línea 14: los parámetros pasados por el usuario al script se encuentran en la lista [sys.argv];
- línea 15: se necesitan dos parámetros, por ejemplo, [mysql-04 true]. El nombre del script cuenta como un parámetro;
- líneas 17-18: si hay dos parámetros, el segundo debe ser una cadena de caracteres con el valor «true» o «false»;
- líneas 24-29: cálculo de un texto que se muestra en la línea 33;
- líneas 39-44: se ejecutan los comandos del archivo [./data/commandes.sql];
- líneas 45-49: si se produce un error en la conexión (línea 40) o no es gestionado por el script [execute_file_of_commands], se muestra el error y se detiene todo;
- líneas 55-62: en caso de ejecución correcta, se muestra el número de errores encontrados en la ejecución de los comandos SQL;
Ejecución n.º 1
Primero se realiza una ejecución sin transacción. Para ello, se creará una configuración de ejecución tal y como se ha hecho en el apartado |configuración de un contexto de ejecución|:
- en [1-4], se crea una configuración de ejecución en Python;
- [5]: nombre de la configuración de ejecución;
- [6]: ruta del script a ejecutar;
- [7]: parámetros del script;
- [8]: carpeta de ejecución;
Por lo tanto, esta configuración corresponde a una ejecución del archivo SQL con una transacción. Utilice el botón [Apply] para validar la configuración.
De la misma manera, creamos la configuración de ejecución [mysql mysql-04 without_transaction]:
Por lo tanto, esta configuración corresponde a la ejecución del archivo SQL sin transacción. Utilice el botón [Apply] para validar la configuración.
Primero ejecutamos version sin transacción:
Los resultados son los siguientes:
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:
- línea 19: se observa que, tras el error, la ejecución de las órdenes SQL continuó, ya que la ejecución se realizó sin transacción y con el parámetro [arrêt=False]. Por lo tanto, se han ejecutado todas las órdenes SQL. Por lo tanto, deberíamos tener una tabla [personnes] que refleje esta ejecución;
Verificación con phpMyAdmin:
Ejecución n.º 2
Ahora ejecutamos la configuración [mysql mysql-04 with_transaction]. Los resultados son los siguientes:
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:
- línea 19: se observa que, tras el error, ya no se ejecutan más órdenes SQL, debido a que la ejecución se realizó en una transacción y, ante el primer error encontrado, se deshizo la transacción y se detuvo la ejecución de las órdenes SQL. Esto significa que el resultado de las órdenes de las líneas 9, 11 y 13 se ha revertido. Por lo tanto, deberíamos tener una tabla [personnes] vacía;
Comprobaciones con phpMyAdmin:
- en [5], se observa que la tabla [personnes] [2] está vacía;
16.7. script [mysql_05]: uso de consultas parametrizadas
El script [mysql_05] introduce el concepto de consultas parametrizadas:
# importaciones
from mysql.connector import connect, DatabaseError, InterfaceError
# identidad del usuario
ID = "admpersonnes"
PWD = "nobody"
# máquina host del SGBD
HOST = "localhost"
# identidad de la base de datos
BASE = "dbpersonnes"
# lista de personas (apellidos, nombre, edad)
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))
# otra lista de personas
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))
# acceso a SGBD
connexion = None
try:
# conexión
connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
# cursor
curseur = connexion.cursor()
# eliminación de registros existentes
curseur.execute("delete from personnes")
# Inserciones persona por persona con una consulta preparada
for personne in personnes:
curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
# Inserción en bloque de una lista de personas
curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
# validación de la transacción
connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
# Visualización de error
print(f"L'erreur suivante s'est produite : {erreur}")
# cancelación de la transacción
if connexion:
connexion.rollback()
finally:
# cierre de sesión
if connexion:
connexion.close()
Notas
- líneas 12-21: se crean dos listas de personas para incluir en la base de datos [dbpersonnes];
- línea 27: conexión a la base de datos;
- línea 31: eliminación del contenido de la tabla [personnes];
- líneas 33-34: inserción de personas con una consulta parametrizada. Línea 34: el primer parámetro es la orden SQL que se va a ejecutar. Esta está incompleta. Contiene parámetros [%s] que se sustituirán uno a uno y en orden por los valores de la lista del segundo parámetro;
- línea 36: inserción de personas, esta vez con una única instrucción [curseur.executemany]. El segundo parámetro de [executemany] es, por tanto, una lista de listas;
El interés de las consultas parametrizadas radica en dos puntos:
- se ejecutan más rápidamente que las consultas «fijas», que deben analizarse en cada ejecución. La consulta parametrizada [executemany] solo se analiza una vez. A continuación, se ejecuta n veces sin volver a analizarse;
- los parámetros introducidos en la consulta parametrizada se verifican. Si contienen caracteres reservados, como el apóstrofo por ejemplo, estos se «protegen» para que no interfieran en la ejecución de la orden SQL. Para verificar este punto, se han incluido nombres y apellidos con apóstrofos en la lista (líneas 16 y 21);
Los resultados obtenidos en phpMyAdmin son los siguientes:
- Cabe señalar que las cadenas que contienen un apóstrofo, carácter reservado en SQL, se han insertado correctamente. La consulta parametrizada las ha «protegido». Sin una consulta parametrizada, habríamos tenido que hacer este trabajo nosotros mismos;