SQLite

This article is a draft

This is not a complete article: This is a draft, a work in progress that is intended to be published into an article, which may or may not be ready for inclusion in the main wiki. It should not necessarily be considered factual or authoritative.

SQLite is a "pocket database", in that it is a full-featured relational database which however dispenses with the client/server architecture of most such databases and instead exists entirely in a single diskfile. Software in a wide variety of common languages can read from and write to this database file using standard SQL queries using the language's standard API for database interactions, assuming one exists and the database can be transferred to another computer simply by copying the file there.

Using SQLite directly

You can access an SQLite database directly using the native client:

[name@server ~]$ sqlite3 foo.sqlite

If the file foo.sqlite does not already exist, the SQLite software will create it and the client starts in an empty database, otherwise you are connected to the existing database. You may then execute whichever queries you wish on the database, such as SELECT * FROM tablename; to print to the screen the entire contents of the table tablename.

Accessing SQLite from software

This most common way of interacting with an SQLite (or other) database is programmatically, i.e. inside of a program written in one of various languages like R or Python, using a series of function calls to open a connection to the database, execute queries that can read or update existing data in the database as well as inserting new data and finally close the connection to the SQLite database.

Python

For Python we can use the module sqlite3, installed in a virtual environment, to access an SQLite database:

File : sqlite.py

#!/usr/bin/env python3
import sqlite3

dbase = sqlite3.connect("foo.sqlite")
crs = dbase.cursor()
params = ("employees",)
crs.execute("SELECT * FROM ?;",params)
rows = crs.fetchall()
for r in rows:
    print(r)

dbase.close()

R

Using R, the first step is to install the RSQLite package in your R environment, after which you can use code like the following to interact with the SQLite database,

File : sqlite.R

library(DBI)
# Connect to the database...
dbase <- dbConnect(RSQLite::SQLite(),"foo.sqlite")

# A parameterized query
rs <- dbSendQuery(dbase,"SELECT * FROM tname",params = list(tname = "employees"))
while (!dbHasCompleted(rs)) {
  # Grab a maximum of ten rows at a time...
  df <- dbFetch(rs,n = 10)
  # Print the number of rows we actually got from dbFetch
  print(nrow(df))
}
dbClearResult(rs)

# Close the database connection
dbDisconnect(dbase)

C/C++

CC++

#include <string.h>
#include <sqlite3.h>

int main(int argc,char** argv)
{
  return 0;
}

#include <string>
#include <sqlite3.h>

int main(int argc,char** argv)
{
  std::string query;
  sqlite3* dbase;

  sqlite3_open("foo.sqlite",&dbase);
  query = "SELECT * FROM " + std::string(argv[1]) + ";";
  sqlite3_exec(dbase,query.c_str(),nullptr,nullptr,nullptr);
  sqlite3_close(dbase);

  return 0;
}

Caveats

SQLite is as the name suggests easy to use and intended for relatively simple databases and which are neither excessively large (hundreds of gigabytes or more) nor too complicated in terms of their entity-relationship diagram. As your SQLite database grows in size and complexity the performance could start to degrade, in which case the time may have come to consider the use of more sophisticated database software which uses a client/server model