SQLでJSONを使う

MySQLやMaria DBでも、JSON を使える

create tableでJSON型を指定

CREATE TABLE AlphaVantage (
   t_date date NOT NULL,
   ticker VARCHAR(255) NOT NULL,
   income_stmt JSON,
   CHECK (JSON_VALID(income_stmt)),
   balance JSON,
   CHECK (JSON_VALID(balance)),
   PRIMARY KEY (t_date,ticker)
);

insert 文. json.dump

url = 'https://www.alphavantage.co/query?function=INCOME_STATEMENT～' 
r = requests.get(url)
income_data = r.json()

url = 'https://www.alphavantage.co/query?function=BALANCE_SHEET~'
r = requests.get(url)
balance_data = r.json()

try :
   sql = ('INSERT INTO AlphaVantage  (t_date, ticker, income_stmt,balance) VALUES (now(6), %s, %s,%s) ON DUPLICATE KEY UPDATE income_stmt = VALUES (income_stmt),balance=VALUES(balance);')
data = [
(ticker_name, json.dumps(income_data),json.dumps(balance_data)) 
]
cur.executemany(sql, data)

select は JSON_EXTRACTで

select JSON_EXTRACT(balance, '$.annualReports[0].totalShareholderEquity')
from AlphaVantage;