python怎么往数据库插null_Python-NULL数据导致数据库数据库插入错误

以下是我用于将json插入数据库中的代码

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)

但是，某些项目可能为NULL，这会导致我的插入语句中断，从而导致此错误：

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'

我试过在每个元素的末尾附加或NONE，使查询像这样：

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

还是没有运气！你们如何建议我考虑上述代码中的NONE / NULL值

解决方案

每当您尝试使用此方法访问python字典中的键的值时element['highPrice']['bid']，都必须确保该键存在，否则将触发异常。如果不确定密钥是否存在，则应使用以下命令：

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

这不会引发异常，并且如果密钥不存在，默认情况下它将返回None。如果键不存在，您还可以提供所需的返回值，如下所示：

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