API Reference¶

Treeno Base¶

Treeno is built around the concept of creating python nodes that represent SQL constructs. These constructs must be interoperable with SQL strings, which means:

The trees created by a hierarchy of nodes can produce valid SQL.
We can construct a Treeno tree from valid SQL.

In order to do this, this module introduces some preliminary classes for printing/formatting SQL and the base Sql superclass responsible for representing all SQL nodes.

class treeno.base.Sql¶

A base class for all SQL nodes in Treeno.

assert_equals(other)¶

Assert whether two Sql nodes are the same.

For more information, refer to Sql.equals().

Return type: None

equals(other)¶

Checks to see whether two Sql nodes have identical content.

Note

Why do we not use __eq__ here? Because for the sake of syntactical sugar, we need that operator for Value to evaluate whether two objects are equal in SQL space (thus generating a Sql node using Equal rather than returning to us a boolean value).

Parameters: other (Any) – Another potentially SQL-like object.
Return type: bool
Returns: True if both objects are equal, False otherwise.

abstract sql(opts)¶

Converts the Treeno object into SQL string.

All objects that represent some concept in SQL inherit from Sql and must implement this function to return its SQL representation which may be more compact or more readable depending on the PrintOptions. By default, all Treeno objects has a __str__ representation which calls this function with default print options.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.base.PrintOptions(mode=NOTHING, spaces=4)¶

mode: treeno.base.PrintMode¶

spaces: int¶

Treeno Expressions¶

Treeno supports arbitrary expressions including arithmetic binary, arithmetic unary, function, boolean expressions and more. Underneath the hood, the values to be expressed in SQL are actually nodes of a tree and the tree is traversed to generate SQL for execution. The base class which supports a suite of syntactic sugar such as __add__, __eq__ and is Value.

Here’s an example using the CLI tool:

❯ treeno tree expression "1+2*3"
                                     Add
     _________________________________|__________________________
    |                  |                                       right
    |                  |                                         |
    |                  |                                      Multiply
    |                  |               __________________________|__________________
    |                 left            |                 left                      right
    |                  |              |                  |                          |
    |               Literal           |               Literal                    Literal
    |          ________|______        |          ________|_______           ________|______
data_type data_type         value data_type data_type          value   data_type         value
    |         |               |       |         |                |         |               |
 INTEGER   INTEGER            1    INTEGER   INTEGER             2      INTEGER            3

A simple multiply and add is represented as nodes in a tree, each node having a data type (here, all of them are INTEGER).

A myriad of python data types are supported to convert to Literal nodes using wrap_literal(). We even support decimal.Decimal types for fixed precision decimal point classes in python. A notable exception is NoneType, which cannot be wrapped to a literal. Instead, please use the NULL singleton.

class treeno.expression.Add(left, right, *, data_type=NOTHING)¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.AliasedStar(star, aliases, *, data_type=NOTHING)¶

Represents one or more aliases corresponding to an unpacked star

aliases: List[str]¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

star: treeno.expression.Star¶

class treeno.expression.AliasedValue(value, alias, *, data_type=NOTHING)¶

Represents an alias on a value. For unpacking individual column aliases from a star, see AliasedStar

alias: str¶

identifier()¶

Identifier for the value.

Values if aliased can have identifiers which are useful to capture output information. By default, Value s are not identifiable by themselves.

Return type: Optional[str]
Returns: String name for the value’s identifier if one exists, otherwise None.

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.And(left, right, *, data_type=NOTHING)¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Array(values, *, data_type=NOTHING)¶

classmethod from_values(*vals)¶

Return type: Array

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

values: List[treeno.expression.Value]¶

class treeno.expression.Between(value, lower, upper, *, data_type=NOTHING)¶

lower: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

upper: treeno.expression.Value¶

value: treeno.expression.Value¶

class treeno.expression.BinaryExpression(left, right, *, data_type=NOTHING)¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

class treeno.expression.Case(branches, else_=None, value=None, *, data_type=NOTHING)¶

branches: List[treeno.expression.When]¶

else_: Optional[treeno.expression.Else]¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: Optional[treeno.expression.Value]¶

class treeno.expression.Cast(expr, *, data_type=NOTHING)¶

expr: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.DistinctFrom(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

class treeno.expression.Divide(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Else(value)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.Equal(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

class treeno.expression.Expression(*, data_type=NOTHING)¶

Represents a complex expression which involves a function and its corresponding arguments.

Expressions can be grouped into two general groups - treeno.functions.base.Function s and built-in SQL operators. For all functions, please refer to the module treeno.functions. Operators live in treeno.expression, and support basic arihmetic scalar transforms such as add, subtract, divide, etc. and boolean clauses such as LIKE, IS NULL, DISTINCT FROM, etc.

data_type: treeno.datatypes.types.DataType¶

class treeno.expression.Field(name, table=None, *, data_type=NOTHING)¶

Represents a field referenced in the input relations of a SELECT query

Variables

name (str) – The name of the field
table (Optional[Union[str, treeno.expression.Value]]) – The source of the field. This is sometimes necessary when there are multiple relations with duplicate column names and the name is not sufficient to disambiguate the field.

identifier()¶

Identifier for the value.

Values if aliased can have identifiers which are useful to capture output information. By default, Value s are not identifiable by themselves.

Return type: Optional[str]
Returns: String name for the value’s identifier if one exists, otherwise None.

name: str¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

table: Optional[Union[str, treeno.expression.Value]]¶

class treeno.expression.GreaterThan(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.GreaterThanOrEqual(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.InList(value, exprs, *, data_type=NOTHING)¶

exprs: List[treeno.expression.Value]¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

value: treeno.expression.Value¶

class treeno.expression.InQuery(value, query, *, data_type=NOTHING)¶

query: treeno.relation.Query¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

value: treeno.expression.Value¶

class treeno.expression.Interval(value, from_interval, to_interval=None, *, data_type=NOTHING)¶

from_interval: str¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_interval: Optional[str]¶

value: str¶

class treeno.expression.IsNull(value, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

value: treeno.expression.Value¶

class treeno.expression.Lambda(inputs, expr, *, data_type=NOTHING)¶

Represents an anonymous function. This expression will currently have an unknown type because it can never be expressed as a standalone expression.

class Variable(name, *, data_type=NOTHING)¶

identifier()¶

Identifier for the value.

Values if aliased can have identifiers which are useful to capture output information. By default, Value s are not identifiable by themselves.

Return type: Optional[str]
Returns: String name for the value’s identifier if one exists, otherwise None.

name: str¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

expr: treeno.expression.Value¶

classmethod from_generic_expr(inputs, expr)¶

Return type: Lambda

inputs: List[treeno.expression.Lambda.Variable]¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.LessThan(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.LessThanOrEqual(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Like(value, pattern, escape=None, *, data_type=NOTHING)¶

escape: Optional[treeno.expression.Value]¶

pattern: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

value: treeno.expression.Value¶

class treeno.expression.Literal(value, *, data_type=NOTHING)¶

Represents a literal value in SQL.

Literal values are expressions that don’t require calling constructors to any SQL data types such as ARRAY, and have a known data type (with the exception of NULL).

>>> from decimal import Decimal
>>> # Don't directly create a Literal value
>>> Literal("a").data_type
Traceback (most recent call last):
    ...
AssertionError: Please use wrap_literal to construct a Literal value so the data types are detected
>>> print(wrap_literal("a").data_type)
VARCHAR(1)
>>> print(wrap_literal(True).data_type)
BOOLEAN
>>> print(wrap_literal(1).data_type)
INTEGER
>>> print(wrap_literal(100000000000).data_type)
BIGINT
>>> print(wrap_literal(2.2).data_type)
DOUBLE
>>> # Use decimal.Decimal if you wish to represent a fixed precision decimal point in SQL
>>> print(wrap_literal(Decimal("2.2")).data_type)
DECIMAL(2,1)

Variables: value (Any) – A value in python representing a value in SQL.

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: Any¶

class treeno.expression.Minus(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Modulus(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Multiply(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Negative(value, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.Not(value, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.NotEqual(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

to_string(opts, negate=False)¶

Return type: str

class treeno.expression.Or(left, right, *, data_type=NOTHING)¶

data_type: treeno.datatypes.types.DataType¶

left: treeno.expression.Value¶

right: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.Positive(value, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.RowConstructor(values, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

values: List[treeno.expression.Value]¶

class treeno.expression.Star(table=None, *, data_type=NOTHING)¶

Represents a * or a table.* statement

Note

The reason Star does not inherit from Field is because a star has no name. Fields must have a name, and allow an optional table identifier.

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

table: Optional[Union[str, treeno.expression.Value]]¶

class treeno.expression.Subscript(value, index, *, data_type=NOTHING)¶

index: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

class treeno.expression.TryCast(expr, *, data_type=NOTHING)¶

expr: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.expression.TypeConstructor(value, type_name, *, data_type=NOTHING)¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

type_name: str¶

value: str¶

class treeno.expression.UnaryExpression(value, *, data_type=NOTHING)¶

value: treeno.expression.Value¶

class treeno.expression.Value(*, data_type=NOTHING)¶

Represents a basic SQL value.

A value can be one of the following:

(Literal) A literal value with well-defined type
(Field) A reference to a field in a table, which doesn’t always have a well-defined type before
resolution.
(Star) A reference to ALL of the fields in a table.
(AliasedValue and AliasedStar) Aliased versions of (2) and (3).
(Expression) A nested complex expression involving any Value. This can be a
built-in operator such as +, a lambda expression, a complex function involving potentially variadic arguments as seen in treeno.functions inheriting from treeno.functions.base.Function, etc.

Variables: data_type (treeno.datatypes.types.DataType) – The data type of the value. If not specified, defaults to UNKNOWN, which means the data type can’t be directly determined.

data_type: treeno.datatypes.types.DataType¶

identifier()¶

Identifier for the value.

Values if aliased can have identifiers which are useful to capture output information. By default, Value s are not identifiable by themselves.

Return type: Optional[str]
Returns: String name for the value’s identifier if one exists, otherwise None.

class treeno.expression.When(condition, value)¶

condition: treeno.expression.Value¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Returns: A SQL string for the given object.

value: treeno.expression.Value¶

treeno.expression.builtin_binary_str(val, string_format, opts)¶

Return type: str

treeno.expression.builtin_unary_str(val, string_format, opts)¶

Return type: str

treeno.expression.pemdas_str(current_type, val, opts, is_left=True)¶

Apply parenthesization onto the nested expression if required for pemdas.

Parameters

current_type (Type[Value]) – Current value’s type.
val (Value) – One of the direct children of the current value.
is_left (bool) – Whether this value is the leftmost child. This is used to determine whether the right child should be parenthesized or not. i.e. 1+(2+3) or 1+2+3.

Return type

str

treeno.expression.wrap_literal(val)¶

Convenience method to wrap a literal value into a treeno Value

Return type: Value

treeno.expression.wrap_literal_list(vals)¶

Return type: List[Value]

Treeno Relations¶

Every relation in Treeno is a Relation. Relations are SQL entities that contain rows of data, with every row being the same data type, and each element in the row are assigned its corresponding column name. You can think of relations as “things I can FROM in a SELECT query”.

For example, the following constructs are all relations:

trino> VALUES 1,2,3;
 _col0
-------
     1
     2
     3

trino> SELECT 1,2,3;
 _col0 | _col1 | _col2
-------+-------+-------
     1 |     2 |     3

trino> SELECT * FROM UNNEST(ARRAY[1,2,3]);
 _col0
-------
     1
     2
     3

trino> SELECT * FROM some_table;
 _col0
-------
     1
     2
     3

Not all Relation s are directly queryable though! For those that are directly queryable, i.e. SELECT statements, TABLE statements, etc, they inherit from Query which directly inherits from Relation.

Since Treeno does not try to communicate with the metastore(s) powering Table s, we need user input to resolve schemas. More information can be found in Relation.

class treeno.relation.AliasedRelation(relation, alias, column_aliases=None)¶

Represents an alias corresponding to a relation

Aliased relations can change/add identifier to another underlying Relation, and can also rname the columns. Here’s an example using resolve:

>>> from treeno.expression import wrap_literal
>>> aliased_relation = AliasedRelation(SelectQuery([wrap_literal(1), wrap_literal('2')]), "query", ["x", "y"])
>>> aliased_schema = aliased_relation.resolve(Schema.empty_schema())
>>> [schema_field.name for schema_field in aliased_schema.fields]
['x', 'y']
>>> aliased_schema.relation_ids
{'query'}

Variables

relation (treeno.relation.Relation) – A relation to alias over.
alias (str) – Alias for the relation.
column_aliases (Optional[List[str]]) – An optional list of names for each column of the underlying relation.

alias: str¶

column_aliases: Optional[List[str]]¶

identifier()¶

Shorthand identifier for the relation

Return type: Optional[str]
Returns: A string if the relation has a well-defined identifier, otherwise None

relation: treeno.relation.Relation¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

This function is used to resolve missing types in queries. The user can pass in existing_schemas to hint at types of dynamically determined fields i.e. a table in Trino, but we also use resolve() underneath the hood in order to resolve some types that can only be determined through a cross-query tree traversal. For more information refer to Schema.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.ExceptQuery(left_query, right_query, set_quantifier=SetQuantifier.DISTINCT, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

Represents a set subtract of left query with right query.

Note that except queries cannot use set quantifier ALL, since Trino implements EXCEPT by hashing the rows and subsequently deduplicates input query rows.

>>> from treeno.expression import NULL
>>> q = SelectQuery([NULL])
>>> str(ExceptQuery(q, q, set_quantifier=SetQuantifier.DISTINCT))
'(SELECT NULL) EXCEPT DISTINCT (SELECT NULL)'
>>> ExceptQuery(q, q, set_quantifier=SetQuantifier.ALL)
Traceback (most recent call last):
    ...
AssertionError: EXCEPT does not support ALL

left_query: treeno.relation.Query¶

right_query: treeno.relation.Query¶

set_quantifier: treeno.base.SetQuantifier¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.IntersectQuery(left_query, right_query, set_quantifier=SetQuantifier.DISTINCT, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

Represents an intersection of two queries.

Note that intersect queries cannot use set quantifier ALL, since Trino implements INTERSECT by hashing the rows and subsequently deduplicates input query rows.

>>> from treeno.expression import NULL
>>> q = SelectQuery([NULL])
>>> str(IntersectQuery(q, q, set_quantifier=SetQuantifier.DISTINCT))
'(SELECT NULL) INTERSECT DISTINCT (SELECT NULL)'
>>> IntersectQuery(q, q, set_quantifier=SetQuantifier.ALL)
Traceback (most recent call last):
    ...
AssertionError: INTERSECT does not support ALL

left_query: treeno.relation.Query¶

right_query: treeno.relation.Query¶

set_quantifier: treeno.base.SetQuantifier¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.Join(left_relation, right_relation, config)¶

Represents a join between two relations

Variables

left_relation (treeno.relation.Relation) – An arbitrary relation to be joined on
right_relation (treeno.relation.Relation) – An arbitrary relation to be joined on
config (treeno.relation.JoinConfig) – Details on what type of join we’re performing. Refer to JoinConfig for more info.

config: treeno.relation.JoinConfig¶

left_relation: treeno.relation.Relation¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

right_relation: treeno.relation.Relation¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.JoinConfig(join_type, natural=False, criteria=None)¶

Details the method of join used in a Join.

Variables

join_type (treeno.relation.JoinType) – The type of JOIN (i.e. CROSS JOIN, INNER JOIN, etc)
natural (bool) – Whether the join is natural (i.e. joins on equality on column names that are the same between two queries)
criteria (Optional[treeno.relation.JoinCriteria]) – What boolean expression criteria the join requires. Refer to JoinCriteria for more info.

criteria: Optional[treeno.relation.JoinCriteria]¶

join_type: treeno.relation.JoinType¶

natural: bool¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.JoinCriteria¶

Join criterias are complex expressions that describe exactly how a JOIN is done.

abstract build_sql(opts)¶

Creates a dictionary mapping of statements to strings

Return type: Dict[str, str]

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.JoinOnCriteria(constraint)¶

Perform a join between two relations using boolean expressions.

An example ON usage:

SELECT * FROM (SELECT 1 AS "foo") "a" JOIN (SELECT 1 AS "foo") "b" ON "a"."foo" = "b"."foo";
 foo | foo
-----+-----
   1 |   1
(1 row)

Variables: constraint (treeno.expression.Value) – A boolean expression constraining the join to where the expression evaluates to True using two subqueries’ rows.

build_sql(opts)¶

Creates a dictionary mapping of statements to strings

Return type: Dict[str, str]

constraint: treeno.expression.Value¶

class treeno.relation.JoinType(value)¶

An enumeration.

CROSS = 'CROSS'¶

INNER = 'INNER'¶

LEFT = 'LEFT'¶

OUTER = 'FULL OUTER'¶

RIGHT = 'RIGHT'¶

class treeno.relation.JoinUsingCriteria(column_names)¶

Using allows us to join on equality criterion on multiple rows.

There’s one subtle difference between USING and ON, which is the output number of columns:

SELECT * FROM (SELECT 1 AS "foo") JOIN (SELECT 1 AS "foo") USING("foo");
 foo
-----
   1
(1 row)

Selects the column once. If we use ON:

SELECT * FROM (SELECT 1 AS "foo") "a" JOIN (SELECT 1 AS "foo") "b" ON "a"."foo" = "b"."foo";
 foo | foo
-----+-----
   1 |   1
(1 row)

If the output columns have the same name, then upon referencing the columns Trino will fail.

Variables: column_names (List[str]) – A list of column names to check equality clauses for.

build_sql(opts)¶

Creates a dictionary mapping of statements to strings

Return type: Dict[str, str]

column_names: List[str]¶

class treeno.relation.Lateral(subquery)¶

Represents a correlated subquery.

Variables: subquery (treeno.relation.Query) – The subquery to correlate with the current scope.

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

subquery: treeno.relation.Query¶

class treeno.relation.Query(*, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

Represents a query with filtered outputs.

Note that Queries are also treeno.expression.Values, in that they are just row data typed expressions.

limit: Optional[int]¶

offset: Optional[int]¶

orderby: Optional[List[treeno.orderby.OrderTerm]]¶

with_: List[treeno.relation.AliasedRelation]¶

class treeno.relation.Relation¶

Represents a SQL relation.

A Relation can be one of the following:

(Table) A table reference.
(Query) A subquery. Query is an abstract class, and its subclasses contain
SelectQuery, ValuesTable, SetQuery and its subclasses, etc.
(Unnest) An unnested expression of arrays.
(Lateral) Shares the current relation’s namespace with any Query.
(TableSample) A subset sample of any relation.
(Join) A join composing of any relation.
(AliasedRelation) An alias (both as relation name and its column names) of any relation.

identifier()¶

Shorthand identifier for the relation

Return type: Optional[str]
Returns: A string if the relation has a well-defined identifier, otherwise None

abstract resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

class treeno.relation.SampleType(value)¶

An enumeration.

BERNOULLI = 'BERNOULLI'¶

SYSTEM = 'SYSTEM'¶

class treeno.relation.Schema(fields, relation_ids)¶

Represents the output schema of a given Relation

Schema s are used to impute missing types from trees with partial type information. This class serves two main functions. The first is to resolve cross-query references i.e. WITH queries which can be referenced from the FROM clause in a SelectQuery. The second is to allow the user to pass in schemas to resolve relations like Table s which can’t resolve its own schema without access to external metadata (i.e. from a metastore).

Variables

fields (List[treeno.relation.SchemaField]) – A list of SchemaField s that exist in the current namespace
Relation_ids – A set of relations used to denote the existence of a relation included in the schema. This set can contain relations that contains no fields - e.g. for a table with an undefined schema, the fields would be empty but the relation would still be in the set

classmethod empty_schema()¶

Return type: Schema

fields: List[treeno.relation.SchemaField]¶

merge(another_schema)¶

Merge two schemas together to create a new schema

>>> from treeno.datatypes.builder import bigint
>>> schema_a = Schema([SchemaField("x", Table("a"), bigint())], relation_ids={"a"})
>>> schema_b = Schema([SchemaField("y", Table("b"), bigint())], relation_ids={"b"})
>>> resulting_schema = schema_a.merge(schema_b)
>>> assert resulting_schema.fields == schema_a.fields + schema_b.fields
>>> assert resulting_schema.relation_ids == {"a", "b"}

Parameters: another_schema (Schema) – The other schema to merge with. The fields from it are appended to the end of the resulting Schema fields
Return type: Schema
Returns: A new Schema object with both schemas’ fields and relations in the same namespace.

relation_ids: Set[str]¶

class treeno.relation.SchemaField(name, source, data_type)¶

Represents a single field in a Schema

A schema must have a data type, a source, and optionally have a name. For example, a SUM(x) term with no alias should have no name. It still shows up in the output, but there’s no way to refer to it.

Variables

name (Optional[str]) – An optional name of the schema field. If the name is None, then the field cannot be selected later on, and Trino will autogenerate column names for these e.g. _col0
source (treeno.relation.Relation) – The source of which the field came from. Important to disambiguate SchemaField with same names across different sources
data_type (treeno.datatypes.types.DataType) – The data type of the field

data_type: treeno.datatypes.types.DataType¶

name: Optional[str]¶

source: treeno.relation.Relation¶

class treeno.relation.SelectQuery(select, from_=None, where=None, groupby=None, having=None, select_quantifier=NOTHING, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING, window=None)¶

Represents a high level SELECT query.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> table = Table("a")
>>> query = SelectQuery(
...     select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5],
...     from_=table,
...     where=Field("a") > 5
... )
>>> # We can get the SQL string of the query via __str__
>>> str(query)
'SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5'
>>> # The schema of the fields can be retrieved from resolve
>>> query_fields = query.resolve(Schema.empty_schema()).fields
>>> # We aliased the literal value as "foo", but we didn't give a name for the second field
>>> [schema_field.name for schema_field in query_fields]
['foo', None]
>>> # The type for field a is unknown without a schema supplied to the Table object.
>>> [str(schema_field.data_type) for schema_field in query_fields]
['INTEGER', 'UNKNOWN']
>>> # Alternatively, we can also get the whole row type of the select query via:
>>> str(query.data_type)
'ROW(INTEGER,UNKNOWN)'

Variables

select (List[treeno.expression.Value]) – A list of Value s as outputs to the query.
from_ (Optional[treeno.relation.Relation]) – An optional relation to select from. If from is not specified, select must contain values that do not reference any external relations, i.e. SELECT 1.
where (Optional[treeno.expression.Value]) – An optional boolean clause to filter rows by. If where is not specified, all rows are used.
groupby (Optional[treeno.groupby.GroupBy]) – An optional GroupBy which allows the user to run partial aggregate functions over different groupings. For more information refer to treeno.groupby.
having (Optional[treeno.expression.Value]) – An optional boolean clause to filter groups by. If having is not specified, all groups are used.
select_quantifier (treeno.base.SetQuantifier) – Whether to return all rows or only the distinct ones. Defaults to ALL.
window (Optional[Dict[str, treeno.window.Window]]) – An optional mapping of window names to Window s. For more information refer to treeno.window.

from_: Optional[treeno.relation.Relation]¶

groupby: Optional[treeno.groupby.GroupBy]¶

having: Optional[treeno.expression.Value]¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

select: List[treeno.expression.Value]¶

select_quantifier: treeno.base.SetQuantifier¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

where: Optional[treeno.expression.Value]¶

window: Optional[Dict[str, treeno.window.Window]]¶

class treeno.relation.SetQuery(left_query, right_query, set_quantifier=SetQuantifier.DISTINCT, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

Represents a set operation on two subqueries.

For all set operations, the input query schemas must be the coercible with each other (i.e. integer and bigint). Otherwise, Trino will complain:

trino> SELECT 1 UNION SELECT 'a';
... column 1 in UNION query has incompatible types: integer, varchar(1)

Variables

left_query (treeno.relation.Query) – An arbitrary Query. The set query’s output will be identical in schema to this query.
right_query (treeno.relation.Query) – An arbitrary Query to filter left_query on.
set_quantifier (treeno.base.SetQuantifier) – Quantifier for the output rows. If DISTINCT, then the output rows will only contain unique rows. If ALL, then the output rows will contain all rows of the set operation.

left_query: treeno.relation.Query¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

right_query: treeno.relation.Query¶

set_quantifier: treeno.base.SetQuantifier¶

class treeno.relation.Table(name, schema=None, catalog=None, column_schema=NOTHING)¶

A table reference uniquely identified by a qualified name

Tables can be standalone queries by themselves (in the form TABLE {table name}), and thus can be subject to orderby, offset, and limit constraints. Refer to TableQuery for more information.

>>> from treeno.datatypes.builder import bigint
>>> table = Table("a")
>>> assert table._column_schema == Schema([], relation_ids={"a"})
>>> # Supply a schema to the table via resolve
>>> new_schema = table.resolve(Schema([SchemaField("x", table, bigint())], relation_ids={"a"}))
>>> schema_fields = new_schema.fields
>>> assert table._column_schema == new_schema
>>> [schema_field.name for schema_field in schema_fields]
['x']
>>> [str(schema_field.data_type) for schema_field in schema_fields]
['BIGINT']

Variables

name (str) – The name of the table.
schema (Optional[str]) – The schema the table belongs to. Can be unspecified to denote the current session’s schema.
catalog (Optional[str]) – The catalog the schema belongs to. Can be unspecified to denote the current session’s catalog.

catalog: Optional[str]¶

identifier()¶

Shorthand identifier for the relation

Return type: Optional[str]
Returns: A string if the relation has a well-defined identifier, otherwise None

name: str¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

schema: Optional[str]¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.TableQuery(table, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

A light wrapper around a table for querying.

>>> str(TableQuery(Table("x")))
'TABLE "x"'

The above statement is equivalent to SELECT * FROM x.

Variables: table (treeno.relation.Table) – The underlying table to select from.

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

table: treeno.relation.Table¶

class treeno.relation.TableSample(relation, sample_type, percentage)¶

Represents a sampled relation.

Note that TABLESAMPLE can work with all relations, including JOINs. However, for JOINs we need to take extra care to parenthesize the expression. This doesn’t work:

trino> WITH f (a) AS (SELECT 1), g (b) AS (SELECT 1) SELECT f.a, g.b FROM f JOIN g ON f.a = g.b TABLESAMPLE BERNOULLI(99);
... mismatched input 'TABLESAMPLE' ...

But this does:

trino> WITH f (a) AS (SELECT 1), g (b) AS (SELECT 1) SELECT f.a, g.b FROM (f JOIN g ON f.a = g.b) TABLESAMPLE BERNOULLI(99);
 a | b
---+---
 1 | 1

Because JOINs are higher on the parsing hierarchy than TABLESAMPLEs, potentially due to an ambiguous parse otherwise where TABLESAMPLE can be applied to the right relation in a JOIN.

percentage: treeno.expression.Value¶

relation: treeno.relation.Relation¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sample_type: treeno.relation.SampleType¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

class treeno.relation.UnionQuery(left_query, right_query, set_quantifier=SetQuantifier.DISTINCT, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

Represents a union of two queries’ rows.

>>> from treeno.expression import NULL
>>> q = SelectQuery([NULL])
>>> str(UnionQuery(q, q, set_quantifier=SetQuantifier.DISTINCT))
'(SELECT NULL) UNION DISTINCT (SELECT NULL)'
>>> str(UnionQuery(q, q, set_quantifier=SetQuantifier.ALL))
'(SELECT NULL) UNION ALL (SELECT NULL)'

data_type: treeno.datatypes.types.DataType¶

left_query: treeno.relation.Query¶

limit: Optional[int]¶

offset: Optional[int]¶

orderby: Optional[List[treeno.orderby.OrderTerm]]¶

right_query: treeno.relation.Query¶

set_quantifier: treeno.base.SetQuantifier¶

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

with_: List[treeno.relation.AliasedRelation]¶

class treeno.relation.Unnest(arrays, *, with_ordinality=False)¶

Represents an unnested set of arrays representing a table

An example of unnest:

trino> SELECT * FROM UNNEST(ARRAY[1,2]);
 _col0
-------
     1
     2

Variables

arrays (List[treeno.expression.Value]) – The array(s) to use in an unnest. The i-th array in the list corresponds to the i-th output column.
with_ordinality (bool) – Whether there should be an ordinality column appended at the end of the UNNEST.

arrays: List[treeno.expression.Value]¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

with_ordinality: bool¶

class treeno.relation.ValuesQuery(exprs, *, data_type=NOTHING, offset=None, limit=None, orderby=None, with_=NOTHING)¶

A literal table constructed by literal ROWs

For a given comma separated list of expressions, the VALUES keyword turns the expression into a subquery that can be used later on. VALUES is often used for unit testing fixtures for mock tables.

Values tables can be standalone queries by themselves, and thus can be subject to orderby, offset, and limit constraints. Refer to ValuesQuery for more information.

>>> from decimal import Decimal
>>> from treeno.expression import wrap_literal, RowConstructor
>>> # Types are inferred from all input values coerced together
>>> query = ValuesQuery([wrap_literal(1), wrap_literal(Decimal("2.2"))])
>>> str(query)
'VALUES 1,2.2'
>>> str(query.data_type)
'DECIMAL(11,1)'
>>> query = ValuesQuery([
...     RowConstructor([
...         wrap_literal(1),
...         wrap_literal('a')
...     ]),
...     RowConstructor([
...         wrap_literal(2),
...         wrap_literal('b')
...     ])
... ])
>>> str(query)
"VALUES (1,'a'),(2,'b')"
>>> str(query.data_type)
'ROW(INTEGER,VARCHAR(1))'

Variables: exprs (List[treeno.expression.Value]) – Values for each row of the query.

exprs: List[treeno.expression.Value]¶

resolve(existing_schema)¶

Resolves the current relation’s schema with supplemental schema information.

Parameters: existing_schema (Schema) – Schema with a partial list of fields to resolve the current field’s schema
Return type: Schema
Returns: A new schema for the current relation

sql(opts)¶

Converts the Treeno object into SQL string.

When writing a new class that inherits from Sql, remember to pass along opts to its childrens’ sql() call to make sure the print options are recursively applied.

>>> from treeno.expression import wrap_literal, AliasedValue, Field
>>> from treeno.relation import SelectQuery, Table
>>> table = Table("a")
>>> query = SelectQuery(select=[AliasedValue(wrap_literal(2), "foo"), Field("a") / 5], from_=table, where=Field("a") > 5)
>>> print(query.sql(PrintOptions(mode=PrintMode.DEFAULT)))
SELECT 2 "foo","a" / 5 FROM "a" WHERE "a" > 5
>>> print(query.sql(PrintOptions(mode=PrintMode.PRETTY, spaces=2)))
SELECT 2 "foo","a" / 5
  FROM "a"
 WHERE "a" > 5

Parameters: opts (PrintOptions) – The print options to control the SQL output format.
Return type: str
Returns: A SQL string for the given object.

treeno.relation.maybe_prune_schema(relation, existing_schema)¶

Prune the schema depending on whether the relation will enter a new namespace

Some relations don’t want extra schema information to be passed through because it’s in its own parenthesized expression which is not aware of the outer namespace.

Parameters

relation (Relation) – The relation to enter into with the given schema
existing_schema (Schema) – The schema to pass into the relation

Return type

Schema

Returns

Either an empty Schema or the original Schema.

treeno.relation.relation_string(relation, opts, newline=True, special_parenthesize_relations=None)¶

Return type: str