I have trouble making the lists store all the data of the loop and not only the last one-CodePudding

** Dear partner Programmers! :D I want to store the json i get from the API so i can make an API to manipulate the data, but i only get the json of the last loop. Im new to python and sql(2 weeks), and im not sure how to get the lists dynamic, also i need to use the last query(pandas) but its not working and im not sure why. Last i need a recommendation on how to make an API maybe some steps, i have to make it list the locations

List the latest forecast for each location for every day
List the average the_temp of the last 3 forecasts for each location for every day
Get the top n locations based on each available metric where n is a parameter given to the API call.

import requests
import json
import sqlite3
import pandas as pd #library for data frame

print(sqlite3.sqlite_version)



for x in range(20,28):         # i need to get LONDjson/BERLjson/SANjson lists dynamic to store bot 7 jsons from each urls
    r = requests.get('https://www.metaweather.com/api/location/44418/2021/4/' str(x) '/') #GET request from the source url 
    LONDjson=r.json() #JSON object of the result
    r2 = requests.get('https://www.metaweather.com//api/location/2487956/2021/4/' str(x) '/')
    SANjson=r2.json()
    r3 = requests.get('https://www.metaweather.com//api/location/638242/2021/4/' str(x) '/')
    BERLjson=r3.json()


conn= sqlite3.connect('D:\weatherdb.db') #create db in path
cursor = conn.cursor()

#import pprint
#pprint.pprint(LONDjson)

cursor.executescript('''
                     DROP TABLE IF EXISTS LONDjson;
                     DROP TABLE IF EXISTS SANjson;
                     DROP TABLE IF EXISTS BERLjson;
                     CREATE TABLE LONDjson (id int, data json);
                     
                     ''');

for LOND in LONDjson:
    cursor.execute("insert into LONDjson values (?, ?)",
    [LOND['id'], json.dumps(LOND)])
    conn.commit()




z=cursor.execute('''select json_extract(data, '$.id', '$.the_temp', '$.weather_state_name', '$.applicable_date' ) from LONDjson;
                 ''').fetchall() #query the data

CodePudding user response：

hint: in your initial for loop you are not storing the results of api call. you are storing in variable but that is just getting re-written each loop.

a common solution for this starting with empty list that you append to. where perhaps if storing mutliple variables you are storing a dictionary as elements of list

example

results = []
for x in range(10):
    results.append(
      {
        'x': x,
        'x_sqaured': x*x,
        'abs_x': abs(x)
      }
    )
print(results)

CodePudding user response：

It looks like there's at least two things that can be improved in the data manipulation part of your code.

Using an array to store the retrieved data

LONDjson = []
SANjson = []
BERLjson = []

for x in range(20,28):  
    r = requests.get('https://www.metaweather.com/api/location/44418/2021/4/' str(x) '/')
    LONDjson.append(r.json())
    
    r2 = requests.get('https://www.metaweather.com//api/location/2487956/2021/4/' str(x) '/')
    SANjson.append(r2.json())
    
    r3 = requests.get('https://www.metaweather.com//api/location/638242/2021/4/' str(x) '/')
    BERLjson.append(r3.json())

Retrieving the data from the array

# The retrieved data is a dictionary inside a list with only one entry
for LOND in LONDjson:
    print(LOND[0]['id'])

Hope this helps you out.