Working with func in dates, No response, no error message

[James Paul Chibole]

Hi everyone, I am trying to retrieve deceased persons who died in the current month but the output gives no result. Here is my code with query done in Python Flask:

from datetime import datetime                                      from sqlalchemy import func                              
@app.route('/user/<username>')                            @login_required                                                     def user(username):    current_month = datetime.today().date().strftime("%B")               monthly_anniversaries =                                                   current_user.followed_deaths().filter(Deceased.burial_cremation_dat  e      <datetime.today().date()).filter(func.strftime('%B',Deceased.date_o  f_death== current_month)).order_by(Deceased.timestamp.desc())      return render_template("user.html", monthly_anniversaries     =monthly_anniversaries)

[Philip Semanchuk]

Flask is an extra layer of complication here that’s getting in the way of what you’re trying to debug. That’s not a knock on Flask (I use it too), it’s just not germane to a SQLAlchemy problem. My suggestion is that you try putting a breakpoint in your flask app so you can play with the query inside the debugger. Ensure that current_month is what you think it is, hardcode query params instead of passing variables to see if that changes results, remove some of the filter clauses to see if the results change the way you expect, etc. And of course having a look at the SQL that’s being sent to the server will give you some clues too, although that can be hard to get to depending on your environment.


Hope this helps
Philip

[James Paul Chibole]

Thank you Philip for your suggestion.

[Nancy Andeyo]

I am still struggling to find the solution on this issue, the issue is with the func, because when I omit the comparison with the the func sqlachemy function, the outcome is expected. However, I want to ensure that only items for this month are displayed: 

he filter .filter(func.strftime('%m', func.date(Events.date_of_event) == current_month = datetime.today().strftime('%m'))) to get all the events that happened in the current month is not working. But displays all the events. Help me figure out the problem. I am using SQLite and sqlalchemy with Flask to query the db.  I have imported the `func` from sqlalchemy as well as `datetime ` from `datetime` as required.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f6a17840-cdfd-4f26-bdc5-0a75af82fa91n%40googlegroups.com.

[Nancy Andeyo]

I am still struggling to find the solution on this issue, the issue is with the func, because when I omit the comparison with the func sqlalchemy function, the outcome is expected. However, I want to ensure that only items for this month are displayed: 

he filter .filter(func.strftime('%m', func.date(Events.date_of_event) == datetime.today().strftime('%m'))) to get all the events that happened in the current month is not working. But displays all the events. Help me figure out the problem. I am using SQLite and sqlalchemy with Flask to query the db.  I have imported the `func` from sqlalchemy as well as `datetime ` from `datetime` as required.

--

[Lele Gaifax]

Nancy Andeyo <nand...@gmail.com> writes:

> the filter *.filter(func.strftime('%m', func.date(Events.date_of_event) ==
> datetime.today().strftime('%m')))* to get all the events that happened in

> the current month is not working. But displays all the events.

Read carefully what you have written:

> func.strftime('%m', func.date(Events.date_of_event) == datetime.today().strftime('%m'))

You are passing a boolean value to the strftime() function, and that's
not what you probably meant/want.

ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Nancy Andeyo]

Hi Lee, thanks for responding.

The code was only part of the entire query, perhaps I needed to provide the entire query and try to explain and give an explanation a little bit.

Here is the entire code:

monthly_events = current_user.followed_events().filter(Event.event_date < datetime.today().date()).filter(func.strftime('%m', Event.event_date = datetime.today().strftime('%m'))).order_by(Event.timestamp.desc())

The section of the query: current_user.followed_events().filter(Event.event_date <= datetime.today().date()) gives all upcoming events that are yet to happen, including those that are due today. This part works correctly. 

The section: current_user.followed_events().filter(Event.event_date < datetime.today().date()).order_by(Event.timestamp.desc()) arranges the events in dissenting order and this section works currently for all upcoming events.

However, the part with problems is this one: .filter(func.strftime('%m', Event.event_date = datetime.today().strftime('%m'))) where the aim to filter out events that will take place in the current month. This is the section that I posted, yet I needed to post the entire query for what I am intending to achieve is understood.  Note that I have imported the following modules from sqlalchemy import func and from datetime import datetime at the top.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87ttxueubh.fsf%40metapensiero.it.

[Nancy Andeyo]

Hi Lee, thanks for responding.

The code was only part of the entire query, perhaps I needed to provide the entire query and try to explain and give an explanation a little bit.

Here is the entire code:

monthly_events = current_user.followed_events().filter(Event.event_date < datetime.today().date()).filter(func.strftime('%m', Event.event_date = datetime.today().strftime('%m'))).order_by(Event.timestamp.desc())

The section of the query: current_user.followed_events().filter(Event.event_date <= datetime.today().date()) gives all upcoming events that are yet to happen, including those that are due today. This part works correctly. 

The section: current_user.followed_events().filter(Event.event_date < datetime.today().date()).order_by(Event.timestamp.desc()) arranges the events in dissenting order and this section works currently for all upcoming events.

However, the part with problems is this one: .filter(func.strftime('%m', Event.event_date = datetime.today().strftime('%m'))) where the aim to filter out events that will take place in the current month. This is the section that I posted, yet I needed to post the entire query for what I am intending to achieve is understood.  Note that I have imported the following modules from sqlalchemy import func and from datetime import datetime at the top.

The event_date field in the models.py is stored as a db.DateTime with a default = datetime.utcnow. 

On Wed, Apr 5, 2023 at 9:07 AM Lele Gaifax <le...@metapensiero.it> wrote:

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87ttxueubh.fsf%40metapensiero.it.

[Lele Gaifax]

Nancy Andeyo <nand...@gmail.com> writes:

> However, the part with problems is this one: .filter(func.strftime('%m',
> Event.event_date = datetime.today().strftime('%m'))) where the aim to
> filter out events that will take place in the current month. This is the
> section that I posted, yet I needed to post the entire query for what I am
> intending to achieve is understood.

I can't say if the typos are due to you rewriting the cide in these
messages, or if instead they are effectively present in the real code,
but also the above is not correct:

.filter(func.strftime('%m', Event.event_date = datetime.today().strftime('%m')))

This should raise a syntax error when evaluated by Python...

For comparison, the following complete script works for me:

from datetime import date
from pprint import pprint

from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy.orm import Session

from sol.models import Tourney


engine = create_engine('sqlite:///development.db')
session = Session(engine)

q = session.query(Tourney)
q = q.filter(func.strftime('%m', Tourney.date) == date.today().strftime('%m'))

pprint([(t.description, t.date) for t in q.limit(3).all()])

and emits

[('2° Torneo', datetime.date(2001, 4, 1)),
('7° Torneo', datetime.date(2004, 4, 24)),
('7° Torneo', datetime.date(2005, 4, 30))]

Hope this helps,

[James Paul Chibole]

Sorry, it is a type, it should actually be  .filter(func.strftime('%m', Event.event_date == datetime.today().strftime('%m')))

Let me go through your latest response and will get back to you. Thank you for the prompt response.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/4oPfuzAjw48/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87pm8ier3i.fsf%40metapensiero.it.

--

James Paul Chibole

       -In the name of God-

[Nancy Andeyo]

The only difference between my code in is that I am not using session. Might it be the reason why my query is not working as expected? Here is my query one more time:

monthly_events = current_user.followed_events().filter(Event.event_date < datetime.today().date()).filter(func.strftime('%m', Event.event_date == datetime.today().strftime('%m'))).order_by(Event.timestamp.desc())

Again, this is the problem the query is trying to solve. I want to know the subscribed events (events that I am following) that took in place current month. This is irrespective of whether they took place 5 years ago, or a year ago, so long as they took place in the current month.

However, the query gives no events that took place in the current month, yet I have events that indeed took place in April (the current month). The current month is to be automated, which I have successfully accomplished through datetime.today().strftime('%m'). 

By breaking the query into sections, I was able to know where the problem is. The section of the query: current_user.followed_events().filter(Event.event_date < datetime.today().date())gives all events that have passed (yesterday and beyond). This part works correctly.

The section: current_user.followed_events().filter(Event.event_date < datetime.today().date()).order_by(Event.timestamp.desc())arranges these pasts events in descending order and this section works correctly, as well.

However, the part with problem is: .filter(func.strftime('%m', Event.event_date == datetime.today().strftime('%m')))where the aim is to filter out events that took place in the current month, irrespective of the year they took place.

Note that I have imported the following modules from sqlalchemy import funcand from datetime import datetimeat the top of the routes.py.

The event_date field in the models.py is stored as a db.DateTime with a default = datetime.utcnow. I am using Flask, with SQLite db, but will change it to Postgresql later.

I hope the information is enough, otherwise let me know if additional information is needed.

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2B9bh8dc1ph6UC49pqjVeVfg-DEo%2BE2vFxu0NCXoB9AonsWXtQ%40mail.gmail.com.

[Nancy Andeyo]

The only difference between my code and yours is that I am not using sessions. Might it be the reason why my query is not working as expected? Here is my query one more time:

monthly_events = current_user.followed_events().filter(Event.event_date < datetime.today().date()).filter(func.strftime('%m', Event.event_date) == datetime.today().strftime('%m')).order_by(Event.timestamp.desc())

Again, this is the problem the query is trying to solve. I want to know the subscribed events (events that I am following) that took place in the current month. This is irrespective of whether they took place 5 years ago, or a year ago, so long as they took place in the current month.

However, the query gives no events that took place in the current month, yet I have events that indeed took place in April (the current month). The current month is to be automated, which I have successfully accomplished through datetime.today().strftime('%m'). 

By breaking the query into sections, I was able to know where the problem is. The section of the query: current_user.followed_events().filter(Event.event_date < datetime.today().date())gives all events that have passed (yesterday and beyond). This part works correctly.

The section: current_user.followed_events().filter(Event.event_date < datetime.today().date()).order_by(Event.timestamp.desc())arranges these past events in descending order and this section works correctly, as well.

However, the part with problem is: .filter(func.strftime('%m', Event.event_date) == datetime.today().strftime('%m'))where the aim is to filter out events that took place in the current month, irrespective of the year they took place.

Note that I have imported the following modules from sqlalchemy import funcand from datetime import datetimeat the top of the routes.py.

The event_date field in the models.py is stored as a db.DateTime with a default = datetime.utcnow. I am using Flask, with SQLite db, but will change it to Postgresql later.

I hope the information is enough, otherwise let me know if additional information is needed.

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2B9bh8dc1ph6UC49pqjVeVfg-DEo%2BE2vFxu0NCXoB9AonsWXtQ%40mail.gmail.com.

[Nancy Andeyo]

The solution that finally worked for me is:

 from sqlalchemy import extract
   
    #....
    monthly_events = current_user.followed_events().filter(Event.event_date < datetime.today().date()).filter(extract('month', Event.event_date) == datetime.today().month)).order_by(Event.timestamp.desc())
    #...

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2B9bh8dc1ph6UC49pqjVeVfg-DEo%2BE2vFxu0NCXoB9AonsWXtQ%40mail.gmail.com.

[Jonathan Vanasco]

> The only difference between my code and yours is that I am not using sessions. 

The Flask-Sqlalchemy package handles all the session stuff automatically in the background. The code you write is not interacting with the session explicitly, but it is utilizing the session implicitly.  

IMHO, the best way to troubleshoot these things is to create a standalone script like Lele Gaifax supplied for you above, and iterate fixes on that.