how to insert specific json into sqlite database using python 3 -

-

using python 3, want download api data, returned json, , want insert specific (columns or fields or whatever?) sqlite database. so, here's i've got , issues 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 how requested json data (a dictionary?) python can use. that...

jason.loads(r.text)

then create table want insert specific data:

curs.execute('''create table data( loanid integer not null, noteamount real not null, )''')

no problem there...but now, though json data looks (although there 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 want insert 2 data points sqlite database, "loanid" , "noteamount". believe inserting data database (but know incorrect):

curs.execute('insert data (loanid, noteamount) values (?,?)', (loanid, noteamount))

but @ total loss how that, guess have 2 main issues; getting downloaded data python can use insert specific data database; , how insert data database object holds downloaded data. i'm guessing looping part of answer...but what? in advance!

as the documentation says:

the sqlite3 module supports 2 kinds of placeholders: question marks (qmark style) , named placeholders (named style).

note can insert rows @ once using executemany. in case:

curs.executemany('insert data (loanid, noteamount) '                  'values (:loanid,:noteamount)', json.loads(...)['mynotes'])

Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

python - NameError: name 'subprocess' is not defined -

-
i'm trying make rng website: http://jamesdotcom.com/?p=417 . when run following code: captureimage = subprocess.popen( [ "fswebcam", "-r", "356x292", "-d", "/dev/video0", "static.jpg", "--skip", "10" ], stdout=devnull, stderr=devnull) captureimage.communicate() i following error message: nameerror: name 'subprocess' not defined what should do? thanks have imported first? import subprocess captureimage = subprocess.popen(["fswebcam", ...) subprocess included in python standard library since python 2.4. should not necessary install it.
Read more