[Flask] Allow user to sort DB output

David Nieder davidnieder at gmx.de
Thu Aug 13 13:13:06 CEST 2015


On 11.08.2015 08:53, Alexandru wrote:
> Hello everyone,
Hey!
Since no one responded yet, I try to give you an answer.

>
> I've built a small Flask app that basically requires a a lot of database
> interaction.

If that's the case you really should look into an orm (e.g. SQLAlchemy).

> I'm almost done with it now, I just need a way for the user to sort the
> information that's received from the database (order by name, price,
> location, etc)
> The problem is I'm not that good at coding yet and I'm not sure how to
> go about doing this.
>
> I'm not going to paste my code here, to avoid clutter, so I'm going to
> use Pastebin
>
> This is the section of code that interogates the DB:
> http://pastebin.com/W76H0TSK
> And this is the template portion of the code: http://pastebin.com/BsCHiU3z
>
> I'm looking to insert anchors, or buttons, or something, into that
> template that would allow the users to sort by (for example) pct_lucru
> and/or data_expirarii and/or denumire_med.
>
> Any ideas for me? I'm kind of stumped.

Well, you already have an ORDER clause in your sql-query. You just need 
to build the query more dynamically (meaning: depending on user input).

You could have links in your template that look sth like this (maybe in 
the table head):

<a href="{{ url_for('rezultate', order='pct_lucru-asc') }}">PUNCT LUCRU</a>

Then in the 'rezultate'-function:

if request.args.get('order') == 'pct_lucru-asc':
     order_by = 'ORDER BY stocuri_disponibile.pct_lucru ASC'
else if request.args.get('order') == 'pct_lucru-desc':
     order_by = 'ORDER BY stocuri_disponibile.pct_lucru DESC'
else:
     order_by = 'ORDER BY stocuri_disponibile.denumire_med ASC'

query  = 'SELECT [...] LIKE ? GROUP BY denumire_med '
query += order_by
query += ' LIMIT ? OFFSET ?'

cur1 = g.db.execute(query, ...)

That would be the basic idea, pretty straight forward. Just keep in mind 
to not include sth into the query that comes directly from the user.
Also: building sql statements like this isn't really fun. An orm can 
make your life a lot easier. If you are interested in an example, I have 
a simple blog software where I do sth similar (filter blog-posts) with 
sqlalchemy:
https://github.com/davidnieder/blackboard-light/blob/master/app/query.py#L13

>
> Cheers!
>
Cheers mate!
I hope this was helpful


More information about the Flask mailing list