SQL query to JSON format (as demo)

csv2json.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import json
from datetime import date

NEWLINE, DELIMITER = '\x01\n', '\x02'

def json_serial(obj):
    """JSON serializer for objects not serializable by default json code"""
    if isinstance(obj, datetime.date):
        return obj.isoformat()
    if obj == 'NULL':
        return 'null'
    raise TypeError ("Type %s not serializable" % type(obj))


# first line - col names
colnames = sys.stdin.readline()
colnames = colnames.rstrip(NEWLINE).split(DELIMITER)


# check for unique colnames otherwise raise error
if len(set(colnames)) != len(colnames):
    raise Exception('names of columns are not unique!')

# start
sys.stdout.write('[\n')
for row in  sys.stdin:
    row = row.rstrip(NEWLINE).split(DELIMITER)
    row = [field if field else None for field in row]
    sys.stdout.write(json.dumps(dict(zip(colnames, row)), default=json_serial))
    sys.stdout.write(',\n')
sys.stdout.write(']\n')


# $ vsql -U dbadmin -w letmein -F$'\002' -R$'\001\n' -P footer=off -Ac "select * from T" | python csv2json.py
# [
# {"date": "2000-01-01", "id": "1", "val": "rus"},
# {"date": "2000-01-01", "id": "1", "val": "usa"},
# {"date": "2000-01-02", "id": "1", "val": "usa"},
# {"date": "2000-01-03", "id": "1", "val": "eng"},
# {"date": "2000-01-01", "id": "2", "val": "afg"},
# {"date": "2000-01-02", "id": "2", "val": "eng"},
# {"date": null, "id": "4", "val": null},
# ]