CODEWITHSUNDEEP
pythonflasksqlalchemy
Gleiry Agustin Serrano Wong
Gleiry Agustin Serrano Wong

Reputation: 11

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

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?

Upvotes: 1

Views: 72

Answers (1)

inf581
inf581

Reputation: 642

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}
)

Upvotes: 1

Related Questions