Python - NULL data causing keyerror on DB INSERT

Question

Below is my code that I use for inserting a json into my database

list=result['intervalsDataPoints']
                for item in list:

                    if item['dataPoints'] !=[]:
                        result=item['dataPoints']
                        #print(result)

                        #print('hello')

                        for element in result:

                            dt = datetime.datetime.now()
                            #element["dt"] = dt
                            #element["epic"]=epic
                            #print(element)
                            #print(element['openPrice']['bid'])
                            print(element['timestamp'])
                            date_timestamp = datetime.datetime.fromtimestamp(element['timestamp']/1000)
                            cursor.execute("""INSERT INTO market_data_historic VALUES('%s','%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT ON CONSTRAINT UNIQUE_history DO NOTHING"""%(dt,epics,date_timestamp,element['openPrice']['bid'],element['openPrice']['ask'],element['closePrice']['bid'],element['closePrice']['ask'],element['highPrice']['bid'],element['highPrice']['ask'],element['lowPrice']['bid'],element['lowPrice']['ask'],element['lastTradedVolume']))
                            #cursor.executemany("""INSERT INTO market_data_historic(created_at,epic,timestamp,openprice_bid,openprice_close,closeprice_bid,closeprice_ask,highprice_bid,highprice_ask,lowprice_bid,lowprice_ask,last_traded_volume) VALUES (%(dt)s,%(epic)s,%(timestamp)s,%(openPrice['bid'])s,%(openPrice['close'])s,%(closePrice['bid'])s,%(closePrice['ask'])s,%(highPrice['bid'])s,%(highPrice['ask'])s,%(lowPrice['bid'])s,%(lowPrice['ask'])s,%(lastTradedVolume)s);""",element)
                            conn.commit()
                            #print(item)

However, some of the items may be NULL which causes my insert statement to break causing this error:

    cursor.execute("""INSERT INTO market_data_historic VALUES('%s','%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT ON CONSTRAINT UNIQUE_history DO NOTHING"""%(dt,epics,date_timestamp,element['openPrice']['bid'],element['openPrice']['ask'],element['closePrice']['bid'],element['closePrice']['ask'],element['highPrice']['bid'],element['highPrice']['ask'],element['lowPrice']['bid'],element['lowPrice']['ask'],element['lastTradedVolume']))
KeyError: 'bid'

I have tried appending or NONE to the end of each element making the query like this:

cursor.execute("""INSERT INTO market_data_historic VALUES('%s','%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT ON CONSTRAINT UNIQUE_history DO NOTHING"""%(dt,epics,date_timestamp,element['openPrice']['bid'] or None,element['openPrice']['ask'] or None,element['closePrice']['bid'] or None,element['closePrice']['ask'] or None,element['highPrice']['bid'] or None,element['highPrice']['ask'] or None,element['lowPrice']['bid'] or None,element['lowPrice']['ask'] or None,element['lastTradedVolume'] or None))

Still no luck! How do you guys suggest I go about accounting for NONE/NULL values in my code above

Dimitra Paraskevopoulou · Accepted Answer · 2020-01-22 09:51:28Z

2

Whenever you are trying to access the value of a key in a python dictionary, using this approach element['highPrice']['bid'], you have to be sure the key exists, otherwise an exception will be triggered. If you are not sure that the key exists, then you should use the following:

element['highPrice'].get('bid')

This will not raise an exception and in case the key is not there, it will return None by default. You can also provide the return value you wish in case the key does not exist, like this:

element['highPrice'].get('bid', 'value')

As a note here, I assume you always have the 'highPrice' key available. If not the same logic, using .get should be also applied there. https://www.tutorialspoint.com/python/dictionary_get.htm

answered Jan 22, 2020 at 9:51

Dimitra Paraskevopoulou

1012 bronze badges

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

5 Comments

Aharon Over a year ago

Hi just tried that seeing error: psycopg2.errors.UndefinedColumn: column "none" does not exist LINE 1: ...1.982000','CC.D.LCO.USS.IP','2019-11-07 23:00:00',None,None,...

Dimitra Paraskevopoulou Over a year ago

Hi, this looks strange to me, in the query you posted, you do not specify the column names and I believe any None value should be going to the value of the column. Make sure there is not a dynamic pick up of column name, because this will not work. Maybe even try to specify the column names inside the query and try some debugging.

Aharon Over a year ago

Thanks for responding Dimitra. I have done as you have suggested: ("""INSERT INTO market_data_historic(created_at,epic,timestamp,openprice_bid,openprice_ask,closeprice_bid,closeprice_ask,highprice_bid,highprice_ask,lowprice_bid,lowprice_ask,last_traded_volume) VALUES - Still seeing the same error

Dimitra Paraskevopoulou Over a year ago

Since this is not something somebody can really reproduce, I can only give you some suggestions to help you identify where the real issue is. First, put in the execute a static insert query you know it works and test it succeeds from inside the script. Then, in order to debug you query, assign the query creation to a variable and print it out, before the execution. This way you could get the query and directly apply it to the database. Maybe some columns cannot be Null, maybe some data are not coming to the right column. You will need to analyse the query with the data the script produces.

Aharon Over a year ago

Dimitra Paraskevopoulou Thank you soo much for your help I did exactly as you suggested printing the query for debugging. Turns out the NONE were not in quotes which PostgresSQL has issues with. Again thanks!

Collectives™ on Stack Overflow

Python - NULL data causing keyerror on DB INSERT

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related