def predict(input, scaler, rt_val, test=False): model = keras.models.load_model(PATH + "Model_EB3_2434.h5") raw_forecast = model.predict(input) to_trans = np.zeros(7) to_trans[0] = raw_forecast to_trans = to_trans.reshape(1, len(to_trans)) transform = scaler.inverse_transform(to_trans) transform = transform[0][0] forecast = transform + rt_val if not test: idx = pd.Timestamp( date.strftime( datetime.now(timezone(-timedelta(hours=5))) + timedelta(days=1), "%Y-%m-%d")) df = pd.DataFrame(forecast, index=[idx], columns=["Gage_Height"]) client = influxdb.DataFrameClient(host='34.231.230.82', port=8086, username="******", password="******", database="peko_prediction_results") client.write_points(df, "ppred", database="peko_prediction_results") else: return forecast
def pull_data_from_influx(your_query, database_name, IP_of_influx="127.0.0.1", port=8086): #Initiating the client client = influxdb.DataFrameClient(IP_of_influx, port, database=database_name) #query returns data to dfs_dict dfs_dict = client.query(your_query) #Finding out the measurement name measurement = next(iter(dfs_dict)) #Extracting the measurement data of use ret = dfs_dict[measurement] #Storing the measurement data as a pandas dataframe df = pd.DataFrame.from_dict(ret) #Resetting index df = df.reset_index() #Renaming index as datetime df.rename(columns={'index': 'datetime'}, inplace=True) #print(df) #Parsing datetime df['datetime'] = pd.to_datetime(df['datetime']) #Parsing time from datetime df['Time'] = df['datetime'].dt.time #parsing Time from datetime df['date'] = df['datetime'].dt.date df['date'] = pd.to_datetime(df['date']) #setting index as date df = df.set_index(['date']) return df
def influx(sql): conn = influxdb.DataFrameClient(**config.INFLUX_CONN_SETTING) rows = conn.query(sql) if rows: rows = rows[config.INFLUX_TABLE_POINT] conn.close() return rows
def update_graph_live(n, aggregation_duration, selected_date, aggregation_method): client = influxdb.DataFrameClient(database=config.DATABASE) start_time = arrow.get(selected_date) end_time = start_time + datetime.timedelta(days=1) measures = client.query( f""" select {aggregation_method}({config.INDICATOR}) as {config.INDICATOR} \ from {config.MEASUREMENT} \ where time >= '{start_time.isoformat()}' \ and time <= '{end_time.isoformat()}' \ group by time({aggregation_duration}m), "{config.TAG}" """ ) fig = go.Figure( layout=dict(legend=dict(orientation="h"), margin=dict(l=20, r=20, t=20, b=20)), ) for (_, ((_, tag_value),)), data in measures.items(): fig.add_trace( go.Scatter( x=data.index, y=data[config.INDICATOR], name=tag_value, mode="lines+markers", ), ) return fig
def cli(ctx, verbosity): ctx.obj = {} ctx.obj['client'] = influxdb.DataFrameClient(host=DEFAULT_HOST, port=DEFAULT_PORT, database=DEFAULT_DB_NAME) logging.basicConfig( level=LOGGING_LEVELS[verbosity.upper()], format="%(asctime)s - %(name)s - %(levelname)s - %(message)s")
def connect(self): """ Setup an Influx client connection """ self._influxdb = influxdb.DataFrameClient( host=self.hostname, port=self.port, username=self.user, password=self.password, database=self.database, ssl=self.ssl, verify_ssl=self.verify_ssl, )
def __init__(self, db_input=None, usr='******', pwd='root', cloud=False): if not cloud: host = '192.168.58.71' # 公司:192.168.58.71 阿毛:192.168.38.176 else: host = '39.100.15.89' self.client = influxdb.DataFrameClient(host=host, port=8086, username=usr, password=pwd, database=db_input)
def __connectInfluxdb(self, InfluxdbSource): host = InfluxdbSource['host'] port = InfluxdbSource['port'] username = InfluxdbSource['username'] password = InfluxdbSource['password'] database = InfluxdbSource['database'] client = influxdb.DataFrameClient(host=host, port=port, username=username, password=password, database=database) return client pass
def get_days_data(self, vehicle, days_ago, hours_ago, length): c = influxdb.DataFrameClient(host='18.237.167.98', port=8086) self.client.switch_database("telematics") results = self.client.query("select CF_Clu_Odometer,CR_Vcu_DistEmpty_km,CR_Bms_Soc_Pc, CF_Bms_StdCha, CF_Bms_FstCha " "from {} " "where time > now() - {}d - {}h and time < now() - {}d + {}h ". format(vehicle, days_ago, hours_ago, days_ago, length - hours_ago)) df = results[vehicle].dropna() df_fo = df.loc[(df['CF_Clu_Odometer'] > 1000)] # find all spots where battery jumps df_fo['soc_plus'] = df_fo['CR_Bms_Soc_Pc'].shift().astype(float) + 2.0 df_fo['charge_break'] = df_fo['CR_Bms_Soc_Pc'] > df_fo['soc_plus'].shift() df_fo['charge_list'] = (df_fo['CF_Bms_StdCha'] > 0) | (df_fo['CF_Bms_FstCha'] > 0) return df_fo
def __init__(self, pair, router_port=None, sub_port=None): super().__init__(pair=pair, router_port=router_port, sub_port=sub_port) if not pair.startswith("frx"): self.database_name = pair else: self.database_name = pair.replace('frx', '') # Sets the name of hst file name in case we need to fetch # from hst archive... self.pair_hst_file = self.database_name + '1.hst' # sets the client of market pair from where we would get # minute data self.client = db.DataFrameClient(host='localhost', port=8086, database=self.database_name)
def getDataFromInfluxdb(code,date,database,columns=[]): client = influxdb.DataFrameClient(host=INFLUXDBHOST, port=8086, username='******', password='******', database=database) measure=code b = dtparser.parse(str(date))+ datetime.timedelta(hours=0) e = dtparser.parse(str(date)) + datetime.timedelta(hours=24) colstr='' if len(columns)>0: for col in columns: colstr=colstr+f""" "{col}", """ colstr=colstr[:-2] else: colstr='*' query=f""" select {colstr} from "{database}"."autogen"."{measure}" where time >= {int(b.timestamp() * 1000 * 1000 * 1000)} and time < {int(e.timestamp() * 1000 * 1000* 1000)} """ result=client.query(query) if result!={}: data=pd.DataFrame(result[measure]) else: data=pd.DataFrame() return data
def loaddata_influxdb(_database_name, _measurement_name, start_time, end_time, car_id): client = influxdb.DataFrameClient(host='125.140.110.217', port=8999, username='******', password='******', database=_database_name) _query = 'SELECT DRIVE_SPEED, GPS_LAT, GPS_LONG, time FROM "%s"' % _measurement_name + " WHERE time >= '%s' and time < '%s' and car_id = '%s'" % ( start_time, end_time, car_id) print(_query) _query_result = client.query(_query) df = _query_result[_measurement_name] df = df.reset_index().rename(columns={"index": "RECORD_TIME"}) print(len(df), 'data loaded') return df.to_dict()
def saveToInfluxdb(self, code, date, factor, data): host = InfluxdbServer['host'] port = InfluxdbServer['port'] username = InfluxdbServer['username'] password = InfluxdbServer['password'] database = 'MaoTickFactors' client = influxdb.DataFrameClient(host=host, port=port, username=username, password=password, database=database) dbs = client.get_list_database() if ({'name': database} in dbs) == False: client.create_database(database) client.write_points(dataframe=data, database=database, measurement=code, tags={}, field_columns=list(data.columns), protocol='line') pass
def getdataset(values, spaces, timefilter): # Set up a client for InfluxDB dbclient = influxdb.DataFrameClient('192.168.1.70', 8086, 'root', 'root', 'sensordata') # get data if timefilter != "": timefilter = " AND " + timefilter data = pandas.DataFrame() for value in values: for space in spaces: rs = dbclient.query('SELECT mean(value) FROM "' + value + '" WHERE "ruimte" = \'' + space + '\'' + timefilter + ' GROUP BY time(5m), "ruimte" fill(linear)') datacol = list(rs.values())[0] datacol.columns = [space + '-' + value] if data.empty: data = datacol else: data = data.join(datacol, how='outer') dbclient.close() return data
def update_table(start, end): g_start = start g_end = end r_start = date.strftime( pd.Timestamp(start) - pd.Timedelta(days=1), "%Y-%m-%d") r_end = date.strftime(pd.Timestamp(end) - pd.Timedelta(days=1), "%Y-%m-%d") uapi.collect_data(PATH, "gage", "01480870", g_start, g_end) uapi.collect_data(PATH, "rain", "01480870", r_start, r_end) uapi.collect_data(PATH, "rain", "01480399", r_start, r_end) df1 = uapi.parse_data(PATH + "gage_01480870_%s.txt" % g_end) df1.rename(columns={"data": "Gage_Height"}, inplace=True) df2 = uapi.parse_data(PATH + "rain_01480870_%s.txt" % r_end) df2.rename(columns={"data": "Downingtown"}, inplace=True) df3 = uapi.parse_data(PATH + "rain_01480399_%s.txt" % r_end) df3.rename(columns={"data": "Wagontown"}, inplace=True) client = influxdb.DataFrameClient(host='localhost', port=8086) client.write_points(df1, "Gage_Height", database="training_raw") client.write_points(df2, "Precip", database="training_raw") client.write_points(df3, "Precip", database="training_raw")
def connect_to_db(database='SKYSPARK'): """Function to connect to the database Args: database (string): name of the database to connect to options are 'SKYSPARK' (default) and 'ION' Returns: client (influxdb-python client object): database connection object \n OR \n (None): If the database connection failed """ client = influxdb.DataFrameClient(host='206.12.92.81', port=8086, username='******', password='******', database=database) try: client.ping() print("Successful Connection to " + database + "\n") return client except: print("Failed to Connect to " + database + "\n") return None
def __init__(self, other, timeframe=None): self.market = other.database_name if other else 'EURUSD' self.max_period = 100 # self.monitor = self.timeframes[1] self.monitor = 'EURUSD' self.client = db.DataFrameClient(database=self.market) self.init = self.client.query( f"SELECT open,high,low,close from {self.market} \ LIMIT {self.max_period}").get(self.monitor).dropna() self.ema = Indicator('ema', talib.EMA, timeperiod=20) self.ema.update(self.init) self.ema3 = Indicator('ema3', talib.EMA, timeperiod=50) self.ema3.update(self.init) self.ema4 = Indicator('ema4', talib.EMA, timeperiod=100) self.ema4.update(self.init)
default='127.0.0.1', metavar='HOST', help='Influx host [127.0.0.1]') parser.add_argument( '-u', '--unit', dest='unit', metavar='UNIT', default='arb', help='Unit of measurement, e.g. volt, degC, ... [def: arb]') parser.add_argument(metavar='measurement1:column1', dest='mc1') parser.add_argument(metavar='measurement2:column2', dest='mc2') args = parser.parse_args() clt = influxdb.DataFrameClient(host=args.influx_host, port=8086) clt.switch_database(args.influx_db) m1, col1 = args.mc1.split(':') m2, col2 = args.mc2.split(':') query = f"SELECT {col1} FROM {m1}" if args.start: query += f" WHERE time >= '{args.start}'" if args.end: if args.start: query += f" AND time <= '{args.end}'" else: query += f" WHERE time <= '{args.end}'" t1, val1 = query_to_np(clt, query)
def cmc_df_client(self): """The client connection to the CoinMarketCap database for daily data""" return influxdb.DataFrameClient( self.host, self.port, self.user, self.password, self.config_info.get_field('CMCDBName', self.setting_name))
def day_gdax_df_client(self): """The client connection to the GDAX database for daily data""" return influxdb.DataFrameClient( self.host, self.port, self.user, self.password, self.config_info.get_field('dailyDBName', self.setting_name))
# connecting to the database without using the csv files available in the storage bucket import influxdb import pandas as pd import matplotlib.pyplot as plt import os client = influxdb.DataFrameClient(host='35.203.94.147', port=8086, username='******', password='******') #client = influxdb.InfluxDBClient(host='35.203.80.76', port=8086, username='******', password='******') #client.create_retention_policy(name='2h_policy', duration='1w',default=True, replication="string") print(client.get_list_database()) client.switch_database('PlaceBonaventure') result = client.query('SHOW MEASUREMENTS;') #list tables os.makedirs("processed_data", exist_ok=True) if not os.path.exists("processed_data/sensor_value.pt"): q = """SELECT * FROM "sensor_value" LIMIT 1000000""" res = client.query(q) # keep only if pandas Dataframe instance keys = [k for k, v in res.items() if not isinstance(v, list)] values = [v for _, v in res.items() if not isinstance(v, list)] sensor_data = values[0]
def openDB(self): self.Db = influxdb.DataFrameClient(database="raw", host="localhost:8086") return self.Db
def fetch_missing_data_fill_database( pair, port=INTERMED_ROUTER, start=None, end=None): """ There are three main conditions this function fulfills..... 1. IF NO DATABASE FOR THAT PAIR... It fetches data for a particular time range , creates a new database and saves the fetched data in that database, then returns fetched data 2. IF THERE IS A DATABASE.... It checks in database to make sure there are no loopholes, if any is found it fetches dat DATA and saves the loop data in database, then returns the fetched data 3. IF ["start" and "end"] VARIABLES do not default to None for that pair it fetches for data with regards to that time-range of start/end and returns it.... """ prefix = pair if pair == 'R_50' else 'frx' + pair empty = pd.DataFrame() ctx = zmq.Context() req = ctx.socket(zmq.REQ) req.setsockopt_string(zmq.IDENTITY, f'{pair}1') req.connect(f"tcp://localhost:{port}") def return_val(*args): nonlocal req, empty get_dict = populate_history_schema(*args, 'candles') req.send_json(get_dict) try: msg = req.recv_json() frame = Parse_History_Candle_To_DataFrame(msg['candles']) empty = empty.append(frame) print(msg) except json.JSONDecodeError: print( """Error decoding response sent from router, closing sockets and exiting""") raise Exception("Websocket is not responding...check why") def loop_and_fetch(gen): while True: try: n = next(gen) return_val(*n, prefix) except StopIteration: if len(empty) == 0: raise Exception("Empty frame returned.....") break # CONDITION 3 # I set it here to run before CONDITION 1/2 if (start and end) is not None: print("fetching from range values in binary api") gen = yield_partition(start, end) loop_and_fetch(gen) return empty # CONDITION 1 try: client = db.DataFrameClient(host='localhost', port=8086, database=pair) empty = empty.append( client.query(f'select * from {pair}')[f'{pair}'] ) missing = return_missing_timestamps(empty) gen = return_stamp_interval(np.array(missing, dtype=np.int64)) loop_and_fetch(gen) print("fetched from database") # CONDITION 2 except InfluxDBClientError: now = pd.Timestamp.utcnow() prev = now - pd.Timedelta(days=10) # prev = now - pd.Timedelta(weeks=6) i, e = int(prev.timestamp()), int(now.timestamp()) gen = yield_partition(i, e) loop_and_fetch(gen) client.create_database(f'{pair}') # Code Cleanup and deallocation finally: client.write_points(empty, f'{pair}', protocol='json') client.close() req.close() ctx.term() print("FETCHED DATA SUCCESSFULLY.....") return empty
import influxdb from config import credentials, measurement client = influxdb.DataFrameClient(**credentials) query = ("select * from %s" " where time < now() - 90m" " and time > now() - 100m" " limit 10" % measurement) df = client.query(query).get(measurement) print(df.describe())
def calibrate(self, request, pk=None): if request.method == "POST": form = SensorModelForm(request.POST) ## TODO: Add validation. Currently problematic because there are issues with the models. #if form.is_valid(): raw_sensors = form.data["raw_sensors"] calibration_entity = form.data["calibration_entity"] time_range_start = form.data["time_range_start"] time_range_end = form.data["time_range_end"] measurement = form.data["measurement"] db = influxdb.DataFrameClient( database="home_assistant", host="influxdb", username=os.environ['INFLUXDB_USER'], password=Path( '/run/secrets/influxdb_password').read_text().strip("\n")) for sensor in raw_sensors.split(","): # TODO: Move this to flux so that I can maybe use bind_params. It works for queries, but not sub queries. results = db.query( f"SELECT first(\"value\") AS \"value\", first(\"calibrated_value\") AS \"calibrated_value\" \ FROM (SELECT \"value\" FROM \"raw\" WHERE (entity_id =~ /({ sensor })/) AND time >= { time_range_start }ms and time <= { time_range_end }ms), \ (SELECT \"value\" AS \"calibrated_value\" FROM \"{ measurement }\" WHERE entity_id =~ /({ calibration_entity })/ AND time >= { time_range_start }ms and time <= { time_range_end }ms) \ GROUP BY time(10s)") # Clean up results results = pandas.concat(results, keys=[measurement, "raw"], axis=1) results = results.dropna(axis=0) X = results["raw"] Y = results[measurement] model = linear_model.LinearRegression().fit( X.values.reshape(-1, 1), Y.values.reshape(-1, 1)) expression = re.compile( r'(?P<type>.+)_(?P<device>\d+)_(?P<sensor>.*)'.format( measurement.lower())) s = expression.search(sensor) if s: topic = f"seedship/{ s.group('type') }_{ s.group('device') }/{ s.group('sensor').replace('_raw', '_calibration') }" print( f"Linear calibration for { topic }: Slope({ model.coef_[0] }) Intercept({ model.intercept_[0]})" ) ViewSensor.send_mqtt(topic, { "slope": model.coef_[0][0], "bias": model.intercept_[0] }, retain=True) return HttpResponse(status=204) form = SensorModelForm( initial={ "calibration_entity": pk, "raw_sensors": request.GET.get("raw_sensors"), "time_range_start": request.GET.get("time_range_start"), "time_range_end": request.GET.get("time_range_end"), "measurement": request.GET.get("measurement"), }) context = { 'title': "Calibrate Linear Filter", 'form': form, } return render(request, 'seedship_gui/gf-form.html', context)
import shutil import time import pandas as pd import influxdb from random import randint from settings.default import * from influxdb import InfluxDBClient import datetime import hex2dec_ble dbClient = InfluxDBClient(INFLUX_DB_HOST, INFLUX_DB_PORT, INFLUX_DB_USER, INFLUX_DB_PASSWORD, INFLUX_DB_NAME) batch_count = 10000 influx_pd = influxdb.DataFrameClient(INFLUX_DB_HOST, INFLUX_DB_PORT, INFLUX_DB_USER, INFLUX_DB_PASSWORD, INFLUX_DB_NAME, verify_ssl=False) def move_processed_file(file, type): """ Function to move the processed file to processed folder :param file: :return: """ logging.info("moving the processed file: %s" % file) file_name_array = file.split(".") file_name = file_name_array[0] file_name = file_name + "_" + type + ".csv" shutil.move(SUB_DUMP_DIR + file, PROCESSED_FILES_FOLDER + file_name)
def main(safety_on=False): # Set the working directory. os.chdir(WORKING_DIRECTORY) # Check for options passed to script try: opts, args = getopt.getopt(sys.argv[1:], "hf", ["help", "flush"]) except getopt.GetoptError as err: # print help information and exit: print(err) # will print something like "option -a not recognized" usage() sys.exit(2) flush = False for o, a in opts: if o in ('-h', '--help'): usage() sys.exit() elif o in ('-f', '--flush'): flush = True else: assert False, 'unhandled option' usage() sys.exit() print("\n") print("Oh hello there! Let's see if there are new files.") # Open connection to InfluxDB influxdb_client = influxdb.DataFrameClient(host=INFLUX_HOST, port=INFLUX_PORT, username=INFLUX_USER_NAME, password=INFLUX_PASSWORD, database=INFLUX_DB_NAME) # Check if old files should be flushed. if flush and not safety_on: flush_old_file_names(SQLITE_LOCATION) flush = False print("I will flush the old file register first.") # Make a dictionary that contains list of the different file types old_files = get_names_of_old_files(SQLITE_LOCATION, TYPE_LIST) # Load names of all available input files input_files = find_input_files(START_DATES, GLOB_PATTERNS) input_files = { k: (remove_todays_file(v) if k != 'calvin' else v) for k, v in input_files.items() } # Extract new files new_files = extract_new_files_only(input_files, old_files) # Only load the first x elements of each file list. # Remove the other names from the new_file dictionaries. export = {} for i in new_files.keys(): export[i] = new_files[i][0:CHUNK_SIZE].copy() new_files[i] = new_files[i][CHUNK_SIZE:] # Declare curried function: load_chamber_data = load_data(export, TAGS['base']) # This is the step where we load the actual data into Python. raw_data = { i: load_chamber_data(i) for i in new_files.keys() if i != 'calvin' } # Make Calvin dataframe raw_data['calvin'] = list( map(lambda x: load_calvin_file(x, CALVIN_HEADER), export['calvin'])) raw_data['calvin'] = pd.concat(raw_data['calvin']) # Sort dictionary elements by key names raw_data = dict(sorted(raw_data.items())) # Write chamber data to InfluxDB. for i in raw_data.keys(): if i not in 'calvin': if not len(raw_data[i].index) == 0: influxdb_client.write_points(dataframe=raw_data[i], measurement=('raw_' + i), tag_columns=None, batch_size=1000, protocol='line') else: # If there is data from Calvin write to InfluxDB. if not len(raw_data['calvin'].index) == 0: influxdb_client.write_points(dataframe=raw_data['calvin'], measurement='raw_calvin', tag_columns=TAGS['calvin'], batch_size=1000, protocol='line') # Remove last item from new_files['calvin'] if len(new_files['calvin']) == 0: export['calvin'] = export['calvin'][:-1] # Write the names of the files that were loaded into InfluxDB to the # local database. add_names_to_old_files(SQLITE_LOCATION, export) # Run function recursively if there still elements in the new file list if any([not x.empty for x in new_files.values()]): main(safety_on=True) return
import influxdb import pandas as pd c = influxdb.DataFrameClient() c.create_database("icinga2") c.switch_database("icinga2") df = pd.read_excel("Disk Space.xlsx") df['time'] = df.index df.index = pd.to_datetime(df.index) c.write_points(df, "disk")
file_location = MODEL_OUTPUT_FILE_LOCATION #Set default value for file location #Then check with user-input for different file location prompt = "input path + filename or leave blank for default of " + MODEL_OUTPUT_FILE_LOCATION filename_input = input(prompt) if len(filename_input) > 0: file_location = filename_input username = input("input username:"******"input password:"******"Can not connect to InfluxDB. Is your network connection ok?") #Load the output from the model into a dataframe output = pd.read_csv(file_location) if 'uniqueID' not in output.columns: output.rename(columns={'uniqueId': 'uniqueID'}, inplace=True) if len(output['uniqueID'].unique()) < len(output): raise Exception(