Dans un précédent article, nous vous avons parlé des bases de données, leur importance et leur intérêt. Ici je vais vous parler de SQLite, une bibliothèque donnant accès à un moteur de base de données relationnelle qui vous permettra de travailler avec du SQL et cela sans avoir besoin de configurer ou d'installer quoi que ce soit: simple, rapide et efficace. Vous pouvez à loisir l'inclure dans tous vos projets, le code source de SQLite étant dans le domaine public.
Logo SQLite
Si vous voulez la définition complète de la technologie utilisée et des principales différences avec les autre SGBDR, je vous invite à vous rendre sur la page wikipédia qui est très bien écrite.
Création de la base de données
Normalement vous l'avez déjà installé sur votre ordinateur: ouvrez un terminal et tapez la commande sqlite3, vous devriez obtenir (Pour les allergiques de la ligne de commande, vous pourrez créer et lire des bases de données SQLite avec ce plugin Firefox).
|
1 2 3 4 5 |
$ sqlite3 SQLite version 3.7.7 2011-06-25 16:35:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> |
Si ça ne marche pas, j'ai du mentir quelques lignes plus tôt, et il va vous falloir installer le tout manuellement : téléchargez SQLite ici et suivez les étapes d'installation fournies.
Si c'est ok, vous êtes arrivés sur le "shell" de SQLite, l'endroit où vous pourrez "parler" à votre base de données. Pour l'instant quittez le prompt et spécifiez le nom de votre base de données (masuperbdd.sqlite):
|
1 2 3 4 5 6 |
sqlite> .q $ sqlite3 masuperbdd.sqlite SQLite version 3.7.7 2011-06-25 16:35:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> |
Ca y est, vous avez créé votre première base de données, elle s'appelle "masuperbdd.sqlite". Vous pouvez l'utiliser comme un fichier texte : la déplacer, la partager, l'envier par mail, bref tout ce que vous pouvez faire avec un fichier texte usuel. C'est là un gros point fort de SQLite, vous avez une base de données sans efforts, que vous pourrez emporter partout avec vous 😉 Indépendamment de la plateforme sur laquelle vous vous trouvez !
Bon maintenant, vous avez toutes (ou presque) les commandes SQL à votre disposition et vous pouvez vous amuser. Là où je veux en venir, c'est qu'une fois fait cet effort (si on peut parler d'effort), une base de données est beaucoup plus pratique et remplace facilement un fichier CSV que certains ont l'habitude d'importer dans dans un tableur (genre Excel) qui plantera dès que vous dépasserez 20000 lignes et qui devient un vrai cauchemar dès que vous voulez complexifier un peu les choses.
Un exemple
Prenez par exemple ce fichier (volontairement petit - oui, 2800 lignes c'est peu); récupéré sur Ensembl via Biomart, j'ai sélectionné les gènes de la souris sur le chromosome 4, en y indiquant leur position sur le génome et leur teneur en bases GC (GC content).
Plutôt que de charger ce fichier dans un tableur nous allons donc l'importer dans une base de données SQLite et pour cela nous n'avons besoin que de quelques lignes de commande.
Préparation des données
Téléchargé depuis Biomart la première ligne du fichier indique le nom de chaque colonne, il faut donc supprimer l'en-tête. Grâce à awk je récupère toutes les lignes du fichier sauf la première et j'écris le résultat dans un autre fichier: simple_sans_header.csv.
|
1 |
$ awk 'FNR>1{print}' simple.csv > simple_sans_header.csv |
Connexion sur ma base de données: je peux choisir l'extension qui me convient (le plus utilisé est .sqlite ou .db pour database).
|
1 |
$ sqlite3 mesgenes.db |
Création de la table correspondant à mon header. Pour connaître les types vous pouvez vous rendre sur le site SQLite, il n'y en a pas beaucoup à retenir.
|
1 |
sqlite> create table genes (id integer, gene_start integer, gene_end integer, GCcontent real ); |
Spécifier le séparateur utilisé dans le fichier (ici csv veut dire "comma separated value", donc une virgule).
|
1 |
sqlite>.separator "," |
Importer le fichier simple_sans_header.csv dans la table genes.
|
1 |
sqlite>.import simple_sans_header.csv genes |
C'est fait !!! Pas plus difficile que ça.
Utilisation
Vous pouvez maintenant lire votre base de données:
Lister toute la table:
|
1 |
sqlite> select * from genes; |
Personnellement je préfère l'affichage par défaut sur le terminal, donc je remet le séparateur à "|", et j'aime bien aussi voir le nom des champs que j'ai sélectionnés:
|
1 2 |
sqlite>.separator "|" sqlite> .h on |
Les trois premiers gènes dans la liste rangés par "start":
|
1 2 3 4 5 |
sqlite> select * from genes order by gene_start asc limit 3; id|gene_start|gene_end|GCcontent ENSMUSG00000094042|139922728|139922814|60.92 ENSMUSG00000077214|64444730|64444858|44.96 ENSMUSG00000092677|44221195|44221267|53.42 |
Combien de gènes j'ai en tout ?
|
1 2 |
sqlite>select count(*) from genes; 2826 |
Quel est le GCcontent max, et le moyen?
|
1 2 3 |
sqlite>select max(GCcontent), avg(GCcontent) as moyenne from genes; max(GCcontent) | moyenne 87.69 | 47.1973000707714 |
Combien de gènes ont un GCcontent > 60% et un nombre de paires de bases > à 1500?
|
1 2 3 |
sqlite>select count(*) as nb_genes from genes where GCcontent > 60 and (gene_end - gene_start) > 1500; nb_genes 13 |
Leur ID ?
|
1 2 3 4 5 6 |
sqlite>select id from genes where GCcontent > 60 and (gene_end - gene_start) > 1500; id ENSMUSG00000067261 ENSMUSG00000028445 ENSMUSG00000087288 ... |
Super! Il me fallait justement cette liste d'IDs pour faire une autre requête dans Biomart et filtrer les résultats.
Mais il me les faudrait dans un fichier texte... Ici je peux les recopier à la main, mais s'il y en a 100 voire 1000?
Exporter
Je quitte ma base de données:
|
1 |
sqlite> .q |
Je ré-exécute la même sélection en précisant le nom de ma base de données et en prenant soin de mettre ma sélection entre guillemets, puis je redirige l'output vers un fichier:
|
1 |
$ sqlite3 mesgenes.db "select id from genes where GCcontent > 60 and gene_end - gene_start > 1500;" mes_genes.txt |
Vous pouvez aussi directement spécifier la redirection dans le shell SQLite comme ceci:
|
1 |
sqlite> .output mes_genes.txt |
A vous de choisir 🙂
Voilà, c'est tout pour aujourd'hui, c'est simple et vous pouvez voir que cela peut être bien plus puissant car vous pouvez ajouter d'autres tables et des relations entre elles.
Un moyen direct et rapide pour structurer vos données.
Merci à Bu, Clem_ et nallias pour leur relecture.
Nolwenn
mars 13, 2013 à 11:59
Article intéressant, merci 🙂 !
Toutefois je me permettrai une petit remarque. Plutôt que d'utiliser awk pour retirer l'entête du fichier, il est également possible d'utiliser la commande tail comme ceci :
L'avantage de cette commande c'est que vous ne serez pas obligé de préciser le type de délimiteur à utiliser par awk si vous utilisez un autre type de fichier 😉 !
KooK
mars 17, 2013 à 11:56
Vraiment très intéressant.
J'ai utilisé sqlite pendant très longtemps sans utiliser son interface de commande.
Ah, si j'avais su, j'aurai gagné pas mal de temps par la suite !
marco
avril 7, 2013 à 3:36
Un super outil pour voir, modifier des bdd sqlite: SQLiteMan http://sqliteman.com/
Yoann M.
avril 9, 2013 à 9:30
SQLite DataBase Browser est plus ergonomique je trouve. Ou sinon on peut également s'orienter sur le plugin firefox "SQLite Manager" qui est également très bien léché.