Is there way to convert pymssql query to json based on first column values?

Question

I am trying to convert pymssql query to json where first column "value" will be "key" and corresponding columns will be list of "key:value".

I have tried using jsondumps but get the "Cursor is not JSON serializable" error:

    `conn = pymssql.connect(server, port, db)
     cursor = conn.cursor('select u_business_service_display_value as Business, name, host_name as hostname, install_status, ip_address, used_for from cmdb_ci_server where u_patching_director_display_value = <Name> AND install_status <> "Retired" AND install_status <> "Pending Retirement" order by Business, hostname')
     for row in cursor:
         print("Business=%s, Name=%s, Hostname=%s, install_status=%s, ip_address=%s, used_for=%s" % (row['Business'], row['name'], row['hostname'], row['install_status'], row['ip_address'], row['used_for']))
     print json.dumps(results, indent=1)
     conn.close()`

Output is

- Business=AAA, Name=Value, Hostname=vaule, install_status=Retired, ip_address=<ip>, used_for=None
- Business=AAA, Name=Value, Hostname=vaule, install_status=Retired, ip_address=<ip>, used_for=None
- Business=BBB, Name=Value, Hostname=vaule, install_status=Installed, ip_address=<ip>, used_for=Prod
- Business=BBB, Name=Value, Hostname=vaule, install_status=Installed, ip_address=<ip>, used_for=Prod

Expected output

{
        "AAA":[
        { 
        "Hostname":"Value",
        "install_status":"Retired",
        "ip_address":"<ip>",
        "used_for":"None"
        },
        {
         "Hostname":"Value",
         "install_status":"Retired",
         "ip_address":"<ip>",
         "used_for":"None"
      }
   ],
   "BBB":[ 
      { 
         "Hostname":"Value",
         "install_status":"Installed",
         "ip_address":"<ip>",
         "used_for":"Prod"
      },
      { 
         "Hostname":"Value",
         "install_status":"Installed",
         "ip_address":"<ip>",
         "used_for":"Prod"
      }
   ]
}```

Answer 1

from collections import defaultdict
import json
d = defaultdict(list)
for row in cursor:
    values = dict()
    values['name'] = row['name']
    values['hostname'] = row['hostname']
    values['install_status'] = row['install_status']
    values['ip_address'] = row['ip_address'] 
    values['used_for'] = row['used_for]
    d[row['Business']].append(values)

with open('result.json', 'w') as fp:
    json.dump(d, fp)

• d is defaultdict with list argument so, in case you add key that is not in d it will create list as value when assigned first time.