Il s’agit ici de voir comment, à partir de la ligne de commande, créer une petite base de données sqlite dont le contenu provient de fichiers .csv, puis de voir quelques d’exemples de requêtes.

Préparer les fichiers .csv

Pour que tout se passe bien par la suite, il est recommandé d’avoir un fichier propre, c’est-à-dire avec les lignes ayant toutes le même nombre de colonnes, et en particulier sans lignes vides.

On peut aussi vouloir supprimer la première ligne, si elle contient les titres des colonnes, pour qu’ils se mélangent pas aux données. Cela se fait aisément par:

$ tail -n +2 f.csv > f2.csv

Création de la base

La création de la base se fait en deux temps :

la création des tables ;
le peuplement des tables (ajout des données).

Ouverture de la base

$ sqlite3 base.db

Attention : si le fichier n’existe pas, une nouvelle base est créée, mais s’il existe, la commande ci-dessus l’ouvre. Si on veut repartir de zéro (en particulier si tout ceci est fait à partir d’un script), il faut bien penser à effacer le fichier.

Après cette commande, on se retrouve avec le prompt :

sqlite>

Création des tables

L’instruction pour créer une table est :

sqlite> CREATE TABLE t1 {
  Id INT,
  Nom TEXT,
  Prenom TEXT
  );

Il faut bien sûr créer autant de table que de fichiers .csv à importer, en respectant bien le nombre et le type des colonnes.

Peuplement des tables

Il faut tout d’abord indiquer le séparateur utilisé dans les fichiers .csv :

sqlite> .separator ","

On peut ensuite importer les données. Par exemple, pour importer le contenu du fichier f1.csv dans la table t1 précédemment créée, on fait:

sqlite> .import f1.csv t1

Et c’est tout ! On peut maintenant quitter, et la base de données est sauvée dans le fichier base.db initialement ouvert.

Consulter la base

Requète

On ne donne ici que quelques exemples :

affiche toute la table t1 :
```
  sqlite> SELECT * FROM t1 ;
```
affiche toutes les lignes de la table où la colonne Nom vaut pacalet :
```
  sqlite> SELECT * FROM t1 WHERE Nom = 'pacalet';
```
affiche les colonnes Nom et Prenom des lignes de la table où la colonne Nom commence par paca :
```
  sqlite> SELECT Nom,Prenom FROM t1 WHERE Nom LIKE 'paca%';
```
Plus compliqué, en consultant plusieurs tables:

sqlite> SELECT t1.Nom, Prenom, Codepostal FROM t1,t2
               WHERE Adresse = 'inconnue'
                 AND t1.Id = t2.Id;

consulte les tables t1 et t2 (partie FROM) ;
affiche 3 colonnes (partie SELECT) : on remarque que si le nom de colonne est ambiguë, on peut préciser le nom de la table ;
sélectionne les lignes où Adresse vaut inconnue ;
et, c’est là que c’est bien plus puissant qu’un grep dans le .csv, fait correspondre les lignes où t1.Id = t2.Id !
On peut aussi compter des choses:

sqlite> SELECT Ville,
        COUNT(*) as total,
        COUNT(CASE Genre WHEN "F" THEN 1 END) as femmes
  FROM gens
  WHERE Age > 20
  GROUP BY Ville
  HAVING femmes > (total/2.0)
;

consulte la table gens (partie FROM) ;
sélectionne les lignes où Age > 20 (partie WHERE) ;
groupe ensemble les gens ayant le même champ Ville (partie GROUP BY) ;
compte le nombre de gens sélectionnés par ville (total) ;
compte le nombre de ligne avec Genre = "F" (femmes) ;
et enfin que garde que les villes ayant plus de la moitié de femmes (partie HAVING).

Quelques commandes

.header on permet d’afficher le titre des colonnes dans les résultats,
.mode column permet d’aligner les colonnes,
.width 20 10 spécifie à largeur des colonnes,
.tables affiche une liste des tables,
.schema t1 affiche la structure de la table t1.

Documentaion

Pour en savoir plus :

sur SQL, voir cette excellente documentation ou encore ce site de référence très complet;
comme sqlite ne comprend qu’une partie des commandes SQL, il peut être utile de consulter le sous-ensemble supporté ;
hormis les commandes SQL, sqlite fournit aussi d’autres commandes (celles qui commencent pas un point dans les exemples ci-dessus) ;
un petit xkcd pour la route…

Sqlite en ligne de commande