Python to Parse JSON and store list into SQLite Table

Question

Currently, trying to import the following list items into 11 different columns (each item in the list goes into a different column). I think the root of the issue is that the method I'm using in the python script, needs to have this format: [(1,2,3,...,11),(1,2,3,...,11)] Basically a List of rows separated by parentheses causing tuples.

My attempt at the code:

import sqlite3
import json

lst = []
with open('data.json') as data_file:
    data = json.load(data_file)

for e in data:
    if e == 'participants':
        continue
        #participants_data=data['participants'][0]
        #print participants_data
        #for f in participants_data:
            # print f
            # print participants_data[f]    
    else:
        lst.append(data[e])
        # print e
        # print data[e]


conn = sqlite3.connect('somedbname.sqlite')
c = conn.cursor()
c.execute('''CREATE TABLE if not exists stocks33
             (col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text, col11 text)''')

c.executemany("INSERT INTO stocks33 VALUES (?,?,?,?,?,?,?,?,?,?,?)", lst)
conn.commit()
c.execute("select * from stocks33")
print(c.fetchall())

Data:

{
   "matchId": 1778839570,
   "region": "NA",
   "platformId": "NA1",
   "matchMode": "CLASSIC",
   "matchType": "MATCHED_GAME",
   "matchCreation": 1427867835805,
   "matchDuration": 3424,
   "queueType": "RANKED_SOLO_5x5",
   "mapId": 11,
   "season": "SEASON2015",
   "matchVersion": "5.6.0.194",
   "participants": [
      {
         "teamId": 100,
         "spell1Id": 4,
         "spell2Id": 11,
         "championId": 113,
         "highestAchievedSeasonTier": "GOLD"
      }
   ]
}

What are you getting, and what are you expecting?

Eugene K
– Eugene K

2015-10-08 23:09:23 +00:00
Commented Oct 8, 2015 at 23:09 — Eugene K
– Eugene K, Commented Oct 8, 2015 at 23:09

Eugene K · Accepted Answer · 2015-10-08 23:11:57Z

1

I'm not sure I understand the problem, but you might've meant

c.execute("INSERT INTO stocks33 VALUES (?,?,?,?,?,?,?,?,?,?,?)", lst)

That will insert a single list into the DB.

You provide 1 JSON dict, and don't mention if there's more of them, or how they are stored (i.e. same JSON file as a list?). If you want to insert a list of lists [[...],[...]] then you use executeall. Otherwise you just do execute

answered Oct 8, 2015 at 23:11

Eugene K

3,4672 gold badges25 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Python to Parse JSON and store list into SQLite Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related