How to apply python for loops over Kusto query?

Question

Assume I have a list, I wants my Kusto query run for every item of the list .

(On jupyter nb)

List=[Aa,Bb,Cc,....]

%%kql

Let dt=Stormevent Where city == “Aa”/ “Bb”....;

Dt

Answer 1

You can use ٪kql line magic instead of %%kql cell magic. It will allow you to embbed the query within python code. (if the query is too long, you can assign the query string to a variable q and invoke it as follow %kql -query=q)

To query for a different city in each iteration you can parametrize the query, by setting enable_curly_brackets_params option

After each %kql invocation the current result can be found in _ so you cam assign it to your esult array.

    q = """Let dt=Stormevent Where city == {city};dt"""
    result = {}
    for city in ["boston", "new york"]:
        %kql -enable_curly_brackets_params -query=q
        result[city] = _

Answer 2

You can use it in a loop, e.g.:

for i in range(10):
    q = 'print 1 | project x=rand()'
    %kql res << -query q
    df = res.to_dataframe()
    print(i, ') ', df.loc[0, 'x'])

0 ) 0.8004291377064829

1 ) 0.21431890478779161

2 ) 0.7081267763402214

3 ) 0.018282853469189432

4 ) 0.09503573352465022

5 ) 0.3818887889250695

6 ) 0.44025744193602245

7 ) 0.08743012512835481

8 ) 0.11206495163806265

9 ) 0.39447086339688847