Environment
- MacOS Catalina 10.15.7
- Python 3.9.7
- Flask 2.0.1
- SQLte3 3.28.0
Problem
On connecting to SQLite from Flask application , sqlite3.ProgrammingError is raised. This does not happen every time but rarely raise error after bunch of requesting calls are succeeded.
The error says SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 123145517748224 and this is thread id 123145534537728.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
2021-10-22 22:37:00,419 123145517748224 INFO werkzeug _log 225 127.0.0.1 - - [22/Oct/2021 22:37:00] "GET /_token_refresh HTTP/1.1" 200 -
2021-10-22 22:37:02,476 123145517748224 DEBUG asyncio __init__ 59 Using selector: KqueueSelector
2021-10-22 22:37:02,477 123145517748224 DEBUG aaa.api_client.api_client _send 79 Requesting: POST url:"https://some-api/some-endpoint/", data:"{"hoge": "val"}", headers:"{'Authorization': 'Bearer *redacted*', 'content-type': 'application/json', 'Content-Length': '64'}"
2021-10-22 22:37:02,479 123145534537728 ERROR sqlalchemy.pool.impl.NullPool _finalize_fairy 708 Exception during reset or similar
Traceback (most recent call last):
File "/Users/a-user/.pyenv/versions/3.9.7/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 682, in _finalize_fairy
fairy._reset(pool)
File "/Users/a-user/.pyenv/versions/3.9.7/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 887, in _reset
pool._dialect.do_rollback(self)
File "/Users/a-user/.pyenv/versions/3.9.7/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 667, in do_rollback
dbapi_connection.rollback()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 123145517748224 and this is thread id 123145534537728.
2021-10-22 22:37:02,699 123145534537728 DEBUG urllib3.connectionpool _make_request 452 https://some-api:443 "POST /some-endpoint/ HTTP/1.1" 201 144
2021-10-22 22:37:02,701 123145517748224 DEBUG knewton_poc_client.api_client.api_client _send 109 201
|
123145517748224 is application thread managed by flask.- application calls an web API client requesting logic by async/await as asynchronous routine (line 2, 3), but the log shows it’s the same thread with flask application one.
123145534537728 is the other thread, urllib3.connectionpool assigned this to make_request in async/await routine. And it seems this tries to use SQLite object.
Threading concept for web application
Flask application runs app.run(threaded=True) for receiving multiple access with AJAX.
The web application server also uses asyncio with concurrent feature for requesting external API asynchronously.
1
2
3
4
5
6
7
8
9
10
11
|
async def _send(self, prepped, status_no_content=False):
"""async function for sending any API request"""
...
with ThreadPoolExecutor(max_workers=API_CLIENT_THREAD_POOL_SIZE) as executor:
loop = self.get_or_create_eventloop()
with get_httpconnectionpool(self.url, self.headers) as session:
response = await loop.run_in_executor(executor,
functools.partial(session.send, prepped))
status = response.status_code
...
return response_json, status
|
-
Flask supports one request at a time as a default, but threaded=True parameter enables the process to run multiple requests using threads. But I believe this multiple threads are triggered by client side logic, not by server side concurrent logic.
1
|
app.run(debug=FLASK_DEBUG_MODE, threaded=True)
|
I could not figure out why the other thread (123145534537728) for urllib3.connectionpool tries to use SQLite object even thought the original Flask thread (123145517748224).
Probably, I should choose the other web framework like Django and database like mysql that supports multi thread applications.
Solution (Workaround)
Follow setting solves this as a workaround by not checking the thread when connecting to SQLite over database session.
1
2
3
4
5
6
7
|
databese_file = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'poc_client.db')
engine = create_engine(
'sqlite:///' + databese_file,
convert_unicode=True,
connect_args={'check_same_thread': False}
)
db_session = scoped_session(sessionmaker(autocommit=False,autoflush=False,bind=engine))
|
References