views:

385

answers:

5

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?

+3  A: 

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

msw
I'm letting this answer stand as it is generally useful, but my other answer is probably the correct one.
msw
Even better: add your other answer as the 7th thing to check ;)
ΤΖΩΤΖΙΟΥ
Thanks for your response. I have no any data in this database (cache.db is 0 byte size) so it's not necessary to backup it.1) fuser doesn't output anything2) no db-journal file before starting3) sqlite3 cache.db "pragma integrity_check;" says ok5) I tried to remove and rename cache.db file many times ;-)Now I've tested it on another machine but on the same OS Ubuntu 9.10 server edition and I've got the same result. This error happens when I install python-sqlite package.
Gregory Lo
A: 

The database is locked by another process that is writing to it. You have to wait until the other transaction is committed. See the documentation of connect()

Florian Diesch
A: 

One possible reason for the database being locked that I ran into with SQLite is when I tried to access a row that was being written by one app, and read by another at the same time. You may want to set a busy timeout in your SQLite wrapper that will spin and wait for the database to become free (in the original c++ api the function is sqlite3_busy_timeout). I found that 300ms was sufficient in most cases.

But I doubt this is the problem, based on your post. Try other recommendations first.

sheepsimulator
+1  A: 

Oh, your traceback gave it away: you have a version conflict. You have installed some old version of sqlite in your local dist-packages directory when you already have sqlite3 included in your python2.6 distribution and don't need and probably can't use the old sqlite version. First try:

$ python -c "import sqlite3"

and if that doesn't give you an error, uninstall your dist-package:

easy_install -mxN sqlite

and then import sqlite3 in your code instead and have fun.

msw
I checked to use sqlite3 and it works different. It creates db-journal file and waits. Then "database is locked" again while sqlite without "3" doesn't wait for anything.
Gregory Lo
A: 

here's a neat workaround for simultaneous access...

while(1):
 con=sqlite.connect('user.db',timeout=1)
 c=con.cursor()
 try:
         c.execute("select * from queue")
  res=c.fetchall()
 except sqlite.OperationalError:
      print("database locked")
 num_users=len(res)

...

works for me .. hunting around for hours paid off...!!