Spaces:
Sleeping
Sleeping
File size: 1,967 Bytes
4ad3b10 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | import pandas as pd
import logging
import re
from .db_utils import execute_query
logger = logging.getLogger(__name__)
def fetch_tank_data(days=7):
"""
Fetches oil level data for specific Tank asset IDs.
Uses SQL-side JSON extraction as requested.
"""
query = f"""
SELECT
a.name AS tank_id,
(d.value->>'TankOilLevelInFeet001')::float AS level_feet,
(d.value->>'Timestamp')::timestamp AS sensor_timestamp,
d.timestamp AS db_timestamp
FROM asset_datapoint d
JOIN asset a ON d.entity_id = a.id
WHERE d.entity_id IN (
'2TV1OLqZSv5JwIEHomeRR1',
'6UXHYoFE9as4nc25HA72AN',
'5UwgGOBQ8VqdVZO37E1ci8',
'2H7Gj65gRcLS1V6OTq72Hk',
'7BKD81c3z8gKpGZt5bIc87',
'2pd9BV69qYHsnoyiAAqJmX',
'50Y5udYfIWJWLEbduASYwT',
'6tUKaWyipyL8MfjaTntrSH',
'6UrAIw3WfeBmCn7yYgHGKR',
'64G6XlehtAyuhFP6U5V9iB',
'7gmxORynTjM8Lmp4DDtZSP',
'4la3XSG9sUisBdczxxjYLv'
)
AND d.timestamp >= NOW() - INTERVAL '{days} days'
AND d.attribute_name = 'data'
ORDER BY a.name, d.timestamp ASC
"""
try:
results = execute_query(query)
if not results:
logger.warning(f"No Tank data found in the last {days} days for the specified IDs.")
return None
df = pd.DataFrame(results)
# Consistent normalization: Physical07 instead of Physical7
def normalize_tank_name(name):
if not name: return name
return re.sub(r'Physical(\d)$', r'Physical0\1', name)
df['tank_id'] = df['tank_id'].apply(normalize_tank_name)
df['sensor_timestamp'] = pd.to_datetime(df['sensor_timestamp'])
return df
except Exception as e:
logger.error(f"Failed to fetch Tank data: {e}")
return None
|