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?

I'm pretty nonplussed and can't tell whether it's a bug with 3.1's sqlite, windows in general, vista in particular, or what -- I can't reproduce it. Could you post the simplest way you find to reproduce your problem...? Tanks! — Alex Martelli
– Alex Martelli, Commented Oct 7, 2009 at 5:24
I've similar problem with Vista and Python2.6/Django. One way to simulate may be to go to the db folder with the Windows explorer and then force a refresh — luc
– luc, Commented Mar 26, 2010 at 10:45

Robert Harvey · Accepted Answer · 2009-10-07 05:26:10Z

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.

answered Oct 7, 2009 at 5:26

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

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

1 Comment

Dave Viner Over a year ago

My read of docs.python.org/3.1/library/… is that using isolation level, i can control the BEGIN statement. The database connection is constructed as: conn = sqlite3.connect(db_file, isolation_level=None) which should auto-commit. I've tried using the default level as well, but the same problem occurs. Is there some additional code required to start a transaction before running the executemany statement?

Sergio Morstabilini · Accepted Answer · 2010-01-24 16:43:24Z

0

same error here on windows 7 (python 2.6, django 1.1.1 and sqllite) after some records inserted correctly: sqlite3.OperationalError: unable to open database file

I ran my script from Eclipse different times and always got that error. But as I ran it from the command line (after setting PYTHONPATH and DJANGO_SETTINGS_MODULE) it worked as a charm...

just my 2 cents!

answered Jan 24, 2010 at 16:43

Sergio Morstabilini

2,06521 silver badges28 bronze badges

Collectives™ on Stack Overflow

Python sqlite3 "unable to open database file" on windows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related