def advanticsys_import(blobin: func.InputStream):
"""
The main advanticsys Azure Function routine.
"""
logging.info(f"Starting advanticsys sensor data import process:\n"
f"Name: {blobin.name}\n"
f"Blob Size: {blobin.length} bytes")
# reading in data as pandas dataframe
data_str = str(blobin.read(), "utf-8")
data_stream = StringIO(data_str)
data_df = pd.read_csv(data_stream)
# getting the environmental parameters
user = "******".format(os.environ["CROP_SQL_USER"].strip())
password = "******".format(os.environ["CROP_SQL_PASS"].strip())
host = "{}".format(os.environ["CROP_SQL_HOST"].strip())
port = "{}".format(os.environ["CROP_SQL_PORT"].strip())
database = "{}".format(os.environ["CROP_SQL_DBNAME"].strip())
# uploading data to the database
status, log = import_data(data_df, CONST_ADVANTICSYS, user, password, host,
port, database)
# Logging the advanticsys sensor data upload event
conn_string = make_conn_string(SQL_ENGINE, user, password, host, port)
log_status, log_err = log_upload_event(CONST_ADVANTICSYS, blobin.name,
status, log, conn_string)
if status:
logging.info(
f"SUCCESS: advanticsys sensor data import process finished:\n"
f"Name: {blobin.name}\n"
f"Blob Size: {blobin.length} bytes\n"
f"Info: {log}\n"
f"Log: {log_status} {log_err}")
else:
logging.info(f"ERROR: advanticsys sensor data import process failed:\n"
f"Name: {blobin.name}\n"
f"Blob Size: {blobin.length} bytes\n"
f"Info: {log}\n"
f"Log: {log_status} {log_err}")
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
# Create connection string
SQL_ENGINE = "postgresql"
SQL_USER = os.environ["CROP_SQL_USER"]
SQL_PASSWORD = os.environ["CROP_SQL_PASS"]
SQL_HOST = os.environ["CROP_SQL_HOST"]
SQL_PORT = os.environ["CROP_SQL_PORT"]
SQL_DBNAME = os.environ["CROP_SQL_DBNAME"].strip().lower()
SQL_DEFAULT_DBNAME = "postgres"
SQL_SSLMODE = "require"
SQL_TEST_DBNAME = "test_db"
SQL_CONNECTION_STRING = make_conn_string(
SQL_ENGINE,
SQL_USER,
SQL_PASSWORD,
SQL_HOST,
SQL_PORT,
)
SQL_CONNECTION_STRING = "%s://%s:%s@%s:%s" % (
SQL_ENGINE,
SQL_USER,
SQL_PASSWORD,
SQL_HOST,
SQL_PORT,
)
SQL_CONNECTION_STRING_DEFAULT = "%s/%s" % (SQL_CONNECTION_STRING, SQL_DEFAULT_DBNAME)
SQL_CONNECTION_STRING_CROP = "%s/%s" % (SQL_CONNECTION_STRING, SQL_DBNAME)
os.environ["CROP_SQL_PORT"].strip() if "CROP_SQL_PORT" in os.environ else "DUMMY"
)
SQL_DBNAME = (
os.environ["CROP_SQL_DBNAME"].strip().lower()
if "CROP_SQL_DBNAME" in os.environ
else "DUMMY"
)
SQL_DEFAULT_DBNAME = "postgres"
SQL_SSLMODE = "require"
SQL_TEST_DBNAME = "test_db"
SQL_CONNECTION_STRING = make_conn_string(
SQL_ENGINE,
SQL_USER,
parse.quote(SQL_PASSWORD),
SQL_HOST,
SQL_PORT,
)
SQL_CONNECTION_STRING = "%s://%s:%s@%s:%s" % (
SQL_ENGINE,
SQL_USER,
parse.quote(SQL_PASSWORD),
SQL_HOST,
SQL_PORT,
)
SQL_CONNECTION_STRING_DEFAULT = "%s/%s" % (SQL_CONNECTION_STRING, SQL_DEFAULT_DBNAME)
SQL_CONNECTION_STRING_CROP = "%s/%s" % (SQL_CONNECTION_STRING, SQL_DBNAME)