CODEWITHSUNDEEP
pythonjsonsqlite
user2762594
user2762594

Reputation: 5

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": [
                "ursin",
                "andorhal",
                "scilla",
                "zuluhed"
            ],
            "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)) )

db=sqlite3.connect("temp.db")
c=db.cursor()
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> 
sqlite> .tables
realms
sqlite>

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

Upvotes: 0

Views: 384

Answers (2)

NotTooTechy
NotTooTechy

Reputation: 486

You could dump entire json to sqlite, and later pull from db and do whatever you want.

db=sqlite3.connect("temp.db")
cur = db.cursor()
sql_create_data_table= """CREATE TABLE IF NOT EXISTS myrealms (
                                id integer PRIMARY KEY AUTOINCREMENT,
                                data json NOT NULL
                            ); """
cur.execute(sql_create_data_table)

Inserting:

sql = '''INSERT INTO myrealms(data) VALUES(json('%s'))'''%json.dumps(response)
cur.execute(sql)
db.commit()

Getting dat from db:

cur.execute("SELECT * FROM myrealms")
rows = cur.fetchall()
print(rows)

Upvotes: 0

roganjosh
roganjosh

Reputation: 13175

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.

Upvotes: 2

Related Questions