Using python 3, I want to download API data, which is returned as JSON, and then I want to insert only specific (columns or fields or whatever?) into a sqlite database. So, here's what I've got and the issues I have:
Using python's request module:
##### import modules
import sqlite3
import requests
import json
headers = {
'Authorization' : 'ujbOsdlknfsodiflksdonosB4aA=',
'Accept' : 'application/json'
}
r = requests.get(
'
headers=headers
)
Okay, first issue is how I get the requested JSON data into something (a dictionary?) that python can use. Is that...
jason.loads(r.text)
Then I create the table into which I want to insert the specific data:
curs.execute('''CREATE TABLE data(
loanId INTEGER NOT NULL,
noteAmount REAL NOT NULL,
)''')
No problem there...but now, even though the JSON data looks something like this (although there are hundreds of records)...
{
"myNotes": [
{
"loanId":11111,
"noteId":22222,
"orderId":33333,
"purpose":"Debt consolidation",
"canBeTraded":true,
"creditTrend":"DOWN",
"loanAmount":10800,
"noteAmount":25,
"paymentsReceived":5.88,
"accruedInterest":12.1,
"principalPending":20.94,
},
{
"loanId":11111,
"noteId":22222,
"orderId":33333,
"purpose":"Credit card refinancing",
"canBeTraded":true,
"creditTrend":"UP",
"loanAmount":3000,
"noteAmount":25,
"paymentsReceived":7.65,
"accruedInterest":11.92,
"principalPending":19.76,
}]
}
I only want to insert 2 data points into the sqlite database, the "loanId" and the "noteAmount". I believe inserting the data into the database will look something like this (but know this is incorrect):
curs.execute('INSERT INTO data (loanId, noteAmount) VALUES (?,?)', (loanID, noteAmount))
But I am now at a total loss as to how to do that, so I guess I have 2 main issues; getting the downloaded data into something that python can use to then insert specific data into the database; and then how exactly do I insert the data into the database from the object that holds the downloaded data. I'm guessing looping is part of the answer...but from what? Thanks in advance!
2 Answers
As the documentation says:
The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).
Note that you can even insert all rows at once using executemany.
So in your case:
curs.executemany('INSERT INTO data (loanId, noteAmount) '
'VALUES (:loanId,:noteAmount)', json.loads(...)['myNotes'])
First off, it's js = json.loads(r.text)` so you're very close.
Next, if you want to insert just the loanID and noteAmount fields of each record, then you'll need to loop and do something like
for record in js['myNotes']:
curs.execute('INSERT INTO data (loanId, noteAmount) VALUES (?,?)', (record['loanID'], record['noteAmount']))
If you play with it a bit, you could coerce the JSON into one big INSERT call.
