Update query/dashboard with Python

Hi,

I was trying to update a query result and the dashboard by using Python and redash_toolbelt.

My intention is to send a request to change the query so that the dashboard can be updated automatically depending on the sent parameters.

Is is possible to execute this task by using API?

Thanks

It should be, yes! Can you say more about your intended use? Perhaps I can provide a sample snippet.

Hi,

Thank you for your response. This is my intention:

I have a python web application and also an iframe to embed redash dashboard.
What I need to do is to run some python code in charge of sending a POST request to redash API so that the Query Results are updated depending on attached parameters in the POST request and also update the dashboard so that the user can see an updated dashboard embedded in an iframe.

I tried to apply the code published by redash (redash_toolbelt) with some modifications but I was not capable of updating Query Result and Dashboard with any of existing (work in progress) code examples.

Any suggestion?

Thank you very much

Cool. It sounds like you’re basically just using redash_toolbelt to refresh parameterized queries on a schedule, since the built-in scheduler can’t do this. If that’s the case, can you share the code you used (anonymized of course) that didn’t work?

This is the code extracted from redash_toolbelt with some new lines of code added by me.

def refresh_dashboard(baseurl, apikey, slug):

    client = Redash(baseurl, apikey)
    todays_dates = get_frontend_vals()
    queries_dict = get_queries_on_dashboard(client, slug)

    # loop through each query and its JSON data
    for idx, qry in queries_dict.items():

        # Trying to replace existing query with new value for "user"
        qry['query'] = qry['query'].replace('SomeUserName', 'lemon')
        #print(qry['query'])

        params = {
            p.get("name"): fill_dynamic_val(todays_dates, p)
            for p in qry["options"].get("parameters", [])
        }

        # Trying to set custom datetime values
        params['mydate']['start'] = '2020-11-08 11:44:00'
        params['mydate']['end'] = '2020-11-08 12:00:00'

        print("params:", params)

        # Pass max_age to ensure a new result is provided.
        body = {"parameters": params, "max_age": 0}

        r = client._post(f"api/queries/{idx}/results", json=body)

        print(f"Query: {idx} -- Code {r.status_code}")
        print(r.text)

Are you not using a parameter for the user name? It looks like this code directly substitutes SomeUserName in the query text. This is of course fine. Just making sure I understand.

Also, what happens when you run this code?

That is correct. Only datetime is making use of query parameters so that user can set them at will when viewing the dashboard. The username does not make use of query parameters because I do not want the user to set that field manually in the dashboard. That is why I am trying to replace “SomeUsername” with the required value

This is what I got after executing that code:

Query: 7 – Code 200
{“job”: {“status”: 1, “error”: “”, “id”: “9113b810-d0eb-4cc0-97cf-3bf8b5b767da”, “query_result_id”: null, “updated_at”: 0}}

“SomeUserName” is not being replaced and the date ranges are not being set from inside python code. As a result, the dashboard is not updated

First up, the response you receive back doesn’t indicate an error. It looks like the queries are executing normally. So that’s not a problem.

But I still think there are two missing pieces here:

  1. When you substitute SomeUserName into the query, you aren’t saving the query again. So you are kicking off a new execution. But there’s no way for the dashboard to look up the value. Redash looks up query results using a hash of the value. So you need to first save the query with the new username hard-coded, then execute it.
  2. Then when the dashboard fetches results for some query ID, there will be a cached result.

If you are doing this on a per-user basis then you’ll need a tool other than Redash, since Redash won’t work for embedded analytics.

Since there is an API call like ‘api/dashboards/{}’, which expects “properties” to be passed and the id of the dashboard, I understand it is possible to update the dashboard using the input fields defined in query result, right?

It is not clear to me what is the format of “properties”

That part of the API isn’t documented. You can learn the needed format by looking at handlers.py in the repository.

I did not find handlers.py but a folder with that name (handlers/).
Is that correct?

Thanks you

You’re right. I meant to write api.py which is inside the handlers folder.