Queries

Module containing the SQL-base queries definitions.

TODO: provides details on class hierarchy and organization and scopes stuff.

class networkdisk.sql.queries.CreateIndexQuery(dialect, container, columns, name=None, condition=None, unique=False, ifnotexists=False)

class networkdisk.sql.queries.CreateTableQuery(dialect, container, ifnotexists=False, temporary=False)

class networkdisk.sql.queries.CreateTriggerQuery(dialect, container, when, action, *queries, name=None, ifnotexists=False, temporary=False)

A trigger is a collection of write queries to be executed when some event occurs. The events are of the kind: - “INSTEAD OF <action> ON <container>” - “AFTER <action> ON <container>” - “BEFORE <action> ON <container>”

where ‘<action>’ is one of “INSERT”, “DELETE”, “UPDATE”, or “UPDATE OF <coma separated column list>”, and <container> is a container object such as VIEWs or TABLEs.

Parameters:

when: str: either “INSTEAD OF”, “BEFORE”, or “AFTER”;
action: str | tuple of (str, columns): either “INSERT”, “DELETE”, “UPDATE”, or a pair of the form (“UPDATE”, cols) where cols is a list of container columns;
container:: a container (SchemaView or SchemaTable);
subqueries:: a tuple of queries to be executed when the event happen.

class networkdisk.sql.queries.CreateViewQuery(dialect, container, ifnotexists=False, temporary=False)

class networkdisk.sql.queries.DeleteQuery(dialect, container, condition=None)

class networkdisk.sql.queries.DropIndexQuery(dialect, name, ifexists=False)

class networkdisk.sql.queries.DropTableQuery(dialect, name, ifexists=False)

class networkdisk.sql.queries.DropTriggerQuery(dialect, name, ifexists=False)

class networkdisk.sql.queries.DropViewQuery(dialect, name, ifexists=False)

class networkdisk.sql.queries.ExceptQuery(dialect, first, *others)

networkdisk.sql.queries.FullJoinQuery(dialect, left, right, *on, condition=None, kind='FULL', natural=None)

A wrapper of JoinQuery constructor for joining together two queries: left and right.

Parameters:

left: query: the left subquery
right: query: the right subquery
*on: specifications of pairs of colunms: a tuple of specification of pairs of columns
condition: condition: a condition to be applied within ON clause
kind: str | None, default=None: a string specifying the join kind (default is “INNER”)
natural: bool or None, default=None: a Boolean (bool) indicating whether the join should be natural. When True, then on should be empty and condition should be None.

networkdisk.sql.queries.InnerJoinQuery(dialect, left, right, *on, condition=None, kind='INNER', natural=None)

A wrapper of JoinQuery constructor for joining together two queries: left and right.

Parameters:

left: query: the left subquery
right: query: the right subquery
*on: specifications of pairs of colunms: a tuple of specification of pairs of columns
condition: condition: a condition to be applied within ON clause
kind: str | None, default=None: a string specifying the join kind (default is “INNER”)
natural: bool or None, default=None: a Boolean (bool) indicating whether the join should be natural. When True, then on should be empty and condition should be None.

class networkdisk.sql.queries.InsertQuery(dialect, container, query=None, columns=None)

A class for Insert Queries

Parameters:

container: SQL container: Typically a table or a view. See schema module.
query: ReadQueryFromQueries: Any query from which values to be inserted are taken.
columns: iterable of columns of `container`: The columns of container in which values should be inserted. If STAR then all columns are non-explicitly taken. If None then the columns are automatically determined from the external columns of query, starting by making the correspondence between columns with matching names, and then associating the remaining columns with the untaken columns of container, taken in the order of the container.external_columns iterable.

Attributes:

_insert: str: TODO: should it be in the doc?

get_args_permutation(column_map)

Get the correct order of apparition of arguments.

Parameters:

column_map: mapping: a mapping specification from indices of PLACEHOLDERs, with respect to their occurrence order in self, to name of columns

class networkdisk.sql.queries.IntersectQuery(dialect, first, *others)

class networkdisk.sql.queries.JoinQuery(dialect, *subqueries, joinpairs=(), joinconds=(), joinkinds=(), default_joinkind=None)

class used to defined JOIN like query.

Parameters:

subqueries: AbsractQuery: the ordered tuple of subqueries to build the JOIN with.
joinpairs: iterable, default=(): an iterable defining the partial mapping from subquery indices to list of pairs of columns used for joining the index-corresponding subquery with its preceding one. If not a dict then dict(enumerate(joinpairs, start=1)) is applied first to obtain the mapping. Each mapping image can be a list of specifications of pairs of columns, a single such specification, or the special value None or True. If it is None, then no equality of pairs is used for joining (this is equivalent to the empty list). If it True, then the joining is enforced to be natural, whence the joinconds parameter should have value None. If, otherwise, it is not a list, then it is replaced by the singleton list containing the given value, in order to fall back in the first case. A specification of pairs of columns is either a pair of internal column specifications (a column, a column name, or a column index), or a single column name or index. In the latter case, it is repeated twice to get a pair of column specifications whence falling back in the former case. The image associated with 0, if any, should be None since there is no preceding subquery.
joinconds: iterable, default=(): an iterable defining the partial mapping from subquery indices to conditions (not relating on equality of pairs of columns given by the above joinpairs mapping-defining argument) associated with each join. These conditions are inserted in the ON clause. If the argument is not a dict then dict(enumerate(joinconds, start=1)) is applied first to obtain the mapping. The mapping images should either be conditions or None values, interpreted as the empty condition. The image associated with 0, if any, should be None or the empty condition, since their is no with preceding subquery with which to join.
joinkinds: iterable, default=(): an iterable defining the partial mapping from subquery indices to strings indicating whether LEFT, INNER, RIGHT, or FULL JOIN should be performed for joining the index- corresponding subquery with its preceding one. If not a dict then dict(enumerate(joinkind, start=1)) is applied first in order to obtain the mapping. Subquery indices that do not have a corresponding kind string, are mapped to the default_joinkind string. Each string image is case-insensitive, and trimed before being treated. The SQL keywords ‘OUTER’ and ‘JOIN’ are optional, e.g., the string “Left Outer Join” will be interpreted as “LEFT”. If the prefix “NATURAL” is given, then the mappings resulting from the parameters joinpairs and joinconds must have undefined image or image equal to None (or to True for joinpairs) associated with the corresponding subquery index. The image associated with 0 is always ignored as there is no preceding subquery.
default_joinkind: str or None, default=None: either None or a string defining the default join kind of subqueries (see joinkinds documentation). If None, the default class attribute default_joinkind is used.

Attributes:

default_joinkind: str: The kind of join that will be used.

classmethod get_join_spec(dialect, left, right, jpairs=None, jcond=None, jkind=None, natural=None)

Parameters:

left: query: The left query to join or None
right: query: The right query to join
jpairs: None | bool | list, default=None: Either None, True, a list of specifications of pair of columns, or a single specification of pair of columns. The last case is initially transformed into the singleton list containing the given specification, while the first case (None) is equivalent to the empty list case. It hence remains two cases: True and a list. If True, the JOIN is enforced to be NATURAL. In this case both jcond and jpaircond should be None or the empty condition. Otherwise, the list is considered for getting the list of pairs of left/right columns that should be equal in the joining. Each element of the list is expected to be either a pair of left/right column specifications, or a column name or index. The latter case is replaced by the pair consisting of the given value repeated twice, thus falling in the former case. Every pair (l, r) of column specifications should be such l (resp. r) can be unambiguously resolved within the external columns of the subquery left (resp. right).
jcond: None | condition, default=None: Either None or a condition, to be additionnally applied in the ON clause (conjunction).
jkind: None | str, default=str: either None or a string which specifies the kind of JOIN to performed. It is first trimed and uppercased. The SQL keywords “OUTER” and “JOIN” are optional and ignored. If it starts with the keywords “NATURAL” then the JOIN is ensured to be NATURAL. In this case, jpairs should be None or True and jcond should be None or the empty condition.
natural: bool | None, default=None: either None or a Boolean. If True then enforce the JOIN to be NATURAL, if False enforce the JOIN to be non- NATURAL. If None, let jpairs and/or jkind decide.

classmethod join(dialect, left, right, *on, condition=None, kind=None, natural=None)

A wrapper of JoinQuery constructor for joining together two queries: left and right.

Parameters:

left: query: the left subquery
right: query: the right subquery
*on: specifications of pairs of colunms: a tuple of specification of pairs of columns
condition: condition: a condition to be applied within ON clause
kind: str | None, default=None: a string specifying the join kind (default is “INNER”)
natural: bool or None, default=None: a Boolean (bool) indicating whether the join should be natural. When True, then on should be empty and condition should be None.

naturalize(ignore=False)

ignore: Boolean. If True every subquery is joined naturally, ignoring existing joinpairs and joinconditions (except for the joincondition of the first subquery, which is kept). Otherwise, only subqueries with empty joinpairs and empty joincondition are made natural, thus keeping the joinpairs and joinconditions.

networkdisk.sql.queries.LeftJoinQuery(dialect, left, right, *on, condition=None, kind='LEFT', natural=None)

A wrapper of JoinQuery constructor for joining together two queries: left and right.

Parameters:

left: query: the left subquery
right: query: the right subquery
*on: specifications of pairs of colunms: a tuple of specification of pairs of columns
condition: condition: a condition to be applied within ON clause
kind: str | None, default=None: a string specifying the join kind (default is “INNER”)
natural: bool or None, default=None: a Boolean (bool) indicating whether the join should be natural. When True, then on should be empty and condition should be None.

class networkdisk.sql.queries.NamedQuery(dialect, subquery, name, *subqueries)

class networkdisk.sql.queries.ReplaceQuery(dialect, container, query=None, columns=None)

networkdisk.sql.queries.RightJoinQuery(dialect, left, right, *on, condition=None, kind='RIGHT', natural=None)

A wrapper of JoinQuery constructor for joining together two queries: left and right.

Parameters:

left: query: the left subquery
right: query: the right subquery
*on: specifications of pairs of colunms: a tuple of specification of pairs of columns
condition: condition: a condition to be applied within ON clause
kind: str | None, default=None: a string specifying the join kind (default is “INNER”)
natural: bool or None, default=None: a Boolean (bool) indicating whether the join should be natural. When True, then on should be empty and condition should be None.

class networkdisk.sql.queries.SelectQuery(dialect, *subqueries, columns=None, aliases=(), condition=None, distinct=False, groupby=None, orderby=None, desc=None, limit=None, offset=None)

A SelectQuery is a selection of columns of another query, which can be of any kind. It has attributes:

TODO: The following is outdated doc.

Parameters:

subqueries:: a possibly empty iterable of subqueries from which the columns are taken;
columns: list | None, default=None: a list of specifications of the selected columns. If None the list is automatically computed from the exposed columns of the subqueries.
aliases: dict or tuples of key/values, default=(): a dictionary defining a partial mapping from column names (typically str) or indices (int) to aliases (str). The mapping is partial, column with no associated alias are just left unaliased. The default value is () which is interpreted as the empty mapping. When looking for a specific column alias, indices have precedence over names.
condition: a condition or None, default=None: A condition to apply to the query in the WHERE clause.
orderby: iterable | None, default=None: a set of columns that are used for ordering the selected tuples, or the value None;
distinct: bool, default=False: if True, add the FILTER keyword to prevent multiple occurrence to be return.
groupby: tuple of internal columns or None, default=None: tuple of internal columns for grouping.
desc: bool | None, default=None: a Boolean if orderby is not None, indicating whether the order should be descending or ascending, or the value None if unspecified;
limit: int | None, delault=None: a limit (int) on the number of tuples to select or None;
offset: int | None, default=None: an offset (int) when limit is not None, or None.

except_query