fastapi-sqlalchemy
fastapi-sqlalchemy copied to clipboard
Synchronous path operation functions are not compatible when using SQLite.
Exception
I am developing an embedded program based on SQLite and have encountered the following exception:
Traceback (most recent call last):
File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 260, in _close_connection
self._dialect.do_terminate(connection)
File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 689, in do_terminate
self.do_close(dbapi_connection)
File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 692, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 6206205952 and this is thread id 8700050048.
Reproduce
import uvicorn
from fastapi import FastAPI
from fastapi_sqlalchemy import DBSessionMiddleware, db
app = FastAPI()
app.add_middleware(DBSessionMiddleware, db_url="sqlite:///db.db", commit_on_exit=True)
# Error
@app.get("/")
def main():
db.session.execute(
"CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))"
)
return {"Hello": "World"}
# Ok
@app.get("/async")
async def async_main():
db.session.execute(
"CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))"
)
return {"Hello": "World"}
if __name__ == "__main__":
uvicorn.run(app, host="0.0.0.0", port=8000)
Reason
By ChatGPT.
This error message indicates that you are trying to use an SQLite object (such as a connection or cursor) in a thread different from the one where it was created. SQLite objects are not thread-safe, meaning they can only be used in the thread where they were created.
To resolve this error, you should ensure that all SQLite objects are created and used within the same thread. If you need to use an SQLite object in multiple threads, you should create a new object for each thread.
https://github.com/mfreeborn/fastapi-sqlalchemy/blob/3d182f202cb673a09c52b07fd2d52cb57ce382c5/fastapi_sqlalchemy/middleware.py#L43-L45
When calling with db()
in the main thread, and since the path operation function is a normal function, it will be called and executed in a sub-thread. The same SQLite object appears in different threads, resulting in the above exception being thrown.
The ultimate reason is that fastapi-sqlalchemy does not support synchronous path operation functions, so will this feature be considered for implementation?