Queries

class Queries(session_kwargs, client, return_type='raw')

Examples

>>> import civis
>>> client = civis.APIClient()
>>> client.queries.list(...)

Methods

delete(id)

Sets Query Hidden to true

delete_runs(id, run_id)

Cancel a run

get(id)

Get details about a query

get_runs(id, run_id)

Check status of a run

list(*[, query, database_id, credential_id, ...])

List queries

list_runs(id, *[, limit, page_num, order, ...])

List runs for the given Query job

list_runs_logs(id, run_id, *[, last_id, limit])

Get the logs for a run

post(database, sql, preview_rows, *[, ...])

Execute a query

post_runs(id)

Start a run

put_scripts(id, script_id)

Update the query's associated script

delete(id: int)

Sets Query Hidden to true

Parameters:
idint

The query ID.

Returns:
civis.response.Response
  • idint

    The query ID.

  • databaseint

    The database ID.

  • sqlstr

    The SQL to execute.

  • credentialint

    The credential ID.

  • result_rowsList[List]

    A preview of rows returned by the query.

  • result_columnsList[str]

    A preview of columns returned by the query.

  • errorstr

    The error message for this run, if present.

  • started_atstr (date-time)

    The start time of the last run.

  • finished_atstr (date-time)

    The end time of the last run.

  • statestr

    The state of the last run. One of queued, running, succeeded, failed, and cancelled.

  • script_idint

    The ID of the script associated with this query.

  • exceptionstr

    Deprecated and not used.

  • created_at : str (time)

  • updated_at : str (time)

  • last_run_idint

    The ID of the last run.

  • hiddenbool

    The hidden status of the item.

  • archivedstr

    The archival status of the requested item(s).

  • namestr

    The name of the query.

  • authordict
    • idint

      The ID of this user.

    • namestr

      This user’s name.

    • usernamestr

      This user’s username.

    • initialsstr

      This user’s initials.

    • onlinebool

      Whether this user is online.

  • report_idint

    The ID of the report associated with this query.

delete_runs(id: int, run_id: int)

Cancel a run

Parameters:
idint

The ID of the Query job.

run_idint

The ID of the run.

Returns:
None

Response code 202: success

get(id: int)

Get details about a query

Parameters:
idint

The query ID.

Returns:
civis.response.Response
  • idint

    The query ID.

  • databaseint

    The database ID.

  • sqlstr

    The SQL to execute.

  • credentialint

    The credential ID.

  • result_rowsList[List]

    A preview of rows returned by the query.

  • result_columnsList[str]

    A preview of columns returned by the query.

  • errorstr

    The error message for this run, if present.

  • started_atstr (date-time)

    The start time of the last run.

  • finished_atstr (date-time)

    The end time of the last run.

  • statestr

    The state of the last run. One of queued, running, succeeded, failed, and cancelled.

  • script_idint

    The ID of the script associated with this query.

  • exceptionstr

    Deprecated and not used.

  • created_at : str (time)

  • updated_at : str (time)

  • last_run_idint

    The ID of the last run.

  • hiddenbool

    The hidden status of the item.

  • archivedstr

    The archival status of the requested item(s).

  • namestr

    The name of the query.

  • authordict
    • idint

      The ID of this user.

    • namestr

      This user’s name.

    • usernamestr

      This user’s username.

    • initialsstr

      This user’s initials.

    • onlinebool

      Whether this user is online.

  • report_idint

    The ID of the report associated with this query.

get_runs(id: int, run_id: int)

Check status of a run

Parameters:
idint

The ID of the Query job.

run_idint

The ID of the run.

Returns:
civis.response.Response
  • idint

    The ID of the run.

  • query_idint

    The ID of the Query job.

  • statestr

    The state of the run, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.

  • is_cancel_requestedbool

    True if run cancel requested, else false.

  • created_atstr (time)

    The time the run was created.

  • started_atstr (time)

    The time the run started at.

  • finished_atstr (time)

    The time the run completed.

  • errorstr

    The error, if any, returned by the run.

list(*, query: str = None, database_id: int = None, credential_id: int = None, author_id: int = None, created_before: str = None, created_after: str = None, started_before: str = None, started_after: str = None, state: List[str] = None, exclude_results: bool = None, hidden: bool = None, archived: str = None, limit: int = None, page_num: int = None, order: str = None, order_dir: str = None, iterator: bool = None)

List queries

Parameters:
querystr, optional

Space delimited query for searching queries by their SQL. Supports wild card characters “?” for any single character, and “*” for zero or more characters.

database_idint, optional

The database ID.

credential_idint, optional

The credential ID.

author_idint, optional

The author of the query.

created_beforestr, optional

An upper bound for the creation date of the query.

created_afterstr, optional

A lower bound for the creation date of the query.

started_beforestr, optional

An upper bound for the start date of the last run.

started_afterstr, optional

A lower bound for the start date of the last run.

stateList[str], optional

The state of the last run. One or more of queued, running, succeeded, failed, and cancelled. Specify multiple values as a comma-separated list (e.g., “A,B”).

exclude_resultsbool, optional

If true, does not return cached query results.

hiddenbool, optional

If specified to be true, returns hidden items. Defaults to false, returning non-hidden items.

archivedstr, optional

The archival status of the requested item(s).

limitint, optional

Number of results to return. Defaults to 20. Maximum allowed is 50.

page_numint, optional

Page number of the results to return. Defaults to the first page, 1.

orderstr, optional

The field on which to order the result set. Defaults to created_at. Must be one of: created_at, started_at.

order_dirstr, optional

Direction in which to sort, either asc (ascending) or desc (descending) defaulting to desc.

iteratorbool, optional

If True, return a generator to iterate over all responses. Use when more results than the maximum allowed by limit are needed. When True, limit and page_num are ignored. Defaults to False.

Returns:
civis.response.PaginatedResponse
  • idint

    The query ID.

  • databaseint

    The database ID.

  • sqlstr

    The SQL to execute.

  • credentialint

    The credential ID.

  • result_rowsList[List]

    A preview of rows returned by the query.

  • result_columnsList[str]

    A preview of columns returned by the query.

  • errorstr

    The error message for this run, if present.

  • started_atstr (date-time)

    The start time of the last run.

  • finished_atstr (date-time)

    The end time of the last run.

  • statestr

    The state of the last run. One of queued, running, succeeded, failed, and cancelled.

  • script_idint

    The ID of the script associated with this query.

  • exceptionstr

    Deprecated and not used.

  • created_at : str (time)

  • updated_at : str (time)

  • last_run_idint

    The ID of the last run.

  • archivedstr

    The archival status of the requested item(s).

  • preview_rowsint

    The number of rows to save from the query’s result (maximum: 1000).

  • report_idint

    The ID of the report associated with this query.

list_runs(id: int, *, limit: int = None, page_num: int = None, order: str = None, order_dir: str = None, iterator: bool = None)

List runs for the given Query job

Parameters:
idint

The ID of the Query job.

limitint, optional

Number of results to return. Defaults to 20. Maximum allowed is 100.

page_numint, optional

Page number of the results to return. Defaults to the first page, 1.

orderstr, optional

The field on which to order the result set. Defaults to id. Must be one of: id.

order_dirstr, optional

Direction in which to sort, either asc (ascending) or desc (descending) defaulting to desc.

iteratorbool, optional

If True, return a generator to iterate over all responses. Use when more results than the maximum allowed by limit are needed. When True, limit and page_num are ignored. Defaults to False.

Returns:
civis.response.PaginatedResponse
  • idint

    The ID of the run.

  • query_idint

    The ID of the Query job.

  • statestr

    The state of the run, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.

  • is_cancel_requestedbool

    True if run cancel requested, else false.

  • created_atstr (time)

    The time the run was created.

  • started_atstr (time)

    The time the run started at.

  • finished_atstr (time)

    The time the run completed.

  • errorstr

    The error, if any, returned by the run.

list_runs_logs(id: int, run_id: int, *, last_id: int = None, limit: int = None)

Get the logs for a run

Parameters:
idint

The ID of the Query job.

run_idint

The ID of the run.

last_idint, optional

The ID of the last log message received. Log entries with this ID value or lower will be omitted.Logs are sorted by ID if this value is provided, and are otherwise sorted by createdAt.

limitint, optional

The maximum number of log messages to return. Default of 10000.

Returns:
civis.response.Response
  • idint

    The ID of the log.

  • created_atstr (date-time)

    The time the log was created.

  • messagestr

    The log message.

  • levelstr

    The level of the log. One of unknown,fatal,error,warn,info,debug.

post(database: int, sql: str, preview_rows: int, *, credential: int = None, hidden: bool = None, interactive: bool = None, include_header: bool = None, compression: str = None, column_delimiter: str = None, unquoted: bool = None, filename_prefix: str = None)

Execute a query

Parameters:
databaseint

The database ID.

sqlstr

The SQL to execute.

preview_rowsint

The number of rows to save from the query’s result (maximum: 1000).

credentialint, optional

The credential ID.

hiddenbool, optional

The hidden status of the item.

interactivebool, optional

Deprecated and not used.

include_headerbool, optional

Whether the CSV output should include a header row [default: true].

compressionstr, optional

The type of compression. One of gzip or zip, or none [default: gzip].

column_delimiterstr, optional

The delimiter to use. One of comma or tab, or pipe [default: comma].

unquotedbool, optional

If true, will not quote fields.

filename_prefixstr, optional

The output filename prefix.

Returns:
civis.response.Response
  • idint

    The query ID.

  • databaseint

    The database ID.

  • sqlstr

    The SQL to execute.

  • credentialint

    The credential ID.

  • result_rowsList[List]

    A preview of rows returned by the query.

  • result_columnsList[str]

    A preview of columns returned by the query.

  • errorstr

    The error message for this run, if present.

  • started_atstr (date-time)

    The start time of the last run.

  • finished_atstr (date-time)

    The end time of the last run.

  • statestr

    The state of the last run. One of queued, running, succeeded, failed, and cancelled.

  • script_idint

    The ID of the script associated with this query.

  • exceptionstr

    Deprecated and not used.

  • created_at : str (time)

  • updated_at : str (time)

  • last_run_idint

    The ID of the last run.

  • hiddenbool

    The hidden status of the item.

  • archivedstr

    The archival status of the requested item(s).

  • my_permission_levelstr

    Your permission level on the object. One of “read”, “write”, or “manage”.

  • interactivebool

    Deprecated and not used.

  • preview_rowsint

    The number of rows to save from the query’s result (maximum: 1000).

  • include_headerbool

    Whether the CSV output should include a header row [default: true].

  • compressionstr

    The type of compression. One of gzip or zip, or none [default: gzip].

  • column_delimiterstr

    The delimiter to use. One of comma or tab, or pipe [default: comma].

  • unquotedbool

    If true, will not quote fields.

  • filename_prefixstr

    The output filename prefix.

  • report_idint

    The ID of the report associated with this query.

post_runs(id: int)

Start a run

Parameters:
idint

The ID of the Query job.

Returns:
civis.response.Response
  • idint

    The ID of the run.

  • query_idint

    The ID of the Query job.

  • statestr

    The state of the run, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.

  • is_cancel_requestedbool

    True if run cancel requested, else false.

  • created_atstr (time)

    The time the run was created.

  • started_atstr (time)

    The time the run started at.

  • finished_atstr (time)

    The time the run completed.

  • errorstr

    The error, if any, returned by the run.

put_scripts(id: int, script_id: int)

Update the query’s associated script

Parameters:
idint

The query ID.

script_idint

The ID of the script associated with this query.

Returns:
civis.response.Response
  • idint

    The query ID.

  • databaseint

    The database ID.

  • sqlstr

    The SQL to execute.

  • credentialint

    The credential ID.

  • result_rowsList[List]

    A preview of rows returned by the query.

  • result_columnsList[str]

    A preview of columns returned by the query.

  • errorstr

    The error message for this run, if present.

  • started_atstr (date-time)

    The start time of the last run.

  • finished_atstr (date-time)

    The end time of the last run.

  • statestr

    The state of the last run. One of queued, running, succeeded, failed, and cancelled.

  • script_idint

    The ID of the script associated with this query.

  • exceptionstr

    Deprecated and not used.

  • created_at : str (time)

  • updated_at : str (time)

  • last_run_idint

    The ID of the last run.

  • hiddenbool

    The hidden status of the item.

  • archivedstr

    The archival status of the requested item(s).

  • namestr

    The name of the query.

  • authordict
    • idint

      The ID of this user.

    • namestr

      This user’s name.

    • usernamestr

      This user’s username.

    • initialsstr

      This user’s initials.

    • onlinebool

      Whether this user is online.

  • report_idint

    The ID of the report associated with this query.