0

I have looked into many similar topics but the issue was not resolved anywhere in stackoverflow. I have table:

name|something
john,blabla
john,xdxdxd
john,15kusdprogrammer
eve,givebackrib
malcolm,anything
eve,welcome
eve,to
eve,internet

I would like to get distinct name with counted rows - SQL query looks like this:

select distinct name,count(*) from TABLE_NAME group by name order by 2 desc;

and the result looks like:

eve,4
john,3
malcolm,1

I've tried to create flask-sqlalchemy query like this:

TABLE_NAME.query.distinct(TABLE_NAME.name).with_entities(TABLE_NAME.name, count(TABLE_NAME.something)).all()

but the only one solution which I have created is this one:

for name in TABLE_NAME.query.distinct(TABLE_NAME.name).with_entities(TABLE_NAME.name).all():
print("{}: {}".format(name, TABLE_NAME.query.filter(TABLE_NAME.name== name[0]).count()))

but queries in for loop takes huge amount of time. Can someone please help me create the flask-sqlalchemy query from sqlite code:

select distinct name,count(*) from TABLE_NAME group by name order by 2 desc;
J. Doe
  • 117
  • 1
  • 11

1 Answers1

1

You can try 

TABLE_NAME.query(TABLE_NAME.name, func.count(TABLE_NAME.name).label('count')).group_by(TABLE_NAME.name).all()

and with the order by count it should look like 

TABLE_NAME.query(TABLE_NAME.name, func.count(TABLE_NAME.name).label('count')).group_by(TABLE_NAME.name).order_by(desc(func.count(TABLE_NAME.name))).all()

sqlalchemy function documentation

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Andrew McFadden
  • 21
  • 1