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

I know this is old, but I'm still getting the problem and this is the first link on Google for it. OP said his issue was that the .db was sitting on a SMB share, which was exactly my situation. My ten minutes' research indicates that this is a known conflict between sqlite3 and smb; I've found bug reports going back to 2007.

I resolved it by adding the "nobrl" option to my smb mount line in /etc/fstab, so that line now looks like this:

//SERVER/share /mnt/point cifs credentials=/path/to/.creds,sec=ntlm,nobrl 0 0

This option prevents your SMB client from sending byte range locks to the server. I'm not too up on my SMB protocol details, but I best I can tell this setting would mostly be of concern in a multi-user environment, where somebody else might be trying to write to the same db as you. For a home setup, at least, I think it's safe enough.

My relevant versions:

• Mint 17.1 Rebecca
• SMB v4.1.6-Ubuntu
• Python v3.4.0
• SQLite v3.8.2
• Network share is hosted on a Win12R2 server

Answer 2

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.

Answer 3

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()

Answer 4

You should check out if there is no DBMS administration and development platform working on your database (like pgAdmin), as this is probably the most popular cause of this error. If there is - commit the changes done and the problem is gone.

Answer 5

I also had this problem. I was trying to enter data into the database without saving changes I had made in it. after i saved the changes worked

Answer 6

I had this problem while working with Pycharm and with a database that was originally given to me by another user.

So, this is how I solve it in my case:

1. Closed all tabs in Pycharm that operate with the problematic database.
2. Stop all running processes from the red square botton in the top right corner of Pycharm.
3. Delete the problematic database from the directory.
4. Upload again the original database. And it worked again.