notes from /dev/null

by Charles Choi 최민수

27 Nov 2023

Running SQL Queries on Org Tables

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:

  1. 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 TABLE statement using the above column types.
    • Store the table in an in-memory database.
  2. Run a SQL query on the above created SQLite table via Org Babel and render its result as an Org table.


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.


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:

  • table which is a reference to the named table, and
  • table-name which is the string representation of table above.

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.

#+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;

A couple of observations of the above generated code:

  1. The database is in-memory allowing for flexibility in changing the Org table.
  2. Every time the code block is executed, the SQL table is dropped if it already exists.
  3. The SQLite column types are determined by examining each column element. (Note: implementation is O(n))
  4. An example SQL SELECT statement 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:

| site | city          | totals |
|    4 | Berlin        |    197 |
|    9 | New York      |    243 |
|    6 | Seoul         |    285 |
|    1 | San Francisco |    388 |


The Org Elisp code block cc/org-table-to-sqlite is stored in the file 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 "")

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|
#+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.


emacs   org mode

Past Articles


Flavor #8

Playing vinyl again at Harlan Records on November 16, 2023 for happy hour. Come through!

read more

Customizing the Emacs Tools Menu

If the default Emacs Tools menu is not to your liking, you can always change it. This post shows you how.

read more

ICYMI: Emacs World Clock

Always know what time it is “over there” in Emacs with world-clock.

read more

Flavor #6 at Harlan Records SF (Edited)

Yours truly DJing at Harlan Records, San Francisco on September 28, 2023.

read more

Enhancing Navigation in Emacs View Mode

Navigation can be more than just using the space bar in Emacs view mode.

read more

Using Bookmarks in Emacs like you do in Web Browsers

I like the user experience of managing bookmarks in web browsers. I want to do the same in Emacs. Here are my customizations to achieve that.

read more

Addendum - Defining Repeating Org Agenda Tasks

Using a repeating timestamp for Org tasks can be problematic. You might be better off making copies of the same task, which Org makes easy with the command org-clone-subtree-with-time-shift.

read more

Defining Org Agenda Tasks (or, I've Been Using Org Agenda Wrong)

While I've been using Org Agenda for years, in many ways I've been really using it wrong, resulting with me being surprised, particularly around scheduled timestamps. This post offers guidance on how to implement different kinds of tasks in Org.

read more

Tuning Emacs to Write Prose in Org and Markdown

Some tips to help you write prose in Org or Markdown when in Emacs.

read more

Page 1 / 10   >



Feeds & TagsGet Captee for macOS

Powered by Pelican