Field API documentation

FieldFactory

class querybuilder.fields.FieldFactory

Creates the correct field class based on the type of the passed field

static __new__(cls, field, *args, **kwargs)

Determines which type of field class to instantiate based on the field argument

Parameters:field – The field used in determining which type of Field object to return. This can be a string of the field name, a dict of {‘alias’: field}, or a Field Returns:The Field instance if a valid type was determined, otherwise None Return type:Field or None

Field

class querybuilder.fields.Field(field=None, table=None, alias=None, cast=None, distinct=None)

Abstract field class that all field types extend.

Properties:

name: str

The name that identifies this table if there is no alias

alias: str

The optional alias used to identify this table

auto_alias: str

An alias that is set automatically by the Query if needed for inner query namespacing

ignore: bool

If set to True before the field is added to a table, this field will be ignored and not actually added to the table list. Typically used for fields that will create other fields like ‘*’ or auto date fields.

auto: bool

This is a flag that is read when adding fields which could indicate some other fields need to be automatically created.

__init__(field=None, table=None, alias=None, cast=None, distinct=None)

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation

before_add()

Template method to be implemented by subclasses. This is called before the field is actually added to a table

get_alias()

Gets the alias for the field or the auto_alias if one is set. If there isn’t any kind of alias, None is returned.

Returns:The field alias, auto_alias, or None Return type:str or None

get_identifier()

Gets the name for the field of how it should be referenced within a query. It will be prefixed with the table name or table alias

Returns:the name to reference the field within a query Return type:str

get_name()

Gets the name for the field and returns it. This identifies the field if there is not an alias set.

Returns:The name for the field Return type:str

get_select_sql()

Gets the SELECT field portion for the field without the alias. If the field has a table, it will be included here like table.field

Returns:Gets the SELECT field portion for the field without the alias Return type:str

get_sql()

Gets the SELECT sql part for a field Ex: field_name AS alias

Returns:the sql for this field used in the SELECT portion of the query Return type:str

set_table(table)

Setter for the table. This is meant to be extended by any subclass that might need to do additional processing with the table it belongs to. Ex: aggregate functions which reference multiple fields can set their inner fields’ table.

SimpleField

class querybuilder.fields.SimpleField(field=None, table=None, alias=None, cast=None, distinct=None)

A field that is created with just the string name of the field

__init__(field=None, table=None, alias=None, cast=None, distinct=None)

Sets the name of the field to the passed in field value

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation

MultiField

class querybuilder.fields.MultiField(field=None, table=None, alias=None, cast=None, distinct=None)

A field that contains one or more nested fields

__init__(field=None, table=None, alias=None, cast=None, distinct=None)

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation

get_field_identifier()

Gets the identifier of the nested field

Returns:the identifier of the nested field Return type:str

set_table(table)

Setter for the table of this field. Also sets the inner field’s table.

AggregateField

class querybuilder.fields.AggregateField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

The base class for aggregate functions and window functions.

Properties:

function_name: str

The aggregate function name. This is used to automatically generate the sql for simple aggregate functions.

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

get_over()

Gets the over clause to be used in the window function sql

Returns:the over clause to be used in the window function sql Return type:str

get_select_sql()

Gets the SELECT field portion for the field without the alias. If the field has a table, it will be included here like AggregateFunction(table.field)

Returns:Gets the SELECT field portion for the field without the alias Return type:str

CountField

class querybuilder.fields.CountField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Count aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

AvgField

class querybuilder.fields.AvgField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Average aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

MaxField

class querybuilder.fields.MaxField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Maximum aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

MinField

class querybuilder.fields.MinField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Minimum aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

StdDevField

class querybuilder.fields.StdDevField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Standard deviation aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

NumStdDevField

class querybuilder.fields.NumStdDevField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Number of standard deviations from the average aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_select_sql()

To calculate the number of standard deviations calculate the difference of the field and the average and divide the difference by the standard deviation

SumField

class querybuilder.fields.SumField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Summation aggregation

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

VarianceField

class querybuilder.fields.VarianceField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Variance window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

RowNumberField

class querybuilder.fields.RowNumberField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Row number window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

RankField

class querybuilder.fields.RankField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Rank window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

DenseRankField

class querybuilder.fields.DenseRankField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Dense rank window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

PercentRankField

class querybuilder.fields.PercentRankField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Percent rank window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

CumeDistField

class querybuilder.fields.CumeDistField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Cume dist window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

get_field_identifier()

Gets the identifier of the field used in the aggregate function

Returns:the identifier of the field used in the aggregate function Return type:str

NTileField

class querybuilder.fields.NTileField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, num_buckets=1)

NTile window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, num_buckets=1)

Sets the num_buckets for ntile

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• num_buckets (int) – Number of buckets to use for ntile

get_field_identifier()

Returns the number of buckets

Returns:the number of buckets used for the ntile function Return type:int

LeadLagField

class querybuilder.fields.LeadLagField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Base class for lag and lead window functions

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

get_field_identifier()

Return the lag/lead function with the offset and default value

LagField

class querybuilder.fields.LagField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Lag window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

LeadField

class querybuilder.fields.LeadField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Lead window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

LeadLagDifferenceField

class querybuilder.fields.LeadLagDifferenceField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Base class for lag difference and lead difference window functions

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

get_select_sql()

Calculate the difference between this record’s value and the lag/lead record’s value

LagDifferenceField

class querybuilder.fields.LagDifferenceField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Lag difference window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

LeadDifferenceField

class querybuilder.fields.LeadDifferenceField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Lead difference window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, offset=1, default=None)

Sets the offset and default value for the lag/lead calculation

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• offset (int) – The offset number of rows which to calculate the lag/lead
• default (number or str or object) – The default value to use if the offset doesn’t find a field

FirstValueField

class querybuilder.fields.FirstValueField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

First value window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

LastValueField

class querybuilder.fields.LastValueField(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Last value window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None)

Sets the field to a field instance because aggregate functions are treated as fields that perform an operation on a db column

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over

NthValueField

class querybuilder.fields.NthValueField(field=None, table=None, alias=None, cast=None, distinct=None, over=None, n=1)

Nth value window function

__init__(field=None, table=None, alias=None, cast=None, distinct=None, over=None, n=1)

Sets the Nth value

Parameters:

• field (str) – A string of a field name
• table (Table) – A Table instance used to disambiguate the field. This is optional in simple queries
• alias (str) – An alias to be used for this field
• cast (bool) – A data type name this field should be cast to. Ex: ‘float’
• distinct – Indicates if a DISTINCT flag should be added during sql generation
• over (QueryWindow) – The QueryWindow to perform the aggregate function over
• n (int) – the n value to use for the Nth value function

get_field_identifier()

Returns the field’s sql and the n value

Returns:the field’s sql and the n value Return type:str