How to fetch data that is in JSON format from an API using python to a .db file

Question

I have an API: https://baseballsavant.mlb.com/gf?game_pk=635886 I want to fetch the data from here (which is in JSON format) using a python code and create a table filled with this data so that I can run SQL queries on it using sqlite3. I have gotten the data here in JSON, but I want to push it to my database called pitch.db

Here is what I have so far:

    import pandas as pd
    import requests
    import sqlite3
    import json
    from sqlalchemy import create_engine
    engine = create_engine('sqlite://', echo=False)
    url = "https://baseballsavant.mlb.com/gf?game_pk=635886"
    conn = sqlite3.connect('pitch.db')
    c = conn.cursor()
    df = pd.read_json(url, orient='index')
    df.to_sql('baseball', engine, if_exists='replace')

Answer 1

Ok so here's an example of getting the pitchers into the db. You have to flatten out the json, and then also either a) flatten out any reaming json structures in the columns, or drop them (I dropped them as I don't know what data you want exactly). But this puts the table in db file called pitchers, with table baseball.

import pandas as pd
import requests
import sqlite3

url = url = "https://baseballsavant.mlb.com/gf?game_pk=635886"
jsonData = requests.get(url).json()

pitchers = []
for home_away in ['home', 'away']:
    for x in jsonData[f'{home_away}_pitchers'].values():
        pitchers += x
    
pitchers_df = pd.DataFrame(pitchers)
cols = ['play_id','inning','ab_number','outs','stand','batter_name','p_throws',
        'pitcher_name','team_batting','team_fielding','result','strikes',
        'balls','pre_strikes','pre_balls','call_name','pitch_type','start_speed',
        'extension','zone','spin_rate','hit_speed','hit_distance','hit_angle',
        'is_barrel','is_bip_out','pitch_number','player_total_pitches',
        'game_pk']
pitchers_df = pitchers_df[cols]

conn = sqlite3.connect('pitch.db')
pitchers_df.to_sql('baseball', conn, if_exists='replace',  index=False)
conn.close()

Output: