def test_connect_db(): # Try to connect to an engine that exists status, log, engine = connect_db(SQL_CONNECTION_STRING, TEST_DB_NAME) assert status, log # Try to connect to a db that does not exists: fake_db = "db_name_fake" status, log, engine = connect_db(SQL_CONNECTION_STRING, fake_db) assert status is False, log assert engine is None
def main(db_name): """ Main routine. Arguments: db_name: Database name """ created, log = create_database(SQL_CONNECTION_STRING, db_name) if not created: error_message(log) # creating an engine status, log, engine = connect_db(SQL_CONNECTION_STRING, db_name) if not status: error_message(log) insert_type_data(engine) insert_location_data(engine) insert_sensor_data(engine) insert_adv_data(engine) import_sensor_location(engine)
def test_check_database_structure(): status, log, engine = connect_db(SQL_CONNECTION_STRING, TEST_DB_NAME) assert status, log assert engine is not None good, log = check_database_structure(engine) assert good, log
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 main(db_name): """ Main routine. Arguments: db_name: Database name """ # creating an engine status, log, engine = connect_db(SQL_CONNECTION_STRING, db_name) if not status: error_message(log) insert_user_data(engine)
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 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_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)
from datetime import datetime, timedelta import pandas as pd import matplotlib.pyplot as plt from __app__.crop.constants import ( SQL_CONNECTION_STRING, SQL_DBNAME, ) from __app__.crop.structure import ( ReadingsZensieTRHClass, ReadingsAranetTRHClass, ) from __app__.crop.db import connect_db, session_open, session_close conn_string = SQL_CONNECTION_STRING database = SQL_DBNAME success, log, engine = connect_db(conn_string, database) from sqlalchemy import and_ session = session_open(engine) date_to = datetime.utcnow() date_from = date_to + timedelta(days=-7) def get_results_df(readings_class, date_from, date_to): """ Query the database using the specified SQLAlchemy class Parameters ---------- readings_class: SQLAlchemy class, as defined in structure.py
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
CONST_ADVANTICSYS, SQL_ENGINE, SQL_DBNAME, SQL_HOST, SQL_CONNECTION_STRING_CROP, ) from __app__.crop.db import connect_db, session_open, session_close db_name = "app_db" # TODO The below connection string needs secrets. Read them from environment variables. # The current one is a placeholder. CONNECTION_STRING = "postgresql://username@hostname:password@serverurl:port" # Try to connect to a database that exists success, log, engine = connect_db(CONNECTION_STRING, SQL_DBNAME) def db_query_tmpr_day_zenzie(session, location_zone, date_range): """ Function to query temperature readings from the Crop dabase's zenzie sensors located in the propagation area of the farm location_zone (str): the zone of the farm to query """ query = session.query( ReadingsZensieTRHClass.temperature, ReadingsZensieTRHClass.humidity, ReadingsZensieTRHClass.sensor_id, ).filter( and_( LocationClass.zone == location_zone,
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