Managing Query Templates

This notebook shows how you can manage query templates with the ThanoSQL library. We will cover how to create, update, delete, and list query templates. Stored query templates can then be used to create new queries. First, initialize a ThanoSQL client with your API token and engine URL.

from thanosql import ThanoSQL

client = ThanoSQL(
    api_token="THANOSQL_API_TOKEN",
    engine_url="THANOSQL_ENGINE_URL"
)

Listing Query Templates

You can get the list of stored query templates using the ThanoSQL library. The list() function can be used without any parameters or with optional parameters. To list only table templates which name contains some keyword(s), use the search parameter. By default, results are ordered based on recency. In order to change this ordering, use the order_by parameter. There are three options: recent (default), name_asc (A to Z), and name_desc (Z to A). You can also specify the offset and limit of the results.

query_templates = client.query.template.list(search="search_keyword", offset=1, limit=10, order_by="name_asc")
for query_template in query_templates:
    print(query_template.name, query_template.query)

You can also choose to only show query templates with a certain name with get(). You just need to specify the name.

query_template = client.query.template.get("query_template_name")
print(f"Query template ID: {query_template.id}")
print(f"Query template name: {query_template.name}")
print(f"Query template string: {query_template.query}")
print(f"Query template parameters: {query_template.parameters}")

Creating Query Templates

You can create query templates using the library. You simply need to name the template and write what the template string is. After sending the request, the template will be stored in your workspace database; an ID, date of creation, and date of update will be included, and the list of parameters will be automatically inferred.

query_template = client.query.template.create(name="my_query_template_1", query="SELECT * FROM my_table_1")
print("Parameters:")
for param in query_template.parameters:
    print(param)
print(f"ID: {query_template.id}")

You can also create an empty template, with or without name specified. The template will be automatically named as query_template_{maximum_available_unique_number + 1}.

client.query.template.create()  # this will create query_template_1 with empty contents

By default, calling create() will save the query template to database. If you simply want to check your template by dry running it without saving to database, you can use the dry_run option. By default, dry_run is turned off. When turned on, only name, query, and parameters will be displayed upon successful creation of a query template, which will not be saved.

Updating Query Templates

It is possible to update query templates using the client. You can update both the name and contents of the template. If you only want to update either the name or contents, no need to specify the other one. Only current_name is required in any case. The same validation process will apply after an update call is made; parameters will be inferred from the template. The difference is that there is no dry_run option, as it only makes sense to update templates saved to database. Also, once set, empty/none values are not allowed for either new_name or query.

# updates the name only
client.query.template.update(current_name="my_query_template_1", new_name="my_query_template_1_updated")

# updates the contents only
client.query.template.update(current_name="my_query_template_1_updated", query="SELECT * FROM {{table_name}}")

# updates the name and contents at the same time
client.query.template.update(current_name="my_query_template_1_updated", new_name="my_query_template_1_updated_again", query="SELECT * FROM {{table_name}}")

Deleting Query Templates

You can remove query templates that you no longer use.

client.query.template.delete("my_query_template_1_updated_again")