SQL to JSON (SQL2JSON)

I tend to make SQL server the workhorse of most projects. Obviously it's a powerful tool, but it also works great as a central data store, on the basis that any tools worth their salt (e.g. python, R, octave, qlikvew, tableau etc etc) have mature odbc connectors that allow me to connect in, do work, and possibly deliver the results back to SQL with little bother.

However, since making use of more bespoke visualisation tools like D3 and Sigma.js, which tend to use the JSON file format for their online data storage, I've found myself awkwardly crafting JSONs with a lot of string manipulation and 'for xml path' tricks in SQL.

Recently I decided to fix this once and for all by knocking up a quick SQL->JSON converter in python.

Click permalink to read more...

import pyodbc
import json
import collections
    
if __name__ == '__main__':
    print("Connecting")

    '''CONFIGURE HERE'''
    svr = 'localhost\SQLEXPRESS'
    db = 'TestDB'
    nodeQry = 'select * from outputNodes order by NodeID'
    edgeQry = 'select * from outputEdges'
    '''END'''
	
    connstr = 'DRIVER={SQL Server};SERVER=' + svr + ';DATABASE=' + db + ';Trusted_Connection=yes'
    conn = pyodbc.connect(connstr)
    cursor = conn.cursor()
    
    print("Getting Nodes")
    
    # Get the Nodes
    cursor.execute(nodeQry)     
    rows = cursor.fetchall()

    nodes = []
    for row in rows:
        d = collections.OrderedDict()
        d['name'] = row.Name
        nodes.append(d)
     
    print("Getting Edges")
    
    cursor.execute(edgeQry)
     
    rows = cursor.fetchall()
     
    links = []
    for row in rows:
        d = collections.OrderedDict()
        d['source'] = row.Source
        d['target'] = row.Target
        d['value'] = row.Size
        links.append(d)
    
    # Close the SQL Connection
    conn.close()
    
    print("Creating JSON")
    
    oJson = collections.OrderedDict()
    oJson['nodes'] = nodes
    oJson['links'] = links
        
    j = json.dumps(oJson)
    objects_file = 'sankey.json'
    f = open(objects_file,'w')
    print >> f, j
    
    input("Complete. Press Enter to close.")