FormHandler connects to data

FormHandler lets you read & write data from files and databases.

Here is a sample configuration to read data from a CSV file:

url:
  flags:
    pattern: /flags
    handler: FormHandler
    kwargs:
      url: flags.csv
Source

Try it with the FormHandler tutorial

Supported files

Video

You can read from multiple file formats. The URL may be any file path or URL.

For example, you can read from Excel files in a variety of ways:

    url: /path/to/file.xlsx     # Reads the first sheet from file.xlsx

    url: /path/to/file.xlsx     # Reads the sheet named sales
    sheet_name: sales

    url: /path/to/file.xlsx     # Reads cells A1:C20 from the sales sheet
    sheet_name: sales
    range: A1:C20               # v1.65 onwards

    url: /path/to/file.xlsx     # Reads cells A1:C20 from the defined name "MonthlySales"
    sheet_name: sales
    name: MonthlySales          # v1.65 onwards

    url: /path/to/file.xlsx     # Reads cells A1:C20 from the worksheet table "WeeklySale"
    sheet_name: sales
    table: MonthlySales         # v1.65 onwards

Other data formats supported are:

The type is automatically detected from the extension. Override it using ext:. For example:

url: /path/to/file.txt # This is a CSV with .txt extension
ext: csv # Force reading it as a CSV file

You can read from a HTTP or HTTPS URL. Use ext: to specify the extension of the file.

# This URL is read once and cached forever
url: https://learn.gramener.com/guide/formhandler/flags.csv
ext: csv # Explicitly specify the extension for URL

This is cached permanently unless the URL is changed or the server is restarted. You change the URL using FormHandler parameters like below:

# URL is reloaded when you change ?v=... (or server is restarted)
url: https://learn.gramener.com/guide/formhandler/flags.csv?version={v}
ext: csv # Explicitly specify the extension for HTTP(S) urls

Supported databases

Video

You can read from almost any database as well. To do this, specify url: as an SQLAlchemy URL.

Here is an example for SQLite:

url: "sqlite:///D:/path/to/file.db"
table: sales

Most databases require additional libraries to be installed. For example, PostgreSQL requires psycopg2. Run the pip install ... command below to make these popular databases work:

    # pip install psycopg2
    url: 'postgresql://$USER:$PASS@server/db'
    table: public.sales   # schema.table

    # pip install pymysql
    url: 'mysql+pymysql://$USER:$PASS@server/db'
    table: sales

    # pip install cx_Oracle
    url: 'oracle://$USER:$PASS@server/db'
    table: sales

    # pip install pyodbc
    url: 'mssql+pyodbc://$USER:$PASS@dsn'
    table: sales

Additional parameters like table:, encoding:, connect_args, etc are passed to sqlalchemy.create_engine.

With additional libraries, FormHandler can connect to more databases.

FormHandler filters

Video

The URL supports operators for filtering rows. The operators can be combined.

To control the output, you can use these control arguments:

Note: You can use FormHandler to render specific columns in navbar filters using ?_c=.

FormHandler argument type

Formhandler filter values are converted from strings to the column type, where possible. To explicitly set the type, use argstype:

url:
  flags:
    pattern: /people
    handler: FormHandler
    kwargs:
      url: people.csv
      argstype:
        age: { type: int }
        weight: { type: float }
        is_married: { type: bool }
        date_of_birth: { type: pd.to_datetime }
        date_of_death: { type: pd.to_datetime(_val) if _val else None }

argstype is a dictionary of column names and their types. The type can be:

Note: argstype values can also include an {expanding: true} to treat values as lists. This is used in FormHandler queries to prevent SQL injection in the IN operator.

FormHandler groupby

Video

v1.38. The URL supports grouping by columns using ?_by=col. For example:

You can specify custom aggregations using ?_c=col|aggregation. For example:

Apart from count, min, avg, max, and sum, you can use any aggregation functions the database supports. For example:

To aggregate the entire table, use an empty ?by=. For example:

Filters apply BEFORE grouping. For example:

To filter AFTER grouping, filter by the AGGREGATE column names instead. For example:

Sorting (?_sort=) and pagination (?_limit= and ?_offset=) apply after the group by.

FormHandler formats

Video

By default, FormHandler renders data as JSON. Use ?_format= to change that.

You can also create custom PPTX downloads using FormHandler. For example, this configuration adds a custom PPTX format called pptx-table:

formhandler-flags:
  pattern: /flags
  handler: FormHandler
  kwargs:
    url: flags.csv
    formats:
      pptx-table: # Define a format called pptx-table
        format: pptx # It generates a PPTX output
        source: input.pptx # ... based on input.pptx
        change-table: # The first rule to apply...
          Table: # ... takes all shapes named Table
            table: # ... runs a "table" command (to update tables)
              data: data['data'] # ... using flags data (default name is 'data)

Date parsing

If a file has dates stored as text, use parse_dates: [date_column] to convert date_column to a datetime.

For example:

formhandler-dateparse:
  pattern: /dateparse
  handler: FormHandler
  kwargs:
    url: data.csv
    parse_dates: [start_date]               # convert start_date from string to datetime
    parse_dates: [start_date, end_date]     # convert both columns from string to datetime

This explains how it infers datetime formats.

By default, datetimes are rendered in JSON as epoch times, i.e. time in milliseconds from 1-Jan-1980 UTC. Use formats.date_format: iso to change this to ISO. This returns something like 2019-01-01T00:00:00.000Z.

formhandler-...:
  pattern: /...
  handler: FormHandler
  kwargs:
    url: ...
    formats:
      json:
        date_format: iso # Convert to ISO format instead of epoch

The ISO format can capture the time zone and is more human readable. Both ISO and epoch can be converted into JavaScript date objects via new Date(date).

FormHandler tables

v1.28. Gramex includes the g1 library that includes a FormHandler table component. To use it, add the following code:

<link rel="stylesheet" href="ui/bootstraptheme.css" />
<!-- Add bootstrap -->
<div class="formhandler" data-src="flags"></div>
<!-- Insert component here -->
<!-- Include JS dependencies  -->
<script src="ui/lodash/lodash.min.js"></script>
<script src="ui/jquery/dist/jquery.min.js"></script>
<script src="ui/popper.js/dist/umd/popper.min.js"></script>
<script src="ui/bootstrap/dist/js/bootstrap.min.js"></script>
<script src="ui/g1/dist/g1.min.js"></script>
<script>
  // Render the FormHandler table
  $(".formhandler").formhandler();
</script>

This requires the UI components library mounted at ui/.

Source

FormHandler table example

You can configure the data attributes:

More options can be provided to $().formhandler() via JavaScript. See the API documentation for details.

Exporting the entire table

You can export data in Excel/CSV/JSON/HTML formats by passing the following parameters:

In the above case, if you want to download xlsx data and has fh-data-count=196; visit flags?_limit=196&_format=xlsx&_meta=y.

You can also pass the count as a variable viz. data_count = <fh-data-count>. In this case, you can visit flags?_limit=data_count&_format=xlsx&_meta=y to download data.

FormHandler charts

Video

v1.28. FormHandler supports seaborn charts. To render FormHandler data as charts, use:

formhandler-chart:
  pattern: /chart
  handler: FormHandler
  kwargs:
    url: flags.csv
    function: data.groupby('Continent').sum().reset_index()
    formats:
      barchart: # Define a format called barchart
        format: seaborn # This uses seaborn as the format
        chart: barplot # Chart can be any standard seaborn chart
        ext: png # Use a matplot backend (svg, pdf, png)
        width: 400 # Image width in pixels. Default 640px
        height: 300 # Image height in pixels. Default 480px
        dpi: 48 # Image resolution (dots per inch). Default 96
        x: Continent # additional parameters are passed to barplot()
        y: c1
        headers:
          Content-Type: image/png # Render as a PNG image

The URL chart?_format=barchart renders the chart image.

Bar plot

To insert an SVG via AJAX, set ext: svg and load it via AJAX.

<div id="barchart-svg"></div>
<script>
  $("#barchart-svg").load("chart?_format=barchart-svg");
</script>

The format options are formatted using the URL arguments via {arg} example:

x: "{xcol}" # The X axis for barplot comes from ?xcol=
y: "{ycol}" # The Y axis for barplot comes from ?ycol=

The URL ?xcol=Continent&ycol=c3 draws c3 vs Continents:

c3 by Continent

Image dimensions can be controlled via URL arguments. For example:

width: "{width}" # The width of barplot comes from ?width=
height: 200 # The height of barplot is fixed

c2 by Continent 400 wide

More chart types can be created. See the Seaborn API for examples.

Categorical plots

barplot stripplot swarmplot boxplot violinplot boxenplot pointplot

Regression plots

regplot residplot

Grid plots

jointplot factorplot lmplot

Matrix plots

heatmap clustermap

Plotting entire data

By default, FormHandler chart shows you the filtered data. To plot entire data, ensure that you do not pass _limit and _offset parameters. Passing those parameters would plot the filtered data viz. chart?_format=barchart&_limit=6&_offset=2 would select 4 Continents (due to _limit=4) and skip the first 2 Continents (due to _offset=2) replacing it with next 2 (if available).

More examples to be added.

FormHandler Vega charts

v1.31. FormHandler supports Vega charts. To use it, define a format using format: vega. For example:

url:
  formhandler-vega-1:
    pattern: /vega-1
    handler: FormHandler
    kwargs:
      url: flags.csv
      formats:
        barchart:           # Allows ?_format=barchart
          format: vega
          spec:
            "$schema": "https://vega.github.io/schema/vega/v4.json"
            ...   # The rest of the Vega spec comes here

When you visit vega-1?_format=barchart it renders JavaScript that creates the chart. To include it on your page, just add <script src="vega-1?_format=barchart" data-id="chart1"></script> where you want to include the chart, like below:

<script src="vega-1?_format=barchart" data-id="chart1"></script>
... rest of the page ...
<script src="ui/vega/build/vega.min.js"></script>

This script draws a barchart from /vega-1 formhandler data within <div id="chart1"></div>. If data-id is missing, random ID is created.

To manipulate the Vega object, use this:

//  Returns the vega object
var view = document.querySelector("#chart1").vega;

To redraw the chart with new data (e.g. vega-1?_format=barchart&Continent!=Africa)

var url = "vega-1?_format=json&Continent!=Africa";
fetch(url)
  .then((r) => r.json())
  .then(function (new_data) {
    const view = document.querySelector("#chart1").vega;
    // Suppose, Vega spec in above example uses `data` as (name)[https://vega.github.io/vega/docs/data/]

    // Remove old data from namespace `data`
    view
      .remove("data", function (d) {
        return true;
      })
      .run();
    // Insert new values into namespace `data`
    view.insert("data", new_data).run();
  });

Note: For Vega-Lite, default dataset namespace is source_0

Parameter Substitution

Vega spec can be formatted using the path arguments and URL query parameters.

url:
  formhandler-vega-lite-1:
    pattern: /vega-lite-1
    handler: FormHandler
    kwargs:
      url: flags.csv
      function: data.groupby('Continent').sum().reset_index()
      default:
        COL_METRIC: c1 # COL_METRIC defaults to c1
        COL_DIMENSION: Continent # COL_DIMENSION defaults to Continent
        CHART_TYPE: bar # CHART_TYPE defaults to bar
      formats:
        barchart:
          format: vega-lite
          spec:
            "$schema": "https://vega.github.io/schema/vega-lite/v2.json"
            mark: "{CHART_TYPE}"
            encoding:
              x: { field: "{COL_DIMENSION}", type: ordinal } # COL_DIMENSION set to dim for ?COL_METRIC=dim
              y: { field: "{COL_METRIC}", type: quantitative } # COL_METRIC set to val for ?COL_METRIC=val

Using the above endpoint, below url draws bar chart with y=c4 and x=Continent

<script src="vega-lite-1?_format=barchart&CHART_TYPE=bar&COL_METRIC=c4"></script>
Source

FormHandler Vega Chart examples

Similarly, Vega-Lite charts are also supported. Use format: vega-lite instead of format: vega. To include it on your page, just add <script src="...?_format=barchart"></script> where you want to include the chart, like below:

<script src="vega-lite-1?_format=barchart"></script>
... rest of the page ...
<script src="ui/vega/build/vega.min.js"></script>
<script src="ui/vega-lite/build/vega-lite.min.js"></script>
Source

FormHandler Vega Lite Chart examples

Similarly, Use format: vegam for Vegam charts.

<script src="vegam-1?_format=barchart"></script>
... rest of the page ...
<script src="ui/vega/build/vega.min.js"></script>
<script src="ui/vega-lite/build/vega-lite.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vegam@0.0.2/dist/vegam.min.js"></script>
Source

FormHandler Vegam Chart examples

FormHandler downloads

Video

CSV and XLSX formats are downloaded as data.csv and data.xlsx by default. You can specify ?_download= to download any format as any filename.

FormHandler metadata

Video

When ?_meta=y is specified, the HTTP headers have additional metadata about the request and the response. These headers are available:

All values are all JSON encoded.

FormHandler forms

FormHandler is designed to work without JavaScript. For example:

<form action="flags">
  <p>
    <label><input name="Name~" /> Search for country name</label>
  </p>
  <p>
    <label
      ><input name="c1>~" type="number" min="0" max="100" /> Min c1 value</label
    >
  </p>
  <p>
    <label
      ><input name="c1<~" type="number" min="0" max="100" /> Max c1 value</label
    >
  </p>
  <p>
    <select name="_sort">
      <option value="c1">Sort by c1 ascending</option>
      <option value="-c2">Sort by c1 descending</option>
    </select>
  </p>
  <input type="hidden" name="_format" value="html" />
  <button type="submit">Filter</button>
</form>

This form filters without using any JavaScript code. It applies the URL query parameters directly.

FormHandler transforms

FormHandler has 4 ways of transforming the request / data using a Python expression or pipeline.

  1. prepare runs before loading data and can replace handler.args. Allows variables args as handler.args and handler
  2. queryfunction runs before loading data, and can create dynamic database queries. Allows variables args as handler.args and handler
  3. function runs after loading data but before filtering, and can modify data. Allows variables data as pre-filtered data and handler
  4. modify runs after filtering data, and can modify filtered data. Allows variables data as final data and handler

Click on the links to learn how to use them.

FormHandler prepare

To modify the arguments before executing the query, use prepare:.

url:
  replace:
    pattern: /replace
    handler: FormHandler
    kwargs:
      url: flags.csv
      prepare: args.update(Cross=args.pop('c', []))
      # Another example:
      # prepare: mymodule.calc(args, handler)

This prepare: method or expression replaces the ?c= with ?Cross=. So replace?c=Yes is actually the same as flags?Cross=Yes.

prepare(args, key, handler) is the function signature. You can use:

You can modify args in-place and return None, or return a value that replaces args.

Some sample uses:

FormHandler functions

Add function: ... to transform the data before filtering. Try this example:

url:
  continent:
    pattern: /continent
    handler: FormHandler
    kwargs:
      url: flags.csv
      function: data.groupby('Continent').sum().reset_index()
      # Another example:
      # function: mymodule.calc(data, handler.args)

This runs the following steps:

  1. Load flags.csv
  2. Run function, which must be an expression that returns a DataFrame. The expression can use the variables data (loaded DataFrame) and handler (FormHandler object). You can access URL query parameters via handler.args
  3. Filter the data using the URL query parameters

That this transforms the data before filtering. e.g. filtering for c1 > 1000 filters on the totals, not individual rows. To transform the data after filtering, use modify.

function(data, handler) is the function signature. You can use:

function: also works with database queries, but loads the entire table before transforming, so ensure that you have enough memory.

FormHandler modify

You can modify the data returned after filtering using the modify: key. Try this example:

url:
  totals:
    pattern: /totals
    handler: FormHandler
    kwargs:
      url: flags.csv
      modify: data.sum(numeric_only=True).to_frame().T

Here, modify: returns the sum of numeric columns, rather than the data itself.

modify: runs after filtering. e.g. the Asia result shows totals only for Asia. To transform the data before filtering, use function.

modify: can be any expression / function that uses data & handler. For single datasets, data is a DataFrame. modify: should return a DataFrame.

modify(data, key, handler) is the function signature. You can use:

If you have multiple datasets, data: is a dict of DataFrames. modify: can modify all of these datasets – and join them if required. It should return a dict of DataFrames.

v1.59. modify: can also return a bytestring that has the exact content to be written. For example, to add a chart to an Excel output, you could use modify: add_chart(data, handler) as follows:

def add_chart(data, handler):
    # Create an Excel file called chart.xlsx with the relevant chart using xlwt
    # OPTIONAL: set any headers you want
    handler.set_header('Content-Length', os.stat('chart.xlsx').st_size)
    # Return the contents of the file, read in binary mode
    return open('charts.xlsx', 'rb').read()

modify: also works with database queries.

This example has two modify: – the first for a single query, the second applies on both datasets.

url:
  formhandler-modify-multi:
    pattern: /modify-multi
    handler: FormHandler
    kwargs:
      symbols:
        url: sqlite:///database.sqlite3
        table: flags
        query: "SELECT Continent, COUNT(DISTINCT Symbols) AS dsymbols FROM flags GROUP BY Continent"
        # Modify ONLY this query. Adds rank column to symbols dataset
        modify: data.assign(rank=data['dsymbols'].rank())
      colors:
        url: flags.csv
        function: data.groupby('Continent').sum().reset_index()
      # Modify BOTH datasets. data is a dict of DataFrames.
      modify: data['colors'].merge(data['symbols'], on='Continent')

modify: can modify the results of FormHandler edit methods too. E.g. The modify: below returns the number of URL query parameters passed.

formhandler-edits-multidata-modify:
  pattern: /edits-multidata-modify
  handler: FormHandler
  kwargs:
    sql:
      url: mysql+pymysql://root@$MYSQL_SERVER/DB?charset=utf8
      table: sales
      id: [city, product]
      modify: len(handler.args.keys())

modify: can be any expression/function that uses data – count of records edited and handler – handler.args contains data submitted by the user.

FormHandler query

Use a query: to run a SQL SELECT query on an SQLAlchemy databases. For example:

url:
  query:
    pattern: /query
    handler: FormHandler
    kwargs:
      url: sqlite:///database.sqlite3
      query: "SELECT Continent, COUNT(*) AS num, SUM(c1) FROM flags GROUP BY Continent"

… returns the query result. FormHandler filters apply on top of this query. For example:

Queries bind URL arguments as parameters. :city will be replaced by the value of ?city=. For example:

query: SELECT * FROM sales WHERE city = :city

For ?city=New York it returns all rows where city = "New York". This is SQL-injection safe.

To specific a default city, use:

query: SELECT * from sales where city = :city
default:
  city: New York

To specify parameters programmatically, create a prepare function. For example:

query: SELECT * from sales where city = :city
prepare: mymodule.set_city(handler, args)

def set_city(handler, args):
    args['city'] = find_city_for(handler.current_user)

WARNING:

  1. query loads the full result into memory. So keep the result small.
  2. Don’t use {} parameter substitution for query. Values with spaces won’t work, to avoid SQL injection attack. Use :<name> as described above.
  3. Use the correct SQL flavour. E.g. SQL Server uses SELECT TOP 10 FROM table instead of SELECT * FROM table LIMIT 10.

FormHandler queryfile

For long queries, you can point to an SQL file using queryfile: instead of query:.

url:
  query:
    pattern: /query
    handler: FormHandler
    kwargs:
      url: sqlite:///database.sqlite3
      queryfile: query.sql

For example:

The loaded query is treated exactly like FormHandler queries.

If both query: and queryfile: are present, queryfile: takes priority.

FormHandler queryfunction

For dynamic queries, use queryfunction: instead of query:. This can be any expression that returns a query string. For example:

queryfunction: mymodule.sales_query(args)

# mymodule.py
def sales_query(args):
    cities = args.get('city', [])
    if len(cities) > 0:
        vals = ', '.join("'%s'" % pymysql.escape_string(v) for v in cities)
        return 'SELECT * FROM sales WHERE city IN (%s)' % vals
    else:
        return 'SELECT * FROM sales'

The returned query is treated exactly like FormHandler queries.

The queryfunction: expression can use these 3 variables:

FormHandler query caching

state: can be used to cache queries. state: can be a

If the tables or expresion returns a different value, the query is re-run. Else, it returns previously cached query values.

For example:

    kwargs:
      query: SELECT * from sales, city WHERE sales.city = city.city

      # 1. Re-run if sales has changed. Uses DB-specific logic.
      # For example, it checks information_schema.tables on MySQL.
      state: sales

      # OR: 2. Re-run if sales/city have changed. Uses DB-specific logic.
      # For example, it checks information_schema.tables on MySQL.
      state: [sales, city]

      # OR: 3. Re-run query once per day
      state: datetime.date.today()      # Run once per day

      # OR: 4. Re-run when the number of records in `table` changes
      state: "gramex.data.filter(
                'sqlite:///my.db',
                query='SELECT COUNT(*) FROM table')"

      # OR: 5. Re-run when the latest `date` in `table` changes
      state: "gramex.data.filter(
                'sqlite:///my.db',
                query='SELECT MAX(date) FROM table')"

      # OR: 6. Re-run when any utils.cache_func()'s result changes
      state: utils.cache_func(args, handler)

Preventing SQL injection

See tutorial on Preventing SQL injection

FormHandler parameters

FormHandler parameters such as url:, ext:, table:, query:, queryfile: and all other kwargs (e.g. sheet_name) are formatted using the path arguments and URL query parameters.

This gives front-end developers and users some control over the queries.

For example, to pick up data from different files based on the URL, use:

url:
  dynamic-file:
    pattern: /csv
    handler: FormHandler
    kwargs:
      url: {file}.csv   # Maps ?file=data to data.csv

Now:

You can use regular expressions on the path to define parameters. For example, pattern: /file/(.*?) matches anything beginning with /file/. Each group in brackets (...) can be used as {_0}, {_1}, etc.

url:
  excel-parameters:             #                        {_0}  {_1}
    pattern: /xl/(.*?)/(.*?)    # Matches URLs like /xl/sales/revenue
    handler: FormHandler
    kwargs:
      url: {_0}.xlsx        # sales.xlsx is the Excel file
      sheet_name: '{_1}'    # revenue is the sheet name

The URL /xl/sales/revenue opens sales.xlsx and fetches the revenue sheet.

You can use this for database URLs and queries as well. For example:

url:
  db-parameters:                  #     {_0} {_1} {group}   {col}    {val}
    pattern: /db/(.*?)/(.*?)      # /db/data/sales?group=org&col=city&val=Oslo
    handler: FormHandler
    kwargs:
      url: sqlite:///{_0}.db      # data.db is the DB file
      query:
        SELECT {group}, COUNT(*)  # SELECT org, COUNT(*)
        FROM {_1}                 # FROM sales
        WHERE {col}=:val          # WHERE city=Oslo
        GROUP BY {group}          # GROUP BY org

The URL /db/data/sales?group=org&col=city&val=London returns the results of SELECT org, COUNT(*) FROM sales GROUP BY org WHERE city=London on data.db.

For security, there are 2 constraints:

FormHandler defaults

To specify default values for arguments, use the default: key.

url:
  continent:
    pattern: /continent
    handler: FormHandler
    kwargs:
      url: flags.csv
      function: data.groupby('Continent').sum().reset_index()
      default:
        _limit: 10 # By default, limit to 10 rows, i.e. ?_limit=10
        Continent: [Europe, Asia] # Same as ?Continent=Europe&Continent=Asia

FormHandler validation

The prepare function can raise a HTTPError in case of invalid inputs. For example, add a prepare: mymodule.validate(args, handler) and use this validate() function:

# mymodule.py
from tornado.web import HTTPError
from gramex.http import BAD_REQUEST

def validate(args, handler):
    if 'city' in args and 'state' not in args:
        raise HTTPError(BAD_REQUEST, 'Cannot use ?city= without ?state=')

    admin = 'admin' in (handler.current_user or {}).get('role', '')
    if 'city' in args and not admin:
        raise HTTPError(BAD_REQUEST, 'Only admins can search by ?city=')

This will raise a HTTP 400 Bad Request error if you use ?city= without ?state=, or if a non-admin user requests ?city=.

FormHandler multiple datasets

You can return any number of datasets from any number of sources. For example:

url:
  multidata:
    pattern: /multidata
    handler: FormHandler
    kwargs:
      continents:
        url: flags.csv
        function: data.groupby('Continent').sum().reset_index()
      stripes:
        url: flags.csv
        function: data.groupby('Stripes').sum().reset_index()

Multiple datasets as formatted as below:

By default, filters apply to all datasets. You can restrict filters to a single dataset by prefixing it with a <key>:. For example:

FormHandler runs database filters as co-routines. These queries do not block other requests, and run across datasets in parallel.

Note:

FormHandler directory listing

FormHandler allows listing files in a directory. To set this up, use a dir:// URL like this: url: dir:///path/to/directory:

pattern: /dir
handler: FormHandler
kwargs:
  url: dir:///D:/temp/ # Point to any directory

Here is a sample output:

This URL is interpolatable using arguments as well for example:

pattern: /dir/(.*)
handler: FormHandler
kwargs:
  url: dir:///D:/temp/{_0} # /dir/abc points to abc/ under this directory
# url: dir:///D:/temp/{root}  # /dir/?root=abc points to abc/ under this directory

The arguments are escaped and cannot contain ../ and other mechanisms to go beyond the root directory specified.

FormHandler templates

The output of FormHandler can be rendered as a custom template using the template format. For example, this creates a text format:

pattern: text
handler: FormHandler
kwargs:
  url: flags.csv
  formats:
    text:
      format: template
      template: text-template.txt
      headers:
        Content-Type: text/plain

Here is the output of ?_format=text&_limit=10.

The file text-template.txt is rendered as a Gramex template using the following variables:

FormHandler edits

From v1.23, FormHandler allows users to add, edit or delete data using the POST, PUT and GET HTTP operators. For example:

    POST ?id=10&x=1&y=2         # Inserts a new record {id: 10, x: 1, y: 2}
    PUT ?id=10&x=3              # Update x to 3 in the record with id=10
    DELETE ?id=10               # Delete the record with id=10

A POST, PUT or DELETE operation immediately writes back to the underlying url. For example, this writes back to an Excel file:

# Saves data to Sheet1 of file.xlsx with plant & machine id as keys
url: /path/to/file.xlsx
sheet_name: Sheet1
id: [plant, machine id]

This writes back to an Oracle Database:

# Saves to "sales" table of Oracle DB with month, product & city as keys
# Typically, the primary keys of "sales" should be the same as `id` here
url: "oracle://$USER:$PASS@server/db" # Reads from Oracle
table: sales
id: [month, product, city]

To add or delete multiple values, repeat the keys. For example:

POST ?id=10&x=1&y=2&id=11&x=3&y=4   # Inserts {id:10, x:1, y:2} & {id:11, x:3, y:4}
DELETE ?id=10&id=11                 # Delete id=10 & id=11

In the URL query, prefix by the relevant dataset name. For example this updates only the continents: dataset:

POST ?continents:country=India&continents:population=123123232
PUT ?continents:country=India&continents:population=123123232
DELETE ?continents:country=India

All operators set a a Count-<datasetname> HTTP header that indicates the number of rows matched by the query:

Count-Data: <n>     # Number of rows matched for data: dataset

If redirect: is specified, the browser is redirected to that URL (only for POST, PUT or DELETE, not GET requests). If no redirect is specified, these methods return a JSON dict with 2 keys:

FormHandler POST

This form adds a row to the data.

<!-- flags.csv has ID, Name, Text and many other fields -->
<form action="flags-add" method="POST" enctype="multipart/form-data">
  <label for="ID">ID</label> <input type="text" name="ID" value="XXX" />
  <label for="Name">Name</label>
  <input type="text" name="Name" value="New country" />
  <label for="Text">Text</label>
  <input type="text" name="Text" value="New text" />
  <input type="hidden" name="_xsrf" value="{{ handler.xsrf_token }}" />
  <button type="submit" class="btn btn-submit">Submit</button>
</form>

When the HTML form is submitted, field names map to column names in the data. For example, ID, Name and Text are columns in the flags table.

By defalt, POST returns a JSON object like this:

{
  "data": {
    "filters": [],
    "ignored": [],
    "inserted": [
      {
        "id": 1
      }
    ]
  }
}

The keys of the data object returned by POST are:

When you insert multiple rows, the number of rows inserted is returned in the Count-<dataset> header.

v1.85. To render a template, e.g. to acknowledge submitting a form, use FormHandler templates. For example:

handler: FormHandler
kwargs:
  url: "postgresql://$USER:$PASS@server/db"
  table: sales
  default:
    _format: submit-template
  formats:
    submit-template:
      format: template
      template: $YAMLPATH/template-file.html
      headers:
        Content-Type: text/html

template-file.html can be any Tornado template. It has access to the same variables as any FormHandler template. For example:

<p>You entered name = {{ handler.get_arg('name', '') }}</p>
<p>The inserted ID(s) are {{ meta['inserted'] }}</p>

If the table does not exist, Gramex automatically creates the table. It guesses the column types based on the values in the first POST. To specify column types explicitly, use columns:.

The form can also be submitted via AJAX. See FormHandler PUT for an AJAX example.

FormHandler columns

A POST request automatically creates a table (if required) when inserting a row. But the table structure may not be what you intended.

For example, if the first user POSTs:

Use Columns: to define column type when creating tables. For example:

handler: FormHandler
kwargs:
  url: "postgresql://$USER:$PASS@server/db" # Pick any database
  table: profile # Pick any table name to create
  id: id # The "id" column is primary key
  # Define your table's columns
  columns:
    user: TEXT # Use any SQL type allowed by DB
    password: VARCHAR(40) # including customizations
    age:
      type: INTEGER # You can also specify as a dict
      nullable: true # Allows NULL values for this field
      default: 0 # that default to zero
    timestamp:
      type: TIMESTAMP
      default: # Defaults can also be SQLAlchemy functions
        function: func.now() # e.g. the current time
    id:
      type: INTEGER # Define an integer ID column
      primary_key: true # as a primary key
      autoincrement: true # that auto-increments

The supported keys are:

If the profile table already has any of these columns, it is left unaltered. Else, the missing columns are added. No columns are removed.

This uses gramex.data.alter() behind the scenes to add columns.

FormHandler PUT

This PUT request updates an existing row in the data.

// flags.csv has ID, Name, Text and many other fields
fetch("flags-edit", {
  method: "PUT",
  data: { ID: "XXX", Name: "Country 1", Text: "Text " + Math.random() },
});

This requires primary keys to be defined in the FormHandler as follows:

pattern: /flags
handler: FormHandler
kwargs:
  url: /path/to/flags.csv
  id: ID # Primary key column is "ID"

You may specify multiple primary keys using a list. For example:

id: [state, city] # "state" + "city" is the primary key

If the id columns do not exist in the data, or are not passed in the URL, it raises 400 Bad Request HTTP Error.

The number of rows changed is returned in the Count-<dataset> header.

If the key is missing, PUT currently returns a Count-<dataset>: 0 and does not insert a row. This behaviour may be configurable in future releases.

Note: PUT currently works with single values. In the future, it may update multiple rows based on multiple ID selections.

If you are using multiple datasets, add an id: list to each dataset. For example:

excel:
  url: /path/to/file.xlsx
  sheet_name: Sheet1
  id: [plant, machine id]
oracle:
  url: "oracle://$USER:$PASS@server/db"
  table: sales
  id: [month, product, city]

The PUT request can also be made by subbmitting a HTML form. Form field names map to column names in the data. See FormHandler POST for a HTML example. The ?x-http-method-override=PUT overrides the method to use PUT. You can also use the HTTP header X-HTTP-Method-Override: PUT.

FormHandler Upsert

UPSERT inserts a row if it does not exist, else updates it.

FormHandler does not support UPSERT currently. But each database has different kinds of UPSERT support and SQLAlchemy has partial UPSERT support. Use these.

FormHandler DELETE

This DELETE request deletes existing rows in the data.

<!-- flags.csv has Name as a column -->
<form action="flags-delete" method="POST" enctype="multipart/form-data">
  <input type="hidden" name="x-http-method-override" value="DELETE" />
  <label for="Name">Name</label>
  <input type="checkbox" name="Name" value="Country 1" checked />
  <label for="Name">Name</label>
  <input type="checkbox" name="Name" value="Country 2" />
  <button type="submit" class="btn btn-submit">Submit</button>
</form>

When the HTML form is submitted, existing rows with Name Country 1 will be deleted. This is because only Country 1 is checked by default. The user can uncheck it and check Country 2. On submission, only Country 2 is deleted.

The number of rows deleted is returned in the Count-<dataset> header.

The form can also be submitted via AJAX. See FormHandler PUT for an AJAX example.

Note:

FormHandler JSON body

v1.34. Arguments to the POST, PUT, and DELETE methods can send a Content-Type: application/json. This allows passing JSON data as arguments. The values must be sent as arrays.

For example, ?x=1&y=2 must be sent as {"x": ["1"], "y": ["2"]}.

These two approaches are the same:

// Send using `application/www-url-form-encoded` or `multipart/form-data`
fetch("flags-edit?ID=XXX&Name=Country 1", {
  method: "PUT",
});

// Send using `application/json`
fetch("flags-edit", {
  method: "PUT",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({ ID: ["XXX"], Name: ["Country 1"] }), // Note: values are arrays
});

When using jQuery, the former is easier. But when using fetch or other AJAX / server-side libraries, application/json may be easier.

fetch("flags-edit", {
  method: "POST",
  headers: { "content-type": "application/json" },
  body: JSON.stringify({ ID: ["XXX"], Name: ["Country 1"] }), // Note: values are arrays
});

Custom HTTP Headers

The url.<url>.kwargs section accepts a headers: key. So does url.kwargs.formats.<format>. This sets custom HTTP headers. For example, to access a FormHandler JSON response via AJAX from a different server, add the CORS Access-Control-Allow-Origin headers.

pattern: /flags
handler: FormHandler
kwargs:
    ...
  formats:
    ...
    cors-json:
      format: json
      headers:
        Content-Type: application/json    # Display as json
        Access-Control-Allow-Origin: '*'  # Allow CORS (all servers can access via AJAX)
  headers:
    Cache-Control: public, max-age=3600   # All formats are cached for 1 hour