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