This page was generated from docs/examples/DataSet/Database.ipynb. Interactive online version: .

Databases

This is an example notebook to briefly discuss the use of SQLite3 databases in QCoDeS. Generally, databases are used to store values of parameters obtained during a measurement in DataSet ojects. Specifically, storage occurs as part of the add_result method of the datasaver object in the context manager. DataSets can also be read from the database for analysis.

Imports

For our example we require only a few modules.

from pathlib import Path

import qcodes as qc
from qcodes.dataset import initialise_database, initialise_or_create_database_at

Initialization, creation and upgrade

A database can be initialized with a simple function call, initialise_database, which will initialize a database at the default location which is normally ~/experiments.db. This function produces does not return or print a value, but the location of the currently initialized database is stored in the QCoDeS configuration qc.config.core.db_location.

initialise_database()

print(qc.config.core.db_location)

~/experiments.db

When an existing database is initialized, it is always examined and upgraded to the latest version by a tested automatic process. When this occurs, a more detailed output is produced that details the upgrade process. This upgrade occurs with journaling to ensure data safety, to date there have been no instances of data corruption during database upgrades.

Example database upgrade output: > Upgrading database; v0 -> v1: : 0it [00:00, ?it/s]

Upgrading database; v1 -> v2: 100%|██████████| 1/1 [00:00<00:00, 561.49it/s]

Upgrading database; v2 -> v3: : 0it [00:00, ?it/s]

Upgrading database; v3 -> v4: : 0it [00:00, ?it/s]

Upgrading database; v4 -> v5: 100%|██████████| 1/1 [00:00<00:00, 925.08it/s]

Upgrading database; v5 -> v6: : 0it [00:00, ?it/s]

Upgrading database; v6 -> v7: 100%|██████████| 1/1 [00:00<00:00, 360.49it/s]

Upgrading database; v7 -> v8: 100%|██████████| 1/1 [00:00<00:00, 850.94it/s]

Upgrading database; v8 -> v9: 100%|██████████| 1/1 [00:00<00:00, 1059.97it/s]

Frequently, new databases will need to be created to meet the needs of data storage and organization. Moreover, these should be created a particular locations (e.g. a specific drive) with particular names (e.g. QCoDeS_example.db). These operations are all executed in an intelligent way by the initialise_or_create_database_at function.

Note that this will only change the location of the database in the current session not the default location stored in the qcodes config file. This means that this function is well suited to be part of a measurement script that ensures all data measured goes into the correct db but not to set the default location.

Note: This function requires a path to exist, i.e. it cannot create folders.

initialise_or_create_database_at(
    Path.cwd().parent / "example_output" / "QCoDeS_example.db"
)

print(qc.config.core.db_location)

/home/runner/work/Qcodes/Qcodes/docs/examples/example_output/QCoDeS_example.db

When databases are always created as a v1 database and then upgraded to the current version as detailed in the output. If this function is called again to initalize the database, then no messages are printed.

initialise_or_create_database_at(
    Path.cwd().parent / "example_output" / "QCoDeS_example.db"
)

print(qc.config.core.db_location)

/home/runner/work/Qcodes/Qcodes/docs/examples/example_output/QCoDeS_example.db

Note: Later calls for initializing a database via the initalise_database function will simply initialize the database listed at qc.config.core.db_location.

Note: The database path stored in qc.config.core.db_location may be changed to change the location of the default database.

Some data management options–including the ability to save data in memory only–included as options of the DataSet class:

• Saving data in-memory only example

• Saving data by a background thread

Moreover, we have also written an example notebook of transferring DataSets between database flies that may serve as a template for more complex data organization protocols.

Split Raw Data Storage

As the main database grows with many datasets, browsing experiments and loading metadata can become slower. QCoDeS supports an optional split raw data storage mode that writes the raw measurement data for each dataset into its own individual SQLite file, while keeping all metadata (experiments, runs, parameters, dependencies) in the main database.

This keeps the main database lightweight and makes it faster to work with, while still allowing all existing DataSet APIs to function identically.

Configuration

Split raw data storage is selected via the dataset.raw_data_backend option, with backend-specific settings under dataset.raw_data_backend_config:

• dataset.raw_data_backend (string, default "sqlite_main_db"): the results backend. Set to "sqlite_per_dataset_db" to enable split storage.

• dataset.raw_data_backend_config.sqlite_per_dataset_db.raw_data_path (string, default "{db_location}"): the folder where per-dataset SQLite files are created. The {db_location} placeholder expands to a folder derived from the main database path (e.g. ~/experiments.db becomes ~/experiments_db/).

You can enable it at runtime:

# Enable split raw data storage
qc.config.dataset.raw_data_backend = "sqlite_per_dataset_db"

# Optionally set a custom path for per-dataset files
qc.config.dataset.raw_data_backend_config.sqlite_per_dataset_db.raw_data_path = (
    "/data/raw_measurements/"
)

# Or use the default which derives from the main DB location:
# qc.config.dataset.raw_data_backend_config.sqlite_per_dataset_db.raw_data_path = "{db_location}"
# e.g. ~/experiments.db -> ~/experiments_db/

To make split storage the default for future sessions, set the same options through the qc.config Python interface shown above and persist your configuration - see Configuring QCoDeS.

How It Works

When split storage is enabled:

1. When a measurement starts (mark_started()), a per-dataset SQLite file named <guid>.db is created in the configured folder.

2. All measurement data (results table rows) is written to this per-dataset file instead of the main database.

3. No results table is created in the main database for such datasets; only the run metadata (experiments, runs, parameters, dependencies) is kept there, keeping the main database small.

4. The path to the per-dataset file is saved in a dedicated internal raw_data_db_path column of the runs table (not exposed via DataSet.metadata), so load_by_id() and related functions automatically find and reconnect to the correct file.

5. All DataSet methods (get_parameter_data, to_pandas_dataframe, to_xarray_dataset, cache, export, etc.) work transparently with split storage.

Note: Datasets created with split storage enabled can always be loaded later, even if the configuration is changed back to the default, as long as the per-dataset files remain at their original paths.

Updating Paths After Moving Raw Data Files

If you move the per-dataset raw data files to a different folder, the paths stored in the main database become stale. Use update_raw_data_paths to fix them:

# from qcodes.dataset import update_raw_data_paths

# After moving raw data files to a new folder:
# update_raw_data_paths(
#     db_path="/path/to/main_database.db",
#     new_raw_data_folder="/new/location/of/raw_files/"
# )

The function scans all datasets that have the internal raw_data_db_path column set, checks whether the corresponding .db file exists in the new folder, and updates the stored path. Datasets whose files are not found in the new folder are skipped with a warning.

Purging Orphaned Datasets

After archiving or deleting selected raw data files, some dataset records in the main database may reference files that no longer exist. Use purge_orphaned_datasets to clean up those orphaned records:

# from qcodes.dataset import purge_orphaned_datasets

# Dry run (default) — see what would be removed:
# result = purge_orphaned_datasets("/path/to/main_database.db")
# print(f"{len(result.orphaned_datasets)} orphaned datasets found")

# Actually remove orphaned records:
# result = purge_orphaned_datasets("/path/to/main_database.db", dry_run=False)

Cleaning Up Datasets by Criteria

To free disk space by removing datasets (both raw data files and DB records) based on age, sample name, or file size, use cleanup_datasets. Criteria are combined with AND logic — a dataset must match all specified criteria to be selected.

# from qcodes.dataset import cleanup_datasets

# Remove datasets older than 30 days (dry run first):
# result = cleanup_datasets("/path/to/db.db", older_than_days=30)
# print(f"{len(result.matching_datasets)} datasets would be removed")

# Remove datasets for a specific sample:
# result = cleanup_datasets("/path/to/db.db", sample_name="old-sample", dry_run=False)

# Remove datasets with raw data files larger than 500 MB:
# result = cleanup_datasets("/path/to/db.db", larger_than_mb=500, dry_run=False)