SQLite objects created in a thread can only be used in that same thread

Ideas on solving that (derived from discussion in MR !11 (merged)):

A) Using Asyncio - I've created a ticket for that POC: Use Asyncio instead of Threads (#97), but considering it "nice to have" and not worth the effort just for SQLite support. Which we currently don't even need.
B) provide an interface for using thread-local database sessions (e.g. spawn a new database session in new threads and extend the DB.session interface accordingly)
C) AFAIR most threads access the DB objects in a reading manner only. So they could be detached from the database and/or eager loaded.
D) Getting rid of ORM and DB for caching altogether. Using the heavy foundation of a full database and ORM has historic reasons. Not long ago even Redis was part of the chain. Using it just for caching is a bit overhead IMO. We could only cache the playout API endpoint and hold the programme in memory. But that's food for thought for a later stage i guess.

Considered low-priority as we have no hard requirement for SQLite or might skip DB+ORM altogether (see [EPIC] Schedule programme data model without OR... (#100 - closed)).

Stacktrace

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

Traceback (most recent call last):
  File "/usr/lib/python3.9/threading.py", line 973, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.9/threading.py", line 910, in run
    self._target(*self._args, **self._kwargs)
  File "/home/david/code/aura/engine-sumpfralle/src/events.py", line 253, in func
    self.call_event("on_metadata", data)
  File "/home/david/code/aura/engine-sumpfralle/src/events.py", line 148, in call_event
    method(*args)
  File "/home/david/code/aura/engine-sumpfralle/src/plugins/trackservice.py", line 156, in on_metadata
    self.store_clock_info(data)
  File "/home/david/code/aura/engine-sumpfralle/src/plugins/trackservice.py", line 213, in store_clock_info
    if e.meta_data:
  File "/usr/lib/python3/dist-packages/sqlalchemy/orm/attributes.py", line 294, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/lib/python3/dist-packages/sqlalchemy/orm/attributes.py", line 730, in get
    value = self.callable_(state, passive)
  File "/usr/lib/python3/dist-packages/sqlalchemy/orm/strategies.py", line 759, in _load_for_state
    return self._emit_lazyload(
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python3/dist-packages/sqlalchemy/orm/strategies.py", line 900, in _emit_lazyload
    q(session)
  File "/usr/lib/python3/dist-packages/sqlalchemy/ext/baked.py", line 544, in all
    return list(self)
  File "/usr/lib/python3/dist-packages/sqlalchemy/ext/baked.py", line 444, in __iter__
    return q._execute_and_instances(context)
  File "/usr/lib/python3/dist-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1206, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1204, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 822, in _init_compiled
    self.cursor = self.create_cursor()
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 1178, in create_cursor
    return self._dbapi_connection.cursor()
  File "/usr/lib/python3/dist-packages/sqlalchemy/pool/base.py", line 1000, in cursor
    return self.connection.cursor(*args, **kwargs)
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140517269673536 and this is thread id 140517035734592.
[SQL: SELECT playlist_entry_metadata.artificial_id AS playlist_entry_metadata_artificial_id, playlist_entry_metadata.artificial_entry_id AS playlist_entry_metadata_artificial_entry_id, playlist_entry_metadata.artist AS playlist_entry_metadata_artist, playlist_entry_metadata.title AS playlist_entry_metadata_title, playlist_entry_metadata.album AS playlist_entry_metadata_album 
FROM playlist_entry_metadata 
WHERE ? = playlist_entry_metadata.artificial_entry_id]
[parameters: [{'%(140517270128672 param)s': 4}]]
(Background on this error at: http://sqlalche.me/e/13/f405)

Edited 3 years ago

SQLite objects created in a thread can only be used in that same thread

Stacktrace

Designs

Child items ...

Activity