Python SQLite: database is locked

Question

I'm trying this code:

import sqlite

connection = sqlite.connect('cache.db')
cur = connection.cursor()
cur.execute('''create table item
  (id integer primary key, itemno text unique,
        scancode text, descr text, price real)''')

connection.commit()
cur.close()

I'm catching this exception:

Traceback (most recent call last):
  File "cache_storage.py", line 7, in <module>
    scancode text, descr text, price real)''')
  File "/usr/lib/python2.6/dist-packages/sqlite/main.py", line 237, in execute
    self.con._begin()
  File "/usr/lib/python2.6/dist-packages/sqlite/main.py", line 503, in _begin
    self.db.execute("BEGIN")
_sqlite.OperationalError: database is locked

Permissions for cache.db are ok. Any ideas?

Answer 1

1. Your cache.db is being currently used by another process.
2. Stop that process and try again, it should work.

Answer 2

Set the timeout parameter in your connect call, as in:

connection = sqlite.connect('cache.db', timeout=10)

Answer 3

I'm presuming you are actually using sqlite3 even though your code says otherwise. Here are some things to check:

1. That you don't have a hung process sitting on the file (unix: $ fuser cache.db should say nothing)
2. There isn't a cache.db-journal file in the directory with cache.db; this would indicate a crashed session that hasn't been cleaned up properly.
3. Ask the database shell to check itself: $ sqlite3 cache.db "pragma integrity_check;"
4. Backup the database $ sqlite3 cache.db ".backup cache.db.bak"
5. Remove cache.db as you probably have nothing in it (if you are just learning) and try your code again
6. See if the backup works $ sqlite3 cache.db.bak ".schema"

Failing that, read Things That Can Go Wrong and How to Corrupt Your Database Files

Answer 4

I had the same problem: sqlite3.IntegrityError

As mentioned in many answers, the problem is that a connection has not been properly closed.

In my case I had try except blocks. I was accessing the database in the try block and when an exception was raised I wanted to do something else in the except block.

try:
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('''INSERT INTO ...''')
except:
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('''DELETE FROM ...''')
    cur.execute('''INSERT INTO ...''')

However, when the exception was being raised the connection from the try block had not been closed.

I solved it using with statements inside the blocks.

try:
    with sqlite3.connect(path) as conn:
        cur = conn.cursor()
        cur.execute('''INSERT INTO ...''')
except:
    with sqlite3.connect(path) as conn:
        cur = conn.cursor()
        cur.execute('''DELETE FROM ...''')
        cur.execute('''INSERT INTO ...''')