These notes relate only to topics specific to SQLite. For notes about SQL in general that are applicable in other database engines (joins, queries, etc), see my SQL notes.

Basic Usage

Unlike other more complicated SQL databases, SQLite does not have a complex system to support communications with the data. Each database simply lives in a single file. This makes it easy to segregate projects and to make backups etc. To run use this command:

$ sqlite3 thedatabasefile.db

Even if you have no data and this file doesn’t exist, it’s ok to initialize your database with this.

If you have a file filled with SQL statements that you’d like to be run on a database (extant or not), you can just pipe it in:

$ sqlite3 mydb.sqlite3.db < do_a_bunch_of.sql
$ cat do_a_bunch_of.sql | sqlite3 mydb.sqlite3.db

Note that the SQL statements must end in ; and follow all the rules (are the tables and fields you expect correct?). This technique is useful for converting CSV into INSERT statements and then populating a database.

There is an interesting run time option -html which will make simple HTML tables out of your queries. This can be handy for simple web projects.

Useful SQLite Commands

.help for some help.

To quit the client use .exit or .quit or Ctrl-D.

If you’ve written some random SQL in an editor and you’d like to pretend that you just typed it into your SQLite3 client session, use .read FILENAME.

What’s In The Database

What are the loaded databases?

.database

What are the tables?

.tables

And to see how they’re composed:

.schema MyTable1

Show the data in a table:

SELECT * FROM MyTable1;

Format

Turn on the column names.

.header on

This prints each field on its own line separating each record by a blank. Very useful for tables with too many fields.

.mode line

HTML output.

.mode html

CSV output.

.mode csv

Data Types

The following datatypes are available in SQLite3:

NULL

The value is a NULL value.

INTEGER

The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL

The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT

The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB

The value is a blob of data, stored exactly as it was input.

Other database engines have fancier and more complex types. SQLite3 will generally handle their syntax by converting them into its own more basic types. For example, a TINYINT gets converted into INTEGER and a VARCHAR type gets converted into TEXT. Any thing with INT in it is considered an INTEGER. Then anything with CHAR or TEXT in it is considered TEXT. There are some other implicit conversions that allow other formatted SQL statements to roughly work. Boolean values are actually stored as integers 0 and 1. See this web page for details.

Shell Scripting

SQLite is ideal for use in shell scripts. It runs with no complex daemon dependencies and its output goes right to standard output for easy piping. There are very often pure SQL oriented ways to do stuff like this, but if you know Unix this kind of example can be easier:

$ sqlite3 poems 'SELECT footnotes FROM actions' | wc

Converting CSV And Other Text Data To Tables

Here is a rough technique for reformulating comma or bar separated text into SQL statements using awk. In this example, something that is known in the bar separated input (nickname) is searched for to add something known to the database already (the psersonID of nickname).

cat SomePeople.bsv |\
awk 'BEGIN{FS="|"}\
{
printf(
"INSERT INTO MyTable1 (f1,f2,f3) VALUES (1,(SELECT personID FROM People WHERE People.nickname=\"%s\"),%s);\n"
,$1,$2)
}' |\
sqlite3 thedatabasefile.db

Transactions

Note that if you have a zillion similar statements, for example tons of "INSERT" statements, it may be staggeringly slow. This is because every INSERT statement is, by default its own "transaction" and transactions are slow. The trick is to gang all the INSERT statements up into one transaction by surrounding them like this:

BEGIN;
INSERT INTO MyTable1 VALUES('vf11','vf21',10);
INSERT INTO MyTable1 VALUES('vf12','vf22',15);
INSERT INTO MyTable1 VALUES('vf13','vf23',20);
...
COMMIT;

This will speed things up by many orders of magnitude! (On SQLite at least.) Officially recommended.

Indexing

Often the whole reason for using a database is that there is a lot of data. If you’re trying to optimize performance of dealing with a lot of data, you’ll want to consider using indexes. Indexes are a precomputed list of the order of records according to a certain way of looking at the data (alphabetical by name, grouped by zip code, etc.). If you have an index built on name, then finding records that match that name will be much faster. Here’s an example of this.

The following is a big file (417MB) filled with multi-line records filled with technical data. When using grep to just look through it for a particular record, I get this.

$ time grep VACXED testdata.txt
REFCODE: VACXED
real    0m0.357s

Then I created a database and parsed this text file into organized records in the database. The new SQLite database containing pretty much the same stuff is only 42MB.

$ time sqlite3 testdata.sql "SELECT * FROM thetable WHERE refcode = 'VACXED';"
VACXED|P-1 (# 2)|2|2.0|99.68|11.7162|12.3697|95.227|112.201|99.68
real    0m0.255s

That’s almost a 30% time savings just by doing this, but we can do better. Look what happens when I create an index.

$ sqlite3 testdata.sql "CREATE UNIQUE INDEX r ON thetable(refcode);"

$ time sqlite3 testdata.sql "SELECT * FROM thetable WHERE refcode = 'VACXED';"
VACXED|P-1 (# 2)|2|2.0|99.68|11.7162|12.3697|95.227|112.201|99.68
real    0m0.028s

It now takes 93% less time to find a record than with the grep. Note that the database file increased to 52MB with the new index. In my actual project from which this example was taken, the processing time went from 32 hours to 4 minutes. This is because when the connection remains open, the index is even more relevant. Indexes - use them early and often.

If you’re done with the index you should be able to get rid of it with:

$ sqlite3 testdata.sql "DROP INDEX r;"

You could add the IF EXISTS clause too if you don’t want any errors if the index isn’t present.

Back Up And Restore

To back up the database do this:

sqlite3 Vendors.sqlite .dump > Vendors.sqlite.sql

To restore:

sqlite3 Vendors.sqlite < Vendors.sqlite.sql

SQLite in Python

Here is a great and useful example of SQL in action as accesed by Python.

#!/usr/bin/python
#Converts a Google Chrome cookie file in SQLite format into text.
#This appears to produce a kosher "Netscape" style cookies.txt file
#that can be used for cookie spoofing in wget with:
# $ wget  --load-cookies <file> <URL>

# Original author notes:
# For firefox use this SELECT statement instead:
# cur.execute('SELECT host, path, isSecure, expiry, name, value FROM moz_cookies')

from sqlite3 import dbapi2 as db # python 2.5
cookie_file = r'/home/xed/.config/google-chrome/Default/Cookies'
output_file = r'xedcookiecutter-cookies.txt'

conn = db.connect(cookie_file)
cur = conn.cursor()
cur.execute('SELECT host_key, path, secure, expires_utc, name, value FROM cookies')
f = open(output_file, 'w')
i = 0
for row in cur.fetchall():
  f.write("%s\tTRUE\t%s\t%s\t%d\t%s\t%s\n" % (row[0], row[1], str(bool(row[2])).upper(), row[3], str(row[4]), str(row[5])))
  i += 1
print "%d rows written" % i
f.close()
conn.close()
Note
 This example shows extraction queries. If you are trying to write to an SQLite database, you have to also add a commit line. Using the variables from the above program, that would look like this: conn.commit(). This will actually make the changes you made to the database real and permanent. Without it, nothing happens.

Browsers Love SQLite

Here’s how to get history between some particular points in time out of your Firefox records.

echo "SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch') dd, moz_places.url \
FROM moz_places, moz_historyvisits \
WHERE moz_places.id = moz_historyvisits.place_id \
AND dd > datetime('2015-10-03 04:17:30') \
AND dd < datetime('2015-10-04 12:00:00') \
ORDER BY dd;" | \
sqlite3 ~/.mozilla/firefox/*.default/places.sqlite

Here is the full schema nicely diagrammed.