17. Uso de SGBD PostgreSQL
El SGBD PostgreSQL está disponible gratuitamente. Es una alternativa a la «comunidad» version de MySQL.
Lo utilizamos aquí para demostrar que es bastante sencillo migrar scripts de Python / MySQL a scripts de Python / PostgreSQL.
Con el SGBD MySQL, la arquitectura de nuestros scripts era la siguiente:
Con SGBD y PostgreSQL, será la siguiente:
17.1. Instalación de SGBD 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 version 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], el option [Stack Builder] no es necesario para lo que queremos hacer aquí;
- en [10], deje el valor que se le mostrará;
- en [12-13], hemos puesto aquí 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], deje el valor por defecto: es el puerto de escucha de SGBD;
- en [17], deje el valor por defecto;
- en [19], el resumen de la configuración de la instalación;
En Windows, SGBD PostgreSQL se instala como un servicio de Windows que se inicia automáticamente. En la mayoría de los casos, esto no es deseable. Vamos a modificar esta configuración. Escriba [services] en la barra de búsqueda de Windows [24-26]:
- en [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], configure el inicio en modo manual;
- en [33], detenga el servicio;
Cuando desee iniciar manualmente el SGBD, vuelva a la aplicación [services], haga clic con el botón derecho del ratón en el servicio [postgresql] (34) e inícielo (35).
17.2. Administre PostgreSQL con la herramienta [pgAdmin]
Inicie el servicio de Windows de SGBD PostgreSQL (párrafo anterior). A continuación, del mismo modo 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, hemos asignado 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], aquí 1;
- en [6], el servidor PostgreSQL que hemos iniciado;
- en [7], las bases de datos de SGBD, aquí 1;
- en [8], la base [postgresql] es gestionada por el superusuario [postgres];
En primer lugar, 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. Es una forma de aprender el lenguaje SQL, propiedad de PostgreSQL;
- en [22], tras la validación del asistente [Save], se ha creado el usuario [admpersonnes];
Ahora creamos la base [dbpersonnes]:
Hacemos clic con el botón derecho en [23] y, a continuación, 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;
- en [31], tras la validación del asistente [Save], se crea la base [dbpersonnes];
Vamos a utilizar la base [dbpersonnes] con scripts de Python.
17.3. Instalación del conector Python de SGBD PostgreSQL
En el esquema anterior se muestra un conector que establece el enlace entre los scripts de Python y SGBD PostgreSQL. Existen varios. 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 scripts PostgreSQL
- La carpeta [1] de los scripts MySQL se duplica (Ctrl-C / Ctrl-V) y, a continuación, se cambian los nombres de los archivos, pero no su contenido;
17.4.1. módulo [pgres_module]
Este módulo es una copia del módulo [mysql_module] (véase el apartado |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)
Pasa a ser:
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.
17.4.2. script [pgres_01]
El script [pgres_01] es una copia del script [mysql_01] (véase el apartado |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 apartado |script [mysql-02]: conexión a una base de datos MySQL - 2|). 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 (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)
# conexió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 SGBD había fallado, no se muestra nada. De hecho, al investigar el asunto, se observa que se pasa correctamente al [except] de las líneas 35-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 apartado |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
Por otra parte, la firma de la función [execute_sql], que era:
def execute_sql(connexion: MySQLConnection, update: str):
pasa a ser:
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 comprobar la presencia 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 apartado |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 y como se hizo en el apartado |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] da 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: ha sido necesario modificar la orden de eliminación de la tabla [personnes]. A diferencia del conector MySQL, el conector PostgreSQL lanza 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 lanza una excepción si la tabla no existe. La hemos utilizado aquí. Se trata de un ejemplo en el que dos SGBD no se comportan de la misma manera en situaciones análogas;
La tabla [personnes] en la herramienta [pgAdmin] es la siguiente:
La ejecución de la configuración [pgres pgres_04 with_transaction] da 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, tras 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 lo está;
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]. Nos encontramos con una tabla [personnes] vacía;
- PostgreSQL revoca las órdenes [drop table] y [create table]. La tabla vuelve al estado en el que se encontraba antes de la ejecución del script con transacción;
17.4.6. script [pgres_05]
El script [pgres_05] es una copia del script [mysql_05] (véase el apartado |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 ha llevado a cabo con bastante facilidad. Se trata de una excepción. Los dos SGBD no admiten las mismas reglas de nomenclatura de los objetos SQL (bases, tablas, columnas, restricciones, tipos de datos…), 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 propietarias de los SGBD. Esto se hace entonces a expensas del rendimiento.