CODEWITHSUNDEEP
pythonsqlalchemy
moth
moth

Reputation: 2359

How to return multiple rows in sqlalchemy based on value of a column

I have some table in a database that looks like that:

phase_type phase_start phase_end
Obsolete   01/01/2021  02/02/2022
Obsolete   01/03/2021  02/07/2022
Obsolete   05/01/2021  09/02/2022
Available  05/07/2021  09/02/2027
Available  05/07/2023  09/02/2025
Available  05/07/2024  09/02/2029

If I want to select on this table and return only the rows that the date of today is lying between the range of 30 days in the past of phase_end, I could do like this:

from datetime import date,timedelta
fromo sqlalchemy import select

past   = my_table.phase_end - timedelta(30)
future = my_table.phase_end

query = select(my_table).where(date.today() >= past,date.today() <= future)

session.exec(query).fetchall()

However I would like to use phase_start when calculating past and future for the case when phase_type is Obsolete, for all the other cases I would like to use phase_end as above. Thus the range should be calculated based on the value that phase_end takes. How can I do this and return all rows that pass the conditions ?

Upvotes: 0

Views: 723

Answers (1)

JayPeerachai
JayPeerachai

Reputation: 3842

I am not sure that I understand your problem correctly, but if you want to return rows that meet the specified conditions, you can use a case statement in the where clause of your select query.

from datetime import date,timedelta
from sqlalchemy import case, select

past = case([(my_table.phase_type == 'Obsolete', my_table.phase_start)], else_=my_table.phase_end) - timedelta(30)
future = case([(my_table.phase_type == 'Obsolete', my_table.phase_start)], else_=my_table.phase_end)

query = select(my_table).where(date.today() >= past,date.today() <= future)

session.exec(query).fetchall()

This query will return all rows from my_table where date.today() falls within the range of 30 days in the past of either phase_start (if phase_type is Obsolete) or phase_end (for all other cases).

Upvotes: 1

Related Questions