Python variables in query strings

Using your own Python variables, functions, and classes in Deephaven query strings is one of Deephaven's most powerful features. This guide explains how the engine finds your variables and handles each kind of Python value, and when you need type casts. Its examples cover scalars, strings, sequences, and dicts.

How the engine finds and converts variables

The engine finds each variable by query scope rules. It looks up a name in two places, in this order:

  1. The local scope of the function that calls the table operation.
  2. The global scope of that function's module.

This is close to Python's own LEGB rule, with two differences:

  • The engine doesn't search Python's built-in names, such as print or range.
  • The engine sees an enclosing function's variable only if the inner function's own code also refers to that variable.

Query strings can use Deephaven's own built-in functions, constants, and variables without your defining them in Python.

A query string can't use a Python variable whose name is one of the following, because the engine skips it when it compiles the query string:

  • A Java keyword or literal, such as int, double, new, true, or null.
  • One of the query language's special variables: i, ii, or k.

A variable with the same name as a column of the table is hidden too. The query string reads the column instead, with no error. See Columns take precedence over variables.

The Deephaven engine is written in Java, so every variable a query string uses crosses the Python-Java boundary. The variable's type determines what the engine does with it:

  • Python scalars and strings: the engine converts a bool to a Java Boolean, an int to a Java integer type, a float to a double, and a str to a String. See Scalars and Strings.
  • Python lists and dicts: the engine wraps them as Java List and Map objects. See Sequences and Dicts.
  • Functions and other callables: the engine wraps them so query strings can call them. See Python functions in query strings.
  • Java objects, such as arrays created with jpy: the engine uses them unchanged.
  • Other objects, such as tuples, NumPy arrays, and most NumPy scalars: the engine passes them as PyObjects.

Each example below also creates a meta_table so you can see the column types Deephaven assigns.

Scalars

Scalars are single values, such as numbers or booleans. They work in a query string with no special syntax: refer to a scalar variable by its name. The following example uses Python variables in query strings to create new columns in a table:

source_meta shows the Java type each Python value becomes:

  • A Python int becomes the smallest Java integer type (byte, short, int, or long) that holds its value. Column A holds 4, so it is a byte column.
  • A Python float becomes a double, so columns B and C are double columns.
  • A Python bool becomes a java.lang.Boolean, so column D is a java.lang.Boolean column.

Because the column type of a Python int depends on its value, cast the variable when you need a fixed type, for example "A = (long)a". See Casts with Python variables.

If the value equals the null constant of the type the engine picks, such as -128 for a byte, the column reads it as null. A cast doesn't change that, because the conversion to the smaller type happens before the cast widens the value.

Most NumPy scalars, such as numpy.int64, numpy.float32, and numpy.bool_, aren't converted. They reach the query string as PyObjects. Convert one with Python's int, float, or bool before you use it, or cast it in the query string, for example "A = (long)x".

The following example compares a raw NumPy scalar with one converted by Python's int:

Raw is an org.jpy.PyObject column, and Converted is a byte column.

Two NumPy types are exceptions. numpy.float64 is a subclass of Python's float, so it becomes a double. numpy.str_ is a subclass of str, so it becomes a java.lang.String.

Strings

As with scalars, a query string refers to a Python string variable by its name. The following example creates a table with two string columns:

Both columns are java.lang.String columns.

Sequences

In Python, sequences include lists, tuples, NumPy arrays, and more. Of these, the engine wraps only a list as a Java List. Tuples and NumPy arrays reach the query string as PyObjects unless you convert them to Java arrays first, as the end of this section shows.

A list works in a query string as is:

source_list_meta shows that ListColumn is an org.jpy.PyListWrapper column. jpy uses this Java class to wrap a Python list. See PyListWrapper columns for the limitations of this column type.

You can also extract elements of a Python list in a query string. The Deephaven engine can't infer the data type of an extracted list element, so the following example uses type casts to set each column's type:

With the casts, all three columns are int columns.

A Java array avoids calls into Python for each element, so it performs better than a list or a PyObject. Use jpy to convert a Python sequence to a Java primitive array before you use it in a query string. The following example converts a list and reads its first element:

source_java_array_meta shows that ArrayColumn is an int[] column. Indexing a Java array keeps its element type, so FirstElement is an int column with no cast.

The deephaven.dtypes.array function also creates Java arrays from Python sequences, including tuples and NumPy arrays. See Arrays for examples.

Dicts

A Python dict reaches the query string as an org.jpy.PyDictWrapper, a Java Map. Look up a value with my_dict.get(`a`) or my_dict[`a`]. The result is a PyObject, so cast it to get a typed column:

DictColumn is an org.jpy.PyDictWrapper column, and RawValue is an org.jpy.PyObject column. With the cast, IntValue is an int column.