- Le blog participatif de bioinformatique francophone depuis 2012 -

SQLite

-

par

nahoy
dans , ,

Dans un pré­cé­dent article, nous vous avons par­lé des bases de don­nées, leur impor­tance et leur inté­rêt. Ici je vais vous par­ler de SQLite, une biblio­thèque don­nant accès à un moteur de base de don­nées rela­tion­nelle qui vous per­met­tra de tra­vailler avec du SQL et cela sans avoir besoin de confi­gu­rer ou d'installer quoi que ce soit : simple, rapide et effi­cace. Vous pou­vez à loi­sir l'inclure dans tous vos pro­jets, le code source de SQLite étant dans le domaine public.

Logo SQLite
Logo SQLite

Si vous vou­lez la défi­ni­tion com­plète de la tech­no­lo­gie uti­li­sée et des prin­ci­pales dif­fé­rences avec les autre SGBDR, je vous invite à vous rendre sur la page wiki­pé­dia qui est très bien écrite.

Création de la base de données

Nor­ma­le­ment vous l'avez déjà ins­tal­lé sur votre ordi­na­teur : ouvrez un ter­mi­nal et tapez la com­mande sqlite3, vous devriez obte­nir (Pour les aller­giques de la ligne de com­mande, vous pour­rez créer et lire des bases de don­nées SQLite avec ce plu­gin Fire­fox).

1
2
3
4
5
6
$ sqlite3<br>
SQLite ver­sion 3.7.7 2011-06-25 16 :35 :41<br>
Enter ".help" for ins­truc­tions<br>
Enter SQL sta­te­ments ter­mi­na­ted with a ";"<br>
sqlite&gt ;<br>
Si ça ne marche pas, j'ai du men­tir quelques lignes plus tôt, et il va vous fal­loir ins­tal­ler le tout manuel­le­ment : télé­char­gez SQLite <a href="https://www.sqlite.org/download.html">ici</a> et sui­vez les étapes d'ins­tal­la­tion four­nies.

Si c'est ok, vous êtes arri­vés sur le "shell" de SQLite, l'endroit où vous pour­rez "par­ler" à votre base de don­nées. Pour l'instant quit­tez le prompt et spé­ci­fiez le nom de votre base de don­nées (masuperbdd.sqlite):

1
2
3
4
5
6
7
sqlite&gt ; .q<br>
$ sqlite3 masu­per­bdd.sqlite<br>
SQLite ver­sion 3.7.7 2011-06-25 16 :35 :41<br>
Enter ".help" for ins­truc­tions<br>
Enter SQL sta­te­ments ter­mi­na­ted with a ";"<br>
sqlite&gt ;<br>
<strong>Ca y est, vous avez créé votre pre­mière base de données</​strong>, elle s'appelle <em>"masuperbdd.sqlite"</em>. Vous pou­vez l'uti­li­ser comme un fichier texte : la dépla­cer, la par­ta­ger, l'envier par mail, bref tout ce que vous pou­vez 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 pour­rez empor­ter par­tout avec vous ;) Indépen­dam­ment de la pla­te­forme sur laquelle vous vous trou­vez !

Bon main­te­nant, vous avez toutes (ou presque) les com­mandes SQL à votre dis­po­si­tion et vous pou­vez vous amu­ser.
 Là où je veux en venir, c'est qu'une fois fait cet effort (si on peut par­ler d'effort), une base de don­nées est beau­coup plus pra­tique et rem­place faci­le­ment un fichier CSV que cer­tains ont l'habitude d'importer dans dans un tableur (genre Excel) qui plan­te­ra dès que vous dépas­se­rez 20000 lignes et qui devient un vrai cau­che­mar dès que vous vou­lez com­plexi­fier un peu les choses.

Un exemple

Pre­nez par exemple ce fichier (volon­tai­re­ment petit — oui, 2800 lignes c'est peu); récu­pé­ré sur Ensem­bl via Bio­mart, j'ai sélec­tion­né les gènes de la sou­ris sur le chro­mo­some 4, en y indi­quant leur posi­tion sur le génome et leur teneur en bases GC (GC content).

Plu­tôt que de char­ger ce fichier dans un tableur nous allons donc l'importer dans une base de don­nées SQLite et pour cela nous n'avons besoin que de quelques lignes de com­mande.

Préparation des données

Télé­char­gé depuis Bio­mart la pre­mière ligne du fichier indique le nom de chaque colonne, il faut donc sup­pri­mer l'en-tête. Grâce à awk je récu­père toutes les lignes du fichier sauf la pre­mière et j'écris le résul­tat dans un autre fichier : simple_sans_header.csv.

1
2
$ awk 'FNR&gt;1{print}' simple.csv &gt ; simple_​sans_​header.csv<br>
Connexion sur ma base de données : je peux choi­sir l'exten­sion qui me convient (le plus uti­lisé est .sqlite ou .db pour data­base).
1
2
$ sqlite3 mes­genes.db<br>
Création de la table cor­res­pon­dant à mon hea­der. Pour connaître les types vous pou­vez vous rendre <a href="https://​www​.sqlite​.org/​d​a​t​a​t​y​p​e​3​.​h​tml">sur le site SQLite</​a>, il n'y en a pas beau­coup à rete­nir.
1
2
sqlite&gt ; create table genes (id inte­ger, gene_​start inte­ger, gene_​end inte­ger, GCcontent real );<br>
Spécifier le sépara­teur uti­lisé dans le fichier (ici csv veut dire "com­ma sepa­ra­ted value", donc une vir­gule).
1
2
sqlite&gt ;.sepa­ra­tor ","<br>
Impor­ter le fichier <em>simple_​sans_​header</​em>.csv dans la table <em>genes</​em>.
1
2
sqlite&gt ;.import simple_​sans_​header.csv genes<br>
<strong>C'est fait !!! Pas plus dif­fi­cile que ça.</​strong>

Utilisation

Vous pou­vez main­te­nant lire votre base de don­nées :

Lis­ter toute la table :

1
2
sqlite&gt ; select * from genes ;<br>
Per­son­nel­le­ment je préfère l'affi­chage par défaut sur le ter­mi­nal, donc je remet le sépa­ra­teur à "|", et j'aime bien aus­si voir le nom des champs que j'ai sélec­tionnés :
1
2
3
sqlite&gt ;.sepa­ra­tor "|"<br>
sqlite&gt ; .h on<br>
Les trois pre­miers gènes dans la liste rangés par "start":
1
2
3
4
5
6
sqlite&gt ; select * from genes order by gene_​start asc limit 3 ;<br>
id|gene_​start|gene_​end|GCcontent<br>
ENSMUSG00000094042|139922728|139922814|60.92<br>
ENSMUSG00000077214|64444730|64444858|44.96<br>
ENSMUSG00000092677|44221195|44221267|53.42<br>
Com­bien de gènes j'ai en tout ?
1
2
3
sqlite&gt ;select count(*) from genes ;<br>
2826<br>
Quel est le GCcontent max, et le moyen ?
1
2
3
4
sqlite&gt ;select max(GCcontent), avg(GCcontent) as moyenne from genes ;<br>
max(GCcontent) | moyenne<br>
87.69 | 47.1973000707714<br>
Com­bien de gènes ont un GCcontent &gt ; 60% et un nombre de paires de bases &gt ; à 1500 ?
1
2
3
4
sqlite&gt ;select count(*) as nb_​genes from genes where GCcontent &gt ; 60 and (gene_​end - gene_​start) &gt ; 1500 ;<br>
nb_​genes<br>
13<br>
Leur ID ?
1
2
3
4
5
6
7
sqlite&gt ;select id from genes where GCcontent &gt ; 60 and (gene_​end - gene_​start) &gt ; 1500 ;<br>
id<br>
ENSMUSG00000067261<br>
ENSMUSG00000028445<br>
ENSMUSG00000087288<br>
...<br>
Super ! Il me fal­lait jus­te­ment cette liste d'IDs pour faire une autre requête dans Bio­mart et fil­trer les résul­tats.

Mais il me les fau­drait dans un fichier texte… Ici je peux les reco­pier à la main, mais s'il y en a 100 voire 1000 ?

Exporter

Je quitte ma base de don­nées :

1
2
sqlite&gt ; .q<br>
Je ré-exécute la même sélec­tion en précisant le nom de ma base de données et en pre­nant soin de mettre ma sélec­tion entre guille­mets, puis je redi­rige l'out­put vers un fichier :
1
2
$ sqlite3 mes­genes.db "select id from genes where GCcontent &gt ; 60 and gene_​end — gene_​start &gt ; 1500;"  mes_​genes.txt<br>
Vous pou­vez aus­si direc­te­ment spécifier la redi­rec­tion dans le shell SQLite comme ceci :
1
2
sqlite&gt ; .out­put mes_​genes.txt<br>
A vous de choi­sir :)

Voi­là, c'est tout pour aujourd'hui, c'est simple et vous pou­vez voir que cela peut être bien plus puis­sant car vous pou­vez ajou­ter d'autres tables et des rela­tions entre elles.

Un moyen direct et rapide pour struc­tu­rer vos don­nées.

Mer­ci à Bu, Clem_​ et nal­lias pour leur relec­ture.

Vous avez aimé ? Dites-le nous !

Moyenne : 0 /​ 5. Nb de votes : 0

Pas encore de vote pour cet article.

Partagez cet article :

Yohan J.

nahoy

Issu de la formation bioinformatique de Bordeaux 2, je suis co-fondateur de ce blog.

Je travaille actuellement au LCSB au Luxembourg depuis maintenant 1 an et je me prétend un codeur fou.

A bientôt sur bioinfo-fr !

Pour continuer la lecture :

Commentaires

4 réponses à “SQLite”

  1. Norore
    Nolwenn

    Article inté­res­sant, mer­ci 🙂 !
    Tou­te­fois je me per­met­trai une petit remarque. Plu­tôt que d'utiliser awk pour reti­rer l'entête du fichier, il est éga­le­ment pos­sible d'utiliser la com­mande tail comme ceci :

    tail -n+2 simple.csv > simple_sans_header.csv
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 ;) !
    Répondre
  2. Avatar de KooK
    KooK

    Vrai­ment très inté­res­sant.
    J'ai uti­li­sé sqlite pen­dant très long­temps sans uti­li­ser son inter­face de com­mande.
    Ah, si j'avais su, j'aurai gagné pas mal de temps par la suite !

    Répondre
  3. Avatar de marco
    marco

    Un super outil pour voir, modi­fier des bdd sqlite : SQLi­te­Man http://​sqli​te​man​.com/

    Répondre
    1. Yoann M.
      Yoann M.

      SQLite Data­Base Brow­ser est plus ergo­no­mique je trouve. Ou sinon on peut éga­le­ment s'orienter sur le plu­gin fire­fox "SQLite Mana­ger" qui est éga­le­ment très bien léché.

      Répondre

Laisser un commentaire