Différences

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

--- sauvegarde_script_sql [2014/04/08 08:14]
tyrtamos
+++ sauvegarde_script_sql [2014/04/08 10:13]
tyrtamos
@@ Ligne 129: / Ligne 129: @@
 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 range 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.
+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:
+<code python>
+    q = """
+        SELECT "name", "type", "sql"
+        FROM "sqlite_master"
+            WHERE "sql" NOT NULL AND
+            "type" == 'table'
+            ORDER BY "name"
+        """
+</code>
+devient après modification:
+<code python>
+    q = """
+        SELECT "name", "type", "sql"
+        FROM "sqlite_master"
+            WHERE "sql" NOT NULL AND
+            "type" == 'table'
+        """
+</code>
+Dans les versions précédentes de Python, il faut modifier:
+<code python>
+for table_name, type, sql in sorted(schema_res.fetchall()):
+</code>
+Qui devient:
+<code python>
+for table_name, type, sql in schema_res.fetchall():
+</code>
+\\
+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!
-J'utilise la technique ci-dessus pour vérifier l'intégrité de la base de données: c'est très efficace. Si quelqu'un a bidouillé la base sans respecter les contraintes d'intégrité, la reconstitution de la base à partir du script le dira, et donnera l'endroit du script incorrect.
+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.
-Mais il y a un problème dans le code du module dump (utilisé dans iterdump): les tables de la base sont retrouvées, mais **//sont triées ensuite par ordre alphabétique//** pour fabriquer le script! La conséquence, c'est que lors de la reconstruction de la base à partir du script, les tables sont reconstruites et mises à jour quelquefois AVANT les tables dont elles dépendent, ce qui donne une erreur d'intégrité là où il n'y en a pas!
+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 simle: ajouter au tout début de la page dump.py la ligne:
-La solution la plus simple: modifier le fichier dump.py (Windows: C:\Python27\Lib\sqlite3) et retirer le tri dans la ligne:
+<code python>
+from __future__ import unicode_literals
+</code>
+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:
 <code python>
-for table_name, type, sql in sorted(schema_res.fetchall()):
+from sqlite3dump import _iterdump as iterdump
 </code>
-Qui devient:
+Il faudra alors modifier dans la 1ère fonction "base2script" la ligne:
 <code python>
-for table_name, type, sql in schema_res.fetchall():
+        for i, ligne in enumerate(cnx.iterdump()):
+</code>
+par:
+<code python>
+        for i, ligne in enumerate(iterdump(cnx)):
+</code>
+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:
+<code python>
+        cnx = sqlite3.connect(base)
+        cnx.execute("PRAGMA foreign_keys=on;") # active les clés étrangères
 </code>
-Après ça, les tables seront traitées dans l'ordre où elles ont été construites à l'origine.
+On va maintenant reconstruire les codes précédents et en ajouter un autre qui servira au déverminage des modifications de script.
-\\
+==== Conversion d'une base de données sqlite3 en script SQL ====
-Autre problème, mais cette fois-ci avec cx_freeze:
-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!
+<code python>
+#!/usr/bin/python
+# -*- coding: utf-8 -*-
+# python v2.7
-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.
+import os
+import codecs
+import sqlite3
-La correction est évidente: dans toutes les lignes du fichier dump.py qui contiennent un "yield(....)", ajouter un 'u' devant les guillemets. Par exemple:
+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>
-yield('{0};'.format(sql))
+#!/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>
-devient:
+==== 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>
-yield(u'{0};'.format(sql))
+#!/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>
-Voilà: Après ces corrections, la méthode connexion.iterdump() ne posera plus de problème, même avec le traitement par cx_freeze!
+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