5. Querying the AiiDA database {#sec:querybuilder}

We will now learn how to query the AiiDA database using the python interface. Queries are, in essence, questions to your database and you will use the answers you obtain in order to identify good candidate materials for methane storage.

5.1. The AiiDA QueryBuilder

AiiDA provides a querying tool called the QueryBuilder. When working in the verdi shell or a jupyter notebook with %aiida enabled, the QueryBuilder class has already been imported.

Let’s create a new querybuilder for our query:

qb = QueryBuilder()

Now we need to say which nodes of the AiiDA graph we are interested in. All node types of the AiiDA graph derive from the Node class, so in order to select the most generic type of node, simply append Node:

qb.append(Node)

At this point, we can finish our query by asking for all nodes matching these criteria:

qb.all() # Returns all nodes in the database

Note We could also have used qb.first() to get the first node. Always use tab-completion when in doubt which functions can be used on a python object.

This command will return us all the Nodes directly, which may not be the most wise thing to do considering that is the biggest family of AiiDA stored objects that we can query. To understand the size of the result, we can type the following command:

qb.count() # Returns an integer, the number of nodes in the database

If you are interested to retrieve a subclass of a node, append that specific subclass instead of Node:

CifData = DataFactory('cif')
qb = QueryBuilder() # Creating a new QueryBuilder instance
qb.append(CifData) # Telling the QueryBuilder instance that I want a cif data type
qb.all() # Asking for all the results!

Note Remember to create a new qb = QueryBuilder() instance for each new query.

Other data types we will need in the tutorial are:

ParameterData = DataFactory('parameter')
NetworkCalculation = CalculationFactory('zeopp.network')
RaspaCalculation = CalculationFactory('raspa')

---

5.1.1. Exercise

Find the number of CifData and NetworkCalculation nodes in the database. Is there one calculation for every structure?

You may also be interested to learn where the structures are coming from.

---

Node & subclasses	Number in DB
Node	2519
StructureData	0
CifData	503
ParameterData	503
SinglefileData	1
Code	1
JobCalculation	503

List of some Node subclasses and how many times they occur in our test database.

Note Advanced users who are familiar with SQL (Structured Query Language) syntax, can inspect the generated SQL query via:

str(qb)

5.2. Projections

So far, we’ve retrieved node objects but we are interested in the information they contain. In database language, performing a projection means to extract one or more specific columns from a table. In AiiDA language it means to specify which properties of the selected nodes our query should return.

|———| ————————-|

Entity	Properties
Node	id, uuid, type, label, description, ctime, mtime
Computer	id, uuid, name, hostname, description, enabled, transport_type, scheduler_type
User	id, email, first_name, last_name, institution
Group	id, uuid, name, type, time, description
———	————————-

5.2.1. A selection of entities and some of their properties

For example, we might be interested only in type and universally unique identifier (UUID) of each node:

qb = QueryBuilder()
qb.append(Node, project=["type", "uuid"])
qb.all()

Note Please use the id keyword in QueryBuilder queries to access the PK of a node.

5.3. Filters

We already know how to filter by the type of node, but often we would like to filter the results by other node properties.

For example, we might want to select all the calculations that were launched on a specific date. In database language, this is called “adding a filter” to a query. A filter is a boolean operator that returns True or False.

Operator	Datatype	Example
==	All	{'==':12}
in	All	{'in':['FINISHED', 'PARSING']}
>,<,<=,>=	floats, integers, dates	{'>':5.2}
like	Chars	{'like':'calculation%'}
ilike	Chars	{'ilike':'caLculAtioN%'}
or		{'or':[{'<':5.3}, {'>':6.3}]}
and		{'and':[{'>':5.3}, {'<':6.3}]}

5.3.1. Selection of filter operators

If you want to add filters to your query, use the filters and provide a dictionary with the filters you would like to apply. Let’s filter out the structure with label “HKUST1”:

qb = QueryBuilder()  # Instantiating a new QueryBuilder
qb.append(CifData,   # I want structures
  project=["id"],    # I want to know the id
  filters={"label": {"==":"HKUST1"}})  # Only structures with this label
qb.all()