ob-doc-sqlite.org 7.2 KB

Org Mode support for SQLite

Template Checklist [12/12] noexport


SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

Requirements and Setup

Installation and configuration of SQLite software

See the SQLite download page for installation and configuration instructions.

Emacs configuration

Emacs' SQL mode supports the SQLite server. SQL mode is used to edit SQLite source code blocks.

Org-mode configuration (org-babel-do-load-languages)

The ob-sqlite.el file is part of Emacs. To activate SQLite as a Babel language, simply add (sqlite . t) to the =org-babel-do-load-languages= function in your Emacs configuration file, as shown below:

   'org-babel-load-languages (quote ((emacs-lisp . t)
                                      (sqlite . t)
                                      (R . t)
                                      (python . t))))

Babel uses the SQLite command line shell sqlite3 to evaluate SQL statements. The name of the shell is held in the variable =org-babel-sqlite3-command=.

Org Mode Features for SQLite Source Code Blocks

Header Arguments

Language-specific default values

There are no language-specific default header arguments for SQLite.

Language-specific header arguments

There are 11 SQLite-specific header arguments.

a string with the name of the file that holds the SQLite
database. Babel requires this header argument.
if present, turn on headers in the output format. Headers
are also output with the header argument =:colnames yes=.
if present, set the SQLite dot command =.echo= to ON.
if present, set the SQLite dot command =.bail= to ON.
the default SQLite output format for Babel SQLite source
code blocks.
an SQLite output format that outputs a table-like form
with whitespace between columns.
an SQLite output format that outputs query results as
simple HTML tables.
an SQLite output format that outputs query results with one
value per line.
an SQLite output format that outputs query results with the
separator character between fields.
a string that specifies the separator character used by the
SQLite `list' output mode and by the SQLite dot command =.import=.
a string to use in place of NULL values.


It is possible to pass variables to sqlite. Variables can be of type table or scalar. Variables are defined using :var= and referred in the code block as $.

Table variables
Table variables are exported as a temporary csv file that
can then be imported by sqlite. The actual value of the variable is the name of temporary csv file.
Scalar variables
This is a value that will replace references
to variable's name. String variables should be quoted; otherwise they are considered a table variable.


SQLite sessions are not supported.

Result Types

SQLite source code blocks typically return the results of a query. The header arguments :csv, :column, :line, :list, and =:html= determine the output format.

Examples of Use

Hello World!

,#+name: sqlite-populate-test ,#+header: :results silent ,#+header: :dir ~/temp/ ,#+header: :db test-sqlite.db ,#+begin_src sqlite create table greeting(one varchar(10), two varchar(10)); insert into greeting values('Hello', 'world!'); ,#+end_src

,#+name: sqlite-hello ,#+header: :list ,#+header: :separator \ ,#+header: :results raw ,#+header: :dir ~/temp/ ,#+header: :db test-sqlite.db ,#+begin_src sqlite select * from greeting; ,#+end_src

,#+results: sqlite-hello Hello world!

Note that db and dir together specify the path to the file that holds the SQLite database.

Using scalar variables

In this example we create a variable with the name of the relation to query and a value to use in a query where clause. Note that the replacement excludes the quotes of string variables.

,#+BEGIN_SRC sqlite :db /tmp/rip.db :var rel="tname" n=300 :colnames yes
drop table if exists $rel;
create table $rel(n int, id int);
insert into $rel(n,id) values (1,210), (3,800);
select * from $rel where id > $n;

| 3 | 800 |

Using table variables

We can also pass a table to a query. In this case, the contents of the table are exported as a csv file that can then be imported into a relation:

,#+NAME: tableexample
| id |  n |
|  1 |  5 |
|  2 |  9 |
|  3 | 10 |
|  4 |  9 |
|  5 | 10 |

,#+begin_src sqlite :db /tmp/rip.db :var orgtable=tableexample :colnames yes
drop table if exists testtable;
create table testtable(id int, n int);
.mode csv testtable
.import $orgtable testtable
select n, count(*) from testtable group by n;

|  n | count(*) |
|  5 |        1 |
|  9 |        2 |
| 10 |        2 |