Using SQLAlchemy session from Flask raises "SQLite objects created in a thread can only be used in that same thread"
Taking a hint from this SO answer I searched SA docs and found out you can do this:
engine = create_engine('sqlite:////var/www/homepage/blog.db?check_same_thread=False')scoped_session wasn't really suitable in my case since Flask-SQLAlchemy only takes a connection string argument:
from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyclass Config(object): SQLALCHEMY_DATABASE_URI = 'sqlite:///app.db?check_same_thread=False'db = SQLAlchemy()def create_app(): app.config.from_object(Config) app = Flask(__name__) db.init_app(app) ...According to sqlite3.connect:
By default,
check_same_threadisTrueand only the creating thread may use the connection. If setFalse, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.
SQLAlchemy (and in this case SQLite also) doesn't work if you share a session across threads. You may not be using threads explicitly, but mod_wsgi is, and you've defined a global session object. Either use scoped_session to handle creating a unique session for each thread.
session = scoped_session(sessionmaker(bind=engine))@app.teardown_requestdef remove_session(ex=None): session.remove()@app.route('/')def example(): item = session.query(MyModel).filter(...).all() ...Preferably, use Flask-SQLAlchemy which handles this and other things for you. The SQLAlchemy docs recommend you use the integration library rather than doing this yourself.
db = SQLAlchemy(app)@app.route('/')def example(): item = db.session.query(MyModel).filter(...).all() ...Also note that you should only be defining the engine, session, etc. once and importing it elsewhere, rather than redefining it in each file like your current code does.
I had have similar problem with SQLAlchemy + SQLite + FastAPI and I added parameterconnect_args={"check_same_thread": False}
Full Code:
#SqlAlchemy Setup SQLALCHEMY_DATABASE_URL = 'sqlite+pysqlite:///.db.sqlite3:' engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}, echo=True, future=True ) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = declarative_base()