Commit existing journal file in SQLite from prior terminated connection to database

Question

I'm prototyping a script that creates large SQLite databases with a four day time limit. The time limit was reached before the script finished and the connection.commit() command would be executed. The script is dropped and the database connection terminated but the journal is still in the file directory where the database was being created.

I'd like to commit the existing journal to see if the current script is on the right track. Initially I just try (using the Python sqlite3 module on iPython):

connection = sqlite3.connect('mydatabase')

connection.commit()

but I suspect I need to specify that I want to commit the existing journal. I tried passing the journal name as an argument in commit(), but commit() doesn't take arguments in the sqlite3 Python module.

In the future I'll incrementally .commit()

Answer 1

When SQLite is changing a database, some new data is in SQLite's cache, will other new data has been written to disk. When SQLite is killed in the middle of a transaction, all data in the cache has been lost, so the state of the database will be inconsistent. In that situation, the only option is to roll back the transaction to get back into a consistent state.

The -journal file actually contains the old data, the new data has been written into the actual DB file. If you really want to see the changes made by your partial transaction, you can try to delete the -journal file (which prevents SQLite from doing the automaticlly rollback) and then opening the database, but the state of the database then is inconsistent, so it's likely that you won't be able to access all data.