aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@archive.org>2018-12-18 19:38:22 -0800
committerBryan Newbold <bnewbold@archive.org>2018-12-18 19:38:22 -0800
commit96bc49924091126646c545b567252f3916ba0caf (patch)
tree09bd43999c0a9444368a7145e69f728c34978b99
parent6f60f99bbf4b18f2576fb2a021b1ec93f084f651 (diff)
downloadsqlite-notebook-96bc49924091126646c545b567252f3916ba0caf.zip
sqlite-notebook-96bc49924091126646c545b567252f3916ba0caf.tar.gz
first draft
-rw-r--r--README.md12
-rwxr-xr-xsqlite-markdown.py66
2 files changed, 78 insertions, 0 deletions
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()