Différences

Ci-dessous, les différences entre deux révisions de la page.

--- sauvegarde_script_sql [2014/04/08 09:12]
tyrtamos
+++ sauvegarde_script_sql [2014/04/08 10:14]
tyrtamos
@@ Ligne 1: / Ligne 1: @@
-====== Sauvegarde et restauration sous forme de script SQL ======
+====== Sauvegarde / restauration sous forme de script SQL ======
-[en cours de modification le 8/4/2014]
+[modification le 8/4/2014: refonte complète de la page]
 ===== Solution de base =====
@@ Ligne 217: / Ligne 217: @@
 ==== Conversion d'une base de données sqlite3 en script SQL ====
+<code python>
+#!/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()
+</code>
 ==== Conversion d'un script SQL en base de données sqlite3 ====
+<code python>
+#!/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()
+</code>
 ==== Tester la validité d'une modification de script SQL ====
+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.
+<code python>
+#!/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)
+</code>
+Exemple d'utilisation:
+<code python>
+    # test d'intégrité du script
+    script = "mabase.txt"
+    script2base_test(script)
+</code>
+Si, par exemple, la requête numéro 805 (ligne 884) déclenche une erreur, voilà le message affiché:
+<code>
+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','');
+</code>
+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.

Les recettes Python de Tyrtamos

Outils pour utilisateurs

Outils du site

Différences

Outils de la page