Org Code Block cc/org-table-to-sqlite

cc-org-table-to-sql.org

cc/org-table-to-sqlite

Org Babel code block to import an Org table into an in-memory SQLite DB to enable SQL queries on aforementioned table.

Parameters

• table : Org table object The Org table object is referenced via the #+NAME value assigned to the table. The table must have a header row identifying each column. This name value must be a SQL-legal name.
• table-name : string Name of table in string form. It must be identical to the name of the table parameter above.

Example

id	n	city
1	5	Barcelona
2	9	New York
3	10.0	San Francisco
4	2	Seoul
5	10	Berlin
6	7	Singapore

Code Block

(defun cc/org-extract-table-header (table)
  "Extract header row from the Org table TABLE.

This function presumes that TABLE has both header and data sections.
This method is intended to be called from an Org Babel Elisp code block."
  (cl-first table))

(defun cc/org-extract-table-data (table)
  "Extract data rows from the Org table TABLE.

This function presumes that TABLE has both header and data sections.
This method is intended to be called from an Org Babel Elisp code block."
  (cdr table))

(defun cc/list-integerp (data)
  "Predicate to test if all elements in the list DATA are of integer type."
  (seq-reduce '(lambda (x y) (and x y)) (mapcar 'integerp data) t))

(defun cc/list-floatp-or-integerp (data)
  "Predicate to test if all elements in the list DATA are of float or integer type."
  (seq-reduce '(lambda (x y) (and x y)) (mapcar '(lambda (x) (or (floatp x) (integerp x))) data) t))

(defun cc/list-stringp (data)
  "Predicate to test if all elements in the list DATA are of string type."
  (seq-reduce '(lambda (x y) (or x y)) (mapcar 'stringp data) nil))

(defun cc/table-column-sqlite-type (data)
  "Determine the SQL type of the elements in the list DATA."
  (cond ((cc/list-integerp data) "INT")
        ((cc/list-floatp-or-integerp data) "REAL")
        ((cc/list-stringp data) "TEXT")
        (t "TEXT")))

(defun cc/org-table-sqllite-table-create (table table-name query)
  "Emit SQLite code block using TABLE with name TABLE-NAME with QUERY.

TABLE - Org table content
TABLE-NAME - Org table name
QUERY - if non-0, then emit a default SQL query for TABLE-NAME

The SQLite table configured to be stored in-memory. A SQL query line is provided
to be subsequently edited.

This method is intended to be called from an Org Babel Elisp code block.
Returns string of SQLite statements implementing above described behavior."
  (let* ((header (cc/org-extract-table-header table))
         (data (cc/org-extract-table-data table))
         (rowsize (length data))
         (colsize (length (cl-first data))))

    (setq i 0)
    (setq temp-list (list))

    (while (< i colsize)
      (setq column-data (mapcar '(lambda (x) (nth i x)) (cdr table)))
      (setq temp-list (cons (cc/table-column-sqlite-type column-data) temp-list))
      (setq i (1+ i)))

    (setq formatted-arg-list (cl-mapcar (lambda (x y) (format "%s %s" x y )) header (reverse temp-list)))

    (setq result-list (list))

    (push (format "#+NAME: %s_sql" table-name) result-list)

    (push (format "#+begin_src sqlite :db \":memory:\" :var %s=%s :colnames yes" table-name table-name)
          result-list)

    (push (format "drop table if exists %s;" table-name)
          result-list)

    (push (format "create table %s(%s);"
                  table-name
                  (mapconcat 'identity formatted-arg-list ", "))
          result-list)

    (push (format ".import $%s %s" table-name table-name)
          result-list)

    (when (not (= query 0))
      (push "\n-- Edit SQL Query Below" result-list)
      (push (format "select %s from %s;" (mapconcat 'identity header ", ") table-name)
            result-list))

    (push "#+end_src"
          result-list)

    (print (mapconcat 'identity (reverse result-list) "\n"))))

(cc/org-table-sqllite-table-create table table-name query)

Click on "Raw" button above to see all Org markup.