Python Sqlite3 - Data is not saved permanently

Question

I am doing something wrong with SQLite3 and Python 3. Maybe i misunderstood the concept of SQLite Databases, but i expect, that data is stored in the database even after closing the application? When i insert Data and reopen the application, the inserts are gone, database is empty.

Here is my Little DB:

import sqlite3

def createTable():
    conn.execute('''CREATE TABLE VideoFile
           (ID INTEGER PRIMARY KEY NULL,
           FileName           TEXT    NOT NULL,
           FilePath           TEXT    NOT NULL,
           numOfFrames            INT     NOT NULL,
           FPS            INT     NOT NULL,
           Tags           TEXT    NOT NULL,
           Voting         REAL);''')


def insert():
    conn.execute("INSERT INTO VideoFile (Filename, FilePath, numOfFrames,FPS, Tags, Voting) \
                              VALUES ('ARCAM_0010_100', 'Categories/Dirt/Small', 2340, 50, 'Bock', 1 )");
    conn.execute("INSERT INTO VideoFile (Filename, FilePath, numOfFrames,FPS, Tags, Voting) \
                              VALUES ('ARCAM_0010_100', 'Categories/Dirt/Small', 2340, 50, 'Bock', 1 )");

def printAll(cursor):   
    cursor = conn.execute("SELECT ID, FileName, FilePath, numOfFrames  from VideoFile")
    for row in cursor:
       print("ID = ", row[0])
       print("FileName = ", row[1])
       print("FilePath = ", row[2])
       print("numOfFrames = ", row[3], "\n")

    print("Operation done successfully")
    conn.close()


conn = sqlite3.connect('AssetBrowser.db')
createTable()

#comment out after executing once
insert()
printAll()

Where i am doing wrong?

Answer 1

Call conn.commit() to flush the transaction to disk.

When the program exits the last outstanding transaction is rolled back to the last commit. (Or, more accurately, the rollback is done by the next program to open the database.) Thus, if commit is never called, there is no change to the database.

Note that per the docs:

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

Therefore, if you use a with-statement like this:

with sqlite3.connect('AssetBrowser.db') as conn:
    createTable()
    insert()
    printAll()

then the transaction will automatically be committed for you when Python leaves the with-statement assuming there is not error which raises an Exception.

---

By the way, if you use CREATE TABLE IF NOT EXISTS, then the table will only be created if it does not already exist. Done this way, you do not have to comment-out createTable after calling it once.

def createTable():
    conn.execute('''CREATE TABLE IF NOT EXISTS VideoFile
           (ID INTEGER PRIMARY KEY NULL,
           FileName           TEXT    NOT NULL,
           FilePath           TEXT    NOT NULL,
           numOfFrames            INT     NOT NULL,
           FPS            INT     NOT NULL,
           Tags           TEXT    NOT NULL,
           Voting         REAL);''')