gpf.tools.queries module

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

exception gpf.tools.queries.OperatorError

Bases: exceptions.TypeError

Raised when an incorrect SQL operator is specified in a Where instance.

exception gpf.tools.queries.InitError

Bases: exceptions.TypeError

Raised when a Where instance failed to initialize (i.e. because of bad arguments).

class gpf.tools.queries.Where(where_field, {operator}, {values})

Bases: object

Basic query helper class to build basic SQL expressions for ArcPy tools where clauses. Using the & and | bitwise operators, a 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() or repr().

Example of a simple query:

>>> Where('A', '>=', 3.14)
A >= 3.14

The (NOT) IN operator accepts 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', 'not in', [1, 2, True, False, 3])
A NOT IN (0, 1, 2, 3)

The (NOT) BETWEEN operator also accepts an iterable as input. The resulting SQL expression will only use the lower and upper bounds of the list, as shown in the following composite query example:

>>> Where('A', 'like', 'Test%') | Where('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 Where as a wrapper:

>>> Where(Where('A', '=', 1) & Where('B', '=', 0)) | Where('C', 'is null')
(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.

• operator (str, unicode):

  Operator string (e.g. between, in, <, =, is null, etc.).

• values:

  The conditional values that must be met for the specified field and operator. Multiple values and iterables will all be flattened (one level), sorted and de-duped. For the is null and is not null operators, values will be ignored. For all operators except (not) between and (not) in, only the first value will be used.

get_kwargs(keyword='where_clause', **kwargs)

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.

Return type:

dict

fix_fields(datasource)

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.)

See also

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

fields

Returns a sorted tuple of all fields that currently participate in the Where clause.

Return type:tuple

gpf.tools.queries.add_where(keyword_args, where_clause, datasource=None)

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.