def insert_location_data(engine):
"""
Bulk inserts test location data.
Arguments:
engine: SQL engine object
"""
test_csv = "locations.csv"
loc_df = pd.read_csv(os.path.join(CONST_COREDATA_DIR, test_csv))
assert not loc_df.empty
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
# Check if table is empty and bulk inserts if it is
first_entry = session.query(LocationClass).first()
if first_entry is None:
session.bulk_insert_mappings(LocationClass,
loc_df.to_dict(orient="records"))
session_close(session)
assert session.query(LocationClass).count() == len(loc_df.index)
else:
session_close(session)
assert session.query(LocationClass).count() == len(loc_df.index)
def insert_user_data(engine):
"""
Bulk inserts users data.
Arguments:
engine: SQL engine object
"""
users_df = pd.read_csv("users.csv")
assert not users_df.empty
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
for user_row in users_df.iterrows():
data = UserClass(
username=user_row[1]["username"],
email=user_row[1]["email"],
password=user_row[1]["password"],
)
session.add(data)
session_close(session)
def import_data(pd_df, sensor_type, user, password, host, port, db_name):
"""
This function will take the checked sensor data (pd_df)
perform data checks and insert them into the db.
-data: raw data from a sensor as a csv (or dataframe??)
-sensor_type: type of sensor
Parameters required to connect to the database:
-user: my user name
-password: my password
-host: the host name of the server
-port: the port number the server is listening on
-db_name: my database name
"""
connection_string = make_conn_string(SQL_ENGINE, user, password, host,
port)
# Try to connect to a database that exists
success, log, engine = connect_db(connection_string, db_name)
if not success:
return success, log
# Try to connect to a database that exists
success, log, engine = connect_db(connection_string, db_name)
if not success:
return success, log
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
if sensor_type == CONST_ADVANTICSYS:
# load advanticsys sensor data to db
success, log = insert_advanticsys_data(session, pd_df)
if not success:
return success, log
#TODO: add the other types
else:
return False, "Sensor type des not exist"
session_close(session)
return True, log
def log_upload_event(sensor_type, filename, status, log, connection_string):
"""
Function will log the upload event in the database by capturing information
suchas sensor_type, time(now), filename, status, log message.
- sensor_type: the type of sensor(s) for which the data is being uploaded
- filename: the name of the file uploaded
- status: boolean
- log: log message from the upload routine
- connection_string: connecetion string to the database
"""
# Try to connect to a database that exists
success, error, engine = connect_db(connection_string, SQL_DBNAME)
if not success:
return success, error
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
type_id, error = find_sensor_type_id(session, sensor_type)
success = type_id > -1
if not success:
return success, error
if status:
status_msg = "OK"
else:
status_msg = "FAILED"
event_log = DataUploadLogClass(type_id, filename, status_msg, log)
session.add(event_log)
session_close(session)
return success, error
def insert_adv_data(engine):
"""
Bulk inserts test advanticsys data
Arguments:
engine: SQL engine object
"""
file_path = os.path.join(CONST_ADVANTICSYS_DIR, CONST_ADVANTICSYS_TEST_1)
success, log, test_ingress_df = advanticsys_import(file_path)
assert success, log
assert isinstance(test_ingress_df, pd.DataFrame)
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
# tests loading sensor data to db
success, log = insert_advanticsys_data(session, test_ingress_df)
session_close(session)
assert success, log
def test_insert_advanticsys_data():
"""
Bulk inserts test advanticsys data
Arguments:
engine: SQL engine object
"""
file_path = os.path.join(CONST_ADVANTICSYS_DIR, CONST_ADVANTICSYS_TEST_1)
success, log, test_ingress_df = advanticsys_import(file_path)
assert success, log
assert isinstance(test_ingress_df, pd.DataFrame)
# Try to connect to an engine that exists
status, log, engine = connect_db(SQL_CONNECTION_STRING, SQL_TEST_DBNAME)
assert status, log
# trying to import the same data twice
session = session_open(engine)
success, log = insert_advanticsys_data(session, test_ingress_df)
session_close(session)
# This should pass and reeport 75 duplicatee values
assert success is True, log
assert log == "New: 0 (uploaded); Duplicates: 75 (ignored)"
file_path = os.path.join(CONST_ADVANTICSYS_DIR, CONST_ADVANTICSYS_TEST_10)
success, log, test_ingress_df = advanticsys_import(file_path)
assert success, log
assert isinstance(test_ingress_df, pd.DataFrame)
session = session_open(engine)
success, log = insert_advanticsys_data(session, test_ingress_df)
session_close(session)
assert success is False, log
def import_sensor_location(engine):
"""
Bulk inserts sensor location data
Arguments:
engine: SQL engine object
"""
test_csv = "sensor_location.csv"
sensor_df = pd.read_csv(os.path.join(CONST_COREDATA_DIR, test_csv))
assert not sensor_df.empty
# Creates/Opens a new connection to the db and binds the engine
session = session_open(engine)
# Check if table is empty and bulk inserts if it is
first_entry = session.query(SensorLocationClass).first()
if first_entry is None:
session.bulk_insert_mappings(SensorLocationClass,
sensor_df.to_dict(orient="records"))
session_close(session)
assert session.query(SensorLocationClass).count() == len(
sensor_df.index)
else:
session_close(session)
assert session.query(SensorLocationClass).count() == len(
sensor_df.index)
# Trying to upload location history data for a sensor that does not exist
test_csv = "sensor_location_test_1.csv"
sensor_df = pd.read_csv(
os.path.join(CONST_TEST_DIR_DATA, CONST_SENSOR_LOCATION_TESTS,
test_csv))
assert not sensor_df.empty
session = session_open(engine)
try:
session.bulk_insert_mappings(SensorLocationClass,
sensor_df.to_dict(orient="records"))
result = True
except:
session.rollback()
result = False
session_close(session)
assert not result
# Trying to upload location history data for a location that does not exist
test_csv = "sensor_location_test_2.csv"
sensor_df = pd.read_csv(
os.path.join(CONST_TEST_DIR_DATA, CONST_SENSOR_LOCATION_TESTS,
test_csv))
assert not sensor_df.empty
session = session_open(engine)
try:
session.bulk_insert_mappings(SensorLocationClass,
sensor_df.to_dict(orient="records"))
result = True
except:
session.rollback()
result = False
session_close(session)
assert not result
# Trying to upload location history data with an empty installation date
test_csv = "sensor_location_test_3.csv"
sensor_df = pd.read_csv(
os.path.join(CONST_TEST_DIR_DATA, CONST_SENSOR_LOCATION_TESTS,
test_csv))
assert not sensor_df.empty
session = session_open(engine)
try:
session.bulk_insert_mappings(SensorLocationClass,
sensor_df.to_dict(orient="records"))
result = True
except:
session.rollback()
result = False
session_close(session)
assert not result
def import_energy_data(electricity_df, conn_string, database):
"""
Uploads electricity data to the CROP database.
Arguments:
electricity_df: pandas dataframe containing electricity data
conn_string: connection string
database: the name of the database
"""
stark_type_id = -1
success, log, engine = connect_db(conn_string, database)
if not success:
return success, log
# Check if the stark sensor type is in the database
try:
session = session_open(engine)
stark_type_id = (
session.query(TypeClass)
.filter(TypeClass.sensor_type == CONST_STARK)
.first()
.id
)
session_close(session)
except Exception:
status = False
log = "Sensor type {} was not found.".format(CONST_STARK)
return log_upload_event(CONST_STARK, "stark.co.uk", status, log, conn_string)
# Check if data sources are in the database
data_sources = electricity_df["data_source"].unique()
data_sources_dict = {}
for data_source in data_sources:
stark_sensor_id = -1
try:
stark_sensor_id = (
session.query(SensorClass)
.filter(SensorClass.device_id == str(data_source))
.filter(SensorClass.type_id == stark_type_id)
.first()
.id
)
except Exception:
status = False
log = "{} sensor with {} = '{}' was not found.".format(
CONST_STARK, "name", str(data_source)
)
return log_upload_event(
CONST_STARK, "stark.co.uk", status, log, conn_string
)
data_sources_dict[data_source] = stark_sensor_id
# Uploading electricity readings data
add_cnt = 0
dulp_cnt = 0
try:
session = session_open(engine)
for _, row in electricity_df.iterrows():
sensor_id = data_sources_dict[row["data_source"]]
timestamp = row["timestamp"]
electricity = row["electricity"]
try:
query_result = (
session.query(ReadingsEnergyClass)
.filter(ReadingsEnergyClass.sensor_id == sensor_id)
.filter(ReadingsEnergyClass.timestamp == timestamp)
.first()
)
if query_result is not None:
found = True
dulp_cnt += 1
else:
found = False
except Exception:
found = False
if not found:
data = ReadingsEnergyClass(
sensor_id=sensor_id,
timestamp=timestamp,
electricity_consumption=electricity,
)
session.add(data)
add_cnt += 1
session.query(SensorClass).filter(SensorClass.id == sensor_id).update(
{"last_updated": datetime.utcnow()}
)
session_close(session)
status = True
log = "New: {} (uploaded); Duplicates: {} (ignored)".format(add_cnt, dulp_cnt)
return log_upload_event(CONST_STARK, "stark.co.uk", status, log, conn_string)
except Exception:
session_close(session)
status = False
log = "Cannot insert new data to database"
return log_upload_event(CONST_STARK, "stark.co.uk", status, log, conn_string)
def import_zensie_trh_data(conn_string, database, dt_from, dt_to):
"""
Uploads zensie temperature and relative humidity data to the CROP database.
Arguments:
conn_string: connection string
database: the name of the database
dt_from: date range from
dt_to: date range to
Returns:
status, error
"""
log = ""
sensor_type = CONST_ZENSIE_TRH_SENSOR_TYPE
success, log, engine = connect_db(conn_string, database)
if not success:
logging.info(log)
return success, log
# get the list of zensie trh sensors
try:
session = session_open(engine)
zensie_sensor_list = get_zensie_sensors_list(session, sensor_type)
session_close(session)
if zensie_sensor_list is None or len(zensie_sensor_list) == 0:
success = False
log = "No sensors with sensor type {} were found.".format(
sensor_type)
except:
session_close(session)
success = False
log = "No sensors with sensor type {} were found.".format(sensor_type)
if not success:
logging.info(log)
return log_upload_event(CONST_ZENSIE_TRH_SENSOR_TYPE, "Zensie API",
success, log, conn_string)
for _, zensie_sensor in enumerate(zensie_sensor_list):
sensor_id = zensie_sensor["sensors_id"]
sensor_check_id = zensie_sensor["sensors_device_id"]
logging.info("sensor_id: {} | sensor_check_id: {}".format(
sensor_id, sensor_check_id))
if sensor_id > 0 and len(sensor_check_id) > 0:
logging.info("sensor_id: {} | dt_from: {}, dt_to: {}".format(
sensor_id, dt_from, dt_to))
# Sensor data from Zensie
sensor_success, sensor_error, api_data_df = get_api_sensor_data(
CONST_CROP_30MHZ_APIKEY, sensor_check_id, dt_from, dt_to)
logging.info(
"sensor_id: {} | sensor_success: {}, sensor_error: {}".format(
sensor_id, sensor_success, sensor_error))
if sensor_success:
# Sensor data from database
session = session_open(engine)
db_data_df = get_zensie_trh_sensor_data(
session,
sensor_id,
dt_from + timedelta(hours=-1),
dt_to + timedelta(hours=1),
)
session_close(session)
if len(db_data_df) > 0:
# Filtering only new data
new_data_df = api_data_df[~api_data_df.index.
isin(db_data_df.index)]
logging.info("sensor_id: {} | len(db_data_df): {}".format(
sensor_id, len(db_data_df)))
else:
new_data_df = api_data_df
logging.info("sensor_id: {} | len(new_data_df): {}".format(
sensor_id, len(new_data_df)))
if len(new_data_df) > 0:
start_time = time.time()
session = session_open(engine)
for idx, row in new_data_df.iterrows():
data = ReadingsZensieTRHClass(
sensor_id=sensor_id,
timestamp=idx,
temperature=row["Temperature"],
humidity=row["Humidity"],
)
session.add(data)
session.query(SensorClass).\
filter(SensorClass.id == sensor_id).\
update({"last_updated": datetime.now()})
session_close(session)
elapsed_time = time.time() - start_time
logging.debug(
"sensor_id: {} | elapsed time importing data: {} s.".
format(sensor_id, elapsed_time))
upload_log = "New: {} (uploaded);".format(
len(new_data_df.index))
log_upload_event(
CONST_ZENSIE_TRH_SENSOR_TYPE,
"Zensie API; Sensor ID {}".format(sensor_id),
sensor_success,
upload_log,
conn_string,
)
else:
log_upload_event(
CONST_ZENSIE_TRH_SENSOR_TYPE,
"Zensie API; Sensor ID {}".format(sensor_id),
sensor_success,
sensor_error,
conn_string,
)
return True, None
def issue_warnings():
None
def upload_warnings(session, warning):
start_time = time.time()
session = session_open(engine)
for idx, row in warning.iterrows():
data = DataWarningsClass(
type_id=type_id,
timestamp=idx,
priority=prior,
log=warning_log,
# temperature=row["Temperature"],
# humidity=row["Humidity"],
)
session.add(data)
if __name__ == "__main__":
session = session_open(engine)
check_issues_in_farm(session)
session_close(session)
def upload_openweathermap_data(conn_string: str, database: str,
dt_from: datetime, dt_to: datetime):
"""
Uploads openweathermap data to the CROP database.
Arguments:
conn_string: connection string
database: the name of the database
dt_from: date range from
dt_to: date range to
Returns:
status, error
"""
# connect to the DB to get weather data already there, so we don't duplicate
success, log, engine = connect_db(conn_string, database)
if not success:
logging.error(log)
return success, log
session = session_open(engine)
df_db = get_db_weather_data(session, dt_from, dt_to)
# now get the Openweathermap API data
success, error, df_api = get_openweathermap_data(dt_from, dt_to)
# filter out the rows that are already in the db data
new_data_df = df_api[~df_api.index.isin(df_db.index)]
logging.info("new data with size len(new_data_df): {}\n\n".format(
len(new_data_df)))
if len(new_data_df) > 0:
# this is the current time in seconds since epoch
start_time: float = time.time()
session = session_open(engine)
for idx, row in new_data_df.iterrows():
data = ReadingsWeatherClass(
sensor_id=0,
timestamp=idx,
temperature=row["temperature"],
rain_probability=None, # not in openweathermap data
rain=row["rain"],
relative_humidity=row["relative_humidity"],
wind_speed=row["wind_speed"],
wind_direction=row["wind_direction"],
air_pressure=row["air_pressure"],
radiation=None, # not in openweathermap data
icon=row["icon"],
source=row["source"],
)
session.add(data)
session_close(session)
elapsed_time = time.time() - start_time
logging.debug(
"openweathermap | elapsed time importing data: {} s.".format(
elapsed_time))
upload_log = "New: {} (uploaded);".format(len(new_data_df.index))
log_upload_event(
CONST_API_WEATHER_TYPE,
"Openweathermap API",
success,
upload_log,
conn_string,
)
else:
log_upload_event(
CONST_API_WEATHER_TYPE,
"Openweathermap API",
success,
error,
conn_string,
)
return True, None
