gpf.tools.queries module

Module that facilitates working with basic SQL expressions and where clauses in ArcGIS.

class gpf.tools.queries.Where(field_or_clause)[source]

Bases: object

Basic query helper class to build basic SQL expressions for ArcPy tools where clauses. Because all methods return a new instance, the user can “daisy chain” multiple statements.

When used in combination with the gpf.cursors module, the Where clause can be passed-in directly. In other cases (e.g. arcpy tools), the resulting SQL expression is obtained using str(), unicode() or repr(). Note that the first 2 functions check if the resulting expression is a complete SQL query. Since Esri’s arcpy prefers unicode text, the unicode() function is recommended over the str().

Example of a simple query:

>>> Where('A').GreaterThanOrEquals(3.14)
A >= 3.14

The In() and NotIn() functions accept multiple arguments or an iterable as input. The following example shows how boolean values are interpreted as integers and that duplicates are removed:

>>> Where('A').NotIn(1, 2, True, False, 3)
A NOT IN (0, 1, 2, 3)

The Between() and NotBetween() functions also accept multiple arguments or an iterable as input. The resulting SQL expression will only use the lower and upper values of the input list, as shown in the following composite query example:

>>> Where('A').Like('Test%').Or('B').Between([8, 5, 3, 4])
A LIKE 'Test%' OR B BETWEEN 3 AND 8

The following example demonstrates how you can make a grouped composite query using the combine() function:

>>> combine(Where('A').Equals(1).And('B').Equals(0)).Or('C').IsNull()
(A = 1 AND B = 0) OR C IS NULL

Params:

  • where_field (str, unicode, gpf.tools.queries.Where):

    The initial field to start the where clause, or another Where instance.

And(field_or_clause: Union[str, Where])[source]

Adds a new field or another SQL query to a new instance of the current SQL query, separated by an “AND” statement and returns it.

Parameters:field_or_clause (str, unicode, Where) – A field name or another Where instance.
Return type:Where
Or(field_or_clause: Union[str, Where])[source]

Adds a new field or another SQL query to a new instance of the current SQL query, separated by an “OR” statement and returns it.

Parameters:field_or_clause (str, unicode, Where) – A field name or another Where instance.
Return type:Where
In(*values)[source]

Adds an IN expression to a copy of the current instance to complete the SQL query and returns it. The given input values must have similar data types. The values will be ordered and duplicates are removed.

Return type:Where
NotIn(*values)[source]

Adds a NOT IN expression to a copy of the current instance to complete the SQL query and returns it. The given input values must have similar data types. The values will be ordered and duplicates are removed.

Return type:Where
Between(*values)[source]

Adds a BETWEEN expression to a copy of the current instance to complete the SQL query and returns it. The given input values must have similar data types. Only the lower and upper values are used.

Return type:Where
NotBetween(*values)[source]

Adds a NOT BETWEEN expression to a copy of the current instance to complete the SQL query and returns it. The given input values must have similar data types. Only the lower and upper values are used.

Return type:Where
Like(value: Any, escape_char: Union[None, str] = None)[source]

Adds a LIKE expression to a copy of the current instance to complete the SQL query and returns it. Optionally, an escape character can be specified e.g. when a % symbol must be taken literally.

Return type:Where
NotLike(value: Any, escape_char: Union[None, str] = None)[source]

Adds a NOT LIKE expression to a copy of the current instance to complete the SQL query and returns it. Optionally, an escape character can be specified e.g. when a % symbol must be taken literally.

Return type:Where
Equals(value: Any)[source]

Adds a “=” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
NotEquals(value: Any)[source]

Adds a “<>” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
GreaterThan(value: Any)[source]

Adds a “>” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
LessThan(value: Any)[source]

Adds a “<” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
GreaterThanOrEquals(value: Any)[source]

Adds a “>=” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
LessThanOrEquals(value: Any)[source]

Adds a “<=” expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
IsNull()[source]

Adds a IS NULL expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
IsNotNull()[source]

Adds a IS NOT NULL expression to a copy of the current instance to complete the SQL query and returns it.

Return type:Where
get_kwargs(keyword='where_clause', **kwargs) → dict[source]

Returns the where clause SQL string representation as a keyword=value dict.

This can be used in combination with the double-asterisk syntax (**) in an arcpy tool call, for example. If this function is called with existing keyword arguments, the SQL where clause will be appended/updated.

Parameters:
  • keyword – The name of the SQL keyword argument. By default, this is where_clause.
  • kwargs – An optional existing keyword dictionary to which the where clause should be added.
Returns:

A keyword dictionary containing the where_clause key-value pair.
delimit_fields(datasource: Union[str, gpf.paths.Workspace])[source]

Updates the fields in the query by wrapping them in the appropriate delimiters for the current data source.

Parameters:datasource – The path to the data source (e.g. SDE connection, feature class, etc.) or a gpf.paths.Workspace instance.

See also

https://desktop.arcgis.com/en/arcmap/latest/analyze/arcpy-functions/addfielddelimiters.htm

fields

Returns a tuple of all fields (in order of occurrence) that currently participate in the Where clause.

is_ready

Returns True when the query appears to be ready for execution (i.e. has no syntax errors).

gpf.tools.queries.combine(where_clause: gpf.tools.queries.Where) → gpf.tools.queries.Where[source]

The combine function wraps a Where instance in parenthesis “()”. This is typically used to combine 2 or more SQL clauses (delimited by AND or OR) into one.

Example:

>>> combine(Where('A').Equals(1).And('B').Equals(0)).Or('C').IsNull()
(A = 1 AND B = 0) OR C IS NULL

Params:

  • where_clause (Where):

    Another Where instance that should be wrapped in parenthesis.

gpf.tools.queries.add_where(keyword_args: dict, where_clause: Union[str, gpf.tools.queries.Where], datasource: Union[None, str, gpf.paths.Workspace] = None) → None[source]

Updates the keyword arguments dictionary with a where clause (string or Where instance).

Parameters:
  • keyword_args – A keyword argument dictionary.
  • where_clause – A query string or a Where instance.
  • datasource – If the data source path is specified, the field delimiters are updated accordingly. This only has an effect if where_clause is a Where instance.
Raises:

ValueError – If where_clause is not a string or Where instance, or if keyword_args is not a dict.