SQLite Notes

Basic Usage

Useful SQLite Commands

.help for some help.

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

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;

Fancier Queries

Standard format showing condition and sort preference:

select * from MyTable1 where MyField1!="undesirable" order by MyField2;

The greater than means "later than" or "after" the date mentioned. Note that the date can be in many formats. Tends to like something like this though:

select code from actions where trans="acquisition" and date>03/3/2011;

Here’s how you can get a report of how many items are associated with a particular property. In this example, how many actions did each contributer do?

select user,count(*) from actions group by user;'
helper1|32
helper2|16
xed|455

Joining

To join, use the full name of the field which is tablename.fieldname. Here’s an example:

select actions.code,vendors.phone from actions,vendors where actions.code=vendors.code and trans="acquisition" and date>03/3/2011;

Modifying

Create a new table:

create table MyTable1 (f1 varchar(9) primary key, f2 text, f3 real);

To create a new record:

insert into MyTable1 values('val4field','val4f2',10);

To delete an existing record:

delete from MyTable1 where f1='xed' and f2='Redundant';

To make some changes to existing records:

update MyTable1 set f1="xed" where f1 is NULL;

Note	When checking for nullness, you use `f1 is [not] NULL` but when checking for something you use `f1="theValue"`

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

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

Complex Query Example

Here’s a query I wrote that contains a bunch of useful tricks:

SELECT
v.code,v.name,
v.url, v.searchurl,
A.user,
STRFTIME('%Y.%m.%d %H:%M', A.maxdate),
CAST( ROUND(JULIANDAY("NOW")-JULIANDAY(A.maxdate),1) AS TEXT),
v.land, v.phone,v.types,
--v.alive, v.public, v.date_init,
    CASE
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 183 THEN "sixmon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 90 THEN "threemon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 30 THEN "onemon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 7 THEN "oneweek"
    ELSE "recent"
    END
FROM Vendors v
INNER JOIN
    (SELECT code,user,MAX(date) AS maxdate FROM Actions GROUP BY code) A
    ON v.code=A.code
ORDER BY A.maxdate
;

The -- starts a comment in SQL. (Beware!) The CASE allows the field to contain one of a few possibilities. The CAST can help prevent problems where your programming language interface gets back a float but you’re processing everything as text. The AS allows aliases to be put to things that are hard to specify. This style of INNER JOIN allows only the latest action record to be considered for each vendor record (each vendor can have many actions, one to many).