Как переписать SQL-запрос в SQLite

Question

Пишу утилиту, которая замеряет расходы времени на выполнение разных задач В SQLite всего две таблицы: Job & History

class Job(Base):
    """Выполняемые задачи"""
    __tablename__ = 'Job'
    id = Column(Integer, primary_key=True)
    command = Column(String(128))
    description = Column(String(128))

    def __init__(self, command: str, description: str):
        self.command = command
        self.description = description


class History(Base):
    """История выполняемых заданий"""
    __tablename__ = 'History'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    job = Column(Integer)
    started = Column(DateTime, default=datetime.datetime.now)
    ended = Column(DateTime, default=None, nullable=True)

    def __init__(self, user_id: int, job: int):
        self.user_id = user_id
        self.job = job

В конце дня идет вывод отчета за день: сколько времени было потрачено на каждую задачу.

Вычисления написал в SQL, а вот как этот же код оформить в SQLAlchemy догадываюсь смутно

SELECT j.description, sum((strftime('%s', h.ended) - strftime('%s', h.started)) / 60) as minutes
FROM History as h
INNER JOIN Job as j ON j.id = h.job
WHERE h.started >= date('now','-1 day')
GROUP BY job
ORDER BY minutes DESC

Answer 1

Так как Вы не используете внешний ключ для связи History с Job (Foreign Key):

with SessionContext() as session:
    query = session.query(Job)
    query = query.join(History, History.job == Job.id)

Если добавить - job = Column(Integer, ForeignKey('Job.id', ondelete='CASCADE'), nullable=False, index=True), то ORM сам выполнит INNER JOIN:

with SessionContext() as session:
    query = session.query(Job, History)

сразу отфильтруем по дате

    query = query().filter(History.started >= datetime.datetime.now() - datetime.timedelta(days=1))

далее рассмотрим group_by, sum и order_by, попутно выбрав остальные поля.

 ...
   query = query(Job.description,
                (sqlalchemy.func.sum((sqlalchemy.func.strftime('%s', History.ended) -\
                                    sqlalchemy.func.strftime('%s', History.started))/60).label('minutes')\
                                                                  )\
                ).group_by(Job.id).order_by('minutes')

остается только собрать все во едино. Только не уверен, что к minutes можно будет так обратиться в сортировке, возможно будет требоваться корректировка. Как и не проверял работу функций для расчета времени.

Answer 2

Название таблиц принято писать с маленькой буквы, снейк-кейсом.
В SQLAlchemy очень удобно строятся связи, я немного изменил ваш пример, суть вы поймёте. Ссылку на документацию прикрепляю.

class Job(Base):
    """Выполняемые задачи"""
    __tablename__ = 'job'
    id = Column(Integer, primary_key=True)
    command = Column(String(128))
    description = Column(String(128))
    history = relationship("History", back_populates="job")

    def __init__(self, command: str, description: str):
        self.command = command
        self.description = description


class History(Base):
    """История выполняемых заданий"""
    __tablename__ = 'history'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    # job = Column(Integer)
    started = Column(DateTime, default=datetime.datetime.now)
    ended = Column(DateTime, default=None, nullable=True)
    job_id = Column(Integer, ForeignKey('job.id'))
    job = relationship("Job", back_populates="history")

    def __init__(self, user_id: int, job: int):
        self.user_id = user_id
        self.job = job

Отвечая на ваш вопрос:

В конце дня идет вывод отчета за день: сколько времени было потрачено на каждую задачу. Вычисления написал в SQL, а вот как этот же код оформить в SQLAlchemy догадываюсь смутно

Вы можете использовать инструкцию ниже. Она довольно простая. Ах да, вот и время затраченное по задаче.

>> from sqlalchemy.orm import Session
>> session = Session(engine)
>> db_job = session.query(models.Job).first()
>> print(sum([(i.started - i.ended).seconds//60 % 60 for i in db_job.history]))
177  # minutes

db_job.history - список объектов из БД, каждый имеет соответствующие поля

БЛОГ НА HUSL

Как переписать SQL-запрос в SQLite

Ответы (2 шт):