How to ignore "IndexError: list index out of range" on SQL Execute Insert statement

Question

I am working with Python 2.7 to extract data from a JSON API and push it into a SQL-Server table. I am having trouble with inserting data into the database where some of the entries returned from the JSON response are missing a section of the dictionary. IE, "CustomFields": 90% of the entries have information, however 10% don't therefore I get an index error eg

"CustomFields":[
],

vs

"CustomFields":[
{
"Type":"foo",
"Name":"foo",
"Value":"foo"
},
{
"Type":"foo",
"Name":"foo",
"Value":"foo"
},
{
"Type":"foo",
"Name":"foo",
"Value":"foo"
},

What would I change so that if I get a missing index, replace those with 'NULL' entries into the database.

response = '*API URL*'
json_response = json.loads(urllib2.urlopen(response).read())

conn = pypyodbc.connect(r'Driver={SQL Server};Server=*Address*;Database=*DataBase*;Trusted_Connection=yes;')
conn.autocommit = False
c = conn.cursor()

c.executemany("INSERT INTO phil_targetproccess (ResourceType, Id, Name, StartDate, EndDate, TimeSpent, CreateDate, ModifyDate, LastStateChangeDate, ProjectName, EntityStateName, RequestTypeName, AssignedTeamMember#1, Area, SubArea, BusinessTeam) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ,
             [(x['ResourceType'],
               x['Id'],
               x['Name'],
               (parse_date(x['StartDate'])),
               (parse_date(x['EndDate'])),
               x['TimeSpent'],
               (parse_date(x['CreateDate'])),
               (parse_date(x['ModifyDate'])),
               (parse_date(x['LastStateChangeDate'])),
               x['Project']['Name'],
               x['EntityState']['Name'],
               x['RequestType']['Name'],
               y['GeneralUser']['FirstName']+' '+y['GeneralUser']['LastName'],
               x['CustomFields'][0]['Value'],
               x['CustomFields'][1]['Value'],
               x['CustomFields'][2]['Value'])
               for x in json_response['Items']
                  for y in x['Assignments']['Items']])

Many thanks.

Answer 1

I think your issue is here

 x['CustomFields'][0]['Value'],
 x['CustomFields'][1]['Value'],
 x['CustomFields'][2]['Value']

When CustomFields has no elements

Try

 x['CustomFields'][0]['Value'] if len(x['CustomFields']) > 0 else '',
 x['CustomFields'][1]['Value'] if len(x['CustomFields']) > 1 else '',
 x['CustomFields'][2]['Value'] if len(x['CustomFields']) > 2 else '',