Kamil Mieczakowski Kamil Mieczakowski - 6 months ago 22
MySQL Question

Mysqldb and Python KeyError Handling

I am attempting to add multiple values to MySQL table, here's the code:

Try:
cursor.execute("INSERT INTO companies_and_charges_tmp (etags, company_id, created, delivered, satisfied, status, description, persons_entitled) VALUES ('%s, %s, %s, %s, %s, %s, %s, %s')" % (item['etag'], ch_no, item['created_on'], item['delivered_on'], item['satisfied_on'], item['status'], item['particulars'][0]['description'], item['persons_entitled'][0]['name']))

Except KeyError:
pass


The problem is that this code is in the loop and at times one of the values that are beiing inserted will be missing, which will result in Key Error cancelling the entire insertion.

How do I get past the KeyError, so when the KeyError relating to one of the items that are being inserted occurs, others are still added to the table and the one that is missing is simply left as NULL?

Answer

You can use the dict.get() method which would return None if a key would not be found in a dictionary. MySQL driver would then convert None to NULL during the query parameterization step:

# handling description and name separately
try:
    description = item['particulars'][0]['description']
except KeyError:
    description = None

# TODO: violates DRY - extract into a reusable method?
try:
    name = item['persons_entitled'][0]['name']
except KeyError:
    name = None

cursor.execute("""
     INSERT INTO 
         companies_and_charges_tmp 
         (etags, company_id,  created, delivered, satisfied, status, description, persons_entitled) 
     VALUES 
         (%s, %s, %s, %s, %s, %s, %s, %s)""", 
     (item.get('etag'), ch_no, item.get('created_on'), item.get('delivered_on'), item.get('satisfied_on'), item.get('status'), description, name))
Comments