filter and update row a particular column of each row based on the multiple column value in the same row python sqlalchemy

Question

Here's a SQL table

|---------------------|------------------|---------------------|------------------|
|          ID         |     StartDate    |        EndDate      |       Status     |
|---------------------|------------------|---------------------|------------------|
|          0          | 2019-12-19T10:00 |   2019-12-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          1          | 2019-11-19T10:00 |   2019-11-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          2          | 2019-12-13T10:00 |   2019-12-17T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          3          | 2019-10-19T10:00 |   2019-10-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          4          | 2019-12-24T10:00 |   2019-12-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|

I want to update update the column Status of each row based on the value of same row two column values StartDate and EndDate

Condition would be if StartDate < current_date and EndDate < current_date

then update that specific column Status value of that particular row as Inactive

if current_date is 2019-12-13T10:00 this should be the resultant output of this operation should be

|---------------------|------------------|---------------------|------------------|
|          ID         |     StartDate    |        EndDate      |       Status     |
|---------------------|------------------|---------------------|------------------|
|          0          | 2019-12-19T10:00 |   2019-12-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          1          | 2019-11-19T10:00 |   2019-11-28T10:00  |     Inactive     |
|---------------------|------------------|---------------------|------------------|
|          2          | 2019-12-13T10:00 |   2019-12-17T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|
|          3          | 2019-10-19T10:00 |   2019-10-28T10:00  |     Inactive     |
|---------------------|------------------|---------------------|------------------|
|          4          | 2019-12-24T10:00 |   2019-12-28T10:00  |       Active     |
|---------------------|------------------|---------------------|------------------|

I tried DBSession.query(User).filter(and_(User.c.Status=="Active",User.c.StartDate < current_date, User.c.EndDate < current_date)).update({"Status":"Inactive"})

Even when i try this

from sqlalchemy import and_, func, update

DBSession.query(User).filter(and_(User.c.Status=="Active",func.date(User.c.StartDate) < current_date, func.date(User.c.EndDate) < current_date)).update({"Status":"Inactive"}) source: SQLAlchemy: how to filter date field?

but I get this error

> Traceback (most recent call last):   File
> "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 2463, in
> __call__
>     return self.wsgi_app(environ, start_response)   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 2449, in
> wsgi_app
>     response = self.handle_exception(e)   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 1866, in
> handle_exception
>     reraise(exc_type, exc_value, tb)   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\_compat.py", line 39, in
> reraise
>     raise value   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 2446, in
> wsgi_app
>     response = self.full_dispatch_request()   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 1951, in
> full_dispatch_request
>     rv = self.handle_user_exception(e)   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 1820, in
> handle_user_exception
>     reraise(exc_type, exc_value, tb)   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\_compat.py", line 39, in
> reraise
>     raise value   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 1949, in
> full_dispatch_request
>     rv = self.dispatch_request()   File "C:\Users\Pl\Envs\r\lib\site-packages\flask\app.py", line 1935, in
> dispatch_request
>     return self.view_functions[rule.endpoint](**req.view_args)   File "D:\cs\serverv0.6.py", line 145, in campaign
>     DBSession.query(User).filter(and_(User.c.StartDate < current_date, User.c.EndDate < current_date)).update({"status": "Inactive"})   File
> "C:\Users\Pl\Envs\r\lib\site-packages\sqlalchemy\orm\query.py", line
> 3862, in update
>     update_op.exec_()   File "C:\Users\Pl\Envs\r\lib\site-packages\sqlalchemy\orm\persistence.py",
> line 1692, in exec_
>     self._do_pre_synchronize()   File "C:\Users\Pl\Envs\r\lib\site-packages\sqlalchemy\orm\persistence.py",
> line 1754, in _do_pre_synchronize
>     target_cls = query._mapper_zero().class_ AttributeError: 'NoneType' object has no attribute 'class_'

What is going wrong?

Answer 1

I found this way

active_users = userTableSession.query(userTable).filter(userTable.c.Status == 'Active').all()
active_users_df = pd.DataFrame(active_users)

# targeting the end date
active_users_df['EndDates'] = pd.to_datetime(active_users_df['EndDate'], format = '%Y-%m-%dT%H:%M')

# fetch the current date
current_date_time = datetime.now().strftime('%Y-%m-%dT%H:%M')

# pick out the rows end date have not expired
expired_user = active_users_df.loc[(active_users_df['EndDates'] < current_date_time)]

# fetch the user ID in a list
inactive_user_list = list(expired_user['ID'])
print(inactive_user_list)

# update the table
u = userTable.update().values(Status="Inactive").where(userTable.c.ID.in_(inactive_user_list))
userTableSession.execute(u)
userTableSession.commit()

Please post a more lazy answer than this?