sda.api.load_data_pg
====================

.. py:module:: sda.api.load_data_pg

.. autoapi-nested-parse::

   Load timeseries data from a PostgreSQL database (Clever Cloud).

   Each test is stored as a set of tables named ``timeseries_{tag}_{instrument}``.
   ``load_data`` discovers all such tables for a given *tag*, queries the ``time``
   column and every measurement column from each table, then merges them into a
   single wide DataFrame aligned on a rounded time axis (0.1 s precision).

   Database credentials are read from ``~/sda.json`` using the keys
   ``DB_USER``, ``DB_PASSWORD``, ``DB_HOST``, ``DB_PORT``, and ``DB_NAME``.



Functions
---------

.. autoapisummary::

   sda.api.load_data_pg.has_local_db_credentials
   sda.api.load_data_pg.load_commentary
   sda.api.load_data_pg.load_data


Module Contents
---------------

.. py:function:: has_local_db_credentials()

   Return True when all required DB_* keys are present in env vars or ``~/sda.json``.

   Environment variables take priority (used by the Docker/cloud container).
   Falls back to ``~/sda.json`` for local developer setups.

   Used by :func:`sda.api.load_data` to decide whether to connect directly
   to the database or fall back to the SDA cloud API.


.. py:function:: load_commentary(tag, start_time = None, end_time = None)

   Load commentary entries for *tag* from the ``commentary`` table.

   Returns a DataFrame with columns ``time`` (UTC-aware datetime) and
   ``commentary`` (str), ordered by time.  If no rows match, an empty
   DataFrame with those two columns is returned.

   :param tag: P&ID code to filter on (``pid_code`` column), e.g. ``T327``.
   :type tag: :py:class:`str`
   :param start_time: Inclusive lower bound on the ``time`` column.
   :type start_time: :py:class:`str` or :py:class:`datetime.datetime`, *optional*
   :param end_time: Inclusive upper bound on the ``time`` column.
   :type end_time: :py:class:`str` or :py:class:`datetime.datetime`, *optional*

   :raises ValueError: If *tag* contains invalid characters or DB config is incomplete.
   :raises ConnectionError: If the database is unreachable or the query fails.


.. py:function:: load_data(tag, start_time = None, end_time = None)

   Load all timeseries for a test and return a wide, time-aligned DataFrame.

   The function discovers every table named ``timeseriesdata_{tag}_{instrument}``
   in the database, queries all their columns for the requested time window, then
   merges them into a single DataFrame with one ``time`` column and one column per
   measurement.

   :param tag: Test name used to identify the tables (e.g. ``gif1_rev3``).
               All tables named ``timeseries_{tag}_*`` are loaded and merged.
   :type tag: :py:class:`str`
   :param start_time: Start of the time range (inclusive). If omitted, no lower bound is applied.
   :type start_time: :py:class:`str` or :py:class:`datetime.datetime`, *optional*
   :param end_time: End of the time range (inclusive). If omitted, no upper bound is applied.
   :type end_time: :py:class:`str` or :py:class:`datetime.datetime`, *optional*

   :returns: Wide DataFrame with a single ``time`` column (rounded to 0.1 s) followed
             by one column per measurement. Column names are derived from the last
             token(s) of the table name (e.g. ``temperature1`` from
             ``timeseries_gif1_T377_tag_001_temperature1``) and enriched with physical
             units when the measurement type is recognised
             (``°C``, ``m3/h``, ``bar``, ``NL/min``).
   :rtype: :py:class:`pandas.DataFrame`

   :raises ValueError: If *tag* contains invalid characters, no matching tables are found, or
       the database configuration in ``~/sda.json`` is incomplete.
   :raises ConnectionError: If the database is unreachable or a query fails.

   .. rubric:: Examples

   >>> import sda
   >>> df = sda.load_data("T377")  # all data
   >>> df = sda.load_data("T377", "2024-01-01", "2024-01-02")  # time-filtered
   >>> df.columns.tolist()
   ['time', 'temperature1 (°C)', 'pressure (bar)', 'co2 (NL/min)', ...]