is writing to sqlite3 db-file necessary for SELECT FROM with sqlalchemy/sshfs

Question

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) attempt to write a readonly database [SQL: 'SELECT clima_samples.id AS clima_samples_id, clima_samples.temperature AS clima_samples_temperature, clima_samples.humidity AS clima_samples_humidity, clima_samples.timestamp AS clima_samples_timestamp, clima_samples.extra AS clima_samples_extra \nFROM clima_samples ORDER BY clima_samples.id DESC\n LIMIT ? OFFSET ?'] [parameters: (1, 0)]

My setup is a sensor-data grabbing process, writing climate samples into a local sqlite file on a remote raspbian host. Owner of process and the file are the same. However, on my local dev rig, I mounted the sensors-grabbers working directory via sshfs with rw permissions and try to do "select from" to work with the data. I do encounter above exception now and then. My understanding is, that a select rows operation is reading operation. Where do I need to improve my understanding? is sshfs mounting done wrong?

Here my sshfs cmd:

sshfs -C -o idmap=user -o allow_other g@192.168.0.254:/ /media/remote/endtropi/

The file on remote:

baropi@endtropi:~ $ ls -la baropi/klima.db 
-rw-r--r-- 1 baropi baropi 2999296 Nov 16 17:09 baropi/klima.db

on the dev rig:

g@toolbox:~$ ls /media/remote/endtropi/home/baropi/baropi/klima.db -la
-rw-r--r-- 1 1016 1017 2999296 Nov 16 17:10 /media/remote/endtropi/home/baropi/baropi/klima.db

Traceback:

[2017-11-16 16:37:10,566] ERROR in app: Exception on /sensors/dht22 [GET]
Traceback (most recent call last):
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: attempt to write a readonly database

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/g/.local/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/g/.local/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.5/dist-packages/flask_restful/__init__.py", line 480, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/g/.local/lib/python3.5/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/flask_restful/__init__.py", line 595, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/g/src/global/baropi.git/baropi/web.py", line 26, in get
    ClimaSample.id.desc()
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2755, in first
    ret = list(self[0:1])
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2547, in __getitem__
    return list(res)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__
    return self._execute_and_instances(context)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2878, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/g/.local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) attempt to write a readonly database [SQL: 'SELECT clima_samples.id AS clima_samples_id, clima_samples.temperature AS clima_samples_temperature, clima_samples.humidity AS clima_samples_humidity, clima_samples.timestamp AS clima_samples_timestamp, clima_samples.extra AS clima_samples_extra \nFROM clima_samples ORDER BY clima_samples.id DESC\n LIMIT ? OFFSET ?'] [parameters: (1, 0)]

flask returns an "Internal Server Error", however I can make another request after the exception and it works exactly as I am expecting.

Not sure, but maybe it has something to do with the fact that you are accessing your sqlite db by multiple processes at the same time? Check [this](https://stackoverflow.com/questions/10325683/can-i-read-and-write-to-a-sqlite-database-concurrently-from-multiple-connections). — Borys Serebrov, Nov 17 '17 at 14:10
this might be it.. seems like my decision to use sqlite came biting me in the back. Your link mentiones WAL, nice to know this, but sadly it wont help, as it requires the concurrent processes to be on the same machine. I might setting up a fullblown sql server, to have better concurrency. Thanks for the advice, though! — krysopath, Nov 17 '17 at 14:54

is writing to sqlite3 db-file necessary for SELECT FROM with sqlalchemy/sshfs

0 Answers0