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: 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 instanceReturns: 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
orQuery
orModelBase
) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, aTable
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 orField
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: - table (str or dict or
-
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 tableReturns: 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 tableReturns: 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 listParameters: - field (str or dict or
Field
) – This can be a string of a field name, a dict of {‘alias’: field}, or aField
instance - table (str or dict or
Table
) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or aTable
instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
Returns: self
Return type: - field (str or dict or
-
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
orQuery
orModelBase
) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, aTable
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: - table (str or dict or
-
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 aTable
instance - fields (str or tuple or list or
Field
) – The fields to select fromright_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 orField
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: Returns: self
- right_table (str or dict or
-
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 aTable
instance - fields (str or tuple or list or
Field
) – The fields to select fromright_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 orField
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 aTable
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: - right_table (str or dict or
-
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: 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 listParameters: - field (str or dict or
Field
) – This can be a string of a field name, a dict of {‘alias’: field}, or aField
instance - table (str or dict or
Table
) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or aTable
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: Returns: self
- field (str or dict or
-
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
-
should_not_cast_value
(field_object)¶ In Django 4.1 on PostgreSQL, AutoField, BigAutoField, and SmallAutoField are now created as identity columns rather than serial columns with sequences.
-
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
orQuery
orModelBase
) – The table to select fields from. This can be a string of the table name, a dict of {‘alias’: table}, aTable
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: Returns: self
- table (str or dict or
-
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 djangoQ
instance. This will be added to the query’sWhere
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: - q (
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 areorder_by
andpartition_by
(which just callsgroup_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 BYReturns: 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 functionParameters: - 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: Returns: self
- field (str or dict or Field) – This can be a string of a field name, a dict of {‘alias’: field}, or
a
-
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 aTable
instance - fields (str or tuple or list or
Field
) – The fields to select fromtable
. Defaults to None. This can be a single field, a tuple of fields, or a list of fields. Each field can be a string orField
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 aTable
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.
- right_table (str or dict or
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 aTable
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¶
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 aField
instance - table (str or dict or
Table
) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or aTable
instance. A table only needs to be supplied in more complex queries where the field name is ambiguous.
- field (str or dict or
-
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 aField
instance - table (str or dict or
Table
) – Optional. This can be a string of a table name, a dict of {‘alias’: table}, or aTable
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.
- field (str or dict or
-
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
-