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');

ON DELETE CASCADE

Sqlite3 turns off foreign key support by default. With foreign key support off even when a table definition specifies ON DELETE CASCADE the rows in the foreign key tables will not be deleted.

Before each command where foreign key support is needed it's necessary to issue the following command: PRAGMA foreign_keys = ON;

Turning on FK support by default

To turn on foreign key support by default, add the following line to the sqlite configuration file, ~/.sqliterc:

PRAGMA foreign_keys = ON;

pyCharm

• Right click on the database connection > Properties
• Advanced tab > foreign_keys option: true

N.B. I tried this with no effect, e.g. tested some code that deleted a record with FKs that were not deleted along with it.

Quitting the sqlite3 program

.quit

or

.exit