PostgreSQL… Python… ça fait PL/Python !

Imaginez, vous utilisez PostgreSQL au quotidien pour stocker et traiter vos données. Comme vous êtes curieux, vous avez essayé Python pour faire quelques traitements et vous vous êtes rendus compte que c’était pas mal quand même… Malheureusement vos scripts sont complètement dissociés de la base de données et vous vous dites qu’il serait pratique que ces scripts soient directement pilotés depuis PostgreSQL.

Et bien vous pouvez mélanger les deux technologies grâce au PL/Python. « Sorcellerie » me direz-vous…

Si, si, c’est possible !

Hassan Cehef – Les Nuls

Le PL/Python est un langage de PostgreSQL permettant d’écrire des fonctions en langage Python directement au sein d’une base de données.

Notez qu’il s’agit d’une langage dit « untrusted » (« sans confiance ») car il permet de faire tout et n’importe quoi (comme modifier des fichiers sur le serveur par exemple). C’est pour cela que seul un superutilisateur peut créer des fonctions avec ce langage.

Introduction

Cet article présuppose que vous avez déjà utilisé Python et PostgreSQL et que vous possédez une version de Python sur votre machine en parallèle d’une installation de PostgreSQL.

Vous devez par ailleurs avoir des notions sur les variables d’environnement et le fonctionnement des services.

Installation du langage

Sous Linux, il faut installer le package en lien avec l’extension : postgresql-plpythonY-Z (avec Y = 3 pour Python 3.X et Z = version de PostgreSQL). Par exemple :

sudo apt install postgresql-plpython3-16

Sous Windows, le PL/Python est préinstallé avec PostgreSQL.

Il suffit ensuite de lancer la requête suivante pour l’activer :

CREATE EXTENSION plpython3u;

Vous pouvez maintenant utiliser le PL/Python pour vos fonctions et procédures. Voici un exemple :

DO 
$function$

	import sys
	plpy.notice(sys.version)

$function$
LANGUAGE plpython3u;

Ce bloc de code anonyme (oui, il n’est pas encapsulé dans une fonction ou un procédure) ne retourne rien mais un message contenant la version de Python utilisé est renvoyé dans la sortie console.

Fonctionnement

Si l’étape précédente a fonctionné du premier coup, félicitation ! Sinon, cette partie devrait vous aider.

Version de Python

Sous Windows, PostgreSQL est compatible avec une version spécifique de Python qui est définie lors de sa compilation. Cette information est disponible dans les notes d’installations présentes dans C:\Emplacement\de\PostgreSQL\XX\doc\installation-notes.html.

Voici un tableau résumant la compatibilité avec l’installeur PostgreSQL fournit par EDB.

Version de PostgreSQL	Version de Python compatible
PostgreSQL 17	Python 3.13.x
PostgreSQL 16	Python 3.12.x
PostgreSQL 15	Python 3.11.x
PostgreSQL 14	Python 3.9.x
PostgreSQL 13	Python 3.8.x
PostgreSQL 12 et avant	Python 2.7

Sous Linux, je n’ai pas explicitement trouvé l’information mais il semble que le tableau précédent soit valable (en tout cas d’après mes quelques tests c’est le cas).

Si vous n’utilisez pas la bonne version de Python, PostgreSQL vous renverra un message d’erreur de ce type lors de l’exécution d’une procédure Python :

SQL Error [58P01]: ERREUR: n'a pas pu charger la bibliothèque « C:/Program Files/PostgreSQL/16/lib/plpython3.dll » : The specified module could not be found.

Choix de l’interpréteur

L’exécution du code Python se fait au travers d’un interpréteur. Pour cela, PostgreSQL utilise l’interpréteur disponible « par défaut » sur la machine et donc celui disponible à travers la variable d’environnement PATH.

Définir un autre interpréteur n’est pas simple…

Sur Linux, c’est l’installation d’une version de Python autre que celle fourni par défaut, qui est complexe ; il faut en général compiler l’interpréteur. En revanche il est relativement simple de redéfinir la variable PATH directement dans la configuration du service (décrit ici).

Sous Windows, c’est l’inverse… Différentes versions de Python peuvent être installées très facilement mais la redéfinition de la variable PATH pour un service spécifique est relativement complexe (mais décrite ici).

En lien avec le sujet, voici un thread stackoverflow.

Installation de modules et environnements

Vous aurez très probablement besoin d’installer des modules. Il est peu recommandé de le faire directement dans l’environnement Python de base et il vaut mieux utiliser des environnements spécifiques à chaque projet.

Il en est de même pour PostgreSQL, vous pouvez créer et utiliser des environnements pour vos projets.

Pour cela il faut utiliser le module virtualenv car l’activation d’un environnement peut être fait directement depuis Python avec cet outil :

pip install virtualenv

Vous devez ensuite créer votre environnement dans un dossier spécifique. Vous pouvez créer un répertoire spécifique dans l’installation de PostgreSQL par exemple :

virtualenv "C:\Program Files\PostgreSQL\16\envs_python\mon_environnement"

Vous pouvez ensuite accéder à cet environnement et y installer tous les modules souhaités :

"C:\Program Files\PostgreSQL\16\envs_python\mon_environnement\Scripts\activate.bat"
pip install geopandas

Comme ce n’est pas vous mais le service qui contrôle le démarrage de PostgreSQL, il ne vous ai pas possible de démarrer l’environnement avant PostgreSQL (encore que en modifiant le service mais ce n’est pas des plus simple). Nous allons donc créer une fonction en PL/Python permettant de démarrer l’environnement :

CREATE OR REPLACE PROCEDURE py_activate_venv(venv text)
RETURNS boolean AS
$BODY$
	import os
	import sys

	if sys.platform in ('win32', 'win64', 'cygwin'):
		activate_this = os.path.join(venv, 'Scripts', 'activate_this.py')
	else:
		activate_this = os.path.join(venv, 'bin', 'activate_this.py')

	try:
		exec(open(activate_this).read(), dict(__file__=activate_this))
	except FileNotFoundError:
		plpy.error('Virtual environment {} does not exist.'.format(name))
	
	return True
$BODY$
LANGUAGE plpython3u
;

Pour activer l’environnement, il suffit de lancer la requête suivante :

SELECT py_activate_venv('C:\Program Files\PostgreSQL\16\envs_python\mon_environnement');

Pour aller plus loin, vous pouvez utiliser ce superbe projet GitHub qui automatise toute la gestion des environnements sous Python :

Module plpy

Le langage PL/Python apporte un module python automatiquement chargé : plpy qui apporte quelques fonctions utiles.

Fonctions

Pour retourner des message, utilisez les fonctions suivantes :

• plpy.debug(msg, **kwargs)
• plpy.log(msg, **kwargs)
• plpy.info(msg, **kwargs)
• plpy.notice(msg, **kwargs)
• plpy.warning(msg, **kwargs)
• plpy.error(msg, **kwargs)
• plpy.fatal(msg, **kwargs)

Les arguments suivants peuvent être utilisés pour ajouter des informations de contexte au message renvoyé :

• detail
• hint
• sqlstate
• schema_name
• table_name
• column_name
• datatype_name
• constraint_name

Pour exécuter une requête SQL au sein de la base de données, utilisez plpy.execute("query" [, limit]) avec :

• query : la requête.
• limit : un entier permettant de limiter le nombre de résultat.

L’objet résultant est une liste (les lignes) de dictionnaires (ensemble de colonnes). Par exemple pour renvoyer une colonne de la 4ème ligne :

result = plpy.execute("SELECT * FROM ma_table", 25)
return result[4]["ma_colonne"]

Il existe trois fonctions équivalentes à leurs homonyme au sein de PostgreSQL : plpy.quote_literal(string), plpy.quote_nullable(string) et plpy.quote_ident(string).

Gestion des erreurs

Les fonctions plpy.execute et plpy.cursor (non décrite dans cet article), renvoient une exception dans le cas où la requête SQL passée en argument échoue. L’exception est une instance de la classe plpy.SPIError qui peut être récupérée via la construction try/except. Voici un exemple :

CREATE FUNCTION test_plpython() 
RETURNS text AS 
$BODY$

    try:
        plpy.execute("UPDATE ma_table SET "ma_colonne" = 12")

    except plpy.SPIError:
        return "La mise à jour a échouée"

    else:
        return "La mise à jour a fonctionnée"

$BODY$
LANGUAGE plpython3u;

Il est possible de cibler une erreur spécifique retournée par PostgreSQL. Pour cela, vous pouvez vérifier la valeur de l’attribut sqlstate de la classe SPIError ou utiliser le module plpy.spiexceptions qui définit une classe d’exception pour chaque condition PostgreSQL.

Pour spécifier une erreur avec spiexception, il suffit de se référer à la liste des erreurs de PostgreSQL et de convertir le nom de l’erreur comme suivant : retirer les soulignés (« _ ») + mettre en majuscule la première lettre de chaque mot. Ainsi :

• privilege_not_granted devient PrivilegeNotGranted
• data_exception devient DataException
• division_by_zero devient DivisionByZero

Pour attraper une erreur spécifique il suffit d’utiliser la syntaxe suivante :

CREATE FUNCTION test_plpython() 
RETURNS text AS 
$BODY$

    try:
        plpy.execute("UPDATE ma_table SET "ma_colonne" = 12")

    except plpy.spiexceptions.UniqueViolation:
        return "La mise à jour a échouée en raison d'une erreur d'unicité"

    except plpy.SPIError as e:
        return "Une erreur s'est produite : SQLSTATE %s" % e.sqlstate

    else:
        return "La mise à jour a fonctionnée"

$BODY$
LANGUAGE plpython3u;

Gestion des transactions

L’exécution d’une fonction en PL/Python se fait toujours au sein d’une transaction. Ainsi, lorsqu’une requête est exécutée via plpy.execute, une sous-transaction est automatiquement démarrée. Il devient alors possible de contrôler l’issue de cette sous-transaction via les fonctions suivantes :

• plpy.commit() : valider la sous-transaction
• plpy.rollback() : annuler la sous-transaction

Après la fin d’une sous-transaction, une nouvelle sous-transaction est automatiquement démarrée.

Dans le cas où plusieurs requêtes doivent être exécutées et que l’intégralité de ces requêtes doivent être validées ou non, il est possible de démarrer une sous-transaction grâce à plpy.subtransaction(). Pour cela, il faudra l’utiliser de la façon suivante :

with plpy.subtransaction():
  plpy.execute("DELETE FROM ma_table WHERE colonne = 'xxx'")
  plpy.execute("INSERT INTO autre_table (col_1, col_2) VALUES ('valeur1', 'valeur2')")

Notez qu’une erreur lors de l’une des requête entraine donc l’annulation de l’ensemble des requêtes de la sous-transaction mais engendre également une exception qui stoppera l’exécution de la fonction et annulera la transaction en cours. Pour limiter la propagation de l’exception, il est possible d’encapsuler le tout au sein d’un try/catch :

try:
  with plpy.subtransaction():
    plpy.execute("DELETE FROM ma_table WHERE colonne = 'xxx'")
    plpy.execute("INSERT INTO autre_table (col_1, col_2) VALUES ('valeur1', 'valeur2')")
except plpy.SPIError as e:
  result = "Erreur de requête : %s" % e.args
else:
  result = "Opération effectuée"

Pour allez plus loin

Lq documentation de PostgreSQL sur le langage PL/Python vous permettra d’approfondir le sujet maintenant que vous avez les éléments clés en main.

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationPython