/search-generic-tables

Flask app which makes result of SQL query into frontend table styled with datatables. Generic templates prevent hard coding of data.

Primary LanguagePython

A Flask app which uses sqlite3 to query the database, and displays it in the frontend with datatables.js.

Implements a class called query_builder.CustomQuery to make issuing SQL commands within a route easy:

@app.route('/')
def index():
  sql = "SELECT * FROM Movies"
  headers, objects = CustomQuery(sql).result()
  return render_template('index.html', headers=headers, objects=objects)

This automatically generates the header rows, so the template code is kept generic (no hard coding of header names in the table. Yay!).

There's also a /search route, which can be used to filter the table based on a predefined SQL query:

@app.route('/search')
def search_result():
    sql = """
      SELECT * FROM Movies 
      WHERE "Lead Studio" = ? COLLATE NOCASE;
    """
    query = request.args.get('query', type=str)
    
    if not query:
        # No query string was supplied, just
        # render the empty template:        
        return render_template('index.html', display_search=True)

    params = (query,)
        
    headers, objects = CustomQuery(sql, params).result()

    return render_template('index.html', headers = headers, objects = objects, display_search=True)

This is slightly redundant due to datatables having a built in search bar, but demonstrates how to pass parameters to your SQL query.

References