Sqlite3 command line options

Date: March 16th 2016
Last updated: March 16th 2016

Up until now I have focused on queries using python. At this stage I don't feel comfortable that I am in full control of the database schema. Therefore, the purpose of this entry is to provide a set of commands to evaluate a database schema.

Database schema

Access DB

sqlite3 gitbook.db

Show tables

.tables

## output
animals owner ownerpatient patient vet_visit

Show column names

PRAGMA table_info(patient);

## output
0|patientid|INTEGER|0||1
1|patientname|TEXT|0||0
2|patientanimal|INTEGER|0||0

Turn headers ON

.headers OFF # to turn off
.headers ON
PRAGMA table_info(patient);

## output
cid|name|type|notnull|dflt_value|pk
0|patientid|INTEGER|0||1
1|patientname|TEXT|0||0
2|patientanimal|INTEGER|0||0

Headers also available on SELECT query

SELECT * FROM patient;

## output
patientid|patientname|patientanimal
1|rover|1
2|tinkles|2
3|peanut|1
4|pickles|6
5|snoopy|1

Turn Timer ON

.timer OFF # to turn off
.timer ON
SELECT * FROM patient;

## output
patientid|patientname|patientanimal
1|rover|1
2|tinkles|2
3|peanut|1
4|pickles|6
5|snoopy|1
Run Time: real 0.001 user 0.000244 sys 0.000073

Show table schema

.schema 
# returns entire DB schema (not shown here)

.schema patient
## output
CREATE TABLE patient 
           (patientid 
            INTEGER PRIMARY KEY AUTOINCREMENT, 
            patientname TEXT,
            patientanimal INTEGER, 
            FOREIGN KEY(patientanimal) 
             REFERENCES animals(animalid));

Show all DB references

SELECT sql FROM sqlite_master 
WHERE sql LIKE '%REFERENCES%';

## output
CREATE TABLE patient 
          (patientid 
          INTEGER PRIMARY KEY AUTOINCREMENT,
          patientname TEXT, 
          patientanimal INTEGER, 
          FOREIGN KEY(patientanimal) 
          REFERENCES animals(animalid))

CREATE TABLE vet_visit 
          (visitid 
          INTEGER PRIMARY KEY AUTOINCREMENT,
          visitpatientid INTEGER, 
          visitanimalid INTEGER, 
          visitprice REAL,             
          sqltime 
          TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
          FOREIGN KEY(visitpatientid) 
          REFERENCES patient(patientid),
          FOREIGN KEY(visitanimalid) 
          REFERENCES animals(animalid))

CREATE TABLE ownerpatient
          (patient_id INTEGER,             
          owner_id INTEGER,
          FOREIGN KEY(patient_id) 
          REFERENCES patient(patientid)
          ON DELETE CASCADE,
          FOREIGN KEY(owner_id) 
          REFERENCES owner(ownerid) 
          ON DELETE CASCADE)

Run Time: real 0.000 user 0.000318 sys 0.000096