Echanger des données avec d'autres logiciels et entre bases de données

Certains logiciels de stockage ou de mesures permettent d'obtenir des données sous la forme de fichiers contenant du texte au format CSV (CSV : Comma Separated Values.).

SQLite3 permet d'importer des données dans des tables ou d'exporter le contenu d'une table dans un fichier de type CSV.

Parfois on a besoin de récupérer des données à partir d'une autre base de données relationnelle. Dans ce cas on essaiera d'utiliser le SQL pour faire l'échange.

Vous trouverez, ici, des exemples :

• de fichiers CSV
• d'importation-exportation au format CSV, directement avec SQLite3
• d'importation-exportation grâce à des scripts SQL avec SQLite3
• d'importation-exportation CSV en utilisant un programme Python.

Des noms et prénoms (chaînes de caractères "simples") séparés par des virgules

decodts,jordan
delanghe,mohamed
denys,gatien
duhamel,kevin
everaert,alexis
herbaut,thibault
jover,justine
kor,franck
laumon,andre

Des chaînes de caractères séparées par des virgules

"Apprendre à programmer avec Python, Version 3","2002"
"AppreNdre à programmer avec Python, Version 3","2001"
"Apprendre à programmer avec Python, Version 3","2002"
"AppreNdre à programmer avec Python, Version 3","2001"

Des nombres réels séparés par des point-virgules

1,1;2,4468240195
2,1;2,8420511522
3,1;0,6243095624

CSV : Comma Separated Values.

Le format est variable et paramétrable :

• La virgule, qui sert de séparateur par défaut, peut-être remplacé par tout autre caractère : point-virgule, tabulation, deux points, etc. ;
• On peut demander que la première ligne du fichier contiennt le nom (la signification) des champs (des données) séparés par des virgules ;
• Les données peuvent être encadrées par des guillemets ou des apostrophes ;
• L'encodage des caractères peut être : cp1252, ANSI, MS-DOS, ASCII, UTF-8, Unicode, … .

Nous disposons d'un fichier CSV contenant des coordonnées (X et Y) de points. Dans cet exemple, ces coordonnées viennent d'une feuille de calcul d'un tableur.

x_y2.csv

1,1;2,4468240195
2,1;2,8420511522
3,1;0,6243095624
4,1;-2,1674193599
5,1;-2,9664329183
6,1;-1,038121732
7,1;1,8446337872
8,1;3,0314415094
9,1;1,4311558881
10,1;-1,4849278566
11,1;-3,035775778
12,1;-1,7955454494
13,1;1,0955010849
14,1;2,9793489738
15,1;2,1239971562
16,1;-0,6841478515
17,1;-2,8632904797
18,1;-2,4099370456
19,1;0,2591013942
20,1;2,6899232071
21,1;2,6476420286
22,1;0,1711309792
23,1;-2,4627171032
24,1;-2,8323544384
25,1;-0,5979381649
26,1;2,1862196998
27,1;2,9603772548

Nous désirons les stocker dans une base de données nommée essaisImport.db sous la forme d'une table nommée donnees_mesures.

sqlite3 essaisImport.db

Vous lui indiquez : le passage au mode CSV et le format d'entrée des données.

sqlite> .mode csv
sqlite> .separator ";"

Vous demandez à SQLite3 d'importer les données du fichier x_y2.csv dans la table donnees_mesures.

sqlite> .import x_y2.csv donnees_mesures
Error: no such table: donnees_mesures

sqlite> CREATE TABLE donnees_mesures(x DOUBLE, y DOUBLE);

sqlite> .import x_y2.csv donnees_mesures

sqlite> .tables
donnees_mesures  livre

sqlite> SELECT * FROM donnees_mesures
   ...> ;
1,1;2,4468240195
2,1;2,8420511522
3,1;0,6243095624
4,1;-2,1674193599
5,1;-2,9664329183
6,1;-1,038121732
7,1;1,8446337872
8,1;3,0314415094
9,1;1,4311558881
10,1;-1,4849278566
11,1;-3,035775778
12,1;-1,7955454494
13,1;1,0955010849
14,1;2,9793489738
15,1;2,1239971562
16,1;-0,6841478515
17,1;-2,8632904797
18,1;-2,4099370456
19,1;0,2591013942
20,1;2,6899232071
21,1;2,6476420286
22,1;0,1711309792
23,1;-2,4627171032
24,1;-2,8323544384
25,1;-0,5979381649
26,1;2,1862196998
27,1;2,9603772548

sqlite> .mode column
sqlite> .header on
sqlite> select * from donnees_mesures;
x           y
----------  ------------
1,1         2,4468240195
2,1         2,8420511522
3,1         0,6243095624
4,1         -2,167419359
5,1         -2,966432918
6,1         -1,038121732
7,1         1,8446337872
8,1         3,0314415094
9,1         1,4311558881
10,1        -1,484927856
11,1        -3,035775778
12,1        -1,795545449
13,1        1,0955010849
14,1        2,9793489738
15,1        2,1239971562
16,1        -0,684147851
17,1        -2,863290479
18,1        -2,409937045
19,1        0,2591013942
20,1        2,6899232071
21,1        2,6476420286
22,1        0,1711309792
23,1        -2,462717103
24,1        -2,832354438
25,1        -0,597938164
26,1        2,1862196998
27,1        2,9603772548
sqlite>

Format "texte brut", on n'affiche pas les en-têtes de colonnes, le séparateur sera le caractère tabulation.

.mode csv 
.header off
.separator '\t'

Vous préparez la redirection du flux des données affichées vers le fichier livres1.csv

.out livres1.csv

Vous "affichez" les données vers la sortie redirigée.

select * from livre;

Vous redirigez l'affichage vers l'écran

.output stdout

livres1ExportTabulation2.csv

ABC	2001
XYZ	2002

Voici ce qu'affiche l'éditeur NotePadavec l'option "Affichage", "Symboles spéciaux", "Affichage de tous les caractères" : {{ :ouvert_a_tous:prepas:bdd:exportcsvlivresv1sqlite.jpg }} La flèche orange indique le caractère tabulation === Affichage des livres ===

sqlite> .output stdout
sqlite> .mode column

sqlite> SELECT * FROM livre;
ABC     2001
XYZ     2002

=== Suppression des lignes de la table livre et affichage du contenu de cette table ===

sqlite> DELETE FROM livre;
sqlite> SELECT * FROM livre;
sqlite> 

=== Importation des données ===

sqlite> .mode csv
sqlite> .separator "\t"
sqlite> .import livres1ExportTabulation2.csv livre

Affichage des données récupérées

sqlite> .mode column

sqlite> SELECT * FROM livre;
ABC     2001
XYZ     2002

sqlite> .header on

sqlite> SELECT * FROM livre;
lvr_titre   lvr_annee_parution
----------  ------------------
ABC         2001
XYZ         2002
sqlite>

===== Importation-Insertion de données par script SQL ===== ==== Pré-requis ==== La table livre existe. Vous avez un fichier contenant des ordres SQL d'insertion de lignes dans une table. ==== Le fichier SQL d'insertion des lignes ====

file insererLivreV1.sql

INSERT INTO livre VALUES('ABC',2001);
INSERT INTO livre VALUES('XYZ',2002);

<spoiler|Variante pour le script d'insertion> Lorsque la table de destination contient plus de colonnes que la table de départ, il faut préciser explicitement dans quelles colonnes il faut insérer les données.

file insererLivreV2.sql

INSERT INTO livre(lvr_titre,lvr_annee_parution) VALUES('ABC',2001);
INSERT INTO livre(lvr_titre,lvr_annee_parution) VALUES('XYZ',2002);

</spoiler> ==== L'exécution de l'ordre d'insertion SQL ====

sqlite> DELETE FROM livre;
sqlite> SELECT * FROM livre;
sqlite> 

sqlite> .read insererLignesV1.sql

==== Le résultat de l'insertion ====

sqlite> SELECT * FROM livre;
lvr_titre;lvr_annee_parution
ABC;2001
XYZ;2002
sqlite>

Remarques : * l'affichage se fait avec des point-virgules, car on a oublié de revenir au séparateur | (commande .separator "|") après l'exportation en CSV. * si on avait passé la commande .header on avant l'exporatation, la première ligne du fichier aurait contenu les en-têtes de colonnes de la table. ===== Exportation de données au format SQL ===== Vous allez créer, ici, un fichier d'ordres d'insertion SQL dans une table à partir d'une base de données existante.

sqlite> .output dumpDonneesMesures.txt
sqlite> .dump donnees_mesures
sqlite>

Le fichier dumpDonneesMesures.txt a été créé dans le dossier où vous vous trouvez. Vous pouvez le visualiser avec le Bloc-Notes ou avec un éditeur de texte tel que NotePad.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE donnees_mesures(x DOUBLE, y DOUBLE);
INSERT INTO "donnees_mesures" VALUES('1,1','2,4468240195');
INSERT INTO "donnees_mesures" VALUES('2,1','2,8420511522');
INSERT INTO "donnees_mesures" VALUES('3,1','0,6243095624');
INSERT INTO "donnees_mesures" VALUES('4,1','-2,1674193599');
/*
...
...
*/
INSERT INTO "donnees_mesures" VALUES('4,4','-0,9516020739');
INSERT INTO "donnees_mesures" VALUES('4,5','-0,9775301177');
INSERT INTO "donnees_mesures" VALUES('4,6','-0,9936910036');
INSERT INTO "donnees_mesures" VALUES('4,7','-0,9999232576');
COMMIT;

Importation-exportation CSV en utilisant un programme Python