The APyT SQL module

This module provides a lightweight Python interface to the APyT SQL database. It implements convenience functions for downloading, querying, and updating measurement data stored in the central database.

The design goal of this module is to abstract away low-level HTTP/SQL details and provide a consistent API for accessing experimental data in Python. In particular:

  • All functions can use explicit authorization credentials (username, password) if required for database access.

  • Each function returns both the HTTP status code of the request and the retrieved or updated content (if applicable).

  • Downloaded measurement data can optionally be cached on disk in NumPy .npy files to reduce repeated network requests.

Typical use cases

  • Fetching structured measurement data from the database for analysis.

  • Updating metadata fields (e.g., experiment or evaluation parameters) of existing records.

  • Executing SQL queries for specific columns and records.

List of functions

  • download(): Download and (optionally) cache measurement data from the database.

  • dump_record(): Dump a single SQL database record to a JSON file.

  • load_record(): Load a JSON record from file and upload it to the SQL database.

  • query(): Query one or more fields from a SQL database record.

  • update(): Update a specific field of a database record.

Implementation notes

  • Database access is performed via HTTP requests to PHP scripts (download.php, update.php, query.php) hosted at the configured database URL (see apyt.io.config.get_setting()).

  • Binary measurement datasets are streamed and converted directly into NumPy arrays.

  • Error handling and logging are integrated throughout; failed requests return the corresponding HTTP status code.

apyt.io.sql.download(id, use_cache=False, auth=None)[source]

Download and (optionally) cache measurement data from the database.

This function retrieves measurement data stored in the APyT SQL database. Data are returned as a structured NumPy array with predefined fields such as detector position, voltage signals, and timing information.

To reduce repeated network requests, measurement data can be cached on disk in a local .npy file, identified by the database record’s custom_id. If caching is enabled, subsequent calls will load data directly from the cache instead of re-downloading.

Parameters:

  • id (int) – The measurement ID of the record in the SQL database.

  • use_cache (bool, optional) – If True, attempt to load data from a local cache file. If the cache does not exist, data will be downloaded and then written to disk. If False, data are always fetched from the database. The latter is the default behavior.

  • auth (tuple of (str, str), optional) – A tuple (username, password) providing authorization credentials. If None, access to the database may fail, depending on its access/security configuration.

Returns:

  • status (int) – HTTP status code returned by the database request.

    • 200 indicates success.

    • Other codes indicate failure.

  • data (numpy.ndarray or None) – A structured NumPy array containing the measurement events with the following fields:

    • U_base : float32 — Base voltage

    • U_pulse : float32 — Pulse voltage

    • U_reflectron : float32 — Reflectron voltage

    • x_det : float32 — Detector x-position

    • y_det : float32 — Detector y-position

    • tof : float32 — Time-of-flight

    • epoch : int32 — Epoch time

    • pulse_num : uint32 — Pulse number

    Returns None if the download or query fails.

Notes

  • Creates or reads a cache file <custom_id>.npy in the current working directory when use_cache = True.

  • Requires network access to the SQL database unless loading from cache.

  • Logs progress and errors using the module-level logger.

apyt.io.sql.dump_record(id, file_name=None)[source]

Dump a single SQL database record to a JSON file.

This function retrieves a measurement record from the SQL database and writes its content to a JSON file. If no output filename is provided, one will be generated automatically based on the record’s custom_id and the current timestamp.

Parameters:

  • id (int) – The measurement ID of the record to retrieve.

  • file_name (str or Path, optional) – The name of the output file. If None, the filename is constructed as <custom_id>_<YYYYMMDD_HHMMSS>.rec.

Returns:

The path to the created JSON file, or None if the query failed.

Return type:

str or None

Warns:

UserWarning – If the SQL query does not succeed.

apyt.io.sql.load_record(id, file_name, auth)[source]

Load a JSON record from file and upload it to the SQL database.

This function reads a JSON file containing record data and updates the corresponding entry in the SQL database. Each key–value pair is uploaded individually via the update() function, except for read-only fields.

Parameters:

  • id (int) – The measurement ID of the record in the SQL database.

  • file_name (str or Path) – Path to the JSON file containing the record data.

  • auth (tuple of (str, str)) – Authentication credentials (username, password) for the SQL database.

Returns:

True if all fields were uploaded successfully, False if the input file is missing or if any upload fails.

Return type:

bool

Warns:

UserWarning –

  • If the input file does not exist.

  • If uploading any field fails.

apyt.io.sql.query(id, keys, auth=None)[source]

Query one or more fields from a SQL database record.

This function retrieves specific fields of a measurement entry from the APyT SQL database. Results are returned as a dictionary mapping field names to values.

Parameters:

  • id (int) – The measurement ID of the record in the SQL database.

  • keys (str or iterable of str) – The field(s) to retrieve from the database entry. If a single string is provided, it is automatically converted to a tuple. "*" retrieves all fields from the database entry.

  • auth (tuple of (str, str), optional) – A tuple (username, password) providing authorization credentials. If None, access to the database may fail, depending on its access/security configuration.

Returns:

  • status (int) – HTTP status code returned by the database request.

    • 200 indicates success.

    • Other codes indicate failure (see requests.codes).

  • result (dict or None) – Dictionary containing the requested fields. Returns None if the request fails or the record is missing.

Notes

  • If the field 'custom_id' is present, it is converted to str (in case of a numeric-only custom ID).

  • Errors are logged with the module-level logger.

apyt.io.sql.update(id, key, value, auth=None, method='GET')[source]

Update a specific field of a database record.

This function modifies an existing entry in the APyT SQL database by updating a single key–value pair. The update request is sent via HTTP (either GET or POST) to the configured database endpoint.

Parameters:

  • id (int) – The measurement ID of the record in the SQL database.

  • key (str) – The field name to update in the database entry.

  • value (str) – The new value for the specified field.

  • auth (tuple of (str, str), optional) – A tuple (username, password) providing authorization credentials. If None, access to the database may fail, depending on its access/security configuration.

  • method ({'GET', 'POST'}, default 'GET') – The HTTP request method to use when submitting the update.

Returns:

  • status (int) – HTTP status code returned by the database request.

    • 200 indicates success.

    • Other codes indicate failure.

  • response (str or None) – The raw text returned by the database endpoint. Returns None if the request fails.

Notes

  • The database endpoint returns "OK" when the update is successful.

  • Errors returned by the database are converted to readable text and logged.