Skip to content

Instantly share code, notes, and snippets.

@moertel
Last active July 16, 2016 12:33
Show Gist options
  • Save moertel/b2b87dab860999249533da826054c789 to your computer and use it in GitHub Desktop.
Save moertel/b2b87dab860999249533da826054c789 to your computer and use it in GitHub Desktop.
Display a postgreSQL result set as bar chart
#!/usr/bin/env python
from optparse import OptionParser
import sys
import re
import colorama
# Original output:
# $ psql -c "select '2016-07-01'::date, 37483::int as value union select '2016-07-02'::date, 121782::int as value;"
# date | value
# ------------+--------
# 2016-07-01 | 37483
# 2016-07-02 | 121782
# (2 rows)
#
# Output when piped to this script:
# $ psql -c "select '2016-07-01'::date, 37483::int as value union select '2016-07-02'::date, 121782::int as value;" | python chart.py -x value -y date -s '*'
#
# 2016-07-01 | 37483 | ******************************* |
# 2016-07-02 | 121782 | **************************************************************************************************** |
#
def main():
parser = OptionParser(usage="Usage: %prog [options]", version="1.0")
parser.add_option("-x", "--horizontal", dest='xaxis', help="Name of the column to use as x-axis")
parser.add_option("-y", "--vertical", dest='yaxis', help="Name of the column to use as y-axis")
parser.add_option("-s", "--symbol", dest='symbol', default='*', help="Symbol to use for bar chart")
(options, args) = parser.parse_args()
result_set = ''
for line in sys.stdin:
result_set += line
#sys.stdout.write(result_set)
result_set_lines = result_set.split("\n")
filtered_result_set_lines = [list(map(lambda element:element.strip(), line.strip().split('|'))) for line in result_set_lines if not re.search('^[-+\s]+$', line) and not re.search('^\(\d{1,}\s+row(s)?\)$', line) and not line == '']
x = -1
y = -1
for index, column in enumerate(filtered_result_set_lines[0]):
if column == options.xaxis:
x = index
elif column == options.yaxis:
y = index
max_value_length = 0
max_value = 0
for t in filtered_result_set_lines[1:]:
if len(t[y]) > max_value_length:
max_value_length = len(t[y])
if abs(int(t[x])) > max_value:
max_value = abs(int(t[x]))
for t in filtered_result_set_lines[1:]:
print t[y].ljust(max_value_length), \
'|', \
str(t[x]).rjust(len(str(max_value))), \
'|', \
options.symbol.ljust(int(round(float(t[x])/float(max_value)*100)), options.symbol).ljust(100), \
'|'
if __name__ == '__main__':
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment