Skip to Content

SQL and Python

Build a metric with Python

Fetch data from an API with Python code and turn the result into a chart.

Before you start

  • Your role has the Python View and Edit permissions. The default Editor and Developer roles have them.
  • Every plan includes Python metrics.
  • If your code calls an API with a key, store the key as a secret first.

Steps

  1. In the metric builder, select the SQL tab above the schema list.

  2. Set Query language to Python.

    SQL editor toolbar with the Query language select highlighted, set to SQL.
    The language select switches the editor between SQL and Python.
  3. Write code that puts a list of dictionaries in a variable named result:

url = secrets['secrets_BASE_URL']
rows = requests.get(f"{url}/orders", params={"client": client_id}, timeout=10).json()
result = [{"status": r["status"], "orders": r["count"]} for r in rows]
  1. Select Run code, or press Cmd+Enter (Ctrl+Enter on Windows).

    Python editor with three lines of code reading a secret, calling an API and building result, and the Run code button highlighted.
    Run code runs your Python and shows the result rows.
  2. Check the result, then select Chart builder to pick the chart and save the metric.

Select Instructions in the editor for a short reminder of these rules.

Instructions popover listing the requests import, the client_id and metric_filters variables, and filter value types.
Instructions lists the imports and variables your code gets.

What your code gets

NameWhat it is
requestsThe Python requests library, already imported
client_idThe client ID of the viewer, as a string
metric_filtersThe values of the metric’s variable filters, keyed by filter name
secretsYour company’s secrets, keyed by the name shown on the Secrets page, for example secrets_BASE_URL

Only metric filters set to Variable Filter reach your code. See Python guidelines.

If something goes wrong

  • Error: result should be an array of objects. Set result to a list of dictionaries, for example [{"key": "value"}].
  • No result variable defined. Your code must assign result.
  • Please use the given template, do not write restricted code. Your code used a blocked import or call. See the allowed imports in Python guidelines.

On this page