How to easily run SQL queries against your org-mode spreadsheet by empowering termsql .
I tend to work a lot in the console, and with org-mode. The latter is this wonderful contraption encompassing the ability to organize notes, execute code & do some spreadsheet calculations. Pretty priceless for a terminal dweller who’s an emacs user.
Thing is, org-mode doesn’t (yet) have any SQL support built in for its pesky tables. So, unless you want to export your table to .tsv or the like, and then import in mysql/postgresql/sqlite in order to perform any nontrivial queries, a shortcut really comes in handy.
That little buddy takes in an org-mode table on the stdin, and outputs something that’s parseable by termsql, which, if you don’t know what it is, is one of the several tools that allow a user to quickly import a text (csv,tsv..) file into an sqlite engine and run queries against it.
Enjoy!
module.exports = "#!/usr/bin/env python\nimport re,sys\nimport csv\n\"\"\"\n\nwe've got two modes of operation:\n\n# extract a table from within an 'hours' heading, directly feedable into termsql\n# echoing to stdout valid termsql input\ncat table.org | tsorg.py 'hours' | termsql -r5 -0 -1 'select * from tbl' \n\n# extract a table from a default-formatted termsql output (https://github.com/tobimensch/termsql)\n# with the -0 and -1 flags, echoing to stdout a valid org-mode table.\ncat table.csv | tsorg.py \n\"\"\"\n\ndef parse_org(stdin,section_filter,delimiter=\"\\t\",whitespaces=False,header_separator=False,rjust=False):\n in_header = was_in_section = in_section=False \n\n if section_filter:\n # seek & skip header\n for ln in stdin:\n l = ln.strip()\n snres = re.compile('^([\\*]+) (.+)').search(l)\n if snres: section_name = snres.group(2)\n was_in_section = snres and snres.group(2)==section_filter and True or was_in_section\n in_section=was_in_section and l.startswith('|') and True or False\n if not (in_section and section_name==section_filter): continue #not our section\n break #got to the joovce!\n\n in_ = [ln for ln in stdin]\n if in_[0].startswith('|---'): del in_[0] #hackitty\n if in_[-1].startswith('|---'): del in_[-1] #hackitty \n \n del in_[1]# get rid of table header line\n mins,out,headers,cnt = parse_ts(in_,delimiter=delimiter,doprint=False,input_delimiter='|',line_trim_s=1,line_trim_to=-1,do_strip=True)\n\n print_op(mins,out,headers,cnt,delimiter=delimiter,header_separator=header_separator,rjust=rjust)\n \n\ndef print_op(mins,out,headers,cnt,delimiter='|',header_separator=True,rjust=True):\n def pr(r):\n if rjust:\n arr = [r[i].rjust(mins[i]) for i in range(len(r))]\n else:\n arr = r\n print delimiter+(delimiter.join(arr))+delimiter\n pr(headers)\n if header_separator: print delimiter+('-'*(sum([v+1 for v in mins.values()])-1))+delimiter\n for r in out: pr(r)\n \ndef parse_ts(stdin,delimiter,doprint=True,input_delimiter=',',line_trim_s=0,line_trim_to=None,do_strip=False):\n cnt=0\n mins={}\n out=[]\n reader = csv.reader(stdin,delimiter=input_delimiter)\n for ln in reader:\n r= ln\n r = r[line_trim_s:line_trim_to]\n if do_strip: r = [el.strip() for el in r]\n cnt+=1\n if cnt==1:\n headers =r\n else:\n out.append(r)\n for i in range(len(r)):\n if i not in mins: mins[i]=0\n if mins[i]<len(r[i]): mins[i]=len(r[i])\n if not doprint: return (mins,out,headers,cnt)\n print_op(mins,out,headers,cnt,delimiter='|')\n \n \n\nif __name__=='__main__':\n section_filter = len(sys.argv)>1 and not sys.argv[1].startswith('--') and sys.argv[1] or None\n args = dict([a[2:].split('=') for a in sys.argv if a.startswith('--')])\n \n whitespaces = bool(args.get('whitespaces',False))\n header_separator = bool(args.get('header-separator',False))\n rjust=bool(args.get('rjust',False))\n\n delimiter = args.get('delimiter',\"\\t\")\n parse_org(sys.stdin,section_filter,delimiter=delimiter,whitespaces=whitespaces,header_separator=header_separator,rjust=rjust)\n \n"© 2023 Web GMA R&D Ltd.