class InfluxDataFrameReader:
def __init__(self,
host='127.0.0.1',
port=8086,
user=None,
password=None,
dbname='db0',
interval='10m'):
self.host = host
self.port = port
self.dbname = dbname
self.user = user
self.password = password
self.ssl = False
self.verify_ssl = False
self.interval = interval
# self.ssl = True if self.host != '127.0.0.1' else False
# self.verify_ssl = True if self.host != '127.0.0.1' else False
self.client = DataFrameClient(host=self.host,
port=self.port,
database=self.dbname,
username=self.user,
password=self.password,
ssl=self.ssl,
verify_ssl=self.verify_ssl)
try:
self._ver = self.client.ping()
logger.info('Connected to DB {} version = {}'.format(
self.dbname, self._ver))
except Exception as error:
self._ver = False
logger.error('Connection error: {}'.format(error))
def time_query(self, measurement='outdoor_weather', time_shift='1h'):
if not self._ver:
result = None
else:
query = "SELECT * FROM {} WHERE time > now() - {} AND time < now()".format(
measurement, time_shift)
try:
result = self.client.query(
query, database=self.dbname).get(measurement)
except influxdb.client.InfluxDBClientError as error:
logger.error(
'Error while trying to query DB: {}'.format(error))
result = None
return result
def read_results(self):
_df_pv = self.time_query('pv_measurement', self.interval)
_df_hp = self.time_query('hp_measurement', self.interval)
df_pv = _df_pv.loc[:, ['power']]
df_pv.columns = ['pv_power']
df_hp = _df_hp.loc[:, [
'0_set_temp', '1_sens_on', '2_sens_off', '3_hp_on_off',
'4_hysteresis_on', '5_hysteresis_off'
]]
return df_pv, df_hp
class InfluxServerIO:
logger = logging.getLogger('influx_server_io')
def __init__(self,
host=None,
database=None,
port=None,
username=None,
password=None):
"""
Constructor.
"""
self.logger.debug("Setting connection parameters")
self.host = host
self.database = database
self.port = port
self.username = username
self.password = password
self.client = None
def connect(self):
"""
Open connection.
"""
try:
self.logger.debug("Connecting to DB")
self.client = DataFrameClient(self.host,
self.port,
self.username,
self.password,
self.database,
timeout=15)
self.client.ping()
self.logger.debug("DB Connection set")
except Exception as err:
self.logger.error("DB Connection failed: " + str(err))
raise Exception("DB Connection failed: " + str(err))
def disconnect(self):
"""
Close connection.
"""
try:
self.logger.debug("Closing DB connection")
if self.client is not None:
self.client.close()
self.client = None
self.logger.debug("DB Connection closed")
except Exception as err:
self.logger.error("Can't disconnect from DB: " + str(err))
raise Exception("Can't disconnect from DB: " + str(err))
def read_data(self,
device_id=None,
data_source_id=None,
time_upload=None,
limit=None):
"""
Read data from db according to object's parameters.
:param device_id: list of ids [uuid1, uuid2, ..., uuidN]
:param data_source_id: list of ids [id1, id2, ..., idN]
:param time_upload: list of tuples of dates [(d_min1 d_max1), (d_min2 d_max2), ..., (d_minN d_maxN)]
:param limit: retrieved data rows limit
:return: list of queries results
"""
results = pd.DataFrame()
try:
self.logger.debug("Reading data")
if limit is not None:
self.logger.debug("Data reading limit set to " + str(limit))
limit = "LIMIT " + str(limit)
else:
limit = ""
for di, dsi, tu in zip(device_id, data_source_id, time_upload):
params = {
"di": str(di),
"dsi": str(dsi),
"limit": limit,
"from": datetime.datetime.strftime(tu[0],
"%Y-%m-%dT%H:%M:%SZ"),
"to": datetime.datetime.strftime(tu[1],
"%Y-%m-%dT%H:%M:%SZ")
}
query = r"SELECT value FROM data WHERE device_id='{di}' ".format(
**params)
query += r"and data_source_id='{dsi}' ".format(**params)
query += r"and time >= '{from}' and time <= '{to}' ".format(
**params)
query += r"{limit}".format(**params)
self.logger.debug("Executing query " + str(query))
result = self.client.query(query)
name = str(di) + '_' + str(dsi)
if len(result) != 0:
r = result['data']
self.logger.debug("Column " + name + " contains " +
str(len(r)) + " rows")
r.rename(columns={"value": name}, inplace=True)
results = pd.merge(results,
r,
how='outer',
left_index=True,
right_index=True)
else:
self.logger.debug("Column " + name + " contains " +
str(0) + " rows")
results[name] = np.nan
except Exception as err:
self.logger.error("Impossible to read: " + str(err))
raise Exception("Impossible to read: " + str(err))
self.logger.debug("Reading complete: " + str(results.shape) +
" entries returned")
return results
def write_data(self, result_id=None, output_data=None):
"""
Write data from this object to db.
:param result_id: list of ids [uuid1, uuid2, ..., uuidK]
:param output_data: DataFrame
:return: list of result objects
"""
self.logger.debug("Writing data")
results = []
try:
for col, ri in zip(output_data.columns, result_id):
df = pd.DataFrame(output_data[col])
if col.startswith('bool'):
df.rename(columns={col: 'boolean'}, inplace=True)
elif col.startswith('val'):
df.rename(columns={col: 'value'}, inplace=True)
else:
self.logger.warning(
"Column name: " + str(col) +
" (doesnt's start with 'val' or 'bool', renaming to 'val')"
)
df.rename(columns={col: 'value'}, inplace=True)
v = self.client.write_points(df, 'data_result',
{'result_id': str(ri)})
results.append(str(ri))
except Exception as err:
self.logger.error("Writing to DB failed: " + str(err))
raise Exception("Writing to DB failed " + str(err))
self.logger.debug("Writing to DB complete " + str(results))
return results
class InfluxConnection():
"""Influx DB API assistant Class."""
def __init__(self,
db_host=DB_HOST,
db_port=DB_PORT,
db_username=DB_USERNAME,
db_password=DB_PASSWORD,
database=DB_NAME):
self.host = db_host
self.port = db_port
self.username = db_username
self.password = db_password
self.database = database
self.db_client = InfluxDBClient(host=self.host,
port=self.port,
username=self.username,
password=self.password,
database=self.database)
self.wait_for_influxdb()
self.df_client = DataFrameClient(host=self.host,
port=self.port,
username=self.username,
password=self.password,
database=self.database)
self.wait_for_dataFrameClient()
def wait_for_influxdb(self):
"""Function to wait for the influxdb service to be available."""
try:
self.db_client.ping()
print(f"InfluxDBClient Connected | {datetime.now()}")
return None
except ConnectionError:
print("InfluxDBClient Connection FAILED: Trying again (1s)")
time.sleep(1)
self.wait_for_influxdb()
def wait_for_dataFrameClient(self):
"""Function to wait for the influxdb service to be available."""
try:
self.df_client.ping()
print(f"DataFrameClient Connected | {datetime.now()} ")
return None
except ConnectionError:
print("DataFrameClient Connection FAILED: Trying again (1s)")
time.sleep(1)
self.wait_for_dataFrameClient()
def get_n_recent_readings(self,
number_of_readings,
table_name=DB_CONNECTOR_TABLE):
query_to_execute = (
'select * from "{}" group by * order by DESC limit {}').format(
table_name, number_of_readings)
return self.db_client.query(query_to_execute).raw['series']
def get_n_recent_readings_with_condition(self,
number_of_readings,
adc,
channel,
table_name=DB_CONNECTOR_TABLE):
query_to_execute = (
'select * from "{}" where adc=\'{}\' and channel=\'{}\' group by *'
' order by DESC limit {}').format(table_name, adc, channel,
number_of_readings)
return self.db_client.query(query_to_execute).raw['series']
def push_to_database(self, json_data, table_name="converted_measurements"):
pass
def print_data(in_q, break_q):
# Setup Django environment
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
env = environ.Env()
env_path = os.path.join(BASE_DIR, '.env')
environ.Env.read_env('.env')
# Create InfluxDb client
DB_HOST = env('DB_HOST')
DB_USER = env('DB_USER')
DB_PASSWORD = env('DB_PASSWORD')
print("Connecting to InfluxDB...")
while True:
if break_q.qsize() > 0:
break_q.put(1)
break
try:
client = DataFrameClient(host=DB_HOST,
port='8086',
username=DB_USER,
password=DB_PASSWORD,
database='mmbox')
ping = client.ping()
if ping:
print("YES!")
break
except:
continue
print("Connected to InfluxDB v. " + ping)
# listening for data to send
while True:
# check if keyboard interrupt has been detected in the main thread
if break_q.qsize() > 0:
break_q.put(1)
break
time.sleep(5) # The delay in seconds between writes to the DB
poses = in_q.get(
) # pop the data that's been put on "out_q" in the record_data function
if len(
poses
) > 0: # Check if people are detected (and if the pose detection has started)
timestamp = time.time()
dtidx = pd.DatetimeIndex(
data=[pd.to_datetime(timestamp, unit='s', origin='unix')],
name='Time')
df = pd.DataFrame(data=poses, index=dtidx, columns=poses)
# Alternative way to get unix timestamp
#times = [time.time()]
#df = pd.DataFrame(data=output, index=pd.to_datetime(times, unit='s', origin='unix'), columns=output)
try:
client.write_points(
df, 'mmbox_video_pose',
batch_size=1000) # send dataframe to InfluxDB
except:
print("Error writing to InfluxDB")
print(df)
print("Printer stopped")
class DataCollector:
def __init__(self, host, port, user, password, database):
self.host = host
self.port = port
self.user = user
self.password = password
self.database = database
try:
self.client = DataFrameClient(host, port, user, password, database, timeout=30)
self.client.ping()
self.experimentIds = self.cache_experimentIds()
except requests.exceptions.ConnectTimeout as e: # Timeout of InfluxDB connection
print(e)
self.client = None
def get_data(self, experimentId, measurements=[], fields=[], additional_clause=None, chunked=False, chunk_size=10000, limit=None, offset=None, max_lag="1s"):
if not measurements:
results = self.client.query(f"SHOW measurements WHERE (ExperimentId =~ /{experimentId}/ or ExecutionId =~ /{experimentId}/)")
measurements = [item["name"] for item in results["measurements"]]
measurements = ", ".join([f'"{item}"' for item in measurements])
fields = ', '.join([f'"{item}"' for item in fields]) if fields else '*'
limit = f' LIMIT {limit}' if limit else ''
offset = f' OFFSET {offset}' if offset else ''
if not additional_clause:
additional_clause = ''
df = self.query_df(f'SELECT {fields} FROM {measurements} WHERE (ExperimentId =~ /{experimentId}/ or ExecutionId =~ /{experimentId}/){additional_clause}{limit}{offset}')
df = df.set_index('time')
df.index = pd.to_datetime(df.index).floor(max_lag)
df = df.mean(level=0) # .dropna(axis=0)
return df
def get_experimentIds_for_measurement(self, measurement):
result = self.client.query(f'SELECT distinct(ExecutionId) as ExecutionId from (SELECT * from "{measurement}")', chunked=False, chunk_size=1000, epoch='ns')
return list(result[measurement].iloc[:, 0])
def get_measurements_for_experimentId(self, experimentId):
result = self.client.query(f'SHOW measurements WHERE ExecutionId =~ /{experimentId}/ or ExperimentId =~ /{experimentId}/', chunked=False, chunk_size=1000, epoch='ns')
return [item["name"] for item in list(result['measurements'])]
def cache_experimentIds(self):
experimentIds = []
measurements = [measurement['name'] for measurement in self.client.get_list_measurements()]
for measurement in tqdm(measurements, desc="Getting ExecutionIds"):
results = self.query_df(f'''SELECT distinct(ExecutionId) as ExecutionId from (SELECT * from "{measurement}")''')
if not results.empty:
experimentIds += list(results['ExecutionId'].astype(str))
return sorted(list(set(experimentIds)))
def query_df(self, query):
data = {}
data['db'] = self.database
data['u'] = self.user
data['p'] = self.password
data['precision'] = 'ns'
data['q'] = query
url_values = urllib.parse.urlencode(data)
url = f"http://{self.host}:{self.port}/query?" + url_values
request = urllib.request.Request(url, headers={'Accept': 'application/csv'})
response = urllib.request.urlopen(request)
response_bytestr = response.read()
if response_bytestr:
return pd.read_csv(BytesIO(response_bytestr), sep=",", low_memory=False)
else:
return pd.DataFrame()
def get_all_experimentIds(self):
return self.experimentIds
