Running SQL Queries on Org Tables
27 Nov 2023 Charles Choi
One of the best things in Org Mode are tables and if you know SQL, you have at your disposal the ability to process your tables like a SQL database. Even better, you can run a SQL query on your table without the overhead of manually creating the database. This post shows you how.
SQL Query on Org Table Workflow
To implement an SQL query on an Org table we do the following:
- Convert the Org table to a SQLite table via the Org Babel SQLite integration.
- Inspect the Org table columns to determine its SQL type.
- Auto-generate the SQL
CREATE TABLEstatement using the above column types. - Store the table in an in-memory database.
- Run a SQL query on the above created SQLite table via Org Babel and render its result as an Org table.
Requirements
All code here has been tested on Emacs 29.1 and Org 9.6.11.
- Have Org Babel SQLite support installed. Instructions for doing this are provided on the Org website.
Demo
Given the following named table example_table, find the aggregate total units per city.
#+NAME: example_table
| site | city | units |
|------+---------------+-------|
| 1 | San Francisco | 182 |
| 2 | San Francisco | 82 |
| 3 | San Francisco | 124 |
| 4 | Berlin | 105 |
| 5 | Berlin | 92 |
| 6 | Seoul | 104 |
| 7 | Seoul | 84 |
| 8 | Seoul | 97 |
| 9 | New York | 103 |
| 10 | New York | 140 |
To convert the above Org table to a SQLite table, we will call the named code block cc/org-table-to-sqlite with two arguments as shown below:
tablewhich is a reference to the named table, andtable-namewhich is the string representation oftableabove.
The table name must be SQL-legal.
#+CALL: cc/org-table-to-sqlite(table=example_table, table-name="example_table")
Invoking C-c C-c (org-ctrl-c-ctrl-c) on the above will output the following generated Org SQLite code block.
#+RESULTS:
#+begin_src sqlite :db ":memory:" :var orgtable=example_table :colnames yes
drop table if exists example_table;
create table example_table(site INT, city TEXT, units INT);
.import $orgtable example_table
-- Edit SQL Query Below
select site, city, units from example_table;
#+end_src
A couple of observations of the above generated code:
- The database is in-memory allowing for flexibility in changing the Org table.
- Every time the code block is executed, the SQL table is dropped if it already exists.
- The SQLite column types are determined by examining each column element. (Note: implementation is O(n))
- An example SQL
SELECTstatement is generated using the Org table header.
As we want to determine the aggregate total units per city, let’s modify the above SELECT statement as follows:
select site, city, sum(units) as totals from example_table group by city order by totals;
Invoking C-c C-c on the generated and edited code block yields the following result:
#+RESULTS:
| site | city | totals |
|------+---------------+--------|
| 4 | Berlin | 197 |
| 9 | New York | 243 |
| 6 | Seoul | 285 |
| 1 | San Francisco | 388 |
Implementation
The Org Elisp code block cc/org-table-to-sqlite is stored in the file cc-org-table-to-sql.org and is posted as a GitHub gist (note click on “Raw” to see all Org markup).
It can be loaded via the org-babel-lob-ingest command as follows:
(org-babel-lob-ingest "cc-org-table-to-sql.org")
Remembering how to invoke the code block cc/org-table-to-sqlite can be bit much. Thankfully we can use YASnippet to help. Shown below is a snippet for creating a named table and call to the code block cc/org-table-to-sqlite referencing said named table.
# name: org-table-to-sqlite
# key: otsql_
# --
#+NAME: ${1:example_table}
| ${2:a} | ${3:b} |
|---|---|
| 1| 10|
$0
#+CALL: cc/org-table-to-sqlite(table=$1, table-name="$1")
Closing Thoughts
Turning an Org table into an ad-hoc SQL database opens a world of possibilities for data journaling and analysis. This post merely scratches the surface of what can be done.