3

I'm getting an OperationalError: (OperationalError) database is locked error when connection via SQLAlchemy in an IPython notebook instance and I'm not sure why.

I've written a Python interface to a SQLite database using SQLAlchemy and the Declarative Base syntax. I import the database models into an IPython notebook to explore the data. This worked just fine this morning. Here is the code:

from psf_database_interface import session, PSFTable
query = session.query(PSFTable).first()

But this afternoon after I closed my laptop with IPython running (it restarts the server just fine) I started getting this error. It's strange because I can still open the database from the SQLite3 command line tool and query data. I don't expect any other processes to be connecting to this database and running fuser on the database confirms this. My application is not using any concurrent processes (in the code I've written, IDK if something is buried in SQLAlchemy or IPython), and even if it were I'm just doing a read operation, which SQLite does support concurrently.

I've tried restarting the IPython kernel as well as killing and restarting the IPython notebook server. I've tried creating a backup of the database and replacing the database with the backup as suggested here: https://stackoverflow.com/a/2741015/1216837. Lastly, out of desperation, I tried adding the following to see if I could clean out something stuck in the session somehow:

print session.is_active
session.flush()
session.close()
session.close_all()
print session.is_active

Which returns True and True. Any ideas?

Update: I can run the code snippet that is causing errors from a python file without any issues, the issue only occurs in IPython.

Community
  • 1
  • 1
ACV
  • 1,895
  • 1
  • 19
  • 28
  • possible duplicate of [Python SQLite: database is locked](http://stackoverflow.com/questions/2740806/python-sqlite-database-is-locked) – David Nehme Nov 13 '13 at 23:42
  • @DavidNehme Hi, I would argue this is not a duplicate because 1) IPython seems to be causing the lock not SQLite, 2) I think I've already tried the steps in the possible duplicate question that are relevant to my code, most of them aren't thought because 3) I'm using SQLAlchemy not the SQLite3 module directly. – ACV Nov 14 '13 at 03:41
  • So the sqlite file stays "locked" even when all the Python processes are killed? the file itself might have the lock bit set. Copying it or maybe catting it out to a new file may resolve. – zzzeek Nov 15 '13 at 17:54
  • @zzzeek No, the SQLite file is never locked from the command line or from a Python script. Only from an IPython session, and once it locks I can't open it from IPython again. – ACV Nov 17 '13 at 06:33
  • im saying, when all the python procs are killed, nobody's accessing the SQLite file, but starting up again, it's not accessible. the file might have the locking flags set on it, is what I'm getting at. take a look at [how to corrupt your database files](http://sqlite.org/lockingv3.html) in the locking section here. – zzzeek Nov 17 '13 at 13:19

1 Answers1

0

I faced the same problem. I can run python scripts but the IPython raise the below exception.

You need to check with fuser there is no process which is using this. But if you cannot find anything and your history of commands are not important to you, you can use the following workaround.

When I deleted the /home/my_user/.ipython/profile_default/history.sqlite file, I can start the IPython. The history is empty as I mentioned above.

    $ ipython                                                             
    [TerminalIPythonApp] ERROR | Failed to create history session in /home/my_user/.ipython/profile_default/history.sqlite. History will not be saved.
    Traceback (most recent call last):
    File "/home/esadrfa/libs/anaconda3/lib/python3.6/site-packages/IPython/core/history.py", line 543, in __init__
        self.new_session()
    File "<decorator-gen-22>", line 2, in new_session
    File "/home/esadrfa/libs/anaconda3/lib/python3.6/site-packages/IPython/core/history.py", line 58, in needs_sqlite
        return f(self, *a, **kw)
    File "/home/esadrfa/libs/anaconda3/lib/python3.6/site-packages/IPython/core/history.py", line 570, in new_session
        self.session_number = cur.lastrowid
    sqlite3.OperationalError: database is locked
    [TerminalIPythonApp] ERROR | Failed to open SQLite history :memory: (database is locked).
eSadr
  • 395
  • 5
  • 21