Query a Database

Query a Database#

This section shows where DuckAtoms shines. Once you have inserted your data into a database, you can write SQL queries to obtain the information you are looking for. The result is typically a table. If used interactively, the result will be directly printed (under the hood the query is lazily evaluated and only executed when its __repr__ is called).

db.sql("""SELECT *
          FROM atoms
          LIMIT 3;
  """)
>>>
┌───────┬──────────────┬──────────────┬────────┬─────────────┬─────────────┬────────────┐
│  id   │ structure_id │ ase_atoms_id │ number │      x      │      y      │     z      │
│ int64 │    int64     │    int64     │ int64  │   double    │   double    │   double   │
├───────┼──────────────┼──────────────┼────────┼─────────────┼─────────────┼────────────┤
│  1    │            1 │            0 │     44 │         0.0 │         0.0 │ 2.36158973 │
│  2    │            1 │            1 │     44 │  2.26636267 │  2.26636267 │ 0.78719658 │
│  3    │            1 │            2 │     44 │ 18.12921135 │ 18.12278201 │ 5.51418449 │
└───────┴──────────────┴──────────────┴────────┴─────────────┴─────────────┴────────────┘

Result conversion#

The result of a query can directly be converted to different data structures. For example if you wanted the previous result in pandas.DataFrame use:

df = db.sql("""SELECT *
          FROM atoms
          LIMIT 3;
  """).df()

Other possible data types are type | method pandas.DataFrame | .df() list | .fetchall() numpy.array | .fetchnumpy() arrow | .arrow() polars.DataFrame | .pl()

Additionally DuckAtoms gives you the ability to convert any query on the structures table containing the structure_ids directly to a list of atoms objects:

# obtaining 10 samples from the db
atoms = db.sql("""SELECT id
          FROM structures
          USING SAMPLE 10 ROWS;
  """).atoms()

Examples#

There are no limits on how you can query your database. To give you some inspiration of what is possible here are some examples:

Examples coming soon - this section will demonstrate advanced SQL queries for:

  • Finding structures with specific properties

  • Complex filtering and sampling operations

Contents