Error inserting nullable integers into bigquery using python API

Question

I am trying to use the bigquery python API to insert data into bigquery tables. More specifically the command:

errors = client.insert_rows(table, rows[i*BATCH_SIZE:])

The column gas_used in my table has a datatype INTEGER and is NULLABLE. But I get the following error while inserting rows with null values in gas_used.

[{'index': 0, 'errors': [{u'debugInfo': u'', u'reason': u'invalid', u'message': u'Cannot convert value to integer (bad value):', u'location': u'gas_used'}]}]

Here, is the schema of my table:

block_number,transaction_hash,type,trace_address,subtraces,transaction_position,from,to,value_wei,start_gas,input,gas_used,contract_address,output,error

and, here is the row which throws the error:

[('5000001', '0x2cfd7194877466e6adb87222e5876c4babf8bd59de299698fdce7f0a9a232932', 'call', '[]', '0', '11', '0x77cb68e788aa79640b8a613431cdb10d83208d47', '0x4b3ee0ac445d954f8bca68f9538d54e9633890f6', '349950000000000', '0', '0x', '', '', '', 'Out of gas')]

gas_used column is the fourth last column in the schema.

So it corresponds with a value of ''? That doesn't look like a valid integer to me... — Elliott Brossard
Elliott is right, the value '' is an empty string, not integer. You'd have to either replace it with a None value or a specific 0 (zero). — Willian Fuks
@WillianFuks I would like to retain the Null nature of the integer and thus cannot use a specific value 0, but using None is a good idea. I have updated the answer with a solution that I found. — Ankit Chiplunkar

Ankit Chiplunkar Ankit Chiplunkar · Accepted Answer · 2018-05-03T09:14:11

As William Fuks mentioned I have to either mention a Null Integer as None or 0. I would prefer to retain the Null nature of the Integer so would prefer using `Nonè

I read this post and am now uploading a list of dictionaries while removing the keys which are null.

Error inserting nullable integers into bigquery using python API

1 Answers