Source code for ppcpy.io.sql_interaction

import os
import sqlite3
import logging
from datetime import datetime, timezone, timedelta
from collections import defaultdict
import pandas as pd
import numpy as np

import ppcpy.misc.helper as helper
from ppcpy.misc.helper import default_to_regular


MAPPING:dict = {'far_range': 'FR', 'near_range': 'NR', 'dfov': 'DFOV'}
MAPPING_INVERSE:dict = {y: x for x, y in MAPPING.items()}

DB_SCHEME = {
    'LC': {
        'table_name': 'lidar_calibration_constant',
        'column_names': [
            'cali_start_time', 'cali_stop_time', 'liconst', 'uncertainty_liconst', 'used_for_processing', 
            'wavelength', 'nc_zip_file', 'polly_type', 'cali_method', 'telescope'],
        'data_types': [
            'text', 'text', 'real', 'real', 'integer',
            'text', 'text', 'text', 'text', 'text'],
        'unique': ', UNIQUE(cali_start_time, cali_stop_time, wavelength, polly_type, telescope, cali_method)',
    },
    'DC': {
        'table_name': 'depol_calibration_constant',
        'column_names': [
            'cali_start_time', 'cali_stop_time', 'depol_const', 'uncertainty_depol_const', 'used_for_processing', 
            'wavelength', 'nc_zip_file', 'polly_type', 'cali_method', 'telescope'],
        'data_types': [
            'text', 'text', 'real', 'real', 'integer', 
            'text', 'text', 'text', 'text', 'text'],
        'unique': ', UNIQUE(cali_start_time, cali_stop_time, wavelength, polly_type, telescope, cali_method)',
    },
    'WVC': {
        'table_name': 'wv_calibration_constant',
        'column_names': [
            'cali_start_time', 'cali_stop_time', 'wv_const', 'uncertainty_wv_const', 'used_for_processing', 
            'wavelength', 'nc_zip_file', 'polly_type', 'cali_method', 'telescope'],
        'data_types': [
            'text', 'text', 'real', 'real', 'integer', 
            'text', 'text', 'text', 'text', 'text'],
        'unique': ', UNIQUE(cali_start_time, cali_stop_time, wavelength, polly_type, telescope, cali_method)',
    },
}

METHOD_DB_NAMES = {
    "raman": "Raman_Method",
    "klett": "Klett_Method",
    "D90": "D90",
    "transfer": "transfer",
    "model_profile": "Model_Profile_Method",
    "model_regression": "Model_Regression_Method",
}
METHOD_DB_NAMES_INVERSE:dict = {y: x for x, y in METHOD_DB_NAMES.items()}

[docs] def string_to_ts(s): """String of format %Y-%m-%d %H:%M:%S to timestamp (timezone-aware)""" return datetime.strptime(s, "%Y-%m-%d %H:%M:%S").replace(tzinfo=timezone.utc).timestamp()
def _format_ts(unix_timestamp): """Convert a Unix timestamp to a UTC database timestamp.""" return datetime.fromtimestamp( unix_timestamp, timezone.utc, ).strftime("%Y-%m-%d %H:%M:%S")
[docs] def get_from_sql_db(db_path:str, table_name:str, ts_interval:list[str], ts_delta:float=24) -> dict: """Read lidar calibration constant or depol calibration from database. Parameters ---------- db_path : str Name of the specific sqlite db file. table_name : str Default 'lidar_calibration_constant'. ts_interval : str The date or timestamp to look for. Returns ------- dict in calibration storage format **History** - 2026-03-28: First edition by Radenz. - 2026-05-08: Added check for table existence. Added retrieval methods. - 2026-11-01: Gently tried to make it more pythonic """ ret = {} delta = timedelta(hours=ts_delta) start = ( datetime.fromtimestamp(ts_interval[0], timezone.utc) - delta ).strftime("%Y-%m-%d %H:%M") end = ( datetime.fromtimestamp(ts_interval[-1], timezone.utc) + delta ).strftime("%Y-%m-%d %H:%M") if not os.path.exists(db_path): logging.warning(f"Path to Database does not exist {db_path}, likely directory is missing.") return ret with sqlite3.connect(db_path) as conn: exists = conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?;", (table_name,) ).fetchone() if exists is None: logging.warning(f"Database {db_path} not found.") return ret df = pd.read_sql_query( f'SELECT * FROM {table_name} WHERE cali_start_time BETWEEN ? AND ?;', conn, params=(start, end)) conn.close() ## Lidar calibration constants if table_name == 'lidar_calibration_constant': for method_db in ["Raman_Method", "Klett_Method"]: method = METHOD_DB_NAMES_INVERSE[method_db] method_key = f"{method}_db" d = defaultdict(list) df_method = df[df["cali_method"].eq(method_db)] for _, row in df_method.iterrows(): key = f"{row['wavelength']}_total_{MAPPING[row['telescope']]}" d[key].append({ "LC": row["liconst"], "LCStd": row["uncertainty_liconst"], "time_start": int(string_to_ts(row["cali_start_time"])), "time_end": int(string_to_ts(row["cali_stop_time"])), "method": method_key, }) ret[method_key] = default_to_regular(d) logging.info(f"Loaded {df_method.shape[0]} lines from table {table_name} with '{method_db}'.") ## Depolarization calibration constants if table_name == 'depol_calibration_constant': methods = ("D90", "transfer") if "cali_method" in df.columns else ("D90",) for method_db in methods: method = METHOD_DB_NAMES_INVERSE[method_db] method_key = f"{method}_db" d = defaultdict(list) # to capture if reading in an old db that lacks the column cali_method df_method = df[df["cali_method"].eq(method_db)] if "cali_method" in df.columns else df for _, row in df_method.iterrows(): key = f"{row['wavelength']}_{MAPPING[row['telescope']]}" d[key].append({ "eta": row["depol_const"], "eta_std": row["uncertainty_depol_const"], "time_start": int(string_to_ts(row["cali_start_time"])), "time_end": int(string_to_ts(row["cali_stop_time"])), "method": method_key, }) ret[method_key] = default_to_regular(d) logging.info(f"Loaded {df_method.shape[0]} lines from table {table_name} with '{method_db}'.") ## Water Vapor calibration constants if table_name == 'wv_calibration_constant': for method_db in ("Model_Profile_Method", "Model_Regression_Method"): method = METHOD_DB_NAMES_INVERSE[method_db] method_key = f"{method}_db" df_method = df[df["cali_method"].eq(method_db)] d = defaultdict(list) for _, row in df_method.iterrows(): key = f"{row['wavelength']}_{MAPPING[row['telescope']]}" d[key].append({ "WVC": row["wv_const"], "WVCStd": row["uncertainty_wv_const"], "time_start": int(string_to_ts(row["cali_start_time"])), "time_end": int(string_to_ts(row["cali_stop_time"])), "method": method_key, }) ret[method_key] = default_to_regular(d) logging.info(f"Loaded {df_method.shape[0]} lines from table {table_name} with '{method_db}'.") return ret
[docs] def prepare_for_sql_db_writing(data_cube, parameter:str, method:str) -> list[tuple]: """Collect all necessary variable and save it to a list of tuples for inserting into a SQLite table. Parameters ---------- data_cube : object Main PicassoProc object parameter :str Name of parameter to stoere eg. 'LC' or 'DC'. method : str Name of retrieval method used to retrieve the parameter (see `METHOD_DB_NAMES`) eg. 'klett' or 'raman'. Returns ------- rows_to_insert : list of tuples Rows to insert in the database. ** History ** - xxxx-xx-xx: First edition by - xxxx-xx-xx: Translated to python. - 2026-11-01: Gently tried to make it more pythonic """ rows_to_insert = [] method_db = METHOD_DB_NAMES[method] ## Lidar calibration constants if parameter == 'LC': for e in data_cube.LC.get(method, {}).keys(): wv, pol, tel = helper.get_wv_pol_telescope_from_dictkeyname(e) tel_db = MAPPING_INVERSE[tel] for line in data_cube.LC[method][e]: LC = line['LC'] LC_std = line['LCStd'] LC_is_used = True if LC == data_cube.LCused[e]['LC'] else False rows_to_insert.append(( _format_ts(line['time_start']), _format_ts(line['time_end']), float(LC), float(LC_std), LC_is_used, wv, str(data_cube.rawfile), data_cube.device, method_db, tel_db)) ## Depolarization calibration constants elif parameter == 'DC': for e in data_cube.pol_cali.get(method, {}).keys(): wv, tel = e.split('_') tel_db = MAPPING_INVERSE[tel] for line in data_cube.pol_cali[method][e]: eta = line['eta'] eta_std = line['eta_std'] eta_is_used = True if eta == data_cube.etaused[e]['eta'] else False rows_to_insert.append(( _format_ts(line['time_start']), _format_ts(line['time_end']), float(eta), float(eta_std), eta_is_used, wv, str(data_cube.rawfile), data_cube.device, method_db, tel_db)) ## Water vapor calibration constants elif parameter == 'WVC': for e in data_cube.wv_cali.get(method, {}).keys(): wv, tel = e.split('_') tel_db = MAPPING_INVERSE[tel] for line in data_cube.wv_cali[method][e]: WVC = line['WVC'] WVCStd = line['WVCStd'] WVC_is_used = True if WVC == data_cube.WVCused[e]['WVC'] else False rows_to_insert.append(( _format_ts(line['time_start']), _format_ts(line['time_end']), float(WVC), float(WVCStd), WVC_is_used, wv, str(data_cube.rawfile), data_cube.device, method_db, tel_db)) return rows_to_insert
[docs] def setup_empty(db_path:str, table_name:str, column_names:list[str], data_types:list[str], unique:str=''): """Create/Initialise an empty database. Parameters ---------- db_path : str Path to the SQLite database file. table_name : str Name of the target table. column_names : list of str List of column names to insert values into (e.g. ['col1', 'col2']). data_types : list of str List of SQLite data types for each respective columns (e.g. ['text', 'real']) """ if not os.path.exists(db_path): logging.warning(f"Path to Database does not exist {db_path}, will be generated") table_exists = False else: # check if table exists with sqlite3.connect(db_path) as conn: cursor = conn.execute( "SELECT 1 FROM pragma_table_list WHERE name = ?", (table_name,), ).fetchone() table_exists = cursor is not None conn.close() if not table_exists: column_names = ['id'] + column_names data_types = ['INTEGER PRIMARY KEY'] + data_types columns = ', '.join([f"{c} {d}" for c, d in zip(column_names, data_types)]) sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns}{unique}) " with sqlite3.connect(db_path) as conn: cursor = conn.cursor() cursor.execute(sql) conn.commit() conn.close() logging.info(f"added table {table_name}") else: # check he columns with sqlite3.connect(db_path) as conn: cursor = conn.cursor() rows = conn.execute(f'PRAGMA table_info({table_name})').fetchall() conn.close() actual = [row[1] for row in rows] missing_columns = set(actual) ^ set(['id'] + column_names) if missing_columns: logging.warning(f"missing columns {missing_columns} in {table_name} .... fixing database scheme HIC SUNT DRACONES") assert len(missing_columns) == 1, "Currently only implemented for a single missing column" col = list(missing_columns)[0] assert col == 'cali_method', "at the moment the default value is hard-coded (i.e. the used calibration method is an educated guess)" index = column_names.index(col) dtype = data_types[index] logging.warning(f'EXECUTING: ALTER TABLE "{table_name}" ADD COLUMN "{col}" {dtype}') with sqlite3.connect(db_path) as conn: conn.execute( f'ALTER TABLE "{table_name}" ADD COLUMN "{col}" {dtype} NOT NULL DEFAULT \'D90\'' ) conn.execute(f"CREATE UNIQUE INDEX IF NOT EXISTS uq_{table_name} ON {table_name} {unique.removeprefix(', UNIQUE')}")
# enforcing the unique constraint later on is internally a bit different, shown by "c" in the origin key instead of "c" # the alternative would be to recreate the table and move all the contents # not the safest waters to sail in ;)
[docs] def write_rows_to_sql_db(db_path:str, table_name:str, column_names:list[str], rows_to_insert:list[str]): """Insert multiple rows into a SQLite table. Parameters ---------- db_path : str Path to the SQLite database file. table_name : str Name of the target table. column_names : list of str List of column names to insert values into (e.g. ['col1', 'col2']). rows_to_insert : list of tuples Data to insert, e.g. [('a', 'b'), ('c', 'd')]. Notes ----- - The IGNORE syntax somehow did not work. With the UNIQUE colums defined and INSERT OR REPLACE at least the new values are updated. Though they are given a new ID. """ with sqlite3.connect(db_path) as conn: cursor = conn.cursor() rows = conn.execute(f'PRAGMA table_info({table_name})').fetchall() actual = [row[1] for row in rows] logging.debug(f"Expected: {['id'] + column_names}\nActual: {actual}") assert set(actual) == set(['id'] + column_names), f"missing column names {set(actual) ^ set(['id'] + column_names)}" if not actual == ['id'] + column_names: logging.info(f"Order of table differs. Not nice, but columns should be accessed explicitely") placeholders = ', '.join(['?'] * len(column_names)) columns = ', '.join(column_names) sql = f"INSERT OR REPLACE INTO {table_name} ({columns}) VALUES ({placeholders})" try: with sqlite3.connect(db_path) as conn: #print("\nBefore:") #print(pd.read_sql_query(f"SELECT * FROM {table_name}", conn).to_string(index=False)) cursor = conn.cursor() before_changes = conn.total_changes cursor.executemany(sql, rows_to_insert) conn.commit() affected = conn.total_changes - before_changes #print("\nAfter:") #print(pd.read_sql_query(f"SELECT * FROM {table_name}", conn).to_string(index=False)) conn.close() if affected == 0: logging.info(f"no rows added/changed in '{table_name}'.") else: logging.info(f"{affected} rows inserted/updated in '{table_name}'.") except sqlite3.Error as e: logging.warning(f"SQLite error: {e}")