1

Basically I have some JSON data that I want to put in a MySQL db and to do this I'm trying to get the contents of a dictionary in a cursor.execute method. My code is as follows:

for p in d['aircraft']:
with connection.cursor() as cursor:
print(p['hex'])
sql = "INSERT INTO `aircraft` (`hex`, `squawk`, `flight`, `lat`, `lon`, `nucp`, `seen_pos`, " \
                          "`altitude`, `vert_rate`, `track`, `speed`, `messages`, `seen`, `rssi`) " \
                          "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"

cursor.execute(sql, (p['hex'], p['squawk'], p['flight'], p['lat'], p['lon'], p['nucp'], p['seen_pos'], p['altitude'], p['vert_rate'], p['track'], p['speed'], p['messages'], p['seen'], p['rssi']))
print('entered')
connection.commit()

The issue is that any value in the dictionary can be null at any time and I need to find out how to handle this. I've tried to put the code in a try catch block and 'pass' whenever a KeyError exception is raised but this means a record is completely skipped when it has a null value. I've also tried to write a load of if blocks to append a string with the value of the dictionary key but this was pretty useless.

I need to find a way to put a dictionary in my db even if it contains null values.

hiddensquid
  • 33
  • 5

1 Answers1

2

You can use the dict.get() method, or construct a defaultdict that returns None for missing keys:

import collections


keys = ['hex', 'squawk', 'flight', 'lat', 'lon', 'nucp', 'seen_pos', 
        'altitude', 'vert_rate', 'track', 'speed', 'messages', 'seen',
        'rssi']

for p in d['aircraft']:
    with connection.cursor() as cursor:
        sql = "INSERT INTO `aircraft` (`hex`, `squawk`, `flight`, `lat`, `lon`, `nucp`, `seen_pos`, " \
                          "`altitude`, `vert_rate`, `track`, `speed`, `messages`, `seen`, `rssi`) " \
                          "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"

        # Could also use a defaultdict
        cursor.execute(sql, tuple(p.get(key) for key in keys))
        print('entered')
    connection.commit()

For more examples using dict.get(), see this answer: https://stackoverflow.com/a/11041421/1718575

This assumes that SQL will do the right thing when None is provided. If you want to use a string 'NULL', you can supply that as the second argument to dict.get().

NicholasM
  • 4,557
  • 1
  • 20
  • 47