[modification le 8/4/2014: refonte complète de la page]

Comme solution de base, nous allons simplement appliquer les fonctionnalités fournies par Python.

Soit le fichier base de données Sqlite3: “mabase.db3” comportant plusieurs tables, des contraintes et des données.

On veut le convertir en scripts SQL, de sorte que l'exécution ultérieure de ce script donnera:

• la même base de données si aucune modification du script n'a été faite
• une nouvelle base de données comportant les modifications apportées directement au script.

Voilà un exemple de code qui fait ça. C'est en Python 2.7, mais la conversion en Python 3 devrait être facile:

#!/usr/bin/python
# -*- coding: utf-8 -*-
# python v2.7
 
import os
import codecs
import sqlite3
 
#############################################################################
def base2script(base, script, codage='utf-8'):
    """permet de convertir une base de données sqlite3 en script SQL
       base: la base de données (nom du fichier avec son chemin)
       script: le script SQL à créer (nom du fichier avec son chemin)
       codage: encodage du script SQL à créer
    """
    # ouvre la base de données sqlite3
    try:
        cnx = sqlite3.connect(base)
    except sqlite3.Error, err:
        print u"Echec pour la connexion à la base de données\n" + err.args[0]
        return
 
    # convertit la base sqlite3 en script SQL
    with codecs.open(script, 'w', codage) as f:
        for i, ligne in enumerate(cnx.iterdump()):
            f.write(u'%s\n' % (ligne,))
 
    # ferme la base
    cnx.close()

Exemple d'utilisation:

base = "mabase.db3"
script = "mabase.txt"
base2script(base, script)

Vous pouvez ouvrir le nouveau fichier texte “mabase.txt” avec un simple éditeur de texte, qui doit bien sûr respecter l'encodage que vous avez choisi (ici utf-8 par défaut), et constater que toutes les requêtes SQL sont là pour reconstruire les tables et y insérer les données.

Avec cet éditeur de texte, vous pouvez modifier ce que vous voulez, tant que l'exécution ultérieure des requêtes modifiées restent possible. Et puisque le script est complet et en format texte, rien ne vous empêche de faire des modifications de structure qui vous auraient été interdites en SQL direct. Par exemple, effacer des champs, renommer des champs “clé primaire”, retirer ou modifier des contraintes d'intégrité référentielles, etc…

Et voilà comment on reconstruit la base de données sqlite3 avec le script SQL obtenu précédemment:

#!/usr/bin/python
# -*- coding: utf-8 -*-
# python v2.7
 
import os
import codecs
import sqlite3
 
#############################################################################
def script2base(script, base, codage='utf-8-sig'):
    """permet de convertir un script SQL en base de données sqlite3
       script: le script SQL (le nom du fichier avec son chemin)
       base: la base de données (nom du fichier avec son chemin)
       codage: encodage du script SQL à exploiter (supprime le BOM s'il existe)
    """
    # lit et charge en mémoire le script SQL
    with codecs.open(script, 'r', codage) as f:
        scriptsql = f.read()
 
    # supprime la base si elle existe déjà
    if os.path.exists(base):
        os.remove(base) 
 
    # ouvre la base de données sqlite3 et crée un curseur
    try:
        cnx = sqlite3.connect(base)
        cur = cnx.cursor()
    except sqlite3.Error, err:
        print u"Echec pour la connexion à la base de données\n" + err.args[0]
        return
 
    # exécute le script pour reconstruire la base de données sqlite3
    try:
        cur.executescript(scriptsql)
    except sqlite3.Error, err:
        print u"Erreur dans l'exécution du script\n" + err.args[0]
        cur.close()
        cnx.close()
        return
 
    # ferme la base de données
    cur.close()
    cnx.close()

Exemple d'utilisation:

script = "mabase.txt"
base = "mabase2.db3"
script2base(script, base)

Vous pourrez constater que le fichier obtenu “mabase2.db3” est une base de données sqlite3 parfaitement fonctionnelle. Celle-ci est identique à la base de données initiale si aucune modification n'a été apportée au script. Le fichier peut éventuellement être de taille inférieure au fichier de départ, si la base de données a subi des effacements.

Il est difficile de faire plus simple, non?

La solution précédente fonctionne bien, mais si j'ai apporté des modifications au script, j'aimerais bien pouvoir exécuter les requêtes une à une pour que cette exécution me signale l'endroit exact où il y a une erreur. Cela peut être une erreur de syntaxe, mais aussi une erreur d'intégrité (par exemple, un ajout qui ne respecte pas une contrainte de clé étrangère).

Et puis, si j'ai une base de données ayant des contraintes de clés étrangères, il faut que j'active, à l'ouverture de la base, la prise en compte de cette contrainte avec “PRAGMA foreign_keys=on;” (off par défaut).

Et si je fais ça, les codes précédents ne fonctionnent plus! Pourquoi? Parce que le “.iterdump” fourni par Python trie les requêtes de création des tables dans l'ordre alphabétique des noms de tables, et non dans l'ordre où ces tables ont été créées! La conséquence, c'est que dans l'exécution du script SQL ainsi construit, toute mention dans une contrainte d'une table qui n'a pas encore été crée se traduira pas une erreur.

Comment corriger cela? En modifiant le module Python!

Ce module à modifier s'appelle “dump.py”. Sous Windows, il se trouve ici: “C:\Python27\Lib\sqlite3\dump.py”

Avec les versions récentes de Python 2.7 et Python 3, la modification consiste à supprimer l'option de tri d'une requête SQL:

    q = """
        SELECT "name", "type", "sql"
        FROM "sqlite_master"
            WHERE "sql" NOT NULL AND
            "type" == 'table'
            ORDER BY "name"
        """

devient après modification:

    q = """
        SELECT "name", "type", "sql"
        FROM "sqlite_master"
            WHERE "sql" NOT NULL AND
            "type" == 'table'
        """

Dans les versions précédentes de Python, il faut modifier:

for table_name, type, sql in sorted(schema_res.fetchall()):

qui devient:

for table_name, type, sql in schema_res.fetchall():

Autre problème, mais cette fois-ci avec cx_freeze pour Python 2.7 (pas de problème avec Python 3):

Alors que sans cx_freeze, les lignes de script retournées par “iterdump” sont correctement encodées, avec cx_freeze, les caractères accentués des données fournissent des erreurs d'encodage!

En fait, alors que les données lues dans les tables sont en unicode, ce n'est pas le cas des lignes de script retournées par iterdump.

La correction est évidente: dans toutes les lignes du fichier dump.py qui contiennent un “yield(….)”, ajouter un 'u' devant les guillemets. Mais on peut faire plus simple: ajouter au tout début de la page “dump.py” la ligne:

from __future__ import unicode_literals

Ce qui dira à l'interpréteur Python que toutes les chaines de la page seront considérées comme des chaines unicodes, même sans le 'u' devant.

Voilà: Après ces corrections, la méthode connexion.iterdump() ne posera plus de problème, même avec le traitement par cx_freeze!

Si vous ne voulez pas modifier le fichier dump.py, vous pouvez le recopier dans votre répertoire programme, le renommer, par exemple, “sqlite3dump.py”, et l'importer comme:

from sqlite3dump import _iterdump as iterdump

Il faudra alors modifier dans la 1ère fonction “base2script” la ligne:

        for i, ligne in enumerate(cnx.iterdump()):

par:

        for i, ligne in enumerate(iterdump(cnx)):

Vous pouvez même ajouter l'activation des clés étrangères avec l'ouverture de la base dans les 2 fonctions base2script et script2base:

        cnx = sqlite3.connect(base)
        cnx.execute("PRAGMA foreign_keys=on;") # active les clés étrangères

On va maintenant reconstruire les codes précédents et en ajouter un autre qui servira au déverminage des modifications de script.

#!/usr/bin/python
# -*- coding: utf-8 -*-
# python v2.7
 
import os
import codecs
import sqlite3
 
from sqlite3dump import _iterdump as iterdump
 
#############################################################################
def base2script(base, script, codage='utf-8'):
    """permet de convertir une base de données sqlite3 en script SQL
       base: la base de données (nom du fichier avec son chemin)
       script: le script SQL à créer (nom du fichier avec son chemin)
       codage: encodage du script SQL à créer
    """
    # ouvre la base de données sqlite3
    try:
        cnx = sqlite3.connect(base)
        cnx.execute("PRAGMA foreign_keys=on;") # active les clés étrangères
    except sqlite3.Error, err:
        print u"Echec pour la connexion à la base de données\n" + err.args[0]
        return
 
    # convertit la base sqlite3 en script SQL
    with codecs.open(script, 'w', codage) as f:
        for i, ligne in enumerate(iterdump(cnx)):
            f.write(u'%s\n' % (ligne,))
 
    # ferme la base
    cnx.close()

#!/usr/bin/python
# -*- coding: utf-8 -*-
# python v2.7
 
import os
import codecs
import sqlite3
 
from sqlite3dump import _iterdump as iterdump
 
#############################################################################
def script2base(script, base, codage='utf-8-sig'):
    """permet de convertir un script SQL en base de données sqlite3
       script: le script SQL (le nom du fichier avec son chemin)
       base: la base de données (nom du fichier avec son chemin)
       codage: encodage du script SQL à exploiter (supprime le BOM s'il existe)
    """
    # lit et charge en mémoire le script SQL
    with codecs.open(script, 'r', codage) as f:
        scriptsql = f.read()
 
    # supprime la base si elle existe déjà
    if os.path.exists(base):
        os.remove(base) 
 
    # ouvre la base de données sqlite3 et crée un curseur
    try:
        cnx = sqlite3.connect(base)
        cnx.execute("PRAGMA foreign_keys=on;") # active les clés étrangères
        cur = cnx.cursor()
    except sqlite3.Error, err:
        print u"Echec pour la connexion à la base de données\n" + err.args[0]
        return
 
    # exécute le script pour reconstruire la base de données sqlite3
    try:
        cur.executescript(scriptsql)
    except sqlite3.Error, err:
        print u"Erreur dans l'exécution du script\n" + err.args[0]
        cur.close()
        cnx.close()
        return
 
    # ferme la base de données
    cur.close()
    cnx.close()

Voilà le code proposé pour tester si un script modifié respecte bien les contraintes d'intégrité référentielle.

Au lieu d'utiliser la méthode python “executescript” qui exécute tout d'un seul coup, on utilise “execute” qui exécutera une à une chacune des requêtes.

Par ailleurs, il faut retrouver les requêtes complètes pour les exécuter, car elles peuvent être présentées en plusieurs lignes. Il y a une méthode intéressante pour ça, qui s'appelle “complete_statement”.

Enfin, puisque c'est un test de déverminage, la base crée est une base en mémoire (“:memory:”). Si la base ne tient pas en mémoire, vous pouvez prendre un nom de base sur le disque.

#!/usr/bin/python
# -*- coding: utf-8 -*-
# python v2.7
 
import os
import codecs
import sqlite3
 
from sqlite3dump import _iterdump as iterdump
 
#############################################################################
def script2base_test(script, base=":memory:", codage='utf-8-sig'):
    """teste la conversion d'un script SQL en base de données sqlite3
       script: le script SQL (le nom du fichier avec son chemin)
       base: la base de données (nom du fichier avec son chemin)
       codage: encodage du script SQL à exploiter (supprime le BOM s'il existe)
    """
 
    # supprime la base si elle existe déjà et s'il s'agit d'un fichier disque
    if base!=":memory:" and os.path.exists(base):
        os.remove(base) 
 
    # ouvre la base de données sqlite3
    try:
        cnx = sqlite3.connect(base)        
        cnx.execute("PRAGMA foreign_keys=on;") # active les clés étrangères
        cur = cnx.cursor()
    except sqlite3.Error, err:
        print u"Echec pour la connexion à la base de données\n" + err.args[0]
        return
 
    # lit et charge en mémoire le script SQL
    with codecs.open(script, 'r', codage) as f:
        scriptsql = f.readlines()
    nbl = len(scriptsql) # nombre de lignes du script SQL   
 
    # lit et exécute le script SQL, requête par requête
    i = 0 # compteur de lignes
    r = 0 # compteur de requêtes
    c = 0 # compteur d'erreurs
    while i<nbl:
        buffer = scriptsql[i]
        while not sqlite3.complete_statement(buffer.encode('utf-8')):
            # NB: avec python 2, "complete_statement" n'accepte pas l'unicode
            i += 1
            buffer += scriptsql[i]
 
        if buffer.strip() not in [u"BEGIN TRANSACTION;", u"COMMIT;"]:
            try:
                cur.execute(buffer)
                cnx.commit()
            except sqlite3.Error, err:
                cnx.rollback()
                c += 1
                print u"num ligne: %d; num requête: %d; Erreur: %s; requête: \n%s" % (i, r, err.args[0], buffer)
 
        i += 1 # nouvelle ligne attendue
        r += 1 # nouvelle requête attendue
 
    # fermeture et effacement de la base temporaire
    cur.close()
    cnx.close()
 
    # message de fin
    print u"conversion terminée! (nb de lignes: %d  nb de requêtes: %d; nb d'erreurs: %d)" % (i-1, r-1, c)

Exemple d'utilisation:

    # test d'intégrité du script
    script = "mabase.txt"
    script2base_test(script)

Si, par exemple, la requête numéro 805 (ligne 884) déclenche une erreur, voilà le message affiché:

num ligne: 884; num requête: 805; Erreur: foreign key constraint failed; requête: 
INSERT INTO "auteurs" VALUES('IO',137,'TYRTAMOS','Albert','ILANDE','','',0,20,0,'','Xxxxxxxx','','','LOUTH VILLAGE','DUNDALK CO LOUTH','IRLANDE','');

Il y a une faute de frappe sur le nom de pays (“ILANDE” au lieu de “IRLANDE”), et ce nom devrait obligatoirement appartenir à une table des noms de pays avec une contrainte de clé étrangère.

Amusez-vous bien!