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 '',

Answer 2

You can use get method to check whether that value in CustomFields

is available if so check the length of the list and then get the value of the dictionary in that list using the same get method.

For example:

customfield_value = (x['CustomFields'][0]).get("Value",None) if len(x['CustomFields'])>0 else None

This will return None if the value is not present in the index 0. you can follow the same for getting values from other 2 indices. If you didn't understand please comment it 'll explain further.

Answer 3

Final Script. Thanks for the help!

   c.executemany("INSERT INTO phil_targetproccess (ResourceType, Id, Name, StartDate, EndDate, TimeSpent, CreateDate, "
          "ModifyDate, LastStateChangeDate, ProjectName, EntityStateName, RequestTypeName, AssignedTeamMember1, "
          "AssignedTeamMember2, AssignedTeamMember3, AssignedTeamMember4, 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'],
            x['Assignments']['Items'][0]['GeneralUser']['FirstName'] + ' ' + x['Assignments']['Items'][0]['GeneralUser']['LastName'] if len(x['Assignments']['Items']) > 0 else None,
            x['Assignments']['Items'][1]['GeneralUser']['FirstName'] + ' ' + x['Assignments']['Items'][1]['GeneralUser']['LastName'] if len(x['Assignments']['Items']) > 1 else None,
            x['Assignments']['Items'][2]['GeneralUser']['FirstName'] + ' ' + x['Assignments']['Items'][2]['GeneralUser']['LastName'] if len(x['Assignments']['Items']) > 2 else None,
            x['Assignments']['Items'][3]['GeneralUser']['FirstName'] + ' ' + x['Assignments']['Items'][3]['GeneralUser']['LastName'] if len(x['Assignments']['Items']) > 3 else None,
            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 '')
           for x in json_response['Items']])