sqlalchemy: applying an SQL-like date() function on a datetime column

Question

I would like to group by date and count the resulting number of id's using sqlalchemy.

Unfortunately, my column containing date information created_datetime is a datetime and I would like to use an sql function like

group by date(created_datetime)

in order to group by date.

Here is what I have so far...

conn = engine.connect()

s = my_db.my_table.alias()

q = select([s.c.id]).\
    group_by(s.c.created_datetime).\
    count()

result = conn.execute(q)

for i in result:
    print(i)

conn.close()

Answer 1

Use sqlalchemy.sql.functions.func for this:

from sqlalchemy.sql import func

q = select([s.c.id]).\
    group_by(func.date(s.c.created_datetime)).\
    count()