Editing SQLite Cookbook

[[Category:SQLite]] [[Category:Django]] [[Category:Python]] [[Category:Web Development]]
== Overview ==

Task that have been tackled in the past with SQLite.

== Running `sqlite` from the command line in Windows 7 ==

* <strike>Run a bash shell, e.g. `GIT Bash`</strike>
* `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:

<syntaxhighlight lang="bash">
$ /c/path/to/django/install/sqlite/sqlite3 [database_name]
</syntaxhighlight>

== Displaying table properties ==

=== Show tables in the database ===

<syntaxhighlight lang="text">
.tables
</syntaxhighlight>

=== Show table definition ===

<syntaxhighlight lang="sql">
.schema [tablename]
</syntaxhighlight>

=== Display columns in table ===

<syntaxhighlight lang="sql">
PRAGMA table_info(table_name);
</syntaxhighlight>

== Exporting and importing ==

=== Saving database properties to text file ===

==== Database objects only ====

From the command line: 

<syntaxhighlight lang="bash">
$ sqlite3 [database_name] '.schema' > /path/to/export-file.txt
</syntaxhighlight>

==== Database objects and data ====

From the command line:

<syntaxhighlight lang="bash">
$ sqlite3 [database_name] '.dump' > /path/to/export-file.txt
</syntaxhighlight>

From the `sqlite` command prompt <ref>[http://www.sqlitetutorial.net/sqlite-dump/|How To Use The SQLite Dump Command] &mdash; SQLite Tutorial</ref>:

<syntaxhighlight lang="bash">
sqlite> .output /path/to/export-file.sql
sqlite> .dump
</syntaxhighlight>

==== Single table definition ====

From the command line:

<syntaxhighlight lang="bash">
$ sqlite3 [database_name] '.schema [tablename]' > /path/to/export-file.txt
</syntaxhighlight>

=== Import from a .sql file ===

From a sqlite3 prompt:

<syntaxhighlight lang="sql">
sqlite> .read db.sql
</syntaxhighlight>

== Queries ==

=== Controlling query results format ===

<syntaxhighlight lang="sql">
.mode columns
.header on 
</syntaxhighlight>

== 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`.
<syntaxhighlight lang="sql" enclose="div">
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;
</syntaxhighlight>

=== Autoincrement primary key ===

Create a primary key with

<syntaxhighlight lang="sql">
CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    val TEXT
);
</syntaxhighlight>

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 <ref>[http://www.sqlite.org/faq.html#q1|SQLite Frequently Asked Questions]&mdash;sqlite.org</ref>: 

<syntaxhighlight lang="sql">
INSERT INTO my_table (id, val) VALUES (NULL, 'some value');
</syntaxhighlight>

=== `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.<ref>[https://stackoverflow.com/questions/15301643/sqlite3-forgets-to-use-foreign-keys SQLite3 forgets to use foreign keys]&mdash;StackOverflow</ref>

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`:

<pre>
PRAGMA foreign_keys = ON;
</pre>

==== 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 ==

<syntaxhighlight lang="sql">
.quit
</syntaxhighlight>
or 
<syntaxhighlight lang="sql">
.exit
</syntaxhighlight>

== Notes ==
<references />