CODEWITHSUNDEEP
pythonsqlite
goblin_rocket
goblin_rocket

Reputation: 377

Insert JSON response data into SQLite3 DB

I have read a few other similar questions here and still not been able to get my head around this.

Im working on a Flask app that grabs crypto prices from an API endpoint based on a list of coins I pass in. I then want to store each coin and price in this SQLite DB:

drop table if exists prices;
create table prices (
  id integer primary key autoincrement,
  coin text not null,
  usd int not null
);

this is my Flask route and function below along with my first attempt. So here I passed all the data into a dictionary and then tried to put that into the DB using 'db.executemany' which didnt work and threw the error below.

@app.route('/coindata')
def coindata():

coins = ['BTC', 'ETH', 'XRP', 'LTC', 'BCH', 'ADA', 'XLM', 'NEO', 'IOTA', 'DASH' ]

base_url = 'https://min-api.cryptocompare.com/data/price?fsym='
connector = '&tsyms='

fiat = 'USD'

coindata = {}

db = get_db()

for coin in coins:
    endpoint = base_url+coin+connector+fiat


    with urllib.request.urlopen(endpoint) as url:
        data = json.loads(url.read().decode())
        print(data) #returns {'USD': 9769.35}

        for k,v in data.items():
                price = v


        coindata.update({coin:v})


print(coindata)


db.executemany('insert into prices (coin, usd) values (?, ?)', (coindata,))
#sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).

so I think I need to call 'db.execute' within the for loop and then commit each coin from my coin list plus the USD price that is returned to the DB then, but Im not sure how to implement that.

This is what I have tried in the for loop:

with urllib.request.urlopen(endpoint) as url:
            data = json.loads(url.read().decode())
            print(data)

            db.execute('INSERT INTO prices (coin) VALUES (coin)')
            db.execute('INSERT INTO price (usd) VALUES (data[1])')

this returns the following error:

 db.execute('INSERT INTO prices (coin) VALUES (coin)')
sqlite3.OperationalError: no such column: coin

which is weird as my schema has coin setup in it.

I have also tried this:

db.execute('INSERT INTO prices (coin, usd) VALUES (?,?)', coin, 
                data[1])

this returns a a key error though

edit: print(coindata) returns:

{'IOTA': 0.7129, 'BTC': 9825.56, 'NEO': 113.64, 'DASH': 609.78, 'ADA':    0.3068, 'LTC': 190.29, 'XLM': 0.3494, 'ETH': 835.72, 'XRP': 0.8966, 'BCH':    1178.76}

Upvotes: 1

Views: 3713

Answers (1)

Shubhitgarg
Shubhitgarg

Reputation: 584

In sqlite3 executemany takes tuples which are nested in list

data_coin = [(value1, value2), (value3, value4), ...]

So you can use this to your advantage by making a empty list and appending tuples to it.

coindata = []

with urllib.request.urlopen(endpoint) as url:
    data = json.loads(url.read().decode())
    print(data) #returns {'USD': 9769.35}

    for k,v in data.items():
            price = v
            coindata.apend((coin, v))

db.executemany('insert into prices (coin, usd) values (?, ?)', coindata)

Upvotes: 4

Related Questions