Table of Contents

django-query-builder Documentation

querybuilder is a django library for assisting with the construction and execution of sql. This is not meant to replace django querysets; it is meant for managing complex queries and helping perform database operations that django doesn’t handle. Current database support only includes postgres 9.3+.

Why use querybuilder?

The django querybuilder allows you to control all parts of the query construction. This is happens more clearly because the function calls more closely represent the actual sql keywords.

Why not just use django’s .raw() function?

While the raw function lets you execute custom sql, it doesn’t provide any way for the developer to build the query dynamically. Users lacking experience writing “raw” sql should avoid using querybuilder and stick with django’s querysets. The querybuilder’s query construction closely mirrors writing sql, where django querysets simplify the sql generation process for simple queries.

Installation

To install the latest release, type:

pip install django-query-builder

To install the latest code directly from source, type:

pip install git+git://github.com/ambitioninc/django-query-builder.git

Requirements

  • Python 2.7+
  • Django 1.4+

Usage Examples

These examples are meant to provide some basic usage scenarios for what is possible with querybuilder. More advanced examples will be added in the future.

Selecting records as dictionaries

The simplest query is to just specify a table name to select from. By default, all fields (‘*’) will be used for the field list. Select all fields from a table:

from querybuilder.query import Query

query = Query().from_table('account')
query.select()
# [{"id": 1, "name": "Person 1"}, {"id": 2, "name": "Person 2"}]
query.get_sql()
# "SELECT account.* FROM account"

If all fields are not needed, it is possible to specify fields by name. Select specific fields:

from querybuilder.query import Query

query = Query().from_table('account', ['name'])
query.select()
# [{"name": "Person 1"}, {"name": "Person 2"}]
query.get_sql()
# "SELECT account.name FROM account"

It is possible to use a django model class instead of a table name. This is not only more convenient, but it also lets querybuilder know meta data about your table to do some automatic generation as seen in later examples. Select from a django model:

from querybuilder.query import Query
from querybuilder.tests.models import User

query = Query().from_table(User)
query.select()
# [{"id": 1, "email": "user1@test.com"}, {"id": 2, "email": "user2@test.com"}]
query.get_sql()
# "SELECT tests_user.* FROM tests_user"

All of the fields can be explicitly selected by passing the extract_fields flag Explicitly select all fields:

from querybuilder.query import Query
from querybuilder.tests.models import User

query = Query().from_table(User, extract_fields=True)
query.select()
# [{"id": 1, "email": "user1@test.com"}, {"id": 2, "email": "user2@test.com"}]
query.get_sql()
# "SELECT tests_user.id, tests_user.email FROM tests_user"

All tables, fields, and nested queries can be aliased so you can reference them by another name. Alias a table and fields:

from querybuilder.query import Query

query = Query().from_table({
    'my_table': User
}, [{
    'the_id': 'id'
}, {
    'the_email': 'email'
}])
query.select()
# [{"the_id": 1, "the_email": "user1@test.com"}, {"the_id": 2, "the_email": "user2@test.com"}]
query.get_sql()
# "SELECT my_table.id AS the_id, my_table.email AS the_email FROM tests_user AS my_table"

A field object can also be passed in the field list so any other field options can be included. This is especially useful for custom fields, aggregates, and date part fields.

query = Query().from_table(User, [
    SimpleField('id', alias='the_id'),
    {'the_email': SimpleField('email')}
])
query.select()
# [{"the_id": 1, "the_email": "user1@test.com"}, {"the_id": 2, "the_email": "user2@test.com"}]
query.get_sql()
# "SELECT my_table.id AS the_id, my_table.email AS the_email FROM tests_user AS my_table"

Selecting from inner queries is just as simple as selecting from a table. The inner query can be aliased and query builder with set up the nested queries using a WITH clause Select from Query:

from querybuilder.query import Query
from querybuilder.tests.models import User

inner_query = Query().from_table(User)
query = Query().from_table({
    'inner_query': inner_query
})
query.select()
# [{"id": 1, "email": "user1@test.com"}, {"id": 2, "email": "user2@test.com"}]
query.get_sql()
# WITH inner_query AS (SELECT tests_user.* FROM tests_user) SELECT inner_query.* FROM inner_query

This simple example is meant to demonstrate that query nesting is possible, but the usefulness is really demonstrated in the more complex examples.

Sorting

Select all fields using a django model and order by id desc:

from querybuilder.query import Query

query = Query().from_table(User).order_by('-id')
query.select()
# [{"id": 2, "email": "user2@test.com"}, {"id": 1, "email": "user1@test.com"}]
query.get_sql()
# "SELECT tests_user.* FROM tests_user ORDER BY id DESC"

Sort direction can also be specified by passing a flag:

from querybuilder.query import Query

query = Query().from_table(User).order_by('id', desc=True)
query.get_sql()
# "SELECT tests_user.* FROM tests_user ORDER BY id DESC"

Limit and Offset

Limiting the result set is possible by specifying a limit value and an optional offset value

from querybuilder.query import Query

query = Query().from_table(User).limit(1)
query.select()
# [{"id": 1, "email": "user1@test.com"}]
query.get_sql()
# "SELECT tests_user.* FROM tests_user LIMIT 1"

query = Query().from_table(User).limit(1, 1)
query.select()
# [{"id": 2, "email": "user2@test.com"}]
query.get_sql()
# "SELECT tests_user.* FROM tests_user LIMIT 1, 1"

Filtering

Specifying a where clause is similar to django’s filtering system.

query = Query().from_table(User).where(id=1)
query.select()
# [{"id": 1, "email": "user1@test.com"}]
query.get_sql()
# "SELECT tests_user.* FROM tests_user WHERE (id = %(A0s)"
query.get_args()
# {'A0': 1}

The actual query arguments are passed into django’s cursor.execute function to be escaped properly.

Multiple where clauses can be chained together, or multiple clauses can be passed to a single where call:

query = Query().from_table(User).where(id__eq=1, id__lt=5)
query.get_sql()
# SELECT tests_user.* FROM tests_user WHERE (id > %(A0)s AND id < %(A1)s)
query.get_args()
# {'A1': 5, 'A0': 1}

query = Query().from_table(User).where(id__eq=1).where(id__lt=5)
query.get_sql()
# SELECT tests_user.* FROM tests_user WHERE (id > %(A0)s AND id < %(A1)s)
query.get_args()
# {'A1': 5, 'A0': 1}

By default, the conditions are ANDed together, but they can be ORed as well.

query = Query().from_table(User).where(id__eq=1).where(id__eq=5, where_type='OR')
query.get_sql()
# SELECT tests_user.* FROM tests_user WHERE ((id = %(A0)s) OR (id = %(A1)s))
query.get_args()
# {'A1': 5, 'A0': 1}

This is actually using django’s Q object internally, so any complex Q object can be passed in as an argument

condition = Q(id=1) | Q(id=5)
query = Query().from_table(User).where(condition)
query.get_sql()
# SELECT tests_user.* FROM tests_user WHERE ((id = %(A0)s) OR (id = %(A1)s))
query.get_args()
# {'A1': 5, 'A0': 1}

Other supported comparisons: eq gt gte lt lte contains startswith

Fields

All fields in querybuilder inherit from the base Field class. Some field types like SimpleField only have a name specified, but more complex fields like aggregates and date parts provide much more functionality. Custom fields can easily be created by extending one of these field classes as demonstrated later in the examples.

Aggregates

A full list of available fields is available in the Field API documentation. Some more examples are in the field_tests.py file. Some more examples will be added to demonstrate why these are useful. To become more familiar with window functions and how these are used, check out the postgres docs http://www.postgresql.org/docs/9.3/static/functions-window.html

query = Query().from_table(Order, [SumField('revenue')])
query.get_sql()
# SELECT SUM(tests_order.revenue) AS revenue_sum FROM tests_order

query = Query().from_table(Order, ['*', RowNumberField('revenue', over=QueryWindow().order_by('margin'))])
query.get_sql()
# SELECT tests_order.*, ROW_NUMBER() OVER (ORDER BY margin ASC) AS revenue_row_number FROM tests_order

query = Query().from_table(
    Order,
    ['*', RowNumberField('revenue', over=QueryWindow().order_by('margin').partition_by('account_id'))]
)
query.get_sql()
# SELECT tests_order.*, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY margin ASC) AS revenue_row_number
# FROM tests_order

CustomFields

It is possible to create your own custom field and use it in a query by extending a field class.

class MultiplyField(MultiField):
    def __init__(self, field=None, table=None, alias=None, cast=None, distinct=None, multiply_by=1):
        super(MultiplyField, self).__init__(field, table, alias, cast, distinct)
        self.multiply_by = multiply_by
        self.auto_alias = '{0}_{1}'.format(self.field.name, 'mult')

    def get_select_sql(self):
        return '({0}*{1})'.format(self.get_field_identifier(), self.multiply_by)

query = Query().from_table(Order, ['revenue', MultiplyField('revenue', multiply_by=2)])
query.get_sql()
# SELECT tests_order.revenue, (tests_order.revenue*2) AS revenue_mult FROM tests_order

Date functions

Several date part functions exist in order to extract date parts (year, month, day, hour, minute, second).

query = Query().from_table(Order, [Day('time')])
query.get_sql()
# SELECT CAST(EXTRACT(day FROM tests_order.time) AS INT) AS time__day FROM tests_order
query.select()
# [{'time__day': 19}, {'time__day': 19}, {'time__day': 19}, {'time__day': 19}]

A more useful way to use these functions is to pass the auto=True flag. This is used if you want to group records by specific date parts. Normally, to group by day, you would have to extract year, month, and day then group by year, month, and day in addition to any other grouping criteria.

query = Query().from_table(Order, [SumField('revenue'), Day('time', auto=True)])
query.get_sql()
# SELECT SUM(tests_order.revenue) AS revenue_sum,
# CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year,
# CAST(EXTRACT(month FROM tests_order.time) AS INT) AS time__month,
# CAST(EXTRACT(day FROM tests_order.time) AS INT) AS time__day,
# CAST(EXTRACT(epoch FROM date_trunc('day', tests_order.time)) AS INT) AS time__epoch
# FROM tests_order GROUP BY time__year, time__month, time__day, time__epoch ORDER BY time__epoch ASC
query.select()
# [{'time__day': 19, 'time__month': 10, 'revenue_sum': 1800.0, 'time__year': 2012, 'time__epoch': 1350604800}]

Providing additional grouping criteria is simple. Let’s say you want to see this data grouped per account:

query = Query().from_table(Order, ['account_id', SumField('revenue'), Day('time', auto=True)]).group_by('account_id')
query.get_sql()
# SELECT tests_order.account_id,
# SUM(tests_order.revenue) AS revenue_sum,
# CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year,
# CAST(EXTRACT(month FROM tests_order.time) AS INT) AS time__month,
# CAST(EXTRACT(day FROM tests_order.time) AS INT) AS time__day,
# CAST(EXTRACT(epoch FROM date_trunc('day', tests_order.time)) AS INT) AS time__epoch
# FROM tests_order GROUP BY time__year, time__month, time__day, time__epoch, account_id ORDER BY time__epoch ASC
query.select()
# [
#     {'account_id': 1, 'time__day': 19, 'time__month': 10, 'time__year': 2012, 'time__epoch': 1350604800, 'revenue_sum': 300.0},
#     {'account_id': 2, 'time__day': 19, 'time__month': 10, 'time__year': 2012, 'time__epoch': 1350604800, 'revenue_sum': 1500.0}
# ]

Going off of this same example, lets say you wanted to rank the accounts:

query = Query().from_table(Order, ['account_id', SumField('revenue'), Day('time', auto=True)]).group_by('account_id')
rank_query = Query().from_table(query, ['account_id', RankField(over=QueryWindow().order_by('-revenue_sum'))])
rank_query.get_sql()
# WITH T0 AS (
# SELECT tests_order.account_id,
# SUM(tests_order.revenue) AS revenue_sum,
# CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year,
# CAST(EXTRACT(month FROM tests_order.time) AS INT) AS time__month,
# CAST(EXTRACT(day FROM tests_order.time) AS INT) AS time__day,
# CAST(EXTRACT(epoch FROM date_trunc('day', tests_order.time)) AS INT) AS time__epoch
# FROM tests_order
# GROUP BY time__year, time__month, time__day, time__epoch, account_id
# ORDER BY time__epoch ASC)
# SELECT T0.account_id, RANK() OVER (ORDER BY revenue_sum DESC) AS rank FROM T0
rank_query.select()
# [{'account_id': 2, 'rank': 1L}, {'account_id': 1, 'rank': 2L}]

This obviously is not an efficient query for large data sets, but it can be convenient in many cases.

Joins

Json Fields

Filtering and ordering by json fields is currently in an experimental phase. This only works with versions of postgres that have a json data type field and versions of jsonfield that use json field types like jsonfield==0.9.20

Queryset example:

from querybuilder.query import JsonQueryset

records = JsonQueryset(model=MetricRecord).filter(**{'data->field_name': 'my_value'}).order_by('data->my_sort_field')

Custom field example:

from querybuilder.fields import JsonField

my_field = JsonField('data', key='field_name', alias='my_field_alias')
query = Query().from_table(MetricRecord, fields=[my_field]).where(**{
    my_field.get_where_key(): 'my_value'
})

Connection Setup

Arbitrary django connections can be passed into the Query constructor to connect to alternate databases.

from django.db import connections
from querybuilder.query import Query

connections.all()
#[<django.db.backends.postgresql_psycopg2.base.DatabaseWrapper at 0x1127b4390>,
# <django.db.backends.postgresql_psycopg2.base.DatabaseWrapper at 0x1127b44d0>]

Query(connections.all()[0]).from_table('auth_user').count()
# 15L
Query(connections.all()[1]).from_table('auth_user').count()
# 223L

Query API documentation

Query

class querybuilder.query.Query(connection=None)

A Query instance represents an actual query that will be executed. It provides methods for selecting fields from tables, inner queries, joins, filtering, limiting, and sorting. Different types of queries can be executed, such as: select, update, delete, create, and explain.

Properties:

sql: str
The query generated by calling self.get_sql() This is used for caching purposes.
tables: list of Table
A list of Table instances this query is selecting from
joins: list of Join
A list of Join instances this query is joining on
_where: Where
A Where instance containing filtering data for this query
groups: list of Group
A list of Group instances that determine the GROUP BY clause for this query
sorters: list of Sorter
A list of Sorter instances that determine the ORDER BY clause for this query
_limit: Limit
An instance of Limit This will only exist if a limit has been specified for the query
table_prefix: str
A str that determines how to prefix inner queries of this query
__init__(connection=None)

Initializes this instance by calling self.init_defaults

Parameters:connection (DatabaseWrapper) – A Django database connection. This can be used to connect to databases other than your default database.
from django.db import connections
from querybuilder.query import Query

Query(connections.all()[0]).from_table('auth_user').count()
# 15L
Query(connections.all()[1]).from_table('auth_user').count()
# 223L
avg(field)

Returns the average of the field in the result set of the query by wrapping the query and performing an AVG aggregate of the specified field :param field: the field to pass to the AVG aggregate :type field: str

Returns:The average of the specified field
Return type:int
build_from_table()

Generates the sql for the FROM portion of the query

Returns:the FROM portion of the query
Return type:str
build_groups()

Generates the sql for the GROUP BY portion of the query

Returns:the GROUP BY portion of the query
Return type:str
build_joins()

Generates the sql for the JOIN portion of the query

Returns:the JOIN portion of the query
Return type:str
build_limit()

Generates the sql for the LIMIT and OFFSET portions of the query

Returns:the LIMIT and/or OFFSET portions of the query
Return type:str
build_order_by(use_alias=True)

Generates the sql for the ORDER BY portion of the query

Parameters:use_alias (bool) – If True, the alias for the field will be used in the order by. This is an option before query windows do not use the alias. Defaults to True.
Returns:the ORDER BY portion of the query
Return type:str
build_select_fields()

Generates the sql for the SELECT portion of the query

Returns:the SELECT portion of the query
Return type:str
build_where()

Generates the sql for the WHERE portion of the query

Returns:the WHERE portion of the query
Return type:str
check_name_collisions()

Checks if there are any tables referenced by the same identifier and updated the auto_alias accordingly. This is called when generating the sql for a query and should only be called internally.

copy()

Deeply copies everything in the query object except the connection object is shared

count(field='*')

Returns a COUNT of the query by wrapping the query and performing a COUNT aggregate of the specified field

Parameters:field (str) – the field to pass to the COUNT aggregate. Defaults to ‘*’
Returns:The number of rows that the query will return
Return type:int
distinct(use_distinct=True)

Adds a distinct clause to the query

Parameters:use_distinct (bool) – Whether or not to include the distinct clause
Return type:Query
Returns:self
explain(sql=None, sql_args=None)

Runs EXPLAIN on this query

Parameters:
  • sql (str or None) – The sql to run EXPLAIN on. If None is specified, the query will use self.get_sql()
  • sql_args (dict or None) – A dictionary of the arguments to be escaped in the query. If None and sql is None, the query will use self.get_args()
Return type:

list of str

Returns:

list of each line of output from the EXPLAIN statement

find_table(table)

Finds a table by name or alias. The FROM tables and JOIN tables are included in the search.

Parameters:table (str or ModelBase) – string of the table name or alias or a ModelBase instance
Returns:The table if it is found, otherwise None
Return type:Table or None
format_sql()

Builds the sql in a format that is easy for humans to read and debug

Returns:The formatted sql for this query
Return type:str
from_table(table=None, fields='*', schema=None, **kwargs)

Adds a Table and any optional fields to the list of tables this query is selecting from.

Parameters:
  • table (str or dict or Table or Query or ModelBase) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, a Table instance, a Query instance, or a django Model instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to ‘*’. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • kwargs – Any additional parameters to be passed into the constructor of TableFactory
Returns:

self

Return type:

Query

get_args()

Gets the args for the query which will be escaped when being executed by the db. All inner queries are inspected and their args are combined with this query’s args.

Returns:all args for this query as a dict
Return type:dict
get_auto_field_name(model_class)

If one of the unique_fields is the model’s AutoField, return the field name, otherwise return None

get_count_query()

Copies the query object and alters the field list and order by to do a more efficient count

get_cursor()

Get a cursor for the Query’s connection

Return type:CursorDebugWrapper
Returns:A database cursor
get_field_identifiers()

Builds a list of the field identifiers for all tables and joined tables by calling get_field_identifiers() on each table

Returns:list of field identifiers
Return type:list of str
get_field_names()

Builds a list of the field names for all tables and joined tables by calling get_field_names() on each table

Returns:list of field names
Return type:list of str
get_sql(debug=False, use_cache=True)

Generates the sql for this query and returns the sql as a string.

Parameters:
  • debug (bool) – If True, the sql will be returned in a format that is easier to read and debug. Defaults to False
  • use_cache (bool) – If True, the query will returned the cached sql if it exists rather then generating the sql again. If False, the sql will be generated again. Defaults to True.
Return type:

str

Returns:

The generated sql for this query

get_update_sql(rows)

Returns SQL UPDATE for rows rows

UPDATE table_name
SET
    field1 = new_values.field1
    field2 = new_values.field2
FROM (
    VALUES
        (1, 'value1', 'value2'),
        (2, 'value1', 'value2')
) AS new_values (id, field1, field2)
WHERE table_name.id = new_values.id;
get_upsert_sql(rows, unique_fields, update_fields, auto_field_name=None, only_insert=False, return_rows=True)

Generates the postgres specific sql necessary to perform an upsert (ON CONFLICT)

INSERT INTO table_name (field1, field2) VALUES (1, ‘two’) ON CONFLICT (unique_field) DO UPDATE SET field2 = EXCLUDED.field2;

group_by(field=None, table=None, allow_duplicates=False)

Adds a group by clause to the query by adding a Group instance to the query’s groups list

Parameters:
  • field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
  • table (str or dict or Table) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or a Table instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
Returns:

self

Return type:

Query

init_defaults()

Sets the default values for this instance

insert(rows)

Inserts records into the db # TODO: implement this

insert_into(table=None, field_names=None, values=None, **kwargs)

Bulk inserts a list of values into a table

Parameters:
  • table (str or dict or Table or Query or ModelBase) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, a Table instance, a Query instance, or a django Model instance
  • field_names (list) – A list of ordered field names that relate to the data in the values list
  • values (list of list) – A list each values list with the values in the same order as the field names
  • kwargs – Any additional parameters to be passed into the constructor of TableFactory
Returns:

self

Return type:

Query

join(right_table=None, fields=None, condition=None, join_type='JOIN', schema=None, left_table=None, extract_fields=True, prefix_fields=False, field_prefix=None, allow_duplicates=False)

Joins a table to another table based on a condition and adds fields from the joined table to the returned fields.

Parameters:
  • right_table (str or dict or Table) – The table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance
  • fields (str or tuple or list or Field) – The fields to select from right_table. Defaults to None. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • condition (str) – The join condition specifying the fields being joined. If the two tables being joined are instances of ModelTable then the condition should be created automatically.
  • join_type (str) – The type of join (JOIN, LEFT JOIN, INNER JOIN, etc). Defaults to ‘JOIN’
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • left_table (str or dict or Table) – The left table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance. Defaults to the first table in the query.
  • extract_fields (bool) – If True and joining with a ModelTable, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to True.
  • prefix_fields (bool) – If True, then the joined table will have each of its field names prefixed with the field_prefix. If not field_prefix is specified, a name will be generated based on the join field name. This is usually used with nesting results in order to create models in python or javascript. Defaults to True.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True. If no field_prefix is set, one will be automatically created based on the join field name.
Return type:

Query

Returns:

self

join_left(right_table=None, fields=None, condition=None, join_type='LEFT JOIN', schema=None, left_table=None, extract_fields=True, prefix_fields=False, field_prefix=None, allow_duplicates=False)

Wrapper for self.join with a default join of ‘LEFT JOIN’

Parameters:
  • right_table (str or dict or Table) – The table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance
  • fields (str or tuple or list or Field) – The fields to select from right_table. Defaults to None. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • condition (str) – The join condition specifying the fields being joined. If the two tables being joined are instances of ModelTable then the condition should be created automatically.
  • join_type (str) – The type of join (JOIN, LEFT JOIN, INNER JOIN, etc). Defaults to ‘JOIN’
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • left_table (str or dict or Table) – The left table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance. Defaults to the first table in the query.
  • extract_fields (bool) – If True and joining with a ModelTable, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to True.
  • prefix_fields (bool) – If True, then the joined table will have each of its field names prefixed with the field_prefix. If not field_prefix is specified, a name will be generated based on the join field name. This is usually used with nesting results in order to create models in python or javascript. Defaults to True.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True. If no field_prefix is set, one will be automatically created based on the join field name.
Returns:

self

Return type:

Query

limit(limit=None, offset=None)

Sets a limit and/or offset to the query to limit the number of rows returned.

Parameters:
  • limit (int) – The number of rows to return
  • offset (int) – The offset from the start of the record set where rows should start being returned
Return type:

Query

Returns:

self

max(field)

Returns the maximum value of a field in the result set of the query by wrapping the query and performing a MAX aggregate of the specified field :param field: the field to pass to the MAX aggregate :type field: str

Returns:The maximum value of the specified field
Return type:int
min(field)

Returns the minimum value of a field in the result set of the query by wrapping the query and performing a MIN aggregate of the specified field :param field: the field to pass to the MIN aggregate :type field: str

Returns:The minimum value of the specified field
Return type:int
order_by(field=None, table=None, desc=False)

Adds an order by clause to the query by adding a Sorter instance to the query’s sorters list

Parameters:
  • field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
  • table (str or dict or Table) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or a Table instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
  • desc (bool) – Set to True to sort by this field in DESC order or False to sort by this field in ASC order. Defaults to False.
Return type:

Query

Returns:

self

prefix_args(prefix)

Adds an argument prefix to the query’s Where object. This should only be called internally.

select(return_models=False, nest=False, bypass_safe_limit=False, sql=None, sql_args=None)

Executes the SELECT statement and returns the rows as a list of dictionaries or a list of model instances

Parameters:
  • return_models (bool) – Set to True to return a list of models instead of a list of dictionaries. Defaults to False
  • nest (bool) – Set to True to treat all double underscores in keynames as nested data. This will convert all keys with double underscores to dictionaries keyed off of the left side of the underscores. Ex: {“id”: 1”, “account__id”: 1, “account__name”: “Name”} becomes {“id”: 1, “account”: {“id”: 1, “name”: “Name”}}
  • bypass_safe_limit (bool) – Ignores the safe_limit option even if the safe_limit is enabled
  • sql (str or None) – The sql to execute in the SELECT statement. If one is not specified, then the query will use self.get_sql()
  • sql_args (str or None) – The sql args to be used in the SELECT statement. If none are specified, then the query wil use self.get_args()
Return type:

list of dict

Returns:

list of dictionaries of the rows

sql_delete()

Deletes records from the db # TODO: implement this

sum(field)

Returns the sum of the field in the result set of the query by wrapping the query and performing a SUM aggregate of the specified field :param field: the field to pass to the SUM aggregate :type field: str

Returns:The sum of the specified field
Return type:int
update(rows)

Updates records in the db

update_table(table=None, field_names=None, values=None, pk=None, **kwargs)

Bulk updates rows in a table

Parameters:
  • table (str or dict or Table or Query or ModelBase) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, a Table instance, a Query instance, or a django Model instance
  • field_names (list) – A list of ordered field names that relate to the data in the values list
  • values (list of list) – A list each values list with the values in the same order as the field names
  • pk (int) – The name of the primary key in the table and field_names
  • kwargs – Any additional parameters to be passed into the constructor of TableFactory
Return type:

Query

Returns:

self

upsert(rows, unique_fields, update_fields, return_rows=False, return_models=False)

Performs an upsert with the set of models defined in rows. If the unique field which is meant to cause a conflict is an auto increment field, then the field should be excluded when its value is null. In this case, an upsert will be performed followed by a bulk_create

where(q=None, where_type='AND', **kwargs)

Adds a where condition as a Q object to the query’s Where instance.

Parameters:
  • q (Q) – A django Q instance. This will be added to the query’s Where object. If no Q object is passed, the kwargs will be examined for params to be added to Q objects
  • where_type – str
  • where_type – The connection type of the where condition (‘AND’, ‘OR’)
Returns:

self

Return type:

Query

with_query(query=None, alias=None)
Returns:self
Return type:Query
wrap(alias=None)

Wraps the query by selecting all fields from itself

Return type:Query
Returns:The wrapped query

QueryWindow

class querybuilder.query.QueryWindow(connection=None)

This is a query window that is meant to be used in the OVER clause of window functions. It extends Query, but the only methods that will be used are order_by and partition_by (which just calls group_by)

__init__(connection=None)

Initializes this instance by calling self.init_defaults

Parameters:connection (DatabaseWrapper) – A Django database connection. This can be used to connect to databases other than your default database.
from django.db import connections
from querybuilder.query import Query

Query(connections.all()[0]).from_table('auth_user').count()
# 15L
Query(connections.all()[1]).from_table('auth_user').count()
# 223L
build_partition_by_fields()

Equivalent to self.build_groups() except for the GROUP BY clause being named PARTITION BY

Returns:The sql to be used in the PARTITION BY clause
Return type:str
get_sql(debug=False, use_cache=True)

Generates the sql for this query window and returns the sql as a string.

Parameters:
  • debug (bool) – If True, the sql will be returned in a format that is easier to read and debug. Defaults to False
  • use_cache (bool) – If True, the query will returned the cached sql if it exists rather then generating the sql again. If False, the sql will be generated again. Defaults to True.
Return type:

str

Returns:

The generated sql for this query window

partition_by(field=None, table=None)

Equivalent to order_by, but named accordingly to the syntax of a window function

Parameters:
  • field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
  • table (str or dict or Table) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or a Table instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
Return type:

querybuilder.query.QueryWindow

Returns:

self

Join

class querybuilder.query.Join(right_table=None, fields=None, condition=None, join_type='JOIN', schema=None, left_table=None, owner=None, extract_fields=True, prefix_fields=True, field_prefix=None)

Represents the JOIN clauses of a Query. The join can be of any join type.

__init__(right_table=None, fields=None, condition=None, join_type='JOIN', schema=None, left_table=None, owner=None, extract_fields=True, prefix_fields=True, field_prefix=None)

Initializes the default values and assigns any passed params

Parameters:
  • right_table (str or dict or Table) – The table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to None. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • condition (str) – The join condition specifying the fields being joined. If the two tables being joined are instances of ModelTable then the condition should be created automatically.
  • join_type (str) – The type of join (JOIN, LEFT JOIN, INNER JOIN, etc). Defaults to ‘JOIN’
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • left_table (str or dict or Table or None) – The left table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance. Defaults to the first table in the query.
  • owner (Query) – A reference to the query managing this Join object
  • extract_fields (bool) – If True and joining with a ModelTable, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to True.
  • prefix_fields (bool) – If True, then the joined table will have each of its field names prefixed with the field_prefix. If no field_prefix is specified, a name will be generated based on the join field name. This is usually used with nesting results in order to create models in python or javascript. Defaults to True.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True. If no field_prefix is set, one will be automatically created based on the join field name.

Fix for django 1.10 to replace deprecated code. Keep support for django 1.7

get_condition()

Determines the condition to be used in the condition part of the join sql.

Returns:The condition for the join clause
Return type:str or None
get_left_table()

Returns the left table if one was specified, otherwise the first table in the query is returned

Return type:Table
Returns:the left table if one was specified, otherwise the first table in the query
get_sql()

Generates the JOIN sql for the join tables and join condition

Return type:str
Returns:the JOIN sql for the join tables and join condition
set_left_table(left_table=None)

Sets the left table for this join clause. If no table is specified, the first table in the query will be used

Parameters:left_table (str or dict or Table or None) – The left table being joined with. This can be a string of the table name, a dict of {‘alias’: table}, or a Table instance. Defaults to the first table in the query.
set_right_table(table)

Sets the right table for this join clause and try to automatically set the condition if one isn’t specified

Expression

class querybuilder.query.Expression(str)
__init__(str)

Where

class querybuilder.query.Where

Represents the WHERE clause of a Query. The filter data is contained inside of django Q objects and methods are provided to interface with them.

Properties:

arg_index: int
The numeric index that is automatically assigned to query parameters
arg_prefix: str
A prefix for the arg names used to namespace inner queries. This is set by the Query object
args: dict
A dictionary mapping the arg keys to the actual values. This is the data that is passed into cursor.execute
wheres: Q
A django Q object that can contain many nested Q objects that are used to determine all of the where conditions and nested where conditions
__init__()

Initializes default variables

build_where_part(wheres)

Recursive method that builds the where parts. Any Q objects that have children will also be built with self.build_where_part()

Return type:str
Returns:The composed where string
get_condition_operator(operator)

Gets the comparison operator from the Where class’s comparison_map

Returns:the comparison operator from the Where class’s comparison_map
Return type:str
get_condition_value(operator, value)

Gets the condition value based on the operator and value

Parameters:
  • operator (str) – the condition operator name
  • value (object) – the value to be formatted based on the condition operator
Returns:

the comparison operator from the Where class’s comparison_map

Return type:

str

get_sql()

Builds and returns the WHERE portion of the sql

Returns:the WHERE portion of the sql
Return type:str
set_arg(value)

Set the query param in self.args based on the prefix and arg index and auto increment the arg_index

Returns:the string placeholder for the arg
Return type:str

Group

class querybuilder.query.Group(field=None, table=None)

Represents a group by clause used in a Query

__init__(field=None, table=None)
Parameters:
  • field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
  • table (str or dict or Table) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or a Table instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
get_name()

Gets the name to reference the grouped field

Returns:the name to reference the grouped field
Return type:str

Sorter

class querybuilder.query.Sorter(field=None, table=None, desc=False)

Used internally by the Query class to set ORDER BY clauses on the query.

__init__(field=None, table=None, desc=False)

Initializes the instance variables

Parameters:
  • field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
  • table (str or dict or Table) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or a Table instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
  • desc (bool) – Set to True to sort by this field in DESC order or False to sort by this field in ASC order. Defaults to False.
get_name(use_alias=True)

Gets the name to reference the sorted field

Returns:the name to reference the sorted field
Return type:str

Limit

class querybuilder.query.Limit(limit=None, offset=None)

Used internally by the Query class to set a limit and/or offset on the query.

__init__(limit=None, offset=None)

Initializes the instance variables

Parameters:
  • limit (int) – the number of rows to return
  • offset – the number of rows to start returning rows from
get_sql()

Generates the sql used for the limit clause of a Query

Returns:the sql for the limit clause of a Query
Return type:str

Field API documentation

FieldFactory

class querybuilder.fields.FieldFactory

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

static __new__(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)
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

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

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

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

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

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

Tables API documentation

TableFactory

class querybuilder.tables.TableFactory

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

static __new__(table, *args, **kwargs)

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

Parameters:table (str or dict or Table or ModelBase or Query) – The table used in determining which type of Table object to return. This can be a string of the table name, a dict of {‘alias’: table}, a Table instance, a django model class, or a Query instance
Returns:The Table instance if a valid type was determined, otherwise None
Return type:Table or None

Table

class querybuilder.tables.Table(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

Abstract table class that all table 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
__init__(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

Initializes the table and sets default values

Parameters:
  • table (str or dict or Query or ModelBase) – The table name or model. This can be a string of the table name, a dict of {‘alias’: table}, a Query instance, or a django Model instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to ‘*’. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • extract_fields (bool) – If True, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to False.
  • prefix_fields (bool) – If True, then the table will have each of its field names prefixed with the field_prefix. Defaults to False.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True.
  • owner (Query) – A reference to the query managing this Table object
  • alias (str) – An alias to be used for this table
add_field(field)

Adds a field to this table

Parameters:field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
add_fields(fields)

Adds all of the passed fields to the table’s current field list

Parameters:fields (str or tuple or list of str or list of Field or Field) – The fields to select from table. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
before_add_field(field)

This is a template method meant to be extended by subclasses. It is called during the add_field method after the field is returned from the factory, and before calling the field’s before_add method, which is before actually appending the field to the list of fields.

find_field(field=None, alias=None)

Finds a field by name or alias.

Parameters:field (str or dict or Field) – string of the field name or alias, dict of {‘alias’: field}, or a Field instance
Returns:The field if it is found, otherwise None
Return type:Field or None
get_alias()

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

Returns:The table alias, auto_alias, or None
Return type:str or None
get_field_identifiers()

Loop through this tables fields and calls the get_identifier method on each of them to build a list of field identifiers

Returns:A list of field identifiers found in this table
Return type:list of str
get_field_names()

Loop through this tables fields and calls the get_name method on each of them to build a list of field names

Returns:A list of field names found in this table
Return type:list of str
get_field_prefix()

Gets the prefix to be used in front of each field. If no prefix is set, then the identifier for this table is returned

Returns:The field prefix for this table
Return type:str
get_field_sql()

Loop through this tables fields and calls the get_sql method on each of them to build the field list for the FROM clause

Returns:A list of sql for each field in this table
Return type:list of str
get_from_name()

Gets the name to be used in the FROM clause for the table. This is separate from the get_name() method so subclasses don’t always have to reference the table name by the FROM name. Otherwise Table subclasses like a QueryTable would be using the full Query sql for the get_name when getting the identifier in other parts of the query.

get_identifier()

Gets the name to reference the table within a query. If a table is aliased, it will return the alias, otherwise it returns the table name

Returns:the name to reference the table within a query
Return type:str
get_name()

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

Returns:The name for the table
Return type:str
get_sql()

Gets the FROM sql portion for this table Ex: table_name AS alias

Returns:Returns the table identifier to be used in the FROM sql portion of the query
Return type:str
init_defaults()

Template method meant to be overridden by subclasses. This is called in the __init__ before calling set_fields

remove_field(field)

Removes a field from this table

Parameters:field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or a Field instance
set_fields(fields)

This will clear the table’s current fields and add all new fields

Parameters:fields (str or tuple or list of str or list of Field or Field) – The fields to select from table. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance

SimpleTable

class querybuilder.tables.SimpleTable(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

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

__init__(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

Initializes the table and sets default values

Parameters:
  • table (str or dict or Query or ModelBase) – The table name or model. This can be a string of the table name, a dict of {‘alias’: table}, a Query instance, or a django Model instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to ‘*’. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • extract_fields (bool) – If True, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to False.
  • prefix_fields (bool) – If True, then the table will have each of its field names prefixed with the field_prefix. Defaults to False.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True.
  • owner (Query) – A reference to the query managing this Table object
  • alias (str) – An alias to be used for this table
init_defaults()

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

ModelTable

class querybuilder.tables.ModelTable(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

A table that is created by passing a django model for the table field. This allows fields to be extract and for joins to be made without specifying a condition.

__init__(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

Initializes the table and sets default values

Parameters:
  • table (str or dict or Query or ModelBase) – The table name or model. This can be a string of the table name, a dict of {‘alias’: table}, a Query instance, or a django Model instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to ‘*’. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • extract_fields (bool) – If True, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to False.
  • prefix_fields (bool) – If True, then the table will have each of its field names prefixed with the field_prefix. Defaults to False.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True.
  • owner (Query) – A reference to the query managing this Table object
  • alias (str) – An alias to be used for this table
before_add_field(field)

If extract_fields is set to True, then ‘*’ fields will be removed and each individual field will read from the model meta data and added.

init_defaults()

Sets a model instance variable to the table value and sets the name to the table name as determined from the model class

QueryTable

class querybuilder.tables.QueryTable(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

A table that contains a Query object. This is used for inner queries in more complex queries, usually involving window functions or some sort of aggregation.

__init__(table=None, fields=None, schema=None, extract_fields=False, prefix_fields=False, field_prefix=None, owner=None, alias=None)

Initializes the table and sets default values

Parameters:
  • table (str or dict or Query or ModelBase) – The table name or model. This can be a string of the table name, a dict of {‘alias’: table}, a Query instance, or a django Model instance
  • fields (str or tuple or list or Field) – The fields to select from table. Defaults to ‘*’. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string or Field instance
  • schema (str) – This is not implemented, but it will be a string of the db schema name
  • extract_fields (bool) – If True, then ‘*’ fields will be converted to individual fields for each column in the table. Defaults to False.
  • prefix_fields (bool) – If True, then the table will have each of its field names prefixed with the field_prefix. Defaults to False.
  • field_prefix (str) – The field prefix to be used in front of each field name if prefix_fields is set to True.
  • owner (Query) – A reference to the query managing this Table object
  • alias (str) – An alias to be used for this table
get_from_name()

Return the query sql in the FROM clause of the query when building the table sql

init_defaults()

Sets a query instance variable to the table value

Contributing

Contributions and issues are most welcome! All issues and pull requests are handled through github on the ambitioninc repository. Please check for any existing issues before filing a new one!

Running the tests

To get the source source code and run the unit tests, run:

git clone git://github.com/ambitioninc/django-query-builder.git
cd django-query-builder
virtualenv env
. env/bin/activate
python setup.py install
coverage run setup.py test
coverage report

While 100% code coverage does not make a library bug-free, it significantly reduces the number of easily caught bugs! Please make sure coverage is at 100% before submitting a pull request!

Code Quality

For code quality, please run flake8:

pip install flake8
flake8 .

Code Styling

Please arrange imports with the following style

# Standard library imports
import os

# Third party package imports
from mock import patch
from django.conf import settings

# Local package imports
from querybuilder.version import __version__

Please follow Google’s python style guide wherever possible.

Building the docs

When in the project directory:

pip install -r requirements/docs.txt
python setup.py build_sphinx
open docs/_build/html/index.html

Release Checklist

Before a new release, please go through the following checklist:

  • Bump version in querybuilder/version.py

  • Add a release note in docs/release_notes.rst

  • Git tag the version

  • Upload to pypi:

    pip install wheel
    python setup.py sdist bdist_wheel upload
    

Vulnerability Reporting

For any security issues, please do NOT file an issue or pull request on github! Please contact security@ambition.com with the GPG key provided on Ambition’s website.

Release Notes

v0.14.3

  • Respect return_models in upsert method when building upsert sql

v0.14.2

  • Fix upsert to use the proper prepare method on django fields

v0.14.1

  • Fix upsert to handle case when the uniqueness constraint is the pk field

v0.14.0

  • Drop support for django 1.7, add official support for python 3.5

v0.13.0

  • Add paginator class for json queryset
  • No longer change ‘__’ to ‘.’ in filters. Just use a ‘.’ where needed and use django 1.9’s json field support for querying json fields

v0.12.0

  • Run tests with django 1.10
  • Fix bug when filtering json fields with any operator other than equals
  • Fix deprecated method call for django 1.10

v0.11.3

  • Added issubclass check for ModelBase when checking table type

v0.11.2

  • Use correct column name on upsert when existing record updates

v0.11.1

  • Get db prep values for upserts and get column name by model property names

v0.11.0

  • Wrap alias in double quotes to preserve case
  • Fix bulk upsert column names
  • Add upsert support to return affected records as dicts or models
  • Implement distinct_on
  • Fix tests for Postgres9.3
  • Implement icontains
  • Implement DISTINCT for aggregate fields

v0.10.0

  • Added postgres bulk upsert support

v0.9.0

  • Added support for django 1.9 and tests with postgres 9.4 using new django json field

v0.8.1

  • More efficient count query
  • Add limit support on JsonQueryset
  • Added copy method to Query object

v0.7.2

  • Fixed queryset init args

v0.7.1

  • Added Django 1.7 app config

v0.7.0

  • Added basic support for filtering and ordering json fields

v0.5.10

  • Updated the usage examples
  • Fixed infinite loop bug when a MultiField did not implement an identifier method

v0.5.9

  • Fixed issue with certain functions for alternate databases

v0.5.8

  • Added connection parameter to query for multiple database support
  • flake8 cleanup

v0.5.7

  • Added Python 3 compatibility
  • Added sphinx docs