ERROR: sqlite3 thread

[mereacre]

Thread Error

Error value

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140101201913600 and this is thread id 140101210306304.

Example code

from nqm.iotdatabase.database import Database

@app.callback(Output("dataset-footer", "children"),
              [Input("dropdown-datasets", "value")])
def datasets_dropdwon_change(value):
    db = Database(value, "file", "w+")
    return ""

app.callback creates a separate thread from the main thread where it executes the body of the function datasets_dropdown_change.

[mereacre]

Similar issues

1. https://www.reddit.com/r/learnpython/comments/5cwx34/flask_sqlite_error/

2. https://stackoverflow.com/questions/48218065/programmingerror-sqlite-objects-created-in-a-thread-can-only-be-used-in-that-sa

3. https://stackoverflow.com/questions/34009296/using-sqlalchemy-session-from-flask-raises-sqlite-objects-created-in-a-thread-c

[aloisklink]

https://stackoverflow.com/questions/34009296/using-sqlalchemy-session-from-flask-raises-sqlite-objects-created-in-a-thread-c/34010159#34010159 seems like an answer on how to solve it.

http://docs.sqlalchemy.org/en/latest/orm/contextual.html shows how to solve it. It looks like the problem is SQLAlchemy caches the connection by default, so if it uses from a different thread, the same SQLite connection is used. It should be an easy fix, once I get to a proper internet connection/laptop (there is a function called scoped_session I need to use for multithreaded software).

My internet is really dodgy right now, but I'll see if I can get it fixed (with a simple test) by tomorrow morning.

On Tue, Feb 5, 2019, 14:51 Alexandru Mereacre notifications@github.com wrote:

Similar issues

1.

https://www.reddit.com/r/learnpython/comments/5cwx34/flask_sqlite_error/ 2.

https://stackoverflow.com/questions/48218065/programmingerror-sqlite-objects-created-in-a-thread-can-only-be-used-in-that-sa

— You are receiving this because you were assigned.

Reply to this email directly, view it on GitHub https://github.com/nqminds/nqm-iot-database-py/issues/17#issuecomment-460665576, or mute the thread https://github.com/notifications/unsubscribe-auth/ASzaQ9ezK0UNofoCYy8c32lvd-VGsPviks5vKZpigaJpZM4ajXdX .

[mereacre]

An easier and more robust is to use Flask-SQLAlchemy.

So if in my app I define the db as:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

then we need to provide an additional parameter for the nqm-iot-database-py:openDatabase. We can provide the above db instance as a parameter for the Database class constructor. For instance:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from nqm.iotdatabase.database import Database

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

iot_db = Database(db)

In the Database class you will have to use the db.session method (see. example).

Also we want to allow multiple database binds as shown here. To refer to a particular bind one has to use the bind attribute for the basic db functions.

[mereacre]

@aloisklink After merging the branch #18 I still get the sqlite thread error. Did you check my comment above?

[aloisklink]

Hi @mereacre from rainy and misty Wales,

Apologies for the issue, I only wrote the test for addData() in a multithreaded environment, not getData() last night (it was late at night and I was slightly drunk). (Btw, @IGlautier's laptop is pretty good, the battery lasts forever). I've added a test/fix for it in b5f3902a963cf28239ff6669adb29ae9725ac00c on master.

If that doesn't fix the SQLite thread error, I might need to make a full Flask test case.

Yeah, I had a look at using Flask-SQLAlchemy. It looks much more powerful (and is the recommended way of doing it with Flask), but I didn't use it because:

1. Looks like it can only support one connection at a time.
2. Will only be supported in Flask, not for our other use cases.
3. Most important It would take many more lines of code to change to implement. It might be worth using it instead, when we have more time (and I'm not coding on the go :)) Doing it with scoped_session only took like 30 lines of code (even if it isn't as powerful).

[mereacre]

@aloisklink That's why people are not allowed to drive after lost of drinking. The time-space continuum warps.

I'll test your fix now.

[mereacre]

The fix b5f3902 on master looks like it's working.

[aloisklink]

Awesome! That is good to hear.

I'll close this issue once I add docstrings to explain what's been added.

On Wed, Feb 6, 2019, 15:38 Alexandru Mereacre notifications@github.com wrote:

The fix b5f3902 https://github.com/nqminds/nqm-iot-database-py/commit/b5f3902a963cf28239ff6669adb29ae9725ac00c on master looks like it's working.

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/nqminds/nqm-iot-database-py/issues/17#issuecomment-461068602, or mute the thread https://github.com/notifications/unsubscribe-auth/ASzaQ3VHVRFMUab1FkaIHpuwG2NJsR9Zks5vKvbrgaJpZM4ajXdX .