From 96bc49924091126646c545b567252f3916ba0caf Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Tue, 18 Dec 2018 19:38:22 -0800
Subject: first draft

---
 README.md          | 12 ++++++++++
 sqlite-markdown.py | 66 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 78 insertions(+)
 create mode 100755 sqlite-markdown.py

diff --git a/README.md b/README.md
index c31ae4d..7fafc49 100644
--- a/README.md
+++ b/README.md
@@ -1,3 +1,15 @@
 
 An un-inventively named quick script to process markdown documents, executing
 in-line SQL statements against a sqlite database.
+
+You write a markdown file with code tagged `sql`, then run this script against
+it, along with a local sqlite3 database file, and you get HTML output with the
+query results in table form. Sort of like a crude/simple jupyter notebook.
+
+Requires the python package `mistune`. Try something like:
+
+    # debian/ubuntu
+    sudo apt install python3-mistune
+
+    # elsewhere
+    sudo pip3 install mistune
diff --git a/sqlite-markdown.py b/sqlite-markdown.py
new file mode 100755
index 0000000..3c9e72c
--- /dev/null
+++ b/sqlite-markdown.py
@@ -0,0 +1,66 @@
+#!/usr/bin/env python3
+
+import sys
+import sqlite3
+import mistune
+import argparse
+
+class SqliteMarkdownRenderer(mistune.Renderer):
+
+    def __init__(self, conn):
+        super(SqliteMarkdownRenderer, self).__init__()
+        self.conn = conn
+
+    def block_code(self, code, lang):
+        if lang.strip().lower() == 'sql':
+            # remove comment lines
+            core = code.strip()
+            code = '\n'.join(
+                [line.strip() for line in code.split('\n') if not (line.strip().startswith('#') or not line.strip())])
+            core = code.strip()
+            sys.stderr.write("executing: {}\n".format(code.strip()))
+            cursor = self.conn.cursor()
+            result = cursor.execute(code)
+            ret = ""
+            if cursor.description is None:
+                ret = "(empty result)"
+            else:
+                ret += "<table>\n  <thead><tr>\n"
+                for k in cursor.description:
+                    ret += "  <th>{}</th>\n".format(k[0])
+                ret += "</tr></thead>\n"
+                if not result.rowcount:
+                    ret += "<tr><td>(no rows returned)</td></tr>"
+                for row in result:
+                    ret += "<tr>\n"
+                    for v in row:
+                        if v is None:
+                            v = ''
+                        if type(v) == str and (v.startswith("https://") or v.startswith("http://")):
+                            ret += '  <td><a href="{}">{}</a></td>\n'.format(v, v)
+                        elif type(v) == str and v.startswith("10."):
+                            ret += '  <td><a href="https://doi.org/{}">{}</a></td>\n'.format(v, v)
+                        else:
+                            ret += '  <td>{}</td>\n'.format(v)
+                    ret += "</tr>\n"
+                ret += "</table>"
+            ret += "<pre><b>QUERY:</b> {}</pre>\n<br>".format(code)
+            return '<div style="margin: 1em 3em 1em 3em; "><code>' + ret + "</code></div>"
+        else:
+            return "\n<code>" + code + "</code>\n"
+
+def main():
+    parser = argparse.ArgumentParser()
+    parser.add_argument("markdown_file",
+        default=sys.stdin, type=argparse.FileType('rt'))
+    parser.add_argument("sqlite_db_path",
+        type=str)
+    args = parser.parse_args()
+
+    conn = sqlite3.connect(args.sqlite_db_path)
+    renderer = SqliteMarkdownRenderer(conn)
+    markdown = mistune.Markdown(renderer=renderer)
+    print(markdown(args.markdown_file.read()))
+
+if __name__ == '__main__':
+    main()
-- 
cgit v1.2.3