Sqlite (Sqlite3) quick tips: if you know SQL already

Mark Bieda SQL Sqlite Sqlite3

I’m a long-time MySQL user, but recently I’ve been using sqlite (sqlite3).
This is a sqlite tutorial, in a sense, if you know SQL.

As with my other stuff, this is based on my real experience of using this system

Why use sqlite?
The basic thing is that it installs super fast (unbelievably, you just download a .exe file for windows and run it). This is in contrast to the big MySQL model. You get to skip all that client-server business (which is really important in many cases, but not for most stuff that I do).

installation and getting started
1. download and (on windows) just place the .exe somewhere. I like to place it in C:\sqlite3\
2. (windows) At the Start button, click Run and cmd as the run command. Go to C:\sqlite3 and run
sqlite3 temp.db

Critical stuff to know
.help — gives the list of dot commands. Important and useful
.separator "," — means to separate input and output columns (fields) by commas
.separator "\t" — same but with tabs
(important) – you have to set the separator before attempting to load data from a file into the database
.output myresults.txt — starts directing all query (like SELECT statements) output to myresults.txt
.output stdout — starts directing all query (like SELECT statements) output to stdout; will close any previous output file
.import gooddata.csv mytable — imports data from gooddata.csv to mytable using the current separator value to separate fields
.tables — a list of the tables in the database
.databases — a list of the databases
.schema mytable — statements used to create mytable; will also list indexes (useful!)

Control of Sqlite3:
Ctrl-c — ends Sqlite3
; –a semicolon must be used to end a line

A typical session
Note: I “made up” this session, so there could be a few small bugs…
create mytable (idnum varchar(20), salary float, age int);
.separator "\t";
.import persondata.txt mytable;
create index idex on mytable(idnum);
select * from mytable where age<30;

How I use sqlite3:
I know SQL “by heart”, so it is pretty easy for me to do things quickly with files, especially when I have to correlate values in files. Sometimes I reformat files in bash, perl, or more recently, Python.

Note that “sets” in Python (introduced after version 2.4) give really good database like behavior. And sets are fast, in my experience.