PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 15.8 » Programmation serveur » PL/Python -- Langage de procédures Python » Accès à la base de données

46.6. Accès à la base de données

Le module du langage PL/Python importe automatiquement un module Python appelé plpy. Les fonctions et constantes de ce module vous sont accessibles dans le code Python via plpy.foo.

46.6.1. Fonctions d'accès à la base de données

Le module plpy fournit plusieurs fonctions pour exécuter des commandes sur la base de données :

plpy.execute(query [, limit])

L'appel à plpy.execute avec une chaîne pour la requête et une limite de ligne optionnelle permet d'exécuter la requête et de retourner le résultat dans un objet résultant.

Si limit est indiqué et supérieur à zéro, alors plpy.execute récupère au plus limit lignes, tout comme si la requête incluait une clause LIMIT. Omettre limit ou le configurer à zéro fait qu'il n'y a pas de limite de lignes.

L'objet résultant émule une liste ou un objet dictionnaire. L'objet résultant peut être accédé par le numéro de ligne et le nom de colonne. Par exemple :

 rv = plpy.execute("SELECT * FROM my_table", 5)
       

retourne jusqu'à 5 lignes de my_table. Si my_table possède une colonne my_column, elle pourra être accédée ainsi :

 foo = rv[i]["my_column"]
       

Le nombre de lignes retournées peut être obtenu en utilisant la fonction intégrée len.

L'objet résultant contient ces méthodes additionnelles :

nrows()

Retourne le nombre de lignes traitées par cette commande. Notez que cela n'est pas nécessairement identique au nombre de lignes retournées. Par exemple, une commande UPDATE fixera cette valeur mais ne retournera aucune ligne (sauf si RETURNING est utilisé).

status()

La valeur retournée par SPI_execute().

colnames()
coltypes()
coltypmods()

Retourne respectivement une liste de noms de colonne, une liste de type OID de colonne et une liste de type de modifieurs spécifiques à un type pour les colonnes.

Ces méthodes lèvent une exception quand elles sont appelées sur un objet résultant d'une commande n'ayant pas produit d'ensemble de résultat, par ex, UPDATE sans RETURNING, ou DROP TABLE. Il est cependant normal d'utiliser ces méthodes sur un ensemble de résultat ne contenant aucune ligne.

__str__()

La méthode standard __str__ est définie pour qu'il soit possible de débugger les résultats de l'exécution d'une requête en utilisant plpy.debug(rv).

L'objet résultant peut être modifié.

Notez que l'appel à plpy.execute provoquera la lecture de tout l'ensemble de résultat en mémoire. N'utilisez cette fonction que lorsque vous êtes surs que l'ensemble de résultat sera relativement petit. Si vous ne voulez pas risquer une utilisation excessive de mémoire pour récupérer de gros ensembles, préférez plpy.cursor à plpy.execute.

plpy.prepare(query [, argtypes])
plpy.execute(plan [, arguments [, limit]])

plpy.prepare prépare le plan d'exécution pour une requête. Il faut l'appeler avec une chaîne contenant la requête et une liste de types de paramètres, si vous avez des références à des paramètres dans cette requête. Par exemple :

plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
       

text est le type de variable qui sera passé à $1. Le second paramètre est optionel si vous ne voulez pas fournir de paramètre à la requête.

Après avoir préparé une requête, il faut utiliser une variante de la fonction plpy.execute pour l'exécuter :

rv = plpy.execute(plan, ["name"], 5)
       

Il faut fournir le plan comme premier argument (à la place de la chaîne), et une liste de valeurs à substituer dans la requête comme second argument. Le deuxième argument est optionnel si la requête n'attend pas de paramètre. Le troisième argument est la limite de ligne optionnelle comme auparavant.

De manière alternative, vous pouvez appeler la méthode execute sur l'objet plan :

rv = plan.execute(["name"], 5)
       

Les paramètres de requête ainsi que les champs des lignes de résultat sont converties entre les types de données de PostgreSQL et de Python comme décrit dans Section 46.2.

Quand un plan est préparé en utilisant le module PL/Python, il est automatiquement sauvegardé. Voir la documentation de SPI (Chapitre 47) pour une description de ce que cela signifie. Afin d'utiliser efficacement ces appels de fonction, il faut utiliser un des dictionnaires de stockage persistant SD ou GD (voir Section 46.3). Par exemple :

 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
    if "plan" in SD:
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT 1")
        SD["plan"] = plan
     # reste de la fonction
 $$ LANGUAGE plpython3u;
       

plpy.cursor(query)
plpy.cursor(plan [, arguments])

La fonction plpy.cursor accepte les mêmes arguments que plpy.execute (à l'exception de la limite de lignes) et retourne un objet curseur, qui permet de traiter de gros ensembles de résultats en plus petits morceaux. Comme avec plpy.execute, une chaîne de caractère ou un objet plan accompagné d'une liste d'arguments peuvent être utilisés, ou la fonction cursor peut être appelée comme une méthode de l'objet plan.

L'objet curseur fournit une méthode fetch qui requiert en entrée un paramètre entier et retourne un objet résultat. À chaque appel de fetch, l'objet retourné contiendra la prochaine série de lignes, mais jamais plus que la valeur passée en paramètre. Une fois que toutes les lignes ont été épuisées, fetch se met à retourner des objets résultat vides. Les objets curseurs fournissent également une interface d'itérateur, fournissant les lignes une par une jusqu'à épuisement. Les données récupérées de cette façon ne sont pas retournées dans des objets résultat, mais plutôt dans des dictionnaires, chacun correspondant à une unique ligne de résultat.

Un exemple montrant deux façons de traiter des données dans une large table est:

CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
odd = 0
for row in plpy.cursor("select num from largetable"):
    if row['num'] % 2:
         odd += 1
return odd
$$ LANGUAGE plpython3u;

CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("select num from largetable")
while True:
    rows = cursor.fetch(batch_size)
    if not rows:
        break
    for row in rows:
        if row['num'] % 2:
            odd += 1
return odd
$$ LANGUAGE plpython3u;

CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
odd = 0
plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
rows = list(plpy.cursor(plan, [2]))  # or: = list(plan.cursor([2]))

return len(rows)
$$ LANGUAGE plpython3u;
       

Les curseurs sont automatiquement libérés. Mais si vous voulez libérer explicitement toutes les ressources retenues par un curseur, il faut utiliser la méthode close. Une fois fermé, un curseur ne peut plus être utilisé pour retourner des lignes.

Astuce

Il ne faut pas confondre les objets créés par plpy.cursor avec les curseurs DB-API comme définis par la spécification Python Database API. Ils n'ont rien en commun si ce n'est le nom.

46.6.2. Récupérer les erreurs

Les fonctions accédant à la base de données peuvent rencontrer des erreurs, qui forceront leur annulation et lèveront une exception. plpy.execute et plpy.prepare peuvent lancer une instance d'une sous-classe de plpy.SPIError, qui terminera par défaut la fonction. Cette erreur peut être gérée comme toutes les autres exceptions Python, en utilisant la construction try/except. Par exemple :

CREATE FUNCTION essaie_ajout_joe() RETURNS text AS $$
    try:
        plpy.execute("INSERT INTO utilisateurs(nom) VALUES ('joe')")
    except plpy.SPIError:
        return "quelque chose de mal est arrivé"
    else:
        return "Joe ajouté"
$$ LANGUAGE plpython3u;
    

La classe réelle de l'exception levée correspond à la condition spécifique qui a causé l'erreur. Référez-vous à Tableau A.1 pour une liste des conditions possibles. Le module plpy.spiexceptions définit une classe d'exception pour chaque condition PostgreSQL, dérivant leur noms du nom de la condition. Par exemple, division_by_zero devient DivisionByZero, unique_violation devient UniqueViolation, fdw_error devient FdwError, et ainsi de suite. Chacune de ces classes d'exception hérite de SPIError. Cette séparation rend plus simple la gestion des erreurs spécifiques. Par exemple :

CREATE FUNCTION insere_fraction(numerateur int, denominateur int) RETURNS text AS $$
from plpy import spiexceptions
try:
    plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
    plpy.execute(plan, [numerateur, denominateur])
except spiexceptions.DivisionByZero:
    return "denominateur doit être différent de zéro"
except spiexceptions.UniqueViolation:
    return "a déjà cette fraction"
except plpy.SPIError as e:
    return "autre erreur, SQLSTATE %s" % e.sqlstate
else:
    return "fraction insérée"
$$ LANGUAGE plpython3u;
    

Notez que, comme toutes les exceptions du module plpy.spiexceptions héritent de SPIError, une clause except la gérant récupèrera toutes les erreurs d'accès aux bases.

Comme alternative à la gestion des différentes conditions d'erreur, vous pouvez récupérer l'exception SPIError et déterminer la condition d'erreur spécifique dans le bloc except en recherchant l'attribut sqlstate de l'objet exception. Cet attribut est une chaîne contenant le code d'erreur « SQLSTATE ». Cette approche fournit approximativement la même fonctionnalité.