SQLAlchemy: How to group by two fields and filter by date
30,293
Following should point you in the right direction, also see inline comments:
qry = (session.query(
table.c.field1,
table.c.field2,
# #strftime* for year-month works on sqlite;
# @todo: find proper function for mysql (as in the question)
# Also it is not clear if only MONTH part is enough, so that
# May-2001 and May-2009 can be joined, or YEAR-MONTH must be used
func.strftime('%Y-%m', table.c.datestamp),
func.count(),
)
# optionally check only last 2 month data (could have partial months)
.filter(table.c.datestamp < datetime.date.today() - datetime.timedelta(60))
.group_by(
table.c.field1,
table.c.field2,
func.strftime('%Y-%m', table.c.datestamp),
)
# comment this line out to see all the groups
.having(func.count()>1)
)
Author by
0atman
I specialise in functional Rust, Python and Clojure in massively-scalable devops environments. Docker is my god now. Contact me at [email protected]
Updated on July 09, 2022Comments
-
0atman almost 2 years
So I have a table with a datestamp and two fields that I want to make sure that they are unique in the last month.
table.id table.datestamp table.field1 table.field2
There should be no duplicate record with the same field1 + 2 compound value in the last month.
The steps in my head are:
- Group by the two fields
- Look back over the last month's data to make sure this unique grouping doesn't occur.
I've got this far, but I don't think this works:
result = session.query(table).group_by(\ table.field1, table.field2, func.month(table.timestamp))
But I'm unsure how to do this in sqlalchemy. Could someone advise me?
Thanks very much!
-
0atman almost 14 yearsThanks very much van, however your solution pokes holes in my sqlalchemy knowledge, what is the significance of the 'c' attribute of the table object?
-
van almost 14 yearsif you have a
table
object, thenc
is a shortcut tocolumns
. See SQL Expression Language Tutorial: sqlalchemy.org/docs/… -
0atman almost 14 yearsDon't worry, I should have just googled my question, as so often is the case!
-
0atman almost 14 yearsHowever, I have realised that we're not using a native mysql timestamp, we are, in fact, using int(time.time()) to store the time, for compatibility with other systems. I'll modify my question to include this, perhaps I can add a check for only timestamps in the last 2,592,000 seconds? (30 days)
-
0atman almost 14 yearsVan, I didn't notice you answered my question - I had the window still open and I should have F5'd before commenting. Thank you very much.
-
Martin M. about 13 years@van: I love you. It's so simple and clean I wouldn't even have thought of that...