I´m new to programming and I chose python (3.7) as the first working language. I have been working for 5 days on code that consumes an API and returns a nested JSON. My intention is to insert this data into a table in a PostgreSQL database. I can already insert data from other data that this API provides, but this particular JSON structure is giving me problems because my code stops working when it doesn't find a certain key inside the python object (when it doesn't exist. I need it to return as a null value on a table). I'll set you up to make it clearer. In short, and with a piece of dummy data:
#Note that some keys are sometimes present and sometimes not.
myapidata = [
{
"MaxRpm": 2300,
"StartPosition": {
"Longitude": -12.3456,
"Latitude": -78.9456
},
"Engine": 10623,
"Fuel": 20.133
},
{
"MaxRpm": 0.0,
"StartPosition": {
"Longitude": -74.1258,
"Latitude": -96.3258
},
"EndPosition": {
"Longitude": -78.9456,
"Latitude": -85.2369
},
"Engine": 0,
"Fuel": 150.35
},
{
"MaxRpm": 800,
"StartPosition": {
"Longitude": 85.4125,
"Latitude": -45.62145
},
"EndPosition": {
"Longitude": 85.2145,
"Latitude": 74.6789
},
"Engine": 104,
"Fuel": 0.021,
"Pulse": 7
}
]
#Python Code:
import json
import psycopg2
api_json_list = json.loads(myapidata.content)
#Tried to add Null to keys not present (works with non Nested JSON):
allkeys = frozenset().union(*api_json_list)
for a in api_json_list:
for b in allkeys:
if b not in a:
a[b] = None
#Insert data on PostgreSQL:
conn = psycopg2.connect ("host = my_host dbname = my_db user = my_user password = my_pass")
cur = conn.cursor()
cur.execute("TRUNCATE TABLE mytable")
data_extract = []
def get_data():
for data in api_json_list:
dictionary = data
maxrpm = dictionary['MaxRpm']
start_lng = dictionary['StartPosition']['Longitude']
start_lat = dictionary['StartPosition']['Latitude']
end_lng = dictionary['EndPosition']['Longitude']
end_lat = dictionary['EndPosition']['Latitude']
engine = dictionary['Engine']
fuel = dictionary['Fuel']
pulse = dictionary['Pulse']
data_extract.append([maxrpm,start_lng,start_lat,end_lng,end_lat,engine,fuel,pulse])
get_data() #Get a TypeError
def post_gre():
for item in data_extract:
my_data = tuple(item)
cur.execute('INSERT INTO mytable VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', my_data)
post_gre()
conn.commit()
conn.close()
The result I hope to achieve in my database is something like the table below:
Thank you for any help!
EDIT : Answer with the correct code
import json
import psycopg2
api_json_list = json.loads(myapidata.content)
#Insert data on PostgreSQL:
conn = psycopg2.connect ("host = my_host dbname = my_db user = my_user password = my_pass")
cur = conn.cursor()
cur.execute("TRUNCATE TABLE mytable")
data_extract = []
def get_data():
for data in api_json_list:
dictionary = data
maxrpm = dictionary.get('MaxRpm')
if 'StartPosition' in dictionary:
start_lng = dictionary['StartPosition'].get('Longitude')
start_lat = dictionary['StartPosition'].get('Latitude')
else:
start_lng = None
start_lat = None
if 'EndPosition' in dictionary:
end_lng = dictionary['EndPosition'].get('Longitude')
end_lat = dictionary['EndPosition'].get('Latitude')
else:
end_lng = None
end_lat = None
engine = dictionary.get('Engine')
fuel = dictionary.get('Fuel')
pulse = dictionary.get('Pulse')
data_extract.append([maxrpm,start_lng,start_lat,end_lng,end_lat,engine,fuel,pulse])
get_data()
def post_gre():
for item in data_extract:
my_data = tuple(item)
cur.execute('INSERT INTO mytable VALUES (%s,%s,%s,%s,%s,%s,%s,%s)', my_data)
post_gre()
conn.commit()
conn.close()
