python - SQLAlchemy:如何按两个字段分组并按日期过滤

Question

所以我有一个包含日期戳和两个字段的表，我想确保它们在上个月是唯一的。

table.id
table.datestamp
table.field1
table.field2

上个月不应有相同字段1+2复合值的重复记录。

我脑海中的步骤是:

1. 按两个字段分组
2. 回顾上个月的数据，确保不会出现这种独特的分组情况。

我已经走到这一步了，但我认为这行不通:

result = session.query(table).group_by(\
    table.field1,
    table.field2,
    func.month(table.timestamp))

但我不确定如何在 sqlalchemy 中执行此操作。有人可以给我建议吗？

非常感谢!

Answer 1

以下应该为您指明正确的方向，另请参阅内联评论:

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)
  )