user2762594 user2762594 - 11 months ago 71
JSON Question

Json data not writing to SQLite DB - Python

My Json data contains the following (but larger)

"realms": [

"status": true,
"battlegroup": "Shadowburn",
"name": "Zuluhed",
"locale": "en_US",
"queue": false,
"connected_realms": [
"timezone": "America/New_York",
"type": "pvp",
"slug": "zuluhed",
"population": "medium"

and this is my code snipet should put the data into the db file
( json data was loaded into data variable(data = json.loads(response)) )

for record in data['realms']:
c.execute('INSERT INTO realms (status, name, queue, timezone, type, population) VALUES (?,?,?,?,?,?)', (record['status'], record['name'],record['queue'], record['timezone'],record['type'], record['population']))

Running the script runs without error but checkin the contents of the table there is nothing

# sqlite3 temp.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM "realms";
sqlite> .tables

I'm new to json and sqlite so I assume im doing something wrong.

Answer Source

Any updates you do to your database via the cursor object will not take effect until you commit them. In your case, your connection to the database is called db (db=sqlite3.connect("temp.db")) so you need db.commit() somewhere after your INSERT commands.