Python Javascript

Reports

Update Query Texts

You can use the API to update a query’s text without accessing the report in the editor. This can be useful if you want to automate the process of updating a query’s schema or table names, for example, when transitioning from a staging schema to a production schema.

Endpoints used

.../{workspace}/reports/{report_token}/queries/{query_token}
Supported methods:

  • GET: return a query
  • PATCH: update a query
  • DELETE: delete a query

.../{workspace}/reports/{report_token}/queries
Supported method:

  • GET: returns a list of queries in a report

How this example works

In this code example, the helper function get_query_token returns the query token by making a GET request to app.mode.com/api/{workspace}/reports/{report_token}/queries. It then retrieves the query token from the result by finding a query name matching the one provided as an argument to the function. The update_query method makes a PATCH request to app.mode.com/api/{workspace}/reports/{report_token}/queries/{query_token}. We specify the updated query text and query name in the payload to this request.

To try it yourself

  • Download the example code to a .py file
  • Input the API token, API secret, Workspace name, and other custom information as needed. Follow these steps to create an API token.
  • Save the .py file in your desired directory
  • Open the terminal and navigate to that directory
  • Run the command python3 {file_name}.py
  • All of our Python recipes require Python 3
import json
import requests

from requests.auth import HTTPBasicAuth


host = 'https://modeanalytics.com'
ws = 'workspace_name' # Note: workspace_name value should be all lowercase
un = 'api_token'
pw = 'api_secret'


def update_query(report_token, query_name, query_text):
  query_token = get_query_token(report_token, query_name)

  url = '%s/api/%s/reports/%s/queries/%s' % (host, ws, report_token, query_token)
  headers = {'Content-Type': 'application/json'}
  payload = {'query':{'raw_query':query_text,'name':query_name}}
  r = requests.patch(url, auth=HTTPBasicAuth(un,pw), json=payload, headers=headers)
  return r.status_code

def get_query_token(report_token, query_name):
  url = '%s/api/%s/reports/%s/queries' % (host, ws, report_token)
  r = requests.get(url, auth=HTTPBasicAuth(un,pw))
  result = r.json()

  queries = result['_embedded']['queries']
  selected_query = [q['token'] for q in queries if q['name'] == query_name]

  return selected_query[0]

update_query('report_token', "query_name", "new_query_text")

const request = require('request-promise');

const host = 'https://modeanalytics.com';
const ws = 'workspaceName'; // Note: workspaceName value should be all lowercase
const username = 'apiToken';
const password = 'apiSecret';

const updateQuery = async (reportToken, queryName, queryText) => {
  const queryToken = await getQueryToken(reportToken, queryName);
  const test = await request({
    url: `${host}/api/${ws}/reports/${reportToken}/queries/${queryToken}`,
    method: 'PATCH',
    json: true,
    auth: { username, password },
    body: {
      query: {
        raw_query: queryText,
        name: queryName,
      },
    },
  });
  console.log(test);
  return test;
};

const getQueryToken = async (reportToken, queryName) => {
  const queries = await request({
    url: `${host}/api/${ws}/reports/${reportToken}/queries`,
    json: true,
    auth: { username, password },
  });
  console.log(queries._embedded.queries);
  return queries._embedded.queries.filter(q => q.name === queryName)[0].token;
};

updateQuery('reportToken', 'queryName', 'newQueryText');