17. Uso de SGBD y PostgreSQL
El SGBD PostgreSQL está disponible gratuitamente. Es una alternativa a la versión «community» de MySQL.
Lo utilizamos aquí para demostrar que es bastante sencillo migrar de scripts de Python / MySQL a scripts de Python / PostgreSQL.
Con el SGBD y el MySQL, la arquitectura de nuestros scripts era la siguiente:
Con el SGBD y el PostgreSQL, será la siguiente:
17.1. Instalación de SGBD y PostgreSQL
Las distribuciones de SGBD y PostgreSQL están disponibles en URL y [https://www.postgresql.org/download/] (mayo de 2019). A continuación mostramos la instalación de la versión para Windows de 64 bits:
- En [1-4], se descarga el instalador de SGBD;
Ejecutamos el instalador descargado:
- En [6], indique una carpeta de instalación;
- en [8], la opción [Stack Builder] no es necesaria para lo que queremos hacer aquí;
- en [10], deje el valor que se le mostrará;
- en [12-13], aquí hemos puesto la contraseña [root]. Esta será la contraseña del administrador de SGBD, que se llama [postgres]. PostgreSQL también lo llama superusuario;
- en [15], deja el valor predeterminado: es el puerto de escucha de SGBD;
- en [17], deja el valor predeterminado;
- en [19], el resumen de la configuración de la instalación;
En Windows, el SGBD PostgreSQL se instala como un servicio de Windows que se inicia automáticamente. La mayoría de las veces esto no es lo ideal. Vamos a modificar esta configuración. Escribe [services] en la barra de búsqueda de Windows [24-26]:
- por [29]; vemos que el servicio de SGBD PostgreSQL está en modo automático. Cambiamos esto accediendo a las propiedades del servicio [30]:
- en [31-32], configura el inicio en modo manual;
- en [33], detenga el servicio;
Cuando desee iniciar manualmente el servicio SGBD, regrese a la aplicación [services], haga clic con el botón derecho en el servicio [postgresql] (34) e inícielo (35).
17.2. Administrar PostgreSQL con la herramienta [pgAdmin]
Inicie el servicio de Windows de SGBD PostgreSQL (párrafo anterior). Luego, de la misma manera en que inició la herramienta [services], inicie la herramienta [pgadmin], que permite administrar SGBD, PostgreSQL y [1-3]:
Es posible que en algún momento se le solicite la contraseña del superusuario. Esta se llama [postgres]. Usted definió su contraseña durante la instalación de SGBD. En este documento, le asignamos la contraseña [root] al superusuario durante la instalación.
- En [4], [pgAdmin] es una aplicación web;
- en [5], la lista de servidores PostgreSQL detectados por [pgAdmin], en este caso 1;
- en [6], el servidor PostgreSQL que hemos iniciado;
- en [7], las bases de datos de SGBD, en este caso 1;
- en [8], la base de datos [postgresql] es administrada por el superusuario [postgres];
Primero, creemos un usuario [admpersonnes] con la contraseña [nobody]:
- en [17], se ha puesto [nobody];
- en [21], el código SQL que emitirá la herramienta [pgAdmin] hacia el SGBD PostgreSQL. Esta es una forma de aprender el lenguaje SQL, propiedad de PostgreSQL;
- en [22], tras la validación del asistente [Save], se creó el usuario [admpersonnes];
Ahora creamos la base de datos [dbpersonnes]:
Hacemos clic con el botón derecho en [23] y luego en [24-25] para crear una nueva base de datos. En la pestaña [26], definimos el nombre de la base de datos [27] y su propietario [admpersonnes] [28].
- en [30], el código SQL de creación de la base de datos;
- en [31], tras la validación del asistente [Save], se crea la base de datos [dbpersonnes];
Vamos a utilizar la base de datos [dbpersonnes] con scripts de Python.
17.3. Instalación del conector de Python de SGBD PostgreSQL
En el diagrama anterior se muestra un conector que establece el enlace entre los scripts de Python y SGBD PostgreSQL. Existen varios de ellos. Instalamos el conector [psycopg2]. Esto se hace en un terminal de Python (no importa en qué carpeta esté abierto dicho terminal). El conector se instala mediante el comando [pip install psycopg2]:
(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\troiscouches\v01\tests>pip install psycopg2
Collecting psycopg2
Downloading psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
|| 1.1 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5
17.4. Migración de los scripts MySQL a los scripts PostgreSQL
- Se duplica (Ctrl-C / Ctrl-V) la carpeta [1] de los scripts MySQL; luego, se cambian los nombres de los archivos, pero se conserva su contenido;
17.4.1. módulo [pgres_module]
Este módulo es una copia del módulo [mysql_module] (véase el párrafo |script [mysql-04]: ejecución de un archivo de comandos SQL|). Se modifican las importaciones:
En lugar de:
# importaciones
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor
se escribe:
# importaciones
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor
La firma de la función [afficher_infos] era:
def afficher_infos(curseur: MySQLCursor):
Ahora es:
def afficher_infos(curseur: cursor)
La firma de la función [execute_list_of_commands] era:
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True)
Ahora es:
def execute_list_of_commands(connexion: connection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
Por lo demás, no cambia nada más.
17.4.2. script [pgres_01]
El script [pgres_01] es una copia del script [mysql_01] (véase el párrafo |script [mysql-01]: conexión a una base de datos MySQL - 1|). En él se realizan las siguientes modificaciones:
En lugar de:
# importación del módulo mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
se escribe:
# importación del módulo psycopg2
from psycopg2 import connect, DatabaseError, InterfaceError
El resto no cambia. Los resultados son los mismos que con MySQL.
17.4.3. script [pgres_02]
El script [pgres_02] es una copia del script [mysql_02] (véase el párrafo |script [mysql-02]: conexión a una base de datos MySQL - 2|). En él se realizan las siguientes modificaciones:
En lugar de:
# importación del módulo mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect
se escribe:
# importación del módulo psycopg2
from psycopg2 import DatabaseError, InterfaceError, connect
Los resultados no son los mismos que los del script [mysql_02]:
El script [pgres_02] es el siguiente:
# importación del módulo mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect
# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
# se conecta y luego se desconecta (nombre de usuario, 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")
# ---------------------------------------------- principal
# datos de inicio de sesión
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# inicio de sesió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="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(erreur)
Aunque las líneas 36-41 deberían haber mostrado un mensaje de error indicando que la conexión con el SGBD había fallado, no se muestra nada. De hecho, al analizar el problema más a fondo, se observa que sí se pasa por el [except] en las líneas 35 a 37, pero que la variable [erreur] tiene el valor [None]. Esto ocurre con la versión 2.8.4 del conector [psycopg2].
Se puede solucionar este problema escribiendo un mensaje genérico, aunque menos preciso:
# inicio de sesión de un usuario inexistente
try:
connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# se muestra el error
print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")
Los resultados son entonces 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/postgresql/pgres_02.py
Connexion réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion réussie
Erreur de connexion à la base [dbpersonnes] par l'utilisateur [xx/yy]
Process finished with exit code 0
17.4.4. script [pgres_03]
El script [pgres_03] es una copia del script [mysql_03] (véase el párrafo |script [mysql-03]: creación de una tabla MySQL|). En él se realizan las siguientes modificaciones:
En lugar de:
from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection
se escribe:
from psycopg2 import DatabaseError, InterfaceError, connect
from psycopg2.extensions import connection
Además, la firma de la función [execute_sql], que era:
def execute_sql(connexion: MySQLConnection, update: str):
ahora es:
def execute_sql(connexion: connection, update: str):
El resto no cambia. El resultado es el siguiente:
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_03.py
create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, unique(nom,prenom)) : requête réussie
Process finished with exit code 0
Se puede verificar la existencia de la tabla [personnes] con la herramienta de administración [pgAdmin]:
17.4.5. script [pgres_04]
El script [pgres_04] es una copia del script [mysql_04] (véase el párrafo |script [mysql-04]: ejecución de un archivo de órdenes SQL|). Utiliza el módulo [pgres_module]:
# se recupera la configuración de la aplicación
import config_04
config = config_04.configure()
# El syspath está configurado; se pueden realizar las importaciones
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError
El resto no cambia.
Se crea una configuración [pgres pgres-04 without_transaction] tal como se hizo en el párrafo |script [mysql-04]: ejecución de un archivo de órdenes SQL|. Del mismo modo, se crea una configuración [pgres pgres-04 with_transaction].
La ejecución de la configuración [pgres pgres-04 without_transaction] arroja los siguientes resultados:
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : -1
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (ERREUR: erreur de syntaxe sur ou près de « xx »
LINE 1: xx
^
)
[insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom, prenom,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
nom, prenom, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)] : Exécution réussie
nombre de lignes modifiées : 1
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from personnes where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
*****************
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR: erreur de syntaxe sur ou près de « xx »
LINE 1: xx
^
)
Process finished with exit code 0
- línea 5: se tuvo que modificar el comando de eliminación de la tabla [personnes]. A diferencia del conector de MySQL, el conector de PostgreSQL genera una excepción si la tabla que se va a eliminar no existe. El comando [drop table] tiene una variante, [drop table if exists], que no genera una excepción si la tabla no existe. La hemos utilizado aquí. Este es un ejemplo en el que dos SGBD no se comportan de la misma manera en situaciones similares;
La tabla [personnes] en la herramienta [pgAdmin] es la siguiente:
Al ejecutar la configuración [pgres pgres_04 with_transaction] se obtienen los siguientes resultados:
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/postgresql/pgres_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : -1
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (ERREUR: erreur de syntaxe sur ou près de « xx »
LINE 1: xx
^
)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR: erreur de syntaxe sur ou près de « xx »
LINE 1: xx
^
)
Process finished with exit code 0
La tabla [personnes] en la herramienta [pgAdmin] es la siguiente:
Aquí, el resultado es diferente al obtenido con MySQL. Si se ejecutan los scripts en las mismas condiciones, es decir, después de ejecutar el script sin transacción, se obtienen los siguientes resultados:
- con MySQL, la tabla [personnes] está vacía;
- con PostgreSQL, la tabla [personnes] no está vacía;
La diferencia radica en las distintas formas en que estos dos SGBD deshacen la transacción:
- MySQL no revoca las órdenes [drop table] y [create table]. Quedamos con una tabla [personnes] vacía;
- PostgreSQL revoca las órdenes [drop table] y [create table]. La tabla queda en el estado en que se encontraba antes de ejecutar el script con la transacción;
17.4.6. script [pgres_05]
El script [pgres_05] es una copia del script [mysql_05] (véase el párrafo |script [mysql-05]: uso de consultas parametrizadas|). El script se modifica de la siguiente manera:
En lugar de:
# importaciones
from mysql.connector import connect, DatabaseError, InterfaceError
se escribe:
# importaciones
from psycopg2 import connect, DatabaseError, InterfaceError
El resto no cambia.
Los resultados obtenidos en [pgAdmin] son los siguientes:
17.5. Conclusión
La migración de los scripts MySQL a los scripts PostgreSQL se llevó a cabo con bastante facilidad. Se trata de una excepción. Los dos SGBD no admiten las mismas reglas de nomenclatura de los objetos que SQL (bases de datos, tablas, columnas, restricciones, tipos de datos…), y tienen extensiones SQL incompatibles… Para garantizar una migración sencilla, hay que ceñirse en ambos casos al estándar SQL sin intentar utilizar las extensiones propias de los SGBD. Esto se hace, entonces, a costa del rendimiento.