Mysqldb and Python KeyError Handling

Question

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?

alecxe · Accepted Answer · 2016-05-22 16:13:41Z

1

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))

answered May 22, 2016 at 16:13

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kamil Mieczakowski Over a year ago

Worked like a charm, thank you! Just curious, why did you have to handle description and name separately?

Kamil Mieczakowski Over a year ago

Sorry, I said this too early. Now when I am runing it through the loop I am getting this error: local variable 'item' referenced before assignment

alecxe Over a year ago

@KamilMieczakowski had to handle the name and description separately since it involves getting items from subdictionaries..as for the error, make sure u have 'item' defined before u are using it. Thanks.

Kamil Mieczakowski Over a year ago

thank you. Unfortunately I am struggling with how to reference the item variable, hence I published another question. If you could help since you know the context I would be grateful: stackoverflow.com/questions/37377695/…

Collectives™ on Stack Overflow

Mysqldb and Python KeyError Handling

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related