Getting Data from Kiva Site

# importing 
import mysql.connector
import urllib.request as urllib
import json
import time
import pandas as pan
from datetime import datetime

# url of the API to get data
url = 'http://api.kivaws.org/v1/teams/'
teams = {}


# to load json file
with open('team.json') as data_file: 
    TeamData = json.load(data_file)
    
len(TeamData)


infile = []
for team in TeamData:
    infile.append(int(team))
len(infile)
missing = []
for i in range(1,11886,1):
    if i not in infile:
        missing.append(i)
len(missing) + len(infile)

11885

# getting data and storing in json file
#for i in range(1,11886,1):
for i in missing:
    #i =1
    try:
        handle = urllib.urlopen(str(url+str(i)+'.json'))
    except:
        continue
    # reading response 
    item_html =  handle.read().decode('utf-8')
    # converting bytes to str
    data = str(item_html)
    # converting to json
    data = json.loads(data)
    team = data['teams']
    
    teams[i]=team
    
    if i % 100 == 0:
        outfile = open("team.json", "w")
        json.dump(teams, outfile, sort_keys = True, indent = 2, ensure_ascii=True)
        outfile.close()        
    
    #print (i, team[0]['member_count'])
    time.sleep(2)

outfile = open("team.json", "w")
json.dump(teams, outfile, sort_keys = True, indent = 2, ensure_ascii=True)
outfile.close()

''' ================================ D A T A B A S E  C O N N E C T I O N ======================================'''

cnx = mysql.connector.connect(user='root', password='root',
                              host='localhost', port='8889',
                              database='Kiva')              
cursor = cnx.cursor()



# loading team data
with open('team.json') as data_file:
    teamData = json.load(data_file)
data_file.close()
    

Team_TABLE = '''
CREATE TABLE TEAM
( category VARCHAR (50),
  description VARCHAR(1000),
  id INTEGER(20),
  loan_because VARCHAR(8000),
  loan_count INTEGER(100),
  loaned_amount INTEGER(100),
  member_count INTEGER(100),
  membership_type VARCHAR(24),
  name VARCHAR(150),
  shortname VARCHAR(100),
  team_since DATETIME,
  website_url VARCHAR(250),
  whereabouts VARCHAR(200) , 
  
  CONSTRAINT PK
      PRIMARY KEY(id)
  );'''
cursor.execute(Team_TABLE)

ref = len(teamData)
# insert team to  table
for team in teamData:
    # team = teamData['2']

    insertTeam = []
    insertTeam.append(teamData[team][0]['category'])
    try:
        insertTeam.append(teamData[team][0]['description'])
    except:
        insertTeam.append(None)
    insertTeam.append(teamData[team][0]['id'])
    try:
        insertTeam.append(teamData[team][0]['loan_because'])
    except:
        insertTeam.append(None)
    insertTeam.append(teamData[team][0]['loan_count'])
    insertTeam.append(teamData[team][0]['loaned_amount'])
    insertTeam.append(teamData[team][0]['member_count'])
    insertTeam.append(teamData[team][0]['membership_type'])
    insertTeam.append(teamData[team][0]['name'])
    insertTeam.append(teamData[team][0]['shortname'])
    insertTeam.append(teamData[team][0]['team_since'][:10])
    try:
        insertTeam.append(teamData[team][0]['website_url'])
    except:
        insertTeam.append(None)
    try:        
        insertTeam.append(teamData[team][0]['whereabouts'])
    except:
        insertTeam.append(None)
  
       
    try:
        cursor.execute('''INSERT INTO `Kiva`.`TEAM`
    (`category`,`description`,`id`,`loan_because`,`loan_count`,`loaned_amount`,`member_count`,`membership_type`,
    `name`,`shortname`,`team_since`,`website_url`,`whereabouts`)
    VALUES
    (%s ,%s, %s,%s ,%s ,%s ,%s ,%s ,%s ,%s,%s,%s,%s);''' ,(insertTeam))
    except:
        continue
    
    
    
cnx.commit()
cnx.close()