Par défaut, la sortie des données est l'écran. Toutefois, tout ce qui est sélectionné peut être envoyé dans un fichier. Mieux, il est possible de paramétrer l'entrée et la sortie (I/O).
Paramétrer I/O
.mode
Cette dot-command permet de formater les données pour toutes les prochaines sélections :
-
.mode tabs
pour que les données soient séparées par une tabulation -
.mode csv
pour que les données soient séparées par un caractère, par défaut, la virgule. -
.mode insert
pour que les données soient envoyées sous forme de requête d'insertion -
.mode html
pour que les données soient envoyées sous forme d'un tableau HTML - ...
.separator
Cette dot-command permet d'indiquer le caractère qui sépare les données pour toutes les prochaines sélections.
Pour séparer les données par un point-virgule : .separator ";"
Dans les pays où on utilise la virgule décimale, on préfère le point-virgule.
Vous pouvez aussi taper .separator "|"
ou ...
Lors d'une importation, il ne peut avoir qu'un seul caractère de séparation.
Toutefois, pour afficher un résultat ( via un SELECT ), on peut en taper
plusieurs.
Par exemple, .separator "---"
Exporter
The sqlite3 program is able to show the results of a query in 14 different formats :
ascii box csv column html insert json line list markdown quote table tabs tcl
Exemple d'exportation :
.open mia2021.db .header off .mode column .width 20 -10 20 .once mia2021.txt SELECT "N° compte" AS compte, printf("%.2f", SUM("Montant")) AS somme, "Catégorie" FROM iban GROUP BY compte ORDER BY printf("%09.2f", somme);
.header
Lors que le mode .csv a été choisi, cette dot-command ajoute ou non le nom des champs sur la première ligne à toutes les prochaines sélections.
.header on
pour ajouter le nom des champs.
.header off
pour ne pas ajouter le nom des champs.
S'applique aussi à la sortie écran.
.once
Cette dot-command envoie la prochaine sortie dans un fichier.
.once result1.csv
pour envoyer le lignes sélectionnées dans le fichier
result1.csv.
Sera écrasé, sans
avertissement, tout éventuel fichier de même nom.
Pour enregistrer la sélection dans le fichier ( situé dans le dossier contenant SQLite ) :
-
provinces.csv :
.header off .mode tabs .once provinces.csv select * from provinces;
0 1 Anvers 2 Brabant Flamand 3 Brabant Wallon 4 Bruxelles (19 communes) 5 Flandre-Occidentale 6 Flandre-Orientale 7 Hainaut 8 Liège 9 Limbourg 10 Luxembourg 11 Namur
-
provinces2.csv :
.header off .mode csv .separator ";" .once provinces2.csv select * from provinces;
0;"" 1;Anvers 2;"Brabant Flamand" 3;"Brabant Wallon" 4;"Bruxelles (19 communes)" 5;Flandre-Occidentale 6;Flandre-Orientale 7;Hainaut 8;"Liège" 9;Limbourg 10;Luxembourg 11;Namur
Les chaînes spéciales sont entourées de guillemets.
= celles qui sont vides, contiennent un espace, un caractère accentué, ... -
provinces.sql :
.mode insert .once provinces.sql select * from provinces;
INSERT INTO provinces VALUES(0,''); INSERT INTO provinces VALUES(1,'Anvers'); INSERT INTO provinces VALUES(2,'Brabant Flamand'); INSERT INTO provinces VALUES(3,'Brabant Wallon'); INSERT INTO provinces VALUES(4,'Bruxelles (19 communes)'); INSERT INTO provinces VALUES(5,'Flandre-Occidentale'); INSERT INTO provinces VALUES(6,'Flandre-Orientale'); INSERT INTO provinces VALUES(7,'Hainaut'); INSERT INTO provinces VALUES(8,'Liège'); INSERT INTO provinces VALUES(9,'Limbourg'); INSERT INTO provinces VALUES(10,'Luxembourg'); INSERT INTO provinces VALUES(11,'Namur');
-
provinces.htm :
.mode html .once provinces.htm select * from provinces;
<TR><TD>0</TD> <TD></TD> </TR> <TR><TD>1</TD> <TD>Anvers</TD> </TR> <TR><TD>2</TD> <TD>Brabant Flamand</TD> </TR> <TR><TD>3</TD> <TD>Brabant Wallon</TD> </TR> ...
Seul le contenu de la balise
<table>
est créé.
.dump
Il est possible d'exporter au format SQL toute une table ou toute la DB
si aucune table n'est précisée. La différence par rapport au .mode insert
est que les requêtes de création de table sont ajoutées avant les insert.
.once db.sql .dump
.system
Cette dot-command permet de simuler un double-clic sur le fichier.
.system result1.csv
permet d'afficher le contenu dans son tableur favori.
Pour enregistrer la sélection dans le fichier provinces2.csv dans le dossier D:/moi/Documents et afficher ce fichier dans son tableur.
.header on .mode csv .separator ";" .once D:/moi/Documents/provinces2.csv select * from provinces; .system D:/moi/Documents/provinces2.csv
Le chemin utilise des slashs. D'autre part, il est recommandé de ne jamais utiliser d'espaces ou de caractères spéciaux dans un nom de dossier ou de fichier.
Importer
Le remplissage de tables peut se faire par importation.
L'importation est une opération délicate, "chirurgicale", qui peut échouer. Pire, elle peut corrompre.
Normalement, les données sont introduites via des requêtes SQL de type insert
into
. Le moteur de la base de données peut alors refuser cet ajout si la demande
ne respecte pas les contraintes fixées par le DB administrator. De plus, les
données sont généralement introduites via une interface graphique, donc un programme qui,
s'il est bien conçu, vérifiera les données fournies par l'utilisateur, avant même de
créer la requête d'insertion. L'importation permet d'introduire des données sans
passer de tests ...
Il faut être particulièrement attentif :
- aux trous dans une grille, à l'absence de valeur (1)
- à l'encodage du fichier CSV (2)
- aux caractères accentués, y compris aux majuscules accentuées
- aux caractères spéciaux et, particulièrement, aux : ' " , ;
- à la virgule décimale (3)
- à l'existence de valeurs identiques dans la colonne
- ...
(1) Un NULL n'est ni une chaîne vide, ni un zéro !
(2) avec SQLite, l'encodage est UTF-8
(3) avec SQLite, les nombres décimaux sont stockés avec un point
décimal
Toutefois, lorsque les données sont disponibles en téléchargement, il est plus rapide
d'importer d'un coup les 2823 codes postaux belges que de les enregistrer, un à un, via
des insert into
; même si quelques manipulations sont nécessaires via un
tableur.
Il est possible de télécharger la liste de tous les codes postaux de Belgique sur le site de la poste. Ce fichier est aussi ici ( 249 Ko ).
Après avoir ouvert ce fichier, via le tableur d'OpenOffice - téléchargeable gratuitement en version française -, nous constatons qu'il mentionne la province. Une information intéressante que nous allons aussi reprendre. Mais, le nom d'une même province apparaît plusieurs fois dans la même colonne. Il s'agit donc de doublons. Or, qui dit nombreux doublons, dit table secondaire.
Même des données fournies par un site officiel peut contenir de légères erreurs
Pour le code postal 1009, on lit "Senat de Belgique" au lieu de "Sénat de
Belgique".
NB : En néerlandais, les caractères accentués n'existe pas.
Créons nos tables secondaires, puis principales.
En l'occurrence, créons donc la table secondaire : provinces
CREATE TABLE provinces( id_province INTEGER PRIMARY KEY, nomProvince TEXT UNIQUE);
Puis, la table principale : localites
CREATE TABLE localites( id_localite INTEGER PRIMARY KEY, nomLocalite TEXT NOT NULL, CP TEXT NOT NULL, num_province INTEGER, FOREIGN KEY(num_province) REFERENCES provinces(id_province) );
Remplissons nos tables secondaires, puis principales.
.read
Cette méthode permet d'exécuter une série de requêtes SQL.
Cette méthode est lente. Mais, il est aisé d'introduire un NULL via une requête SQL.
Créons un fichier texte - auquel on donnera l'extension .sql. On tape une fois la requête d'insertion, puis on fait autant de copier/coller que d'enregistrements à faire.
Puis, on modifie uniquement la valeur des champs.
Attention. Les fichiers *.sql devront être encodés via la page de codes 437 ( CP-437 ), aussi appelée DOS Latin US ou OEM-US.
Si tous les caractères des requêtes sont codés en ASCII, tous les codages sont
valides.
Exemple : DELETE FROM provinces WHERE id_province=..;
provinces.sql :
INSERT INTO provinces VALUES(0,""); INSERT INTO provinces VALUES(1,"Anvers"); INSERT INTO provinces VALUES(2,"Brabant Flamand"); INSERT INTO provinces VALUES(3,"Brabant Wallon"); INSERT INTO provinces VALUES(4,"Bruxelles (19 communes)"); INSERT INTO provinces VALUES(5,"Flandre-Occidentale"); INSERT INTO provinces VALUES(6,"Flandre-Orientale"); INSERT INTO provinces VALUES(7,"Hainaut"); INSERT INTO provinces VALUES(8,"Liège"); INSERT INTO provinces VALUES(9,"Limbourg"); INSERT INTO provinces VALUES(10,"Luxembourg"); INSERT INTO provinces VALUES(11,"Namur");
Le fichier provinces.sql se trouve ici.
Il est probable que, dans votre éditeur de texte, le nom de la province de Liège s'affiche mal. Si tel est le cas, changez de table de décodage. ( Avec NotePad++, choisissez menu Encodage > codage de caractères > Langues d'Europe occidentale > OEM-US )
Enfin, remplissons notre table : .read provinces.sql
.import
Cette dot-command permet de remplir une table, d'un coup. Cette méthode est très rapide, mais le fichier CSV doit être parfait ...
Par exemple : .import nom_fichier.csv nom_table
previously exist
sqlite> .import C:/work/somedata.csv tab1
There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.
In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.
For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.
Ouvrir le fichier téléchargé dans un tableur, le modifier en vue de l'importation et l'enregistrer sous ( localites.ods et sous ) localites.csv ( sha1 = 68594be6b770e28722ba42012b78d8f15ad6bcdf ; fichier en lecture seule )
Choisir comme "jeu de caractères" : UTF-8
Choisir comme séparateur de champ : le point virgule
Choisi comme séparateur de texte : ( rien )
Ce qui produit un fichier de 64 Ko, on ne peut pas faire plus léger !
1;'S Gravenwezel;2970;1 2;'S Herenelderen;3700;9 3;3 Suisses;7510;0 4;Aaigem;9420;6 5;Aalbeke;8511;5 ... 2820;ZWEVEGEM;8550;5 2821;Zwevezele;8750;5 2822;Zwijnaarde;9052;6 2823;ZWIJNDRECHT;2070;1
Puis, importer localites.csv dans la base de données,
.mode csv
.separator ";"
.import localites.csv localites
.exit
Immédiatement, après une importation, il est préférable de fermer et relancer la base de données.
Au final, nous obtenons un fichier zipCodesBelges.db ( 84 Ko, sha1 = e0fe362edb408b8065614e8bfbd8c52548719deb ; fichier en lecture seule ) beaucoup plus léger que le fichier initial ( zipcodes_alpha_fr.xls 249 Ko ), car tous les doublons ont été supprimés. Mais, surtout un fichier capable de répondre à toutes nos questions, sous Windows et sous Linux.
Une base de données créée sous Windows peut être utilisée, sans la moindre modification, sous Linux; et, inversément. Les bases de données SQLite sont multi-plaformes.
SQLite sous Ubuntu
En interne, les chaînes de caractères sont stockées en UTF-8.
En tapant PRAGMA encoding;
, l'encodage utilisé s'affiche :
Maintenant, vous connaissez l'essentiel pour créer et utiliser une base de données !
Prochaine étape, utiliser une base de données via une interface graphique. Ceci nous conduira vers une courte étude du C et une étude, un peu plus longue, du C++.
Ensuite, nous reviendrons aux bases de données côté serveur ( MariaDB ) et aux langages web coté serveur ( PHP et Java )