Skip to content

Instantly share code, notes, and snippets.

@sKwa
Last active July 12, 2017 00:55
Show Gist options
  • Save sKwa/b7af8753f598c9666ad4cfdc05904346 to your computer and use it in GitHub Desktop.
Save sKwa/b7af8753f598c9666ad4cfdc05904346 to your computer and use it in GitHub Desktop.
SQL query to JSON format (as demo)
#!/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},
# ]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment