I have a list of jobs I am adding to an apscheduler BlockingScheduler with a ThreadPoolExecutor number the same size as the number of jobs.
The jobs I am adding are using sqlalchemy and interacting with the same database, but i am getting errors:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
I have used a scoped_session and a sessionmaker in my base sqlalchemy set-up.
from os.path import join, realpath
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
db_name = environ.get("DB_NAME")
db_path = realpath(join( "data", db_name))
engine = create_engine(f"sqlite:///{db_path}", pool_pre_ping=True)
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
Base = declarative_base()
Then an example of the scheduled job class I add to apscheduler I have something like this:
from app.data_structures.base import Base, Session, engine
from app.data_structures.job import Job
from app.data_structures.scheduled_job import ScheduledJob
from app.data_structures.user import User
class AccountingProcessorJob(ScheduledJob):
name: str = "Accounting Processor"
def __init__(self, resources: AppResources, depends: List[str] = None) -> None:
super().__init__(resources)
def job_function(self) -> None:
account_dir = realpath(environ.get("ACCOUNTING_DIRECTORY"))
Base.metadata.create_all(engine, Base.metadata.tables.values(), checkfirst=True)
session = Session()
try:
#do some stuff with the session here e.g.
# with some variables that are setup
user = User(user_name=user_name)
session.add(user)
user.extend(jobs)
session.commit()
except:
session.rollback()
finally:
Session.remove()
I was under the impression that using a scoped_session and a session factory would start a new session for each thread, and make it thread-safe.
where the User and Job are sqlalchemy orm objects, eg:
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql.expression import false
from app.data_structures.base import Base
from app.data_structures.job import Job
class User(Base):
__tablename__ = "users"
user_name: Mapped[str] = mapped_column(primary_key=True),
employee_number = Column(Integer)
manager = relationship("User", remote_side=[user_name], post_update=True)
jobs: Mapped[list[Job]] = relationship()
def __init__(
self,
user_name: str,
employee_number: int = None,
manager: str = None,
) -> None:
self.user_name = user_name
self.employee_number = employee_number
self.manager = manager
Can anyone explain what I am doing wrong, and how to go about fixing it?
