how to insert specific json into sqlite database using python 3

Question

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(
'https://api.lendingclub.com/api/investor/v1/accounts/94837758/detailednotes',
 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!

score 2 · Accepted Answer · answered Mar 16 '15 at 21:12

2

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'])

answered Mar 16 '15 at 21:12

fpbhb

1,469
10
22

Almost there. I now have an off-topic problem, I think. Everything seems to go well until this: "TypeError: the JSON object must be str, not 'ellipsis'" – Jeff F Mar 16 '15 at 22:33
Sorry, you have to insert r.text in place of the ellipsis. The code snippet was meant to illustrate the use of named variables and executemany, not to be runnable. You can also use the js variable, as pointed out in the other answer. – fpbhb Mar 16 '15 at 22:44
Actually had to use the "r.text". Using the "js" variable gave me this: the JSON object must be str, not 'dict'. All is working now. A big thanks to both you and @Celeo – Jeff F Mar 16 '15 at 23:11
wow this insert into `INSERT INTO data (loadid, noteamount) values (:loadid, :noteamount)` should be in the docs. I've been spending a couple hours trying to "insert" with dictionaries. Everything else was easy, but couldn't figure it out for "insert into" – Tristan Isfeld Oct 12 '21 at 19:24

score 1 · Answer 2 · edited May 23 '17 at 12:02

1

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.

edited May 23 '17 at 12:02

Community

1
1

answered Mar 16 '15 at 21:12

Celeo

5,583
8
39
41

Thanks for the "js = json.loads(r.text)" comment, Celeo. Finally got that issue put down for the future. – Jeff F Mar 16 '15 at 22:35

how to insert specific json into sqlite database using python 3

2 Answers2