Python: Retrieving "NULL" from nested dictionary when key is missing

Question

I'm processing some data that comes from nested dictionaries (over an API). I want to store the data into a database, I use psycopg2 to do so and I wrote some code to put together my SQL statements ("INSERT INTO mytable...").

Everything runs fine so far, but every now and then the data does not have all the required information. In rare cases one of the keys is missing. This causes my script to crash. So I tried some kind of error handling, because in the case of missing data, I just want to have "NULL" instead.

But, I only got it to work when I repeated the "try/except block" for every single piece of data. Isn't there an easier way to do this?

Here's an example:

mydict = {'name': {'firstname': 'Peter', 'surname': 'Pan'}, 'contact': {'hometown': 'Neverland', 'phone': '123-456'}}

sql = f"INSERT INTO mytable(firstname, surname, phone)\nVALUES\n("
sql += f"'{mydict['name']['firstname']}',"
sql += f"'{mydict['name']['surname']}',"
sql += f"'{mydict['contact']['phone']}');"

For the case of missing data I tried:

mydict = {'name': {'firstname': 'Peter', 'surname': 'Pan'}, 'contact': {'hometown': 'Neverland'}}

sql = f"INSERT INTO mytable(firstname, surname, phone)\nVALUES\n("
try:
    sql += f"'{mydict['name']['firstname']}',"
except KeyError:
    sql += 'NULL'
try:
    sql += f"'{mydict['name']['surname']}',"
except KeyError:
    sql += 'NULL'
try:
    sql += f"'{mydict['contact']['phone']}');"
except KeyError:
    sql += f"NULL);"

Answer 1

You can use collections.defaultdict to return "NULL" when a requested key doesn't exist. To transform the dictionary, you can use:

from collections import defaultdict
mydict = {
    'name': {'firstname': 'Peter', 'surname': 'Pan'},
    'contact': {'hometown': 'Neverland', 'phone': '123-456'}
}

mydict = defaultdict(
    lambda: defaultdict(lambda: "NULL", {}),
    {k: defaultdict(lambda: "NULL", v) for k, v in mydict.items()}
)

print(mydict["name"]["firstname"])
print(mydict["name"]["missing_key"])
print(mydict["missing_key"]["surname"])

This outputs:

Peter
NULL
NULL

---

If you only want to avoid using try/except blocks, you can also chain calls to .get(). For example, you can do:

sql += mydict.get("name", {}).get("firstname", "NULL")

in place of:

try:
    sql += f"'{mydict['name']['firstname']}',"
except KeyError:
    sql += 'NULL'

The second parameter to .get() is a fallback for cases where the key doesn't exist. For the first call, we return an empty dictionary, which allows us to fall back to the "NULL" default on the second call to .get().