SQLite Source Code Blocks in Org Mode

Table of Contents

Org Mode support for SQLite

Introduction

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-do-load-languages
 '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.

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

Sessions

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.

Documentation from the http://orgmode.org/worg/ website (either in its HTML format or in its Org format) is licensed under the GNU Free Documentation License version 1.3 or later. The code examples and css stylesheets are licensed under the GNU General Public License v3 or later.