How to find and query for data#

An AiiDA database stores a graph of connected entities, which can be queried with the QueryBuilder class.

Before starting to write a query, it helps to:

  • Know what you want to query for.
    In the language of databases, you need to tell the backend what entity you are looking for and optionally which of its properties you want to project.
    For example, you might be interested in the label of a calculation and the PKs of all its outputs.
  • Know the relationships between entities you are interested in.
    Nodes of an AiiDA graph (vertices) are connected with links (edges).
    A node can for example be either the input or output of another node, but also an ancestor or a descendant.
  • Know how you want to filter the results of your query.

Once you are clear about what you want and how you can get it, the QueryBuilder will build an SQL-query for you.

There are two ways of using the QueryBuilder:

  1. In the appender method, you construct your query step by step using the QueryBuilder.append() method.

  2. In the dictionary approach, you construct a dictionary that defines your query and pass it to the QueryBuilder.

Both APIs provide the same functionality - the appender method may be more suitable for interactive use, e.g., in the verdi shell, whereas the dictionary method can be useful in scripting. In this section we will focus on the basics of the appender method. For more advanced queries or more details on the query dictionary, see the topics section on advanced querying.

Selecting entities#

Using the append() method of the QueryBuilder, you can query for the entities you are interested in. Suppose you want to query for calculation job nodes in your database:

from aiida.orm import QueryBuilder
qb = QueryBuilder()       # Instantiating instance. One instance -> one query
qb.append(CalcJobNode)    # Setting first vertex of path

If you are interested in instances of different classes, you can also pass an iterable of classes. However, they have to be of the same ORM-type (e.g. all have to be subclasses of Node):

qb = QueryBuilder()       # Instantiating instance. One instance -> one query
qb.append([CalcJobNode, WorkChainNode]) # Setting first vertices of path, either WorkChainNode or Job.

Note

Processes have both a run-time Process that executes them and a Node that stores their data in the database (see the corresponding topics section for a detailed explanation). The QueryBuilder allows you to pass either the Node class (e.g. CalcJobNode) or the Process class (e.g. CalcJob), which will automatically select the right entity for the query. Using either CalcJobNode or CalcJob will produce the same query results.

Retrieving results#

Once you have appended the entity you want to query for to the QueryBuilder, the next question is how to get the results. There are several ways to obtain data from a query:

qb = QueryBuilder()                 # Instantiating instance
qb.append(CalcJobNode)              # Setting first vertices of path

first_row = qb.first()              # Returns a list (!) of the results of the first row

all_results_d = qb.dict()           # Returns all results as a list of dictionaries

all_results_l = qb.all()            # Returns a list of lists

Tip

If your query only has a single projection, use flat=True in the first and all methods to return a single value or a flat list, respectively.

You can also return your query as a generator:

all_res_d_gen = qb.iterdict()       # Return a generator of dictionaries
all_res_l_gen = qb.iterall()        # Returns a generator of lists

This will retrieve the data in batches, and you can start working with the data before the query has completely finished. For example, you can iterate over the results of your query in a for loop:

for entry in qb.iterall():
    # do something with a single entry in the query result

Important

When looping over the result of a query, use the iterall (or iterdict) generator instead of all (or dict). This avoids loading the entire query result into memory, and it also delays committing changes made to AiiDA objects inside the loop until the end of the loop is reached. If an exception is raised before the loop ends, all changes are reverted.

Filters#

Usually you do not want to query for all entities of a certain class, but rather filter the results based on certain properties. Suppose you do not want all CalcJobNode data, but only those that are finished:

qb = QueryBuilder()                 # Initialize a QueryBuilder instance
qb.append(
    CalcJobNode,                    # Append a CalcJobNode
    filters={                       # Specify the filters:
        'attributes.process_state': 'finished',  # the process is finished
    },
)

You can apply multiple filters to one entity in a query. Say you are interested in all calculation jobs in your database that are finished and have exit_status == 0:

qb = QueryBuilder()                 # Initialize a QueryBuilder instance
qb.append(
    CalcJobNode,                    # Append a CalcJobNode
    filters={                       # Specify the filters:
        'attributes.process_state': 'finished',     # the process is finished AND
        'attributes.exit_status': 0                 # has exit_status == 0
    },
)

In case you want to query for calculation jobs that satisfy one of these conditions, you can use the or operator:

qb = QueryBuilder()
qb.append(
    CalcJobNode,
    filters={
        'or':[
            {'attributes.process_state': 'finished'},
            {'attributes.exit_status': 0}
        ]
    },
)

If we had written and instead of or in the example above, we would have performed the exact same query as the previous one, because and is the default behavior if you provide several filters as key-value pairs in a dictionary to the filters argument. In case you want all calculation jobs with state finished or excepted, you can also use the in operator:

qb = QueryBuilder()
qb.append(
    CalcJobNode,
    filters={
        'attributes.process_state': {'in': ['finished', 'excepted']}
    },
)

Programmatic syntax for filters#

New in version 2.6.

Filter keys may be defined programmatically, providing in modern IDEs (including AiiDA’s verdi shell) autocompletion of fields and operators. For example, the above query may be given as

qb = QueryBuilder()
qb.append(
    CalcJobNode,
    filters={
        CalcJobNode.fields.process_state: {'in': ['finished', 'excepted']},
    },
)

In this approach, CalcJobNode.fields. will suggest (autocomplete) the queryable fields of CalcJobNode allowing the user to explore the node’s attributes directly while constructing the query.

Alternatively, the entire filtering expression may be provided programmatically as logical expressions:

qb = QueryBuilder()
qb.append(
    CalcJobNode,
    filters=CalcJobNode.fields.process_state.in_(['finished', 'excepted']),
)

Note

Logical operations are distributed by type. As such, Node.fields.<some_field>. will only provide the supported operations for the type of some_field, in this case ==, in_, like, and ilike, for type str.

Logical expressions may be strung together with & and | to construct complex queries.

filters=(
    (Node.fields.ctime < datetime(2030, 1, 1))
    & (
        (Node.fields.pk.in_([4, 8, 15, 16, 23, 42]))
        | (Node.fields.label.like("%some_label%"))
    )
    & (Node.fields.extras.has_key("some_key"))
)

Tip

() may be used to override the natural precedence of |.

Operator negations#

A filter can be turned into its associated negation by adding an exclamation mark, !, in front of the operator. So, to query for all calculation jobs that are not a finished or excepted state:

qb = QueryBuilder()
qb.append(
    CalcJobNode,
    filters={
        'attributes.process_state': {'!in': ['finished', 'excepted']}
    },
)

Note

The above rule applies to all operators. For example, you can check non-equality with !==, since this is the equality operator (==) with a negation prepended.

A complete list of all available operators can be found in the advanced querying section.

New in version 2.6: Programamtic filter negation

In the new logical expression syntax, negation can be achieved by prepending ~ to any expression. For example ~(Int.fields.value < 5) is equivalent to Int.fields.value >= 5.

Relationships#

It is possible to query for data based on its relationship to another entity in the database. Imagine you are not interested in the calculation jobs themselves, but in one of the outputs they create. You can build upon your initial query for all CalcJobNode’s in the database using the relationship of the output to the first step in the query:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob')

In the first append call, we query for all CalcJobNode’s in the database, and tag this step with the unique identifier 'calcjob'. Next, we look for all Int nodes that are an output of the CalcJobNode’s found in the first step, using the with_incoming relationship argument. The Int node was created by the CalcJobNode and as such has an incoming create link.

In the context of our query, we are building a path consisting of vertices (i.e. the entities we query for) connected by edges defined by the relationships between them. The complete set of all possible relationships you can use query for, as well as the entities that they connect to, can be found in the advanced querying section.

Note

The tag identifier can be any alphanumeric string, it is simply a label used to refer to a previous vertex along the query path when defining a relationship.

Projections#

By default, the QueryBuilder returns the instances of the entities corresponding to the final append to the query path. For example:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob')

The above code snippet will return all Int nodes that are outputs of any CalcJobNode. However, you can also project other entities in the path by adding project='*' to the corresponding append() call:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob', project='*')
qb.append(Int, with_incoming='calcjob')

This will return all CalcJobNode’s that have an Int output node.

However, in many cases we are not interested in the entities themselves, but rather their PK, UUID, attributes or some other piece of information stored by the entity. This can be achieved by providing the corresponding column to the project keyword argument:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob', project='id')

In the above example, executing the query returns all PK’s of the Int nodes which are outputs of all CalcJobNode’s in the database. Moreover, you can project more than one piece of information for one vertex by providing a list:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob')
qb.append(Int, with_incoming='calcjob', project=['id', 'attributes.value'])

For the query above, qb.all() will return a list of lists, for which each element corresponds to one entity and contains two items: the PK of the Int node and its value. Finally, you can project information for multiple vertices along the query path:

qb = QueryBuilder()
qb.append(CalcJobNode, tag='calcjob', project='*')
qb.append(Int, with_incoming='calcjob', project=['id', 'attributes.value'])

All projections must start with one of the columns of the entities in the database, or project the instances themselves using '*'. Examples of columns we have encountered so far are id, uuid and attributes. If the column is a dictionary, you can expand the dictionary values using a dot notation, as we have done in the previous example to obtain the attributes.value. This can be used to project the values of nested dictionaries as well.

Note

Be aware that for consistency, QueryBuilder.all() / iterall() always returns a list of lists, even if you only project one property of a single entity. Use QueryBuilder.all(flat=True) to return the query result as a flat list in this case.

New in version 2.6: Programmatic syntax for projections

Similar to filters, projections may also be provided programmatically, leveraging the autocompletion feature of modern IDEs.

qb = QueryBuilder()
qb.append(
    Int,
    project=[
        Int.fields.pk,
        Int.fields.value,
    ],
)

As mentioned in the beginning, this section provides only a brief introduction to the QueryBuilder’s basic functionality. To learn about more advanced queries, please see the corresponding topics section.

Shortcuts#

The QueryBuilder is the generic way of querying for data in AiiDA. For certain common queries, shortcuts have been added to the AiiDA python API to save you a couple of lines of code.

Inputs and outputs of processes#

The get_incoming() and get_outgoing() methods, described in the previous section, can be used to access all neighbors from a certain node and provide advanced filtering options. However, often one doesn’t need this expressivity and simply wants to retrieve all neighboring nodes with a syntax that is as succint as possible. A prime example is to retrieve the inputs or outputs of a process. Instead of using get_incoming() and get_outgoing(), to get the inputs and outputs of a process_node one can do:

inputs = process_node.inputs
outputs = process_node.outputs

These properties do not return the actual inputs and outputs directly, but instead return an instance of NodeLinksManager. The reason is because through the manager, the inputs or outputs are accessible through their link label (that, for inputs and outputs of processes, is unique) and can be tab-completed. For example, if the process_node has an output with the label result, it can be retrieved as:

process_node.outputs.result

The inputs or outputs can also be accessed through key dereferencing:

process_node.outputs['result']

If there is no neighboring output with the given link label, a NotExistentAttributeError or NotExistentKeyError will be raised, respectively.

Note

The inputs and outputs properties are only defined for ProcessNode’s. This means that you cannot chain these calls, because an input or output of a process node is guaranteed to be a Data node, which does not have inputs or outputs.

Creator, caller and called#

Similar to the inputs and outputs properties of process nodes, there are some more properties that make exploring the provenance graph easier:

  • called(): defined for ProcessNode’s and returns the list of process nodes called by this node. If this process node did not call any other processes, this property returns an empty list.

  • caller(): defined for ProcessNode’s and returns the process node that called this node. If this node was not called by a process, this property returns None.

  • creator(): defined for Data nodes and returns the process node that created it. If the node was not created by a process, this property returns None.

Note

Using the creator and inputs properties, one can easily move up the provenance graph. For example, starting from some data node that represents the result of a long workflow, one can move up the provenance graph to find an initial input node of interest: result.creator.inputs.some_input.creator.inputs.initial_input.

Calculation job results#

CalcJobNode’s provide the res() property, that can give easy access to the results of the calculation job. The requirement is that the CalcJob class that produced the node, defines a default output node in its spec. This node should be a Dict output that will always be created. An example is the TemplatereplacerCalculation plugin, that has the output_parameters output that is specified as its default output node.

The res() property will give direct easy access to all the keys within this dictionary output. For example, the following:

list(node.res)

will return a list of all the keys in the output node. Individual keys can then be accessed through attribute dereferencing:

node.res.some_key

In an interactive shell, the available keys are also tab-completed. If you type node.res. followed by the tab key twice, a list of the available keys is printed.

Note

The res() property is really just a shortcut to quickly and easily access an attribute of the default output node of a calculation job. For example, if the default output node link label is output_parameters, then node.res.some_key is exactly equivalent to node.outputs.output_parameters.dict.some_key. That is to say, when using res, one is accessing attributes of one of the output nodes, and not of the calculation job node itself.

On this page
Edit on GitHub
Show Source