Insert JSON response data into SQLite3 DB

Question

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}

Are you sure get_db is connecting the to database you think it is? Try pragma table_info(prices); or select sql from sqlite_master where type = 'table' and name = 'prices'; — Schwern
– Schwern, Commented Feb 23, 2018 at 3:32
Note: plenty of document databases exist that'll allow you to store json — OneCricketeer
– OneCricketeer, Commented Feb 23, 2018 at 3:47
@Schwern Im pretty sure its connecting to the right db yes and I only have one right now, I will try your suggestion anyway. — goblin_rocket
– goblin_rocket, Commented Feb 23, 2018 at 3:57

davidism · Accepted Answer · 2018-08-17 14:17:07Z

4

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)

edited Aug 17, 2018 at 14:17

davidism

128k31 gold badges417 silver badges348 bronze badges

answered Feb 23, 2018 at 3:45

Shubhitgarg

5846 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert JSON response data into SQLite3 DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related