Python sqlite3 "unable to open database file" on windows

Question

I am working on a windows vista machine in python 3.1.1. I am trying to insert a large number of rows into a SQLite3 db. The file exists, and my program properly inserts some rows into the db. However, at some point in the insertion process, the program dies with this message: sqlite3.OperationalError: unable to open database file

However, before it dies, there are several rows that are properly added to the database.

Here is the code which specifically handles the insertion:

idx = 0
lst_to_ins = []
for addl_img in all_jpegs:
    lst_to_ins.append((addl_img['col1'], addl_img['col2']))
    idx = idx + 1
    if idx % 10 == 0:
        logging.debug('adding rows [%s]', lst_to_ins)
        conn.executemany(ins_sql, lst_to_ins)
        conn.commit()
        lst_to_ins = []
        logging.debug('added 10 rows [%d]', idx)
if len(lst_to_ins) > 0:
    conn.executemany(ins_sql, lst_to_ins)
    conn.commit()
    logging.debug('adding the last few rows to the db')

This code inserts anywhere from 10 to 400 rows, then dies with the error message

conn.executemany(ins_sql, lst_to_ins)
sqlite3.OperationalError: unable to open database file

How is it possible that I can insert some rows, but then get this error?

Answer 1

SQLite does not have record locking; it uses a simple locking mechanism that locks the entire database file briefly during a write. It sounds like you are running into a lock that hasn't cleared yet.

The author of SQLite recommends that you create a transaction prior to doing your inserts, and then complete the transaction at the end. This causes SQLite to queue the insert requests, and perform them using a single file lock when the transaction is committed.

In the newest version of SQLite, the locking mechanism has been enhanced, so it might not require a full file lock anymore.