Query fails after inserting into database (db_session not working) in SQLAlchemy

Question

I faced the following error after running an insertion using SQLAlchemy:

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 140671235118848 and this is thread id 140671365665408.

I am running this on an interactive python shell. I have the following code in /app/database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

And my model is in app/models/product/init.py

So I go to the interactive shell and run the following

>> from app.database import db_session
>> from app.models.product import Product
>> product = Product(name="My Product",price=100)
>> db_session.add(product)
>> db_session.commit()
>> Product.query.all()

For some reasons this fails with the error in the beginning. If I restart my interactive shell it would work appropriately. Any clue on why this might be happening?

inf581 · Accepted Answer · 2021-02-14 22:21:22Z

1

Please try to use StaticPool as it'd maintain a single connection, which might be more suitable for single-thread use (in the shell). Reference

from sqlalchemy.pool import StaticPool
engine = create_engine(
    'sqlite:////tmp/test.db',
    poolclass=StaticPool,
    connect_args={'check_same_thread': False}
)

answered Feb 14, 2021 at 22:21

inf581

6521 gold badge8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gleiry Agustin Serrano Wong Over a year ago

:O This solve the issue on my interactive shell. Thanks any reasoning behind this? or docs where I could have told.

Gleiry Agustin Serrano Wong Over a year ago

Also why is this failing in my single thread application. Anyone has any pointers on a full explanation of the situation?

inf581 Over a year ago

Most likely an outdated version of sqlite

inf581 Over a year ago

Also there may be something weird in your imports. I'd make a minimal app which can reproduce the problem and post its code here. Without that its hard to say the exact reason (cause there is nothing wrong in the code you posted)

Collectives™ on Stack Overflow

Query fails after inserting into database (db_session not working) in SQLAlchemy

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related