title: Create Json file from multiple tables from MySQL DB
body: <p>I have 2 different tables(Person and sport) in MySQL database. I would like to create a json file from these 2 tables in the following format. Person table has first_name, middle_name and last_name columns and sport table has sport_name and league_name columns. Logically each person may have more than one sport item. Please help me</p>
  
  <pre><code>“score”: {
       “ps”:{
          “person”:{
              “first_name”: "fName1"
              “middle_name”: "MName1"
              “last_name”: "Lname1"               
              "sports":{
                  "sport":{
                      “sport_name”: "sport1"
                      “league_name”: "legue1"
                  }
                  "sport":{
                      “sport_name”: "sport2"  
                      “league_name”: "legue2"
                  }
               }
          }
          “person”:{
              .......
          }
      }
  }
  </code></pre>
  
  <hr>
  
  <p>I tied the following code and answer but I would like to see the sport as sub array
  I tried the following code. I would like to have sport as sub array of person.</p>
  
  <pre><code>#!/usr/bin/env python
  import MySQLdb
  import json
  import collections
  from collections import defaultdict 
  class Dict(defaultdict):
      def __init__(self):
          defaultdict.__init__(self, Dict)
      def __repr__(self):
          return dict.__repr__(self) 
  
  db = MySQLdb.connect("Server","userName","pawd","dbName" )
  
  # prepare a cursor object using cursor() method
  cursor = db.cursor()
  sql = "SELECT person_id,first_name,last_name,gender FROM scorefoliodev1.person "
  cursor.execute(sql)
  
  rows = cursor.fetchall()
  objects_list = []
  for row in rows:
      d = Dict()
      d["Person"]['person_id'] = row[0]
      d["Person"]['first_name'] = row[1]
      d["Person"]['last_name'] = row[2]
      d["Person"]['gender'] = row[3]
      objects_list.append(d)
  
  j = json.dumps(objects_list, indent=4)
  print j
  objects_file = 'person_info55.js'
  f = open(objects_file,'w')
  print &gt;&gt; f,j 
  db.close()
  </code></pre>
  
  <p>Answer</p>
  
  <pre><code>[
      {
          "Person": {
              "person_id": 111, 
              "gender": "Male", 
              "first_name": "FName1", 
              "last_name": "LName"
          }
      }, 
      {
          "Person": {
              "person_id": 112, 
              "gender": "Male", 
              "first_name": "fname2", 
              "last_name": "lName2"
          }
      }
  ]
  </code></pre>