SQLite … du SQL dans un fichier !

SQLite … késako ?

Vous en avez rêvé ? le D. Richard Hipp et son collègue l’ont imaginé et créé. Mais de quoi parle-t-il ? et bien d’avoir les capacités de bases de données mais sans serveur et dans un seul fichier ! alors bien sur il n’est pas question d’y retrouver toutes les caractéristiques de nos bonnes vieilles bases de données relationnelles. De toute façon ce n’est ni l’objectif ni l’intérêt ! A contrario, pouvoir faire du SQL sans serveur c’est toute de suite plus intéressant.

Qui plus est si le moteur respecte les propriétés ACID qui sont le le rappelle :

• ATOMIQUE : la succession d’opérations (au sein d’une transaction) est indivisible. Cela signifie que si l’une des opérations est en échec toutes les opérations sont annulées (Rollback). C’est un véritable retour en arrière de tout ce qui a été exécuté jusqu’à lors (même si les précédentes opérations étaient en succès).
• COHERENTE : le contenu final (à la fin de la transaction). Cela n’impose pas pour autant que chaque opération pendant la transaction donne un contenu cohérent. Par contre un contenu à la fin de la transaction incohérent doit entraîner un Rollback (Cf. point précédent).
• ISOLEE : lorsque deux transactions sont exécutées en même temps, les modifications effectuées par l’une ne sont pas visible par l’autre tant que la transaction n’a pas commité (Commit).
• DURABLE : Une fois validé, l’état de la base de données doit être permanent …

Pour le coup, nous faisons un réel bond entre les bons vieux fichiers indexés et nos SGBDR classiques. Un bond vers l’agilité bien sur mais qui ne va néanmoins répondre qu’à certains cas d’usage.

Choisir ou non SQLite ?

Si on devait résumer les raisons d’utiliser SQLite, je dirais :

• Besoin d’avoir un moteur SQL très léger
• Facilité d’export/import
• Un seul fichier
• Ne nécessite pas d’authentification
• Ne nécessite pas de Serveur / Aucun travail d’administration
• Multi-plateformes
• API est facile à utiliser (nous allons le voir plus loin)
• Pas de dépendances externes

C’est parfait donc pour un Data Analyst ou un Data Scientist qui a besoin d’avoir localement des capacités SQL.

Par contre ça devient plus compliqué d’utiliser SQLite :

• Dés lors que l’on va être multi-utilisateur. La gestion de la concurrence existe mais il faut la mettre en œuvre …
• Les fichiers SQLite3 prennent du poids à une vitesse hallucinante, attention donc ce n’est pas un outil qui va bien gérer les forts volumes !
• Pas de cache pour les requêtes
• Il ne supporte pas certaines constructions importantes telles que SQL RIGHT JOIN et FULL OUTER JOIN.

Bien sur les listes précédentes ne sont pas exhaustive, mais voilà rien n’est magique et il ne faut pas s’imaginer avoir un réel SGBDR comme Oracle ou SQL Server dans un fichier avec une API !

SQLite avec Python

Utiliser SQLite avec Python s’avère d’une facilité déconcertante ! Pour commencer vous devez bien sur importer la librairie sqlite3 (utiliser pip install sqlite3).
Nous allons travailler avec un objet de type cusrsor. Attention à la confusion il ne s’agit pas du Cursor que l’on manipule habituellement avec Oracle par exemple. Pas du tout, avec SQLite un Cursor est plutot comme une connexion au fichier SQLite :

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()

Créons maintenant une table. Si vous jetez un oeil à la documentation officielle vous remarquerez que le nombre de types de données gérées est plutôt limité. Pour résumer vous n’avez accès qu’à 4 types : INTEGER, REAL, TEXT et BLOB.

NB: Notez que SQLite gère la notion de NULL.

sql = 'CREATE TABLE test2 (col1 text, col2 text, col3 numeric, col4 real, col5 text)'
c.execute(sql)

Pour exécuter une requête rien de plus simple :

c.execute("INSERT INTO test2 VALUES ('data1', 'data2', 1, 100.35, '10/12/2018')")

J’allais oublier, n’oubliez pas de commiter vos opérations :

conn.commit()

Pour récupérer un dataset, ce n’est pas bien compliqué non plus :

t = ('data1',)
c.execute('SELECT * FROM test2 WHERE col1=?', t)
c.fetchone()

SQLite avec Java

Pour vous connecter à SQLite en Java nous allons utiliser JDBC. Pour cela nous devons d’abord télécharger le driver JDBC SQLite correspondant, faites le ici. Une fois cela effecuté, référencez votre fichier JAR (driver) dans votre IDE préférée (personnellement j’adore NetBeans pour le développement Java) ou placez le dans votre CLASSPATH.

Ensuite vous devez connaitre la chaine de connexion du driver : jdbc:sqlite:sqlite_database_file_path

Voici un exemple :

jdbc:sqlite:C:/sqlite/db/test.db

Maintenant il suffit de s’y connecter :

package com.bcayla.sqlite.tuto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLiteJDBCDriverConnection {
    public static void connect() {
        Connection conn = null;
        try {
            String jdbcConnectionString= "jdbc:sqlite:C:/sqlite/db/test2.db";
            conn = DriverManager.getConnection(jdbcConnectionString);
            System.out.println("Connection to SQLite OK !");

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }
    public static void main(String[] args) {
        connect();
    }
}

Ensuite il suffit d’utiliser l’API JDBC de manière tout à fait classique …