Json data not writing to SQLite DB - Python

Question

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

Did you do db.commit() afterwards? The cursor won't update the table until you commit the changes. — roganjosh
– roganjosh, Commented Nov 8, 2016 at 22:40
Please reduce your original program to the shortest possible program and data set that demonstrates the problem. Please edit your post and copy-paste that entire short program and dataset, along with the expected and actual output. See minimal reproducible example for more info. — Robᵩ
– Robᵩ, Commented Nov 8, 2016 at 22:44
You don't describe where/how you invoked create table realms, and that table doesn't appear in the temp.db that you examine. It is possible that the temp.db that your Python writes to is in a different directory than the temp.db that you examine by hand. Can you print(os.getcwd()) in your Python program? — Robᵩ
– Robᵩ, Commented Nov 8, 2016 at 22:46
@roganjosh That is exactly what I was forgetting thank you, if you make a full comment ill mark that as the answer. — user2762594
– user2762594, Commented Nov 8, 2016 at 22:53
@Robᵩ please read the question again before requesting the info you are requesting, 1) its not possible to reduce the question further as all data pertains to a]what im writing to the db b] how im writing it the db 2) how I invoked the tables in temp.db does not matter as the tables exist - seen in the sqlite3 command and subcommand output, im not sure if you read the question or not but the table exists as seen by sqlite> .tables realms — user2762594
– user2762594, Commented Nov 8, 2016 at 22:58

roganjosh · Accepted Answer · 2016-11-08 22:56:44Z

2

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.

answered Nov 8, 2016 at 22:56

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

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

Comments

NotTooTechy · Accepted Answer · 2020-02-28 16:03:34Z

0

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)

answered Feb 28, 2020 at 16:03

NotTooTechy

5065 silver badges10 bronze badges

Collectives™ on Stack Overflow

Json data not writing to SQLite DB - Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related