CODEWITHSUNDEEP
pythonjsonsqlitepython-3.x
Jeff F
Jeff F

Reputation: 1015

how to insert specific json into sqlite database using python 3

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!

Upvotes: 5

Views: 21665

Answers (2)

fpbhb
fpbhb

Reputation: 1519

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

Upvotes: 3

Celeo
Celeo

Reputation: 5682

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.

Upvotes: 1

Related Questions