def _check_influxdb_connected(self, host, port, username, password, db_name):
client = DataFrameClient(host, port, username, password, db_name)
result = True
try:
client.get_list_database()
print "Connect to database server"
except:
result = False
print "Cannot connect. Please check configuration server"
return result
def check_influxdb_connected(self, host, port, username, password,
db_name):
client = DataFrameClient(host, port, username, password, db_name)
result = True
try:
client.get_list_database()
print "Connect to database server"
except:
result = False
print "Cannot connect. Please check configuration server"
return result
class CInflux:
def __init__(self, dbinfo, dbname, iredis=create_redis_obj()):
self.redis = iredis
self.dbname = dbname
self.df_client = DataFrameClient(dbinfo['host'],
dbinfo['port'],
dbinfo['user'],
dbinfo['password'],
self.dbname,
timeout=10)
def __del__(self):
self.redis = None
self.df_client = None
def get_all_databases(self):
if self.redis.exists(ALL_IN_DATABASES):
return set(
str(dbname, encoding="utf8")
for dbname in self.redis.smembers(ALL_IN_DATABASES))
else:
all_dbs = self._get_all_databses()
for _db in all_dbs:
self.redis.sadd(ALL_IN_DATABASES, _db)
return all_dbs
def _get_all_databses(self):
return [x['name'] for x in self.df_client.get_list_database()]
def get(self, dbname=None):
if dbname is None: dbname = self.dbname
return self.df_client.query("select * from %s" % dbname)
def get_newset_row(self, dbname=None):
if dbname is None: dbname = self.dbname
return self.df_client.query("select last(*) from %s" % dbname)
def set(self, df, dbname=None):
dbname = dbname if dbname is not None else self.dbname
try:
self.df_client.write_points(df, dbname, protocol='json')
return True
except InfluxDBServerError as e:
logger.error(e)
return False
def create(self, dbname=None):
if dbname is None: dbname = self.dbname
if dbname in self.get_all_databases(): return True
self.df_client.create_database(dbname)
self.redis.sadd(ALL_IN_DATABASES, dbname)
return True
def delete(self, dbname=None):
if dbname is None: dbname = self.dbname
if dbname not in self.get_all_databases(): return True
self.df_client.drop_database(dbname)
self.redis.srem(ALL_IN_DATABASES, dbname)
return True
def main(pair=None, logic=None):
df = DataFrameClient(host='localhost', port=8086)
database = df.get_list_database()
lis = [i for x in database for i in x.values() if i in MARKET_PAIRS]
if logic:
for x in lis:
df.drop_database(x)
return 'Finished'
elif pair in lis:
df.drop_database(pair)
return
def load():
df = pd.read_csv('GHI_DHI_Temp_Wind_20130101_english_units.csv',
skiprows=1)
df.index = pd.to_datetime(df['DATE (MM/DD/YYYY)'] + ' ' + df['MST'],
format='%m/%d/%Y %H:%M')
df.columns = [
u'DATE (MM/DD/YYYY)', u'MST',
u'AtmosphericAnalogKind_irradanceGlobalHorizontal',
u'AtmosphericAnalogKind_irradanceDirectNormal',
u'AtmosphericAnalogKind_irradanceDiffuseHorizontal',
u'AtmosphericAnalogKind_ambientTemperature',
u'AtmosphericAnalogKind_humidity', u'AtmosphericAnalogKind_speed',
u'AtmosphericAnalogKind_bearing'
]
dbname = 'proven'
protocol = 'json'
client = DataFrameClient(host='localhost', port=8086)
# print("Delete database: " + dbname)
# client.drop_database(dbname)
print("Create pandas DataFrame")
print("Create database: " + dbname)
# client.drop_database(dbname)
client.create_database(dbname)
dbs = client.get_list_database()
print(dbs)
client.switch_database(dbname)
# print("Write DataFrame")
client.write_points(df.loc['2013-7-1':'2013-7-31'],
'weather',
protocol=protocol)
client.write_points(df.loc['2013-8-1':'2013-8-31'],
'weather',
protocol=protocol)
client.write_points(df.loc['2013-9-1':'2013-9-30'],
'weather',
protocol=protocol)
print("Write DataFrame with Tags")
# client.write_points(df, 'demo',
# {'k1': 'v1', 'k2': 'v2'}, protocol=protocol)
print("Read DataFrame")
def test_get_list_database(self):
data = {'results': [
{'series': [
{'measurement': 'databases',
'values': [
['new_db_1'],
['new_db_2']],
'columns': ['name']}]}
]}
cli = DataFrameClient('host', 8086, 'username', 'password', 'db')
with _mocked_session(cli, 'get', 200, json.dumps(data)):
self.assertListEqual(
cli.get_list_database(),
[{'name': 'new_db_1'}, {'name': 'new_db_2'}]
)
def test_get_list_database(self):
data = {'results': [
{'series': [
{'measurement': 'databases',
'values': [
['new_db_1'],
['new_db_2']],
'columns': ['name']}]}
]}
cli = DataFrameClient('host', 8086, 'username', 'password', 'db')
with _mocked_session(cli, 'get', 200, json.dumps(data)):
self.assertListEqual(
cli.get_list_database(),
[{'name': 'new_db_1'}, {'name': 'new_db_2'}]
)
class DbClient:
def __init__(self, database=None, host=None, port=None):
if database is None:
self.database = configParser['database']['name']
else:
self.database = database
if host is None:
self.host = configParser.get('database', 'host')
else:
self.host = host
if port is None:
self.port = configParser.get('database', 'port')
else:
self.port = database
self.client = DataFrameClient(host=self.host, port=self.port, database=self.database)
def save_to_db(self, df, measurement, tags=None):
if tags is None:
print("Write DataFrame")
self.client.write_points(df, database=self.database, measurement=measurement, protocol='json')
else:
print("Write DataFrame with Tags")
self.client.write_points(df, database=self.database, measurement=measurement, tags=tags, protocol='json')
def fetch_from_db(self, query):
print("Read DataFrame")
return self.client.query(query)
def create_db(self):
self.client.create_database('crypto_analyzer')
def drop_db(self):
self.client.drop_database(self.database)
def is_existing(self):
result = self.client.get_list_database()
return result is not None or len(result) > 0
section=args.region,
key="influxdb_password")
influxdb_dbname = read_value(parser,
section=args.region,
key="influxdb_dbname")
else:
sys.exit("Invalid region: '%s'" % args.region)
influxdb_client = DataFrameClient(host=influxdb_host,
port=influxdb_port,
username=influxdb_user,
password=influxdb_password,
database=influxdb_dbname)
dbs = influxdb_client.get_list_database()
create_db = True
for db in dbs:
if db['name'] == influxdb_dbname:
create_db = False
break
if create_db:
influxdb_client.create_database(influxdb_dbname)
options = {
"access_log": access_log,
"request_time_threshold": request_time_threshold,
"log_datetime_format": log_datetime_format,
"plot_chart": plot_chart,
"uri_white_list": uri_white_list,
args = parser.parse_args()
client = DataFrameClient(host=args.host,
port=args.port,
username=args.user,
password=args.password,
database=args.database,
pool_size=1)
logging.getLogger(__name__).info("Updating database with arguments: " +
str(args))
if args.drop:
client.drop_database(args.database)
if args.database not in [d['name'] for d in client.get_list_database()]:
client.create_database(args.database)
client.query(
"ALTER RETENTION POLICY autogen ON cache DURATION INF REPLICATION 1 SHARD DURATION 2600w DEFAULT"
)
client.switch_database(args.database)
with IQFeedHistoryProvider(num_connections=args.iqfeed_conn) as history:
all_symbols = {
(s, args.interval_len, args.interval_type)
for s in set(
iqutil.get_symbols(symbols_file=args.symbols_file).keys())
}
update_to_latest(
client=client,
def analyse_db(db_name, timeframe="12h", host="localhost", port=8086):
logging.info("Analysing temperature database: " + db_name)
client = DataFrameClient(host, port)
logging.debug("InfluxDB dataframe client created")
# db_name = "99-TEST-v99"
db_list = client.get_list_database()
check_name = {"name": db_name}
if check_name in db_list:
client.switch_database(db_name)
logging.debug("using database " + db_name)
else:
logging.critical("Can't find database: " + db_name)
exit(-1)
logging.info(f"Analysing last {timeframe}")
query = "select * from temperature where time >= now() - " + timeframe
logging.debug("Running query: " + query)
# run the query and load the result set into a dataframe
rs = client.query(query)
df = pd.DataFrame(rs['temperature'])
# convert time index to NZ timezone
df.index = df.index.tz_convert('Pacific/Auckland')
logging.info("===========================")
logging.info("Ambient temperature data")
logging.info("---------------------------")
logging.debug(
f"Got {df['ambient_temp'].count():d} ambient temp records...")
logging.info(
f"min ambient = {df['ambient_temp'].min():.2f} at {df['ambient_temp'].idxmin():%Y-%m-%d %H:%M}"
)
logging.info(
f"max ambient = {df['ambient_temp'].max():.2f} at {df['ambient_temp'].idxmax():%Y-%m-%d %H:%M}"
)
logging.info(f"average ambient = {df['ambient_temp'].mean():.2f}")
logging.info(f"std dev ambient = {df['ambient_temp'].std():.2f}")
logging.info("===========================")
logging.info("Fermenter temperature data")
logging.info("---------------------------")
logging.debug(
f"Got {df['fermenter_temp'].count():d} fermenter temp records...")
logging.info(
f"min fermenter = {df['fermenter_temp'].min():.2f} at {df['fermenter_temp'].idxmin():%Y-%m-%d %H:%M}"
)
logging.info(
f"max fermenter = {df['fermenter_temp'].max():.2f} at {df['fermenter_temp'].idxmax():%Y-%m-%d %H:%M}"
)
logging.info(f"average fermenter = {df['fermenter_temp'].mean():.2f}")
logging.info(f"std dev fermenter = {df['fermenter_temp'].std():.2f}")
# calculate zscore to identify outliers
temps = df['fermenter_temp'] # this is a Series
zscores = stats.zscore(temps)
abs_zscores = np.abs(zscores)
outliers = (abs_zscores < 3).groupby(
level=0).all() # this gives us a Series with True/False values
# logging.debug(outliers)
new_df = df[
outliers] # don't really understand how this works but it removes the False values (i.e. outliers)
logging.debug(
f"After removing outliers we now have {new_df['fermenter_temp'].count():d} records"
)
logging.info("===========================")
logging.info("Updated fermenter temperature data")
logging.info("---------------------------")
logging.debug(
f"Got {new_df['fermenter_temp'].count():d} fermenter temp records...")
logging.info(
f"min fermenter = {new_df['fermenter_temp'].min():.2f} at {new_df['fermenter_temp'].idxmin():%Y-%m-%d %H:%M}"
)
logging.info(
f"max fermenter = {new_df['fermenter_temp'].max():.2f} at {new_df['fermenter_temp'].idxmax():%Y-%m-%d %H:%M}"
)
logging.info(f"average fermenter = {new_df['fermenter_temp'].mean():.2f}")
logging.info(f"std dev fermenter = {new_df['fermenter_temp'].std():.2f}")
logging.info("===========================")
# Calculate the heat start lag
logging.info("Calculate lag after heating starts")
logging.info("==================================")
# find heat start times
query = "select fermenter_temp, change_action from temperature where change_action='START HEATING' and time >= now() - " + timeframe
logging.debug("Running query: " + query)
rs = client.query(query)
df = pd.DataFrame(rs['temperature'])
df.index = df.index.tz_convert('Pacific/Auckland')
logging.info(f"Found {df['change_action'].count():d} instances")
logging.debug(df)
lag_list = []
for index, row in df.iterrows():
heat_stop_temp = row['fermenter_temp']
logging.debug(f"Heat stop temp = {heat_stop_temp:.2f}")
# find the minimum temp over the next 10 mins after heating starts
time0 = rfc3339.rfc3339(index)
time1 = rfc3339.rfc3339(index + timedelta(minutes=10))
query = "select min(fermenter_temp) from temperature where '" + time0 + "' <= time and time <= '" + time1 + "'"
rs1 = client.query(query)
df1 = pd.DataFrame(rs1['temperature'])
# df1.index = df1.index.tz_convert('Pacific/Auckland')
max_temp_after_heat_stop = df1.iloc[0][
'min'] # get the first & only value in the min column
logging.debug(
f"Min temp after heat start = {max_temp_after_heat_stop:.2f}")
heat_stop_lag = abs(heat_stop_temp - max_temp_after_heat_stop)
logging.info(f"Heat start lag = {heat_stop_lag:.2f}")
lag_list.append(heat_stop_lag)
# logging.debug(lag_list)
lag_mean = mean(lag_list)
logging.info(f"Average heat start lag = {lag_mean:.3f} C")
# Calculate the heat stop lag
logging.info("Calculate lag after heating stops")
logging.info("=================================")
# find heat stop times
query = "select fermenter_temp, change_action from temperature where change_action='STOP HEATING' and time >= now() - " + timeframe
logging.debug("Running query: " + query)
rs = client.query(query)
df = pd.DataFrame(rs['temperature'])
df.index = df.index.tz_convert('Pacific/Auckland')
logging.info(f"Found {df['change_action'].count():d} instances")
logging.debug(df)
lag_list = []
for index, row in df.iterrows():
heat_stop_temp = row['fermenter_temp']
logging.debug(f"Heat stop temp = {heat_stop_temp:.2f}")
# find the minimum temp over the next 10 mins after heating stops
time0 = rfc3339.rfc3339(index)
time1 = rfc3339.rfc3339(index + timedelta(minutes=10))
query = "select max(fermenter_temp) from temperature where '" + time0 + "' <= time and time <= '" + time1 + "'"
rs1 = client.query(query)
df1 = pd.DataFrame(rs1['temperature'])
# df1.index = df1.index.tz_convert('Pacific/Auckland')
max_temp_after_heat_stop = df1.iloc[0][
'max'] # get the first & only value in the min column
logging.debug(
f"Max temp after heat stop = {max_temp_after_heat_stop:.2f}")
heat_stop_lag = abs(heat_stop_temp - max_temp_after_heat_stop)
logging.info(f"Heat stop lag = {heat_stop_lag:.2f}")
lag_list.append(heat_stop_lag)
# logging.debug(lag_list)
lag_mean = mean(lag_list)
logging.info(f"Average heat stop lag = {lag_mean:.3f} C")
def main():
# Initializes the default logger
logging.basicConfig(
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
level=logging.INFO)
logger = logging.getLogger(APPLICATION_NAME)
# Checks the Python Interpeter version
if (sys.version_info < (3, 0)):
logger.error('Python 3 is requested! Leaving the program.')
sys.exit(-1)
# Parse arguments
args = configuration_parser()
logger.setLevel(args.logging_level)
logger.info(f'Starting application "{APPLICATION_NAME}"...')
logger.debug(f'Arguments: {vars(args)}')
v_latitude, v_longitude = map(float, args.gps_location.split(','))
v_influxdb_host = args.influxdb_host
v_influxdb_port = args.influxdb_port
v_influxdb_database = args.influxdb_database
v_influxdb_username = args.influxdb_username
v_influxdb_password = args.influxdb_password
# Check if "Emon" database exists
_client = DataFrameClient(host=v_influxdb_host,
port=v_influxdb_port,
username=v_influxdb_username,
password=v_influxdb_password,
database=v_influxdb_database)
_dbs = _client.get_list_database()
logger.debug(f'List of InfluxDB databases: {_dbs}')
if v_influxdb_database not in [_d['name'] for _d in _dbs]:
logger.info(
f'InfluxDB database "{v_influxdb_database}" not found. Creating a new one.'
)
_client.create_database(v_influxdb_database)
_client.close()
# Pack all parameters in a dictionary
_userdata = {
'LOGGER': logger,
'LATITUDE': v_latitude,
'LONGITUDE': v_longitude,
'INFLUXDB_HOST': v_influxdb_host,
'INFLUXDB_PORT': v_influxdb_port,
'INFLUXDB_USER': v_influxdb_username,
'INFLUXDB_PASS': v_influxdb_password,
'INFLUXDB_DB': v_influxdb_database,
'MEASUREMENT_TS': args.measurement_ts,
'PROCESSED_TS': args.processed_ts,
'FORECAST_TS': args.forecast_ts,
'WEATHER_TS': args.weather_ts,
'HORIZON_LENGTH': args.horizon_length,
'WEATHER_SERVER_URL': args.weather_server_url,
'WEATHER_FORECAST_INTERVAL': args.weather_forecast_interval,
'WEATHER_START_TIMESTAMP': args.weather_start_timestamp
}
# Instantiate the scheduler and repeatedly run the "forecasting task"
# "forecast interval" seconds after its previous execution
_main_scheduler = continuous_scheduler.MainScheduler()
_main_scheduler.add_task(forecasting_task, 0, args.forecast_interval, 0,
_userdata)
_main_scheduler.start()
def test_get_list_database_fails(self):
"""Test get list of dbs fail for TestInfluxDBClient object."""
cli = DataFrameClient('host', 8086, 'username', 'password')
with _mocked_session(cli, 'get', 401):
cli.get_list_database()
class DbClient(metaclass=Singleton):
def __init__(self, database=None, host=None, port=None):
if database is None:
self.database = configParser['database']['name']
else:
self.database = database
if host is None:
self.host = configParser['database']['host']
else:
self.host = host
if port is None:
self.port = configParser['database']['port']
else:
self.port = port
self._instance = DataFrameClient(host=self.host,
port=self.port,
database=self.database)
def save_to_db(self, df, measurement, tags=None):
""" Saving dataframe to influx db """
if tags is None:
print("Write DataFrame")
self._instance.write_points(df,
database=self.database,
measurement=measurement,
protocol='json')
else:
print("Write DataFrame with Tags")
self._instance.write_points(df,
database=self.database,
measurement=measurement,
tags=tags,
protocol='json')
def fetch_from_db(self, query):
""" Fetching data from influx db """
print("Read from influx db")
return self._instance.query(query)
def create_db(self):
""" Creating the influx db database """
print("Create influx db")
self._instance.create_database('crypto_analyzer')
def drop_db(self):
""" Dropping the influx db database """
print("Influx database with all measurements")
self._instance.drop_database(self.database)
def df_int_to_float(self, df):
""" Converting the int data type columns to float """
for i in df.select_dtypes('int64').columns.values:
df[i] = df[i].astype(float)
return df
def is_existing(self):
""" Checks if database already exists """
result = self._instance.get_list_database()
return result is not None or len(result) > 0
class InfluxClient(object):
def __init__(self, host, port, username, password, database):
self.client = DataFrameClient(host=host,
port=port,
username=username,
password=password,
database=database)
self.database = database
if database is not None and database not in self.GetDatabases():
self.CreateDatabase(database)
self.query_errors = {}
def GetDatabases(self):
return [db['name'] for db in self.client.get_list_database()]
def CreateDatabase(self, database_name):
self.client.create_database(database_name)
def DeleteAllSeriesInDatabase(self):
query = 'Drop series WHERE "" = \'\''
self.RunQuery(query, 'everything gets deleted')
@staticmethod
def GetIdentifier(site_code, var_code, qc_id, source_id, method_id):
"""
InfluxDB Identifiers for iUTAH only:
For the following time series:
Turbidity; Campbell_OBS-3+_Turb, 1, 67, 2
Format as 'wof_{site_code}_{var_code}_{qc_id}_{source_id}_{method_id}'
wof_PUPP2S_Campbell_OBS_3+_Turb_Raw_1_67_2
Encode as a URI (to remove invalid characters while keeping uniqueness)
wof_PUPP2S_Campbell_OBS_3%2B_Turb_1_67_2
Replace all non-word characters with an underscore
wof_PUPP2S_Campbell_OBS_3_2B_Turb_1_67_2
Example python code:
def GetIdentifier(site_code, var_code, qc_id, source_id, method_id):
pre_identifier = 'wof_{}_{}_{}_{}_{}'.format(site_code, var_code, qc_id, source_id, method_id)
return re.sub('[\W]', '_', urllib.quote(pre_identifier, safe=''))
"""
pre_identifier = 'wof_{}_{}_{}_{}_{}'.format(site_code, var_code,
qc_id, source_id,
method_id)
return re.sub('[\W]', '_', urllib.quote(pre_identifier, safe=''))
@staticmethod
def GetUSGSIdentifier(site_code, variable, source, param):
pre_identifier = 'usgs_{}_{}_{}_{}'.format(site_code, variable, source,
param)
return re.sub('[\W]', '_',
urllib.quote(" ".join(pre_identifier.split()), safe=''))
@staticmethod
def GetEnviroDiyIdentifier(result_uuid):
return 'uuid_{}'.format(result_uuid.replace('-', '_'))
@staticmethod
def GetIdentifierBySeriesDetails(series):
if series is None:
return None
return InfluxClient.GetIdentifier(series.site_code,
series.variable_code, series.qc_id,
series.source_id, series.method_id)
@staticmethod
def GetiUtahUrlQueryString(identifier):
return 'http://iutahinflux.uwrl.usu.edu:8086/query?u=web_client&p=password&db=iutah&q=' \
'SELECT%20%2A%20FROM%20%22{}%22'.format(identifier)
def RunQuery(self, query_string, identifier):
try:
return self.client.query(query_string, database=self.database)
except InfluxDBClientError as e:
print 'Query Error for {}: {}'.format(identifier, e.message)
if identifier not in self.query_errors.keys():
self.query_errors[identifier] = []
self.query_errors[identifier].append(e.message)
return None
def AddSeriesToDatabase(self, series):
if series is None:
return None
identifier = self.GetIdentifierBySeriesDetails(series)
print 'Writing data points for ' + identifier
write_success = self.client.write_points(series.datavalues,
identifier,
protocol='json',
batch_size=10000)
if not write_success:
print 'Write failed for series with identifier {}'.format(
identifier)
else:
print '{} Data points written for time series with identifier {}'.format(
len(series.datavalues), identifier)
def AddDataFrameToDatabase(self, datavalues, identifier):
try:
print 'Writing {} data points for {}'.format(
len(datavalues), identifier)
write_success = self.client.write_points(datavalues,
identifier,
protocol='json',
batch_size=10000)
if not write_success:
print 'Write failed for series with identifier {}'.format(
identifier)
else:
print '{} Data points written for time series with identifier {}'.format(
len(datavalues), identifier)
return len(datavalues)
except InfluxDBClientError as e:
print 'Error while writing to database {}: {}'.format(
identifier, e.message)
print datavalues
return 0
def GetTimeSeriesBySeriesDetails(self, series, start='', end=''):
return self.GetTimeSeries(series.site_code, series.variable_code,
series.qc_code, series.source_code,
series.method_code, start, end)
def GetTimeSeries(self,
site_code,
var_code,
qc_code,
source_code,
method_code,
start='',
end=''):
identifier = self.GetIdentifier(site_code, var_code, qc_code,
source_code, method_code)
print 'Getting time series for ' + identifier
query_string = 'Select {select} from {series}'.format(
select='*', series=identifier)
if len(start) > 0:
query_string += ' where time > \'{}\''.format(start)
if len(end) > 0 and len(start) > 0:
query_string += ' and time < \'{}\''.format(end)
elif len(end) > 0:
query_string += ' where time < \'{}\''.format(end)
return self.RunQuery(query_string, identifier)
return None
def GetTimeSeriesStartTime(self, site_code, var_code, qc_code, source_code,
method_code):
identifier = self.GetIdentifier(site_code, var_code, qc_code,
source_code, method_code)
print 'Getting start time for ' + identifier
query_string = 'Select first(DataValue), time from {identifier}'.format(
identifier=identifier)
result = self.RunQuery(query_string, identifier)
if result is not None and len(result) == 1:
dataframe = result[identifier] # type: pandas.DataFrame
return dataframe.first_valid_index().to_pydatetime()
return None
def GetTimeSeriesEndTime(self, identifier):
query_string = 'Select last(DataValue), time from {identifier}'.format(
identifier=identifier)
result = self.RunQuery(query_string, identifier)
if result is not None and len(result) == 1:
dataframe = result[identifier] # type: pandas.DataFrame
return dataframe.first_valid_index().to_pydatetime()
return None
def do_dataframes(host="localhost", port=8086):
client = DataFrameClient(host, port)
print("influxdb dataframe client created")
dbname = "99-TEST-v99"
db_list = client.get_list_database()
check_name = {"name": dbname}
if check_name in db_list:
client.switch_database(dbname)
print("using database " + dbname)
else:
print("can't find database: " + dbname)
exit(-1)
query = "select * from temperature" # where change_action='START HEATING'"
print("running query: " + query)
rs = client.query(query)
# print("resultset is...")
# print(rs)
# print("keys are... ", rs.keys())
# print("temperature values...")
# print(rs['temperature'])
# load the result set into a dataframe
df = pd.DataFrame(rs['temperature'])
# convert time index to NZ timezone
df.index = df.index.tz_convert('Pacific/Auckland')
# print("dataframe is...")
# print(df)
print("from ", df['ambient_temp'].count(), " records...")
print("min ambient = ", df['ambient_temp'].min())
print("average ambient = ", df['ambient_temp'].mean())
# print(df['fermemter_temp'].count(), "records")
# temps = df['ambient_temp'] # this is a Series
# print("ambient temp std dev =", temps.std())
# for i in temps:
# if not np.isnan(i):
# print(i)
# copy the fermeMter values to the fermeNter column
print("fixing the fermenter temp data")
for index, row in df.iterrows():
if not np.isnan(row['fermemter_temp']):
df.at[index, 'fermenter_temp'] = row['fermemter_temp']
print("from", df['fermenter_temp'].count(), "records")
print("min fermenter temp = ", df['fermenter_temp'].min(), "at",
df['fermenter_temp'].idxmin())
print("removing this value")
df = df.drop(index=df['fermenter_temp'].idxmin())
print("min fermenter temp = ", df['fermenter_temp'].min(), "at",
df['fermenter_temp'].idxmin())
print("average fermenter temp = ", df['fermenter_temp'].mean())
print("max fermenter temp = ", df['fermenter_temp'].max(), "at",
df['fermenter_temp'].idxmax())
temps = df['fermenter_temp'] # this is a Series
print("fermenter temp std dev =", temps.std())
def test_get_list_database_fails(self):
"""Test get list of dbs fail for TestInfluxDBClient object."""
cli = DataFrameClient('host', 8086, 'username', 'password')
with _mocked_session(cli, 'get', 401):
cli.get_list_database()
if __name__ == "__main__":
mydb = Set.infl_mydb
path = Set.sr_path
timeshift = Set.sr_timeshift
blocks = Set.sr_blocks
sequence = Set.sr_sequence
# client = InfluxDBClient(host=host, port=port,
# username=username, password=password)
client = DataFrameClient(host=Set.infl_host,
port=Set.infl_port,
username=Set.infl_username,
password=Set.infl_password)
listdb = client.get_list_database()
listdb = [i['name'] for i in listdb]
# print(listdb)
if mydb not in listdb:
print('В influxdb нет БД {}'.format(mydb))
else:
client.switch_database(mydb)
make_report()
# Запускаем пеиодическое выполнление функции
# schedule.every().minute.at(":17").do(make_report)
#
# while True:
# schedule.run_pending()
# time.sleep(1)
# import pdb
influxdb_host = read_value(parser, section=args.region, key="influxdb_host")
influxdb_port = int(read_value(parser, section=args.region, key="influxdb_port"))
influxdb_user = read_value(parser, section=args.region, key="influxdb_user")
influxdb_password = read_value(parser, section=args.region, key="influxdb_password")
influxdb_dbname = read_value(parser, section=args.region, key="influxdb_dbname")
else:
sys.exit("Invalid region: '%s'" % args.region)
influxdb_client = DataFrameClient(host=influxdb_host,
port=influxdb_port,
username=influxdb_user,
password=influxdb_password,
database=influxdb_dbname)
dbs = influxdb_client.get_list_database()
create_db = True
for db in dbs:
if db['name'] == influxdb_dbname:
create_db = False
break
if create_db:
influxdb_client.create_database(influxdb_dbname)
options = {"access_log": access_log,
"request_time_threshold": request_time_threshold,
"log_datetime_format": log_datetime_format,
"plot_chart": plot_chart,
"uri_white_list": uri_white_list,
"statsd": statsd,
class tsdb(object):
def __init__(self,
dbname,
host='localhost',
port=8086,
user='******',
password='******'):
self.host = host
self.port = port
self.user = user
self.password = password
self.dbname = dbname
self.client = None
self.protocol = 'json'
def _connect(self):
if self.client is None:
self.client = DataFrameClient(host=self.host,
port=self.port,
username=self.user,
password=self.password,
database=self.dbname)
#self.client.switch_database(self.dbname)
def _disconnect(self):
if self.cleint is not None:
self.client.close()
self.client = None
def _reconnet(self):
self._disconnect()
self._connect()
def create_db(self):
self._connect()
dbs = self.client.get_list_database()
for e in dbs:
if self.dbname in e.values():
logger.debug("Database {} is already exist.".format(
self.dbname))
return
logger.info("Creating database:{}".format(self.dbname))
self.client.create_database(self.dbname)
#self._set_retantion_policy()
def _set_retantion_policy(self):
self._connect()
self.client.create_retention_policy(name='raw',
duration='12h',
replication=1,
default=True)
self.client.create_retention_policy(name='cooked',
duration='52w',
replication=1,
default=False)
def check_db(self):
self._connect()
db = self.client.get_list_database()
ms = self.client.get_list_measurements()
rp = self.client.get_list_retention_policies(self.dbname)
user = self.client.get_list_users()
print('db: {}, measurements: {}'.format(db, ms))
print('retention policy: {}'.format(rp))
print('users: {}'.format(user))
def insert(self, df, measurement, tags=None):
self._connect()
try:
result = self.client.write_points(df,
measurement,
tags=tags,
time_precision='n',
protocol=self.protocol)
except:
logger.info('influxdb write error')
result = False
return result
def query(self, sql):
self._connect()
result = self.client.query(sql)
return result
class InfluxDBConnector(object):
def __init__(self,
username='******',
password='******',
port=8086,
database=None,
host='localhost'):
'''
:param username: user to connect
:type username: str
:param password: password of the user
:type password: str
:param port: port to connect to InfluxDB
:type port: int
:param database: database name to connect to
:type database: str
:param host: hostname to connect to InfluxDB
:type host: str
'''
self.username = username
self.password = password
self.port = port
self.database = database
self.host = host
self.client = DataFrameClient(self.host, self.port, self.username,
self.password, self.database)
def create_database(self, database):
"""Create a new database in InfluxDB.
:param database: the name of the database to create
:type database: str
"""
self.client.create_database(database)
def delete_database(self, database):
"""Delete a database from InfluxDB.
:param database: the name of the database to drop
:type database: str
"""
self.client.drop_database(database)
def list_databases(self):
"""Get the list of databases in InfluxDB.
:returns: all databases in InfluxDB
:rtype: list of dictionaries
"""
return self.client.get_list_database()
def list_measurements(self):
"""Get the list of measurements in database in InfluxDB
:return:
"""
return self.client.get_list_measurements()
def write_points(self,
dataframe,
measurement,
tags=None,
tag_columns=None,
field_columns=None,
time_precision=None,
database=None,
retention_policy=None,
batch_size=None,
protocol='line',
numeric_precision=None):
"""Write to multiple time series names.
:param dataframe: data points in a DataFrame
:param measurement: name of measurement
:param tags: dictionary of tags, with string key-values
:param tag_columns: N/A. No description in API or source code?
:param field_columns: N/A. No description in API or source code?
:param time_precision: [Optional, default None] Either 's', 'ms', 'u'
or 'n'.
:param batch_size: [Optional] Value to write the points in batches
instead of all at one time. Useful for when doing data dumps from
one database to another or when doing a massive write operation
:type batch_size: int
:param database: the database to write the DataFrame to
:type database: str
:param retention_policy: N/A. No description in API or source code?
:param protocol: Protocol for writing data. Either 'line' or 'json'.
:type protocol: str
:param numeric_precision: Precision for floating point values.
Either None, 'full' or some int, where int is the desired decimal
precision. 'full' preserves full precision for int and float
datatypes. Defaults to None, which preserves 14-15 significant
figures for float and all significant figures for int datatypes.
:returns: True, if the write operation is successful
:rtype: bool
"""
return self.client.write_points(dataframe, measurement, tags,
tag_columns, field_columns,
time_precision, database,
retention_policy, batch_size, protocol,
numeric_precision)
def query(self,
query,
params=None,
epoch=None,
expected_response_code=200,
database=None,
raise_errors=True,
chunked=False,
chunk_size=0,
dropna=True):
"""Send a query to InfluxDB into a DataFrame
:param query: the actual query string
:type query: str
:param params: additional parameters for the request, defaults to {}
:param epoch: response timestamps to be in epoch format either 'h',
'm', 's', 'ms', 'u', or 'ns',defaults to `None` which is
RFC3339 UTC format with nanosecond precision
:param expected_response_code: the expected status code of response,
defaults to 200
:param database: database to query, defaults to None
:type database: str
:param raise_errors: Whether or not to raise exceptions when InfluxDB
returns errors, defaults to True
:param chunked: Enable to use chunked responses from InfluxDB.
With ``chunked`` enabled, one ResultSet is returned per chunk
containing all results within that chunk
:param chunk_size: Size of each chunk to tell InfluxDB to use.
:param dropna: drop columns where all values are missing
:returns: the queried data
:rtype: :class:`~.ResultSet`
"""
return self.client.query(query, params, epoch, expected_response_code,
database, raise_errors, chunked, chunk_size,
dropna)
