tyrtamos

Problématique

Dans certains scripts SQL pour sqlite3, on est obligé de calculer plusieurs fois la même valeur. Lorsque cette recherche est complexe, par exemple une sous-requête, cela constitue une perte de temps et de complexité qu'on cherche à éviter.

Un exemple est un script d'extraction avec SELECT, contenant une sous-requête dans un CASE si on doit la mettre en même temps dans le WHEN pour la condition et dans le THEN pour l'affichage:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CASE WHEN sousreq != ''
THEN sousreq
ELSE 'Non disponible'
END

Il y a des solutions "classiques" comme de créer une table temporaire ou de repousser la sous-requête plus loin, mais cela ne simplifie pas le script.

Comme nous sommes sous Python, nous allons créer les fonctions qui vont bien pour déclarer et utiliser des variables, et les "accrocher" à sqlite3 avec la méthode "create_function" du pilote Python.

Solution

Voilà la solution proposée:

Code PYTHON :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
class Varsql(object):
    """déclaration et utilisation de variables dans sqlite3
    """
 
    def __init__(self):
        """initialise le dictionnaire des variables
        """
        self.dicovar = {}
 
    def set_var(self, var, val):
        """cree la variable var et lui affecte la valeur val
        """
        self.dicovar[var] = val
        return val
 
    def get_var(self, var):
        """retourne la valeur val de la variable var si elle existe
           sinon, retourne None
        """
        if var in self.dicovar:
            return self.dicovar[var]
        else:
            return None
 
varsql = Varsql()  # instanciation de la classe

Et on déclare varsql à chaque ouverture de la base (ici, cnx est la variable de connexion) avec:

Code PYTHON :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
cnx.create_function("set_var", 2, varsql.set_var)
cnx.create_function("get_var", 1, varsql.get_var)

Grâce à cette double déclaration, nous pourrons utiliser set_var et get_var directement dans les scrips SQL pour sqlite3.

Bien sûr, il faut avoir conscience des caractéristiques de cette technique: le dictionnaire des variables ainsi créé est global pour le script, et chaque nouvelle valeur affectée à une même variable écrase la précédente (l'ancienne valeur étant traitée par le ramasse-miettes).

Juste un petit exemple pour vérifier que ça marche. Nous avons une table "test" avec un champ "mots". Nous voulons seulement répéter 2 fois le champ cherché sans le calculer 2 fois ("cur" est ici un curseur):

Code PYTHON :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
cur.execute("""
    SELECT set_var('x', mots), get_var('x')
    FROM test
    """)

Dans le résultat de cette requête, chaque ligne sera composée de 2 champs, le 2ème étant identique au 1er mais calculé qu'une seule fois.

J'utilise maintenant cette technique dans certain scripts complexes, et ça résout très bien le problème: gain de temps et de complexité!