SQLite… SQL in a file!

SQLite … what is that ?

Have you dreamed of it? the D. Richard Hipp and his colleague the imagined and created. But what is he talking about? and good to have the capacities of databases but without server and in only one file! so of course there is no question of finding all the characteristics of our good old relational databases. In any case, this is neither the objective nor the interest! On the other hand, being able to do SQL without a server is immediately more interesting.

What is more if the engine respects the ACID properties which are reminded:

• ATOMIC : the succession of operations (within a transaction) is indivisible. This means that if one of the operations fails all operations are rolled back . It is a real throwback to everything that has been done so far (even if the previous operations were successful).
• CONSISTENT : the final content (at the end of the transaction). This does not mean that every operation during the transaction gives consistent content. On the other hand, an inconsistent content at the end of the transaction must result in a Rollback (see previous point).
• ISOLEE : when two transactions are executed at the same time, the modifications made by one are not visible to the other until the transaction has committed (Commit).
• SUSTAINABLE : Once validated, the state of the database must be permanent …

For once, we are making a real leap between the good old indexed files and our classic RDBMS. A leap towards agility of course, but which will nevertheless only respond to certain use cases.

Choose SQLite or not?

If we had to summarize the reasons for using SQLite, I would say:

• Need to have a very light SQL engine
• Ease of export / import
• One file
• Does not require authentication
• Does not require Server / No administration work
• Multi-platform
• API is easy to use (we’ll see that later)
• No external dependencies

It is therefore perfect for a Data Analyst or a Data Scientist who needs to have SQL capabilities locally.

On the other hand, it becomes more complicated to use SQLite:

• As soon as we are going to be multi-user. Competition management exists but it must be implemented …
• SQLite3 files gain weight at an incredible rate, so be careful, this is not a tool that will handle high volumes well!
• No cache for queries
• It does not support some important constructs such as SQL RIGHT JOIN and FULL OUTER JOIN.

Of course, the previous lists are not exhaustive, but there is nothing magic and you should not imagine having a real RDBMS like Oracle or SQL Server in a file with an API!

SQLite with Python

Using SQLite with Python is incredibly easy! To start you must of course import the sqlite3 library (use pip install sqlite3).
We are going to work with a cusrsor type object. Beware of the confusion it is not the Cursor that we usually handle with Oracle for example. Not at all, with SQLite a Cursor is more like a connection to the SQLite file:

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

Now let’s create a table. If you take a look at the official documentation you will notice that the number of managed data types is rather limited. To summarize, you only have access to 4 types: INTEGER, REAL, TEXT and BLOB.

NB: Note that SQLite manages the notion of NULL.

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

To execute a query, nothing could be simpler:

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

I was going to forget, don’t forget to commit your operations:

conn.commit()

To retrieve a dataset, it’s not very complicated either:

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

QLite with Java

To connect to SQLite in Java we will use JDBC. For that we must first download the corresponding JDBC SQLite driver, do it here . Once this is done, reference your JAR (driver) file in your preferred IDE (personally I love NetBeans for Java development) or place it in your CLASSPATH.

Then you must know the driver connection string: jdbc : sqlite : sqlite_database_file_path

Here is an example :

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

Now just connect to it:

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();
    }
}

Then you just have to use the JDBC API in a very classic way …