Saving Json data to Sqlite Python

Question

I am trying to save my Json data that I have pulled from a URL, but I am struggling to save it in an SQLite database.

Json:

{"Address":"ff00000a70d57128","Celsius":26.8125,"Fahrenheit":80.2625}

I have looked around and gained from inspiration from:

saving json data into sqlite Convert JSON to SQLite in Python - How to map json keys to database columns properly?

import threading
import json
import urllib.request
import sqlite3

def tempRequest():
    # download raw json object
    url = "http://10.0.0.111:8080/getdevice?device=type28_1"
    data = urllib.request.urlopen(url).read().decode()

    # parse json object
    obj = json.loads(data)
    print(obj)

tempRequest()

conn = sqlite3.connect('test.db')
c = conn.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS data(Celcius TEXT)")

def data_entry():
    c.executemany('INSERT INTO data (Celcius) ''VALUES (:Celcius)

    conn.commit()
    c.close()
    conn.close()

create_table()
data_entry()

What i get when i am running this is:

TypeError(f'the JSON object must be str, bytes or bytearray, ' TypeError: the JSON object must be str, bytes or bytearray, not ellipsis

I have tried to change:

obj = json.loads(data)

to

obj = json.loads(str(data))

But i think that the issue is on how i handle the DB part, any advice would be much appreciated. Thanks!

When you debug python script, pdb is your friend. Place import pdb; pdb.set_trace() anywhere you want to stop, then run the script. The pdb interpreter came up, and you can inspect variable such as data just like python interpreter. — Kei Minagawa
– Kei Minagawa, Commented Jul 6, 2019 at 3:21

Malcolm Swaine · Accepted Answer · 2020-04-23 00:33:33Z

7

This is a proof of concept I wrote a short while back to download JSON stock data and store it in row format in SQLite.

Different data, same concept. Hope it helps

import json
import urllib.request
import sqlite3
from os import path

stocks = ['AAPL', "MSFT"]

connection = sqlite3.connect("C:\\Projects\\AIFX\\Spikes\\Data\\Stocks.db")
cursor = connection.cursor()

for stock in stocks:

    url = "https://financialmodelingprep.com/api/v3/historical-price-full/" + stock
    data = urllib.request.urlopen(url).read().decode()

    obj = json.loads(data)

    for child in obj['historical']:
        print(child)

        cursor.execute("Insert into StockPrices values (?, ?, ?, ?, ?, ?, ?)", 
                       (child['date'], child['close'], child['high'], child['low'], 
                       child['open'], child['volume'], stock))
        connection.commit()

The table definition for storing the data in SQLite is

CREATE TABLE "StockPrices" (
    "date"  TEXT,
    "close" INTEGER,
    "high"  INTEGER,
    "low"   INTEGER,
    "open"  INTEGER,
    "volume"    INTEGER,
    "symbol"    TEXT
);

The shape of the JSON is

{
  "symbol" : "AAPL",
  "historical" : [ {
    "date" : "2015-04-22",
    "open" : 126.99,
    "high" : 128.87,
    "low" : 126.32,
    "close" : 128.62,
    "adjClose" : 119.0,
    "volume" : 3.76545E7,
    "unadjustedVolume" : 3.76545E7,
    "change" : 1.63,
    "changePercent" : 1.284,
    "vwap" : 127.93667,
    "label" : "April 22, 15",
    "changeOverTime" : 0.01284
  }, {
    "date" : "2015-04-23",
    "open" : 128.3,
    "high" : 130.42,
    "low" : 128.14,
    "close" : 129.67,
    "adjClose" : 119.97,
    "volume" : 4.57709E7,
    "unadjustedVolume" : 4.57709E7,
    "change" : 1.37,
    "changePercent" : 1.068,
    "vwap" : 129.41,
    "label" : "April 23, 15",
    "changeOverTime" : 0.01068
  }
}

answered Apr 23, 2020 at 0:33

Malcolm Swaine

2,31226 silver badges16 bronze badges

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

2 Comments

Happy Over a year ago

There is a pandas.DataFrame.to_sql() method which can yahoo finance stock data to DB directly: pandas.pydata.org/docs/reference/api/…

Tuhin Mitra Over a year ago

Where exactly you are storing a JSON ?? It seems to me that you are extracting string values from the child dictionary object & saving them into TEXT field.

Collectives™ on Stack Overflow

Saving Json data to Sqlite Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related