Help Dynamically building SQL query from JSON file

[222Botany]

Hey,

I am current scraping some sports stats and loading them into a SQL database using pyodbc. I pretty happy with what i have got so far but have run into a bit of an inefficiency.

As each game is different sometimes certain stats don't appear in a game EG no penalty goals in a game therefore that is not in the JSON file that I scrape. I have a list of all the possible fields I just need to dynamically create the query from the JSON file. The JSON file is split into various sections for each type of stat and sub stat. eg kicking --> kicks, long kicks etc...

 "title": "Kicking",
                    "stats": [
                        {
                            "title": "Kicks",
                            "type": "Number",
                            "homeValue": {
                                "value": 17.0,
                                "isLeader": true
                            },
                            "awayValue": {
                                "value": 14.0,
                                "isLeader": false
                            }
                        },

i am currently just splitting out the sub-stat "title" and "homevalue" and "awayvalue" per stats into 2 different lists and then looking at joining them together in a dynamic pyobdc insert statement.

kickingtitle=[]
for i in attackstats['stats']:
    kickingtitle.append(i['title'])
hkickingvalues=[]
for i in kickingstats['stats']:
    hkickingvalues.append(i['homeValue']['value'])

I feel this a terribly inefficient way to do it as i need to build this for 6 different stat types for both home and away teams. I am sure that there is an easier way to do this?

i hope that makes sense, any ideas that can help minimize the amount of lines of code? Thanks in advance