SQLite Cookbook

Overview

Task that have been tackled in the past with SQLite.

Running sqlite from the command line in Windows 7

• Run a bash shell, e.g. GIT Bash
• sqlite3 can be run in Powershell
• If the sqlite3 program isn't in the system path, it can be run using the full path to the executable:

$ /c/path/to/django/install/sqlite/sqlite3 [database_name]

Displaying table properties

Show tables in the database

.tables

Show table definition

.schema [tablename]

Display columns in table

PRAGMA table_info(table_name);

Saving database properties to text file

Database objects only

From the command line:

$ sqlite3 [database_name] '.schema' > /path/to/export-file.txt

Database objects and data

From the command line:

$ sqlite3 [database_name] '.dump' > /path/to/export-file.txt

From the sqlite command prompt :

sqlite> .output /path/to/export-file.sql
sqlite> .dump

Single table definition

From the command line:

$ sqlite3 [database_name] '.schema [tablename]' > /path/to/export-file.txt

Queries

Controlling query results format

.mode columns
.header on

Defining and altering tables

Renaming or deleting a column from a SQLite table

• SQLite supports ALTER TABLE [table_name] ADD COLUMN ([column_name] [column_properties]).
• SQLite does not support UPDATE COLUMN or DROP COLUMN.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Autoincrement primary key

Create a primary key with

CREATE TABLE my_table {
    id INTEGER PRIMARY KEY,
    val TEXT
};

Insert a new row in the table, placing a value one greater than the highest existing primary key in the id column, by assigning NULL to the id column :

INSERT INTO my_table (id, val) VALUES (NULL, 'some value');

Quitting the sqlite3 program

.quit

or

.exit