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]

Save table definition to text file

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

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;

Quitting the sqlite3 program

.quit