Saturday, February 11, 2012

HTSQL-powered Flask

With seven lines of code, you can plug HTSQL into a web application platform like Flask. Yes, I know that you know SQLAlchemy already; the point is that then your Flask apps can support HTSQL's rich in-the-URL filtering language. (Also, it lets you skip the schema definition step for read-only apps.)

For instance, this app serves reports from HTSQL's sample "University" database. Each report can run as-is


or can accept arbitrary HTSQL filters


The advantage over plain HTSQL is that you can use full Flask power. In this case, the reports come through a template that includes functioning links in each row, so it's a drill-down report:

<td><a href="/departments?code='{{ row.department_code }}'">
{{ row.dept }}</td>

Here are the magic seven lines:

htsql = HTSQL('pgsql:uni') # customize me

def data(qry):
filters = flask.request.url.replace(flask.request.base_url, '', 1)
if filters and ('?' in qry): # this can be fooled by a filtered subquery
filters = '&%s' % filters.strip()[1:]
qry = '%s%s' % (qry, filters or '')
return htsql.produce(qry)

Now each of your report pages asks for data in the form of an HTSQL query, and the ``data`` function appends any user-supplied filters to that:

return flask.render_template('departments.html', data=data(qry))

full code for the sample app

No comments: