python - 将 Json 数据保存到 Sqlite Python

Question

我正在尝试保存从 URL 中提取的 Json 数据，但我很难将其保存在 SQLite 数据库中。

JSON:

{“地址”:“ff00000a70d57128”，“摄氏度”:26.8125，“华氏度”:80.2625}

我环顾四周并从中获得灵感:

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()

当我运行这个时我得到的是:

TypeError(f'JSON对象必须是str, bytes or bytearray, 'TypeError: JSON对象必须是str、bytes或bytearray，不能是省略号

我尝试过改变:

obj = json.loads(data)

到

obj = json.loads(str(data))

但我认为问题在于我如何处理数据库部分，如有任何建议，我们将不胜感激。谢谢!

Answer 1

这是我不久前写的概念证明，用于下载 JSON 库存数据并将其以行格式存储在 SQLite 中。

不同的数据，相同的概念。希望对你有帮助

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()

在SQLite中存储数据的表定义是

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

JSON的形状是

{
  "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
  }
}