Database Details - SQLite and PostgreSQL
[1]:
import os
import sqlite3
import psycopg2
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
from modules.quicklook.src.analyze_time_series import AnalyzeTimeSeries
from database.modules.utils.tsdb import TSDB
%matplotlib inline
PostgreSQL
to:do add additional explantion here
[2]:
backend = 'psql'
myTS_psql = AnalyzeTimeSeries(backend=backend)
myTS_psql.db.print_db_status()
INFO: Starting AnalyzeTimeSeries
INFO: Starting KPF_TSDB
INFO: Jupyter Notebook environment detected.
INFO: Base data directory: /data/L0
INFO: Backend: psql
INFO: PSQL server: 127.0.0.1
INFO: PSQL username: timeseriesdba
INFO: PSQL user role: admin
INFO: Metadata table exists.
INFO: Metadata table read.
INFO: Data tables exist.
INFO: Database Table Summary:
INFO: Table Columns Rows
INFO: -----------------------------------
INFO: tsdb_base 22 122269
INFO: tsdb_l0 107 122269
INFO: tsdb_2d 84 122013
INFO: tsdb_2d_flux 96 122013
INFO: tsdb_l1 126 121823
INFO: tsdb_l1_medg 106 121823
INFO: tsdb_l1_medr 97 121823
INFO: tsdb_l1_stdg 106 121823
INFO: tsdb_l1_stdr 97 121823
INFO: tsdb_l2 25 99236
INFO: tsdb_l0t 124 122269
INFO: tsdb_l2rv 29 99236
INFO: tsdb_l2ccf 7 99236
INFO: tsdb_l2_bcv 68 99236
INFO: tsdb_l2_bjd 68 99236
INFO: tsdb_l2_ccfw 68 99236
INFO: tsdb_l2_sci1 68 99236
INFO: tsdb_l2_sci2 68 99236
INFO: tsdb_l2_sci3 68 99236
INFO: tsdb_l2_sci 135 99236
INFO: tsdb_l2_cal 135 99236
INFO: tsdb_l2_sky 135 99236
INFO: Dates: 244 days from 20240527 to 20250730
INFO: Last update: 2025-07-30 18:53:03
[3]:
myTS_psql.db.get_first_last_dates()
[3]:
(datetime.datetime(2024, 5, 27, 6, 39, 26, 254000),
datetime.datetime(2025, 7, 30, 18, 46, 20, 915000))
SQLite
This format was chosen because it doesn’t require an elaborate software installation and the database files can be easily passed around and read by other users, facilitiating data transfer.
[4]:
backend = 'sqlite'
db_path = 'kpf_ts.db' # name of database file
myTS_sqlite = AnalyzeTimeSeries(db_path=db_path, backend=backend)
myTS_sqlite.db.print_db_status()
INFO: Starting AnalyzeTimeSeries
INFO: Starting KPF_TSDB
INFO: Jupyter Notebook environment detected.
INFO: Base data directory: /data/L0
INFO: Backend: sqlite
INFO: Path of database file: /code/KPF-Pipeline/docs/source/tutorials/kpf_ts.db
INFO: Metadata table exists.
INFO: Metadata table read.
INFO: Data tables exist.
INFO: Database Table Summary:
INFO: Table Columns Rows
INFO: -----------------------------------
INFO: tsdb_base 22 3345
INFO: tsdb_l0 107 3345
INFO: tsdb_2d 84 3345
INFO: tsdb_2d_flux 96 3345
INFO: tsdb_l1 125 3345
INFO: tsdb_l1_medg 106 3345
INFO: tsdb_l1_medr 97 3345
INFO: tsdb_l1_stdg 106 3345
INFO: tsdb_l1_stdr 97 3345
INFO: tsdb_l2 25 3011
INFO: tsdb_l0t 124 3345
INFO: tsdb_l2rv 29 3011
INFO: tsdb_l2ccf 7 3011
INFO: tsdb_l2_bcv 68 3011
INFO: tsdb_l2_bjd 68 3011
INFO: tsdb_l2_ccfw 68 3011
INFO: tsdb_l2_sci1 68 3011
INFO: tsdb_l2_sci2 68 3011
INFO: tsdb_l2_sci3 68 3011
INFO: tsdb_l2_sci 135 3011
INFO: tsdb_l2_cal 135 3011
INFO: tsdb_l2_sky 135 3011
INFO: Dates: 6 days from 20241001 to 20241006
INFO: Last update: 2025-07-28 03:45:31
[ ]: