def test_write_points_from_dataframe_with_period_index(self): dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[pd.Period('1970-01-01'), pd.Period('1970-01-02')], columns=["column_one", "column_two", "column_three"]) expected = { 'points': [ {'name': 'foo', 'tags': {}, 'fields': { 'column_one': '1', 'column_two': 1, 'column_three': 1.0}, 'time': '1970-01-01T00:00:00+00:00'}, {'name': 'foo', 'tags': {}, 'fields': { 'column_one': '2', 'column_two': 2, 'column_three': 2.0}, 'time': '1970-01-02T00:00:00+00:00'}], 'database': 'db', } with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo") self.assertEqual(json.loads(m.last_request.body), expected)
def main(host='localhost', port=8086): user = '******' password = '******' dbname = 'example' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H')) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo') print("Write DataFrame with Tags") client.write_points(df, 'demo', {'k1': 'v1', 'k2': 'v2'}) print("Read DataFrame") client.query("select * from demo") print("Delete database: " + dbname) client.drop_database(dbname)
def test_write_points_from_dataframe_with_tag_escaped(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame( data=[['blue', 1, "1", 1, 1.0, 'hot'], ['red,green=orange', 0, "2", 2, 2.0, 'cold']], index=[now, now + timedelta(hours=1)], columns=["tag_one", "tag_two", "column_one", "column_two", "column_three", "tag_three"]) expected_escaped_tags = ( b"foo,tag_one=blue " b"column_one=\"1\",column_two=1i " b"0\n" b"foo,tag_one=red\\,green\\=orange " b"column_one=\"2\",column_two=2i " b"3600000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo', field_columns=['column_one', 'column_two'], tag_columns=['tag_one']) self.assertEqual(m.last_request.body, expected_escaped_tags)
def main(host='localhost', port=8086): """Instantiate the connection to the InfluxDB client.""" user = '******' password = '******' dbname = 'demo' # Temporarily avoid line protocol time conversion issues #412, #426, #431. protocol = 'json' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H')) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo', protocol=protocol) print("Write DataFrame with Tags") client.write_points(df, 'demo', {'k1': 'v1', 'k2': 'v2'}, protocol=protocol) print("Read DataFrame") client.query("select * from demo") print("Delete database: " + dbname) client.drop_database(dbname)
class LinktoInfluxDB(): def __init__(self, host, port, user, password, database, measurement, time_presicion='s'): self.host = host self.port = port self.user = user self.password = password self.database = database self.measurement = measurement self.client = DataFrameClient(self.host, self.port, self.database) self.time_presicion = time_presicion def get_data(self, query): try: list_of_df = self.client.query(query) return list_of_df except Exception as e: logger.error(e) def put_data(self, data, columns): try: self.client.write_points(dataframe=data, database=self.database, measurement=self.measurement, field_columns=columns, batch_size=5000, protocol='line', time_precision=self.time_presicion) except Exception as e: logger.error(e)
def write_pd_to_influxdb(self, user_id: str, username: str, stream_name: str, df: pd.DataFrame): """ Store data in influxdb. Influxdb is used for visualization purposes Args: user_id (str): id of a user username (str): username stream_name (str): name of a stream df (pandas): pandas dataframe Raises: Exception: if error occurs during storing data to influxdb """ client = DataFrameClient(host=self.influxdbIP, port=self.influxdbPort, username=self.influxdbUser, password=self.influxdbPassword, database=self.influxdbDatabase) try: df["stream_name"] = stream_name df["user_id"] = user_id df['username'] = username del df["localtime"] tags = ['username', 'user_id', 'stream_name'] df.set_index('timestamp', inplace=True) client.write_points(df, measurement=stream_name, tag_columns=tags, protocol='json') except Exception as e: raise Exception("Error in writing data to influxdb. " + str(e))
def main(host='localhost', port=8086): user = '******' password = '******' dbname = 'demo' protocol = 'line' client = DataFrameClient(host, port, user, password, dbname) print('Create pandas DataFrame') df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H'), columns=['0']) print('Create database: ' + dbname) client.create_database(dbname) print('Write DataFrame') client.write_points(df, 'demo', protocol=protocol) print('Write DataFrame with Tags') client.write_points(df, 'demo', { 'k1': 'v1', 'k2': 'v2' }, protocol=protocol) print('Read DataFrame') client.query('select * from demo') print('Delete database: ' + dbname) client.drop_database(dbname)
def test_write_points_from_dataframe(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) points = [ { "points": [ ["1", 1, 1.0, 0], ["2", 2, 2.0, 3600] ], "name": "foo", "columns": ["column_one", "column_two", "column_three", "time"] } ] with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series") cli = DataFrameClient(database='db') cli.write_points({"foo": dataframe}) self.assertListEqual(json.loads(m.last_request.body), points)
def test_write_points_from_dataframe_with_period_index(self): dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[pd.Period('1970-01-01'), pd.Period('1970-01-02')], columns=["column_one", "column_two", "column_three"]) points = [ { "points": [ ["1", 1, 1.0, 0], ["2", 2, 2.0, 86400] ], "name": "foo", "columns": ["column_one", "column_two", "column_three", "time"] } ] with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series") cli = DataFrameClient(database='db') cli.write_points({"foo": dataframe}) self.assertListEqual(json.loads(m.last_request.body), points)
def test_write_points_from_dataframe(self): """Test write points from df in TestDataFrameClient object.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) expected = ( b"foo column_one=\"1\",column_two=1i,column_three=1.0 0\n" b"foo column_one=\"2\",column_two=2i,column_three=2.0 " b"3600000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo') self.assertEqual(m.last_request.body, expected) cli.write_points(dataframe, 'foo', tags=None) self.assertEqual(m.last_request.body, expected)
def test_write_points_from_dataframe_with_tag_cols_and_global_tags(self): """Test write points from df w/tag + cols in TestDataFrameClient.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[['blue', 1, "1", 1, 1.0], ['red', 0, "2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["tag_one", "tag_two", "column_one", "column_two", "column_three"]) expected = ( b"foo,global_tag=value,tag_one=blue,tag_two=1 " b"column_one=\"1\",column_two=1i,column_three=1.0 " b"0\n" b"foo,global_tag=value,tag_one=red,tag_two=0 " b"column_one=\"2\",column_two=2i,column_three=2.0 " b"3600000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo', tag_columns=['tag_one', 'tag_two'], tags={'global_tag': 'value'}) self.assertEqual(m.last_request.body, expected)
def main(host='localhost', port=8086): """Instantiate the connection to the InfluxDB client.""" user = '******' password = '******' dbname = 'crypytest' # Temporarily avoid line protocol time conversion issues #412, #426, #431. protocol = 'json' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=(i for i in np.arange(30)), index=pd.date_range(start='2019-01-16', periods=30, freq='H'), columns=['bouh']) print(df) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo', protocol=protocol) print("Write DataFrame with Tags") client.write_points(df, 'demo', { 'k1': 'v1', 'k2': 'v2' }, protocol=protocol)
def test_write_points_from_dataframe_with_numeric_column_names(self): now = pd.Timestamp('1970-01-01 00:00+00:00') # df with numeric column names dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)]) expected = { 'database': 'db', 'points': [ {'fields': { '0': '1', '1': 1, '2': 1.0}, 'tags': {'hello': 'there'}, 'time': '1970-01-01T00:00:00+00:00', 'name': 'foo'}, {'fields': { '0': '2', '1': 2, '2': 2.0}, 'tags': {'hello': 'there'}, 'time': '1970-01-01T01:00:00+00:00', 'name': 'foo'}], } with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo", {"hello": "there"}) self.assertEqual(json.loads(m.last_request.body), expected)
def save_to_database(dataframe, user='******', password='******', host='localhost', port=8086): ''' 存pandas.DataFrame类型数据到influxdb数据库中 ''' from influxdb import DataFrameClient import time dbname = 'aiops' print("连接数据库,host={}, port={}, dbname={}, user={}".format( host, port, user, password, dbname)) client = DataFrameClient(host, port, user, password, dbname) # print("Create database: " + dbname) # client.create_database(dbname) tablename = 'sample_label' + time.strftime("_%Y%m%d_%H%M%S", time.localtime()) df = pd.DataFrame(data=np.array(dataframe), index=pd.date_range(start=time.strftime( "%Y-%m-%d", time.localtime()), periods=len(dataframe), freq='ms')) client.write_points(df, tablename, protocol='line') client.close()
def test_write_points_from_dataframe_with_tag_cols_and_global_tags(self): """Test write points from df w/tag + cols in TestDataFrameClient.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[['blue', 1, "1", 1, 1.0], ['red', 0, "2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=[ "tag_one", "tag_two", "column_one", "column_two", "column_three" ]) expected = (b"foo,global_tag=value,tag_one=blue,tag_two=1 " b"column_one=\"1\",column_two=1i,column_three=1.0 " b"0\n" b"foo,global_tag=value,tag_one=red,tag_two=0 " b"column_one=\"2\",column_two=2i,column_three=2.0 " b"3600000000000\n") with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo', tag_columns=['tag_one', 'tag_two'], tags={'global_tag': 'value'}) self.assertEqual(m.last_request.body, expected)
def main (host, port, csv): # Open the file and parse it pd.set_option('display.max_rows', 500) pd.set_option('display.max_columns', 500) pd.set_option('display.width', 1000) # Thanks to # https://stackoverflow.com/questions/29442370/how-to-correctly-read-csv-in-pandas-while-changing-the-names-of-the-columns print("Parsing csv...") headers = ['start_date', 'end_date', 'measurement_duration', 'detector_number', 'sensitivity', 'bubble_count', 'exposure', 'name'] data_frame = pd.read_csv(csv, parse_dates=True, index_col=0, names=headers, header=0) # Format the date time values data_frame.index = pd.to_datetime(data_frame.index, utc=True) print(data_frame); # Create the database connection # Thanks to the influxdb pip docs # https://influxdb-python.readthedocs.io/en/latest/examples.html#tutorials-basic print("Connecting to db...") client_connection = DataFrameClient(host=host, port=port, database=DB_NAME) print("Writing data...") client_connection.write_points(data_frame, DB_NAME, protocol="line") # Close the connection client_connection.close()
def main(host='localhost', port=8086): user = '******' password = '******' dbname = 'demo' # Temporarily used to avoid line protocol time conversion issues #412, #426, #431. protocol = 'json' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H')) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo', protocol=protocol) print("Write DataFrame with Tags") client.write_points(df, 'demo', { 'k1': 'v1', 'k2': 'v2' }, protocol=protocol) print("Read DataFrame") client.query("select * from demo") print("Delete database: " + dbname) client.drop_database(dbname)
class TelegrafLogger(StreamHandler): def __init__(self): StreamHandler.__init__(self) with open('keys/influx_settings.json') as json_file: data = json.load(json_file) self.client = DataFrameClient(host=data.get("host"), port=data.get("port"), username=data.get("username"), password=data.get("password")) self.client.switch_database(data.get("database")) def emit(self, record): # initialize list of lists data = [[ record.getMessage(), record.lineno, record.funcName, record.exc_info ]] df = pd.DataFrame( data, columns=['log_message', 'lineno', 'funcName', 'exc_info'], index=[datetime.utcnow()]) self.client.write_points(measurement="camp_export", dataframe=df, tags={"log_level": record.levelname})
def main(host='qwerty.com.ar', port=8086): """Instantiate the connection to the InfluxDB client.""" user = '******' password = '' dbname = 'pandas' protocol = 'line' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H'), columns=['0']) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo', protocol=protocol) print("Write DataFrame with Tags") client.write_points(df, 'demo', { 'k1': 'v1', 'k2': 'v2' }, protocol=protocol) print("Read DataFrame") client.query("select * from demo")
class DataGetter: def __init__(self, setting_file="db_settings.json"): """Load the settings file and connect to the DB""" # Get the current folder current_script_dir = "/".join(__file__.split("/")[:-3]) path = current_script_dir + "/" + setting_file logger.info("Loading the DB settings from [%s]" % path) # Load the settings with open(path, "r") as f: self.settings = json.load(f) logger.info( "Conneting to the DB on [{host}:{port}] for the database [{database}]" .format(**self.settings)) # Create the client passing the settings as kwargs self.client = InfluxDBClient(**self.settings) self.dfclient = DataFrameClient(**self.settings) def __del__(self): """On exit / delation close the client connetion""" if "client" in dir(self): self.client.close() def exec_query(self, query): # Construct the query to workaround the tags distinct constraint query = query.replace("\\", "\\\\") logger.debug("Executing query [%s]" % query) result = self.client.query(query, epoch="s") if type(result) == list: return [list(subres.get_points()) for subres in result] return list(result.get_points()) def get_measurements(self) -> List[str]: """Get all the measurements sul DB""" result = [x["name"] for x in self.client.get_list_measurements()] logger.info("Found the measurements %s" % result) return result def drop_measurement(self, measurement: str) -> None: self.client.drop_measurement(measurement) def write_dataframe(self, df, measurement, tags): self.dfclient.write_points(df, measurement, tags, time_precision="s") def get_tag_values(self, tag, measurement=None, constraints: dict = None): if measurement: query = """SHOW TAG VALUES FROM "{measurement}" WITH KEY = "{tag}" """.format( measurement=measurement, tag=tag) else: query = """SHOW TAG VALUES WITH KEY = "{tag}" """.format(tag=tag) if constraints: query += " WHERE " + " AND ".join("%s = '%s'" % v for v in constraints.items()) return [x["value"].strip("'") for x in self.exec_query(query)]
def create_db_add_points_example(num_points, batch_size): client = DataFrameClient(host, port, user, password, test_db) x = pd.DatetimeIndex(start=pd.Timestamp("2010-01-01"), periods=num_points, freq="S") y = pd.DataFrame(index=x, data=pd.np.random.randn(num_points, 1)) client.create_database(test_db) client.write_points(y, "perf-test", batch_size=batch_size, protocol='line') client.drop_database(test_db)
def write_points_from_csv_to_influx(filename: str, symbol: str, chunksize: int): """ Args: filename - e.g. "EURUSD_Ticks_06.01.2017-06.01.2017.csv" (with .csv extension) symbol - instrument pair e.g. "EURUSD". This will be the measurement in influxdb Returns: None """ client = DataFrameClient(host='localhost', port=8086) for df in pd.read_csv(filename, chunksize=chunksize, header=0, dtype='str'): print("df.shape", df.shape) print("df size", df.count()) print(df.head()) df['Timestamp'] = pd.to_datetime(df['Timestamp'], format="%Y%m%d %H:%M:%S:%f") df.set_index('Timestamp', inplace=True) client.write_points(df, time_precision='ms', measurement=symbol, database='dukascopy')
def main(host='localhost', port=8086): user = '******' password = '******' dbname = 'example' client = DataFrameClient(host, port, user, password, dbname) print("Create pandas DataFrame") df = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2014-11-16', periods=30, freq='H')) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame") client.write_points(df, 'demo') print("Write DataFrame with Tags") client.write_points(df, 'demo', {'k1': 'v1', 'k2': 'v2'}) print("Read DataFrame") client.query("select * from demo") print("Delete database: " + dbname) client.delete_database(dbname)
def write_influx(df, tags, host='localhost', port=8086): dbname = 'telegraf_agg' user, password = '******', 'dummy' client = DataFrameClient(host, port, user, password, dbname) #print(tags.items()) tag_columns = ['source', 'target', 'l_int'] client.write_points(df, 'h_interface_statistics', tag_columns=tag_columns)
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 update_to_latest(client: DataFrameClient, noncache_provider: typing.Callable, new_symbols: set = None, time_delta_back: relativedelta = relativedelta(years=5), skip_if_older_than: relativedelta = None): """ Update existing entries in the database to the most current values :param client: DataFrameClient client :param noncache_provider: Non cache data provider :param new_symbols: additional symbols to add {(symbol, interval_len, interval_type), ...}} :param time_delta_back: start :param skip_if_older_than: skip symbol update if the symbol is older than... :return: """ filters = dict() new_symbols = set() if new_symbols is None else new_symbols if skip_if_older_than is not None: skip_if_older_than = (datetime.datetime.utcnow().replace(tzinfo=tz.gettz('UTC')) - skip_if_older_than).astimezone(tz.gettz('US/Eastern')) for key, time in [(e[0], e[1][1]) for e in ranges(client).items()]: if key in new_symbols: new_symbols.remove(key) if skip_if_older_than is None or time > skip_if_older_than: bgn_prd = datetime.datetime.combine(time.date(), datetime.datetime.min.time()).replace(tzinfo=tz.gettz('US/Eastern')) filters[BarsFilter(ticker=key[0], bgn_prd=bgn_prd, interval_len=key[1], interval_type=key[2])] = None bgn_prd = datetime.datetime.combine(datetime.datetime.utcnow().date() - time_delta_back, datetime.datetime.min.time()).replace(tzinfo=tz.gettz('US/Eastern')) for (symbol, interval_len, interval_type) in new_symbols: filters[BarsFilter(ticker=symbol, bgn_prd=bgn_prd, interval_len=interval_len, interval_type=interval_type)] = None logging.getLogger(__name__).info("Updating " + str(len(filters)) + " total symbols and intervals; New symbols and intervals: " + str(len(new_symbols))) q = queue.Queue(maxsize=100) threading.Thread(target=partial(noncache_provider, filters=filters, q=q), daemon=True).start() try: for i, tupl in enumerate(iter(q.get, None)): ft, to_cache = filters[tupl[0]], tupl[1] if to_cache is not None and not to_cache.empty: # Prepare data for c in [c for c in to_cache.columns if c not in ['symbol', 'open', 'high', 'low', 'close', 'volume']]: to_cache.drop(c, axis=1, inplace=True) to_cache['interval'] = str(ft.interval_len) + '_' + ft.interval_type if to_cache.iloc[0].name == ft.bgn_prd: to_cache = to_cache.iloc[1:] try: client.write_points(to_cache, 'bars', protocol='line', tag_columns=['symbol', 'interval'], time_precision='s') except Exception as err: logging.getLogger(__name__).exception(err) if i > 0 and (i % 20 == 0 or i == len(filters)): logging.getLogger(__name__).info("Cached " + str(i) + " queries") finally: client.close()
def upload_to_influxdb(pd_obj=None, dbname='Test', measurement='Test', TEST=True, host_id='sw-wus-hx501q2'): chunk_size = 30000 start_row = 0 host = host_id port = 8086 """Instantiate the connection to the InfluxDB client.""" user = '******' password = '******' protocol = 'line' client = DataFrameClient(host, port, user, password, dbname) if TEST: print("Create pandas DataFrame") pd_obj = pd.DataFrame(data=list(range(30)), index=pd.date_range(start='2019-1-16', periods=30, freq='H'), columns=['测试']) print("Create database: " + dbname) client.create_database(dbname) print("Write DataFrame: " + measurement) (row_size, col_size) = pd_obj.shape # truncate the dataframe to 30000 entries per item before sending to influxdb while start_row < (row_size - 1): if (start_row + chunk_size) < row_size: print('size too large, chucking, start ' + str(start_row) \ + ' to ' + str(start_row + chunk_size)) response = client.write_points(pd_obj[start_row:(start_row + chunk_size)], measurement, protocol=protocol) start_row += chunk_size + 1 else: print('last bit, start ' + str(start_row) \ + ' to ' + str(row_size - 1)) response = client.write_points(pd_obj[start_row:row_size - 1], measurement, protocol=protocol) start_row = row_size - 1 print(response)
def threading_put_data(df, measurement_name, tags_dict): dbname = "CKG_QAR" str_IP_address = LOCAL().server_ip() user = '' password = '' client = DataFrameClient(str_IP_address, 8086, user, password, dbname) client.write_points(df, measurement = measurement_name, tags= tags_dict)
def test_write_points_from_dataframe_fails_with_series(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.Series(data=[1.0, 2.0], index=[now, now + timedelta(hours=1)]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series") cli = DataFrameClient(database='db') cli.write_points({"foo": dataframe})
def test_write_points_from_dataframe_fails_without_time_index(self): dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series") cli = DataFrameClient(database='db') cli.write_points({"foo": dataframe})
def connectTest(df): timeValues = df[['NAV']] # TODO: Add Tag # tags = { "AMFI": df[["AMFI"]]} # print('tags are: \n',tags) dbConnDF = DataFrameClient(host, port, user, password, dbName) dbConnDF.write_points(dataframe=df, measurement=tbName, field_columns=timeValues, database=dbName)
def test_write_points_from_dataframe_with_leading_none_column(self): """write_points detect erroneous leading comma for null first field.""" dataframe = pd.DataFrame(dict( first=[1, None, None, 8, 9], second=[2, None, None, None, 10], third=[3, 4.1, None, None, 11], first_tag=["one", None, None, "eight", None], second_tag=["two", None, None, None, None], third_tag=["three", "four", None, None, None], comment=[ "All columns filled", "First two of three empty", "All empty", "Last two of three empty", "Empty tags with values", ]), index=pd.date_range( start=pd.to_datetime('2018-01-01'), freq='1D', periods=5, )) expected = ( b'foo,first_tag=one,second_tag=two,third_tag=three' b' comment="All columns filled",first=1.0,second=2.0,third=3.0' b' 1514764800000000000\n' b'foo,third_tag=four' b' comment="First two of three empty",third=4.1' b' 1514851200000000000\n' b'foo comment="All empty" 1514937600000000000\n' b'foo,first_tag=eight' b' comment="Last two of three empty",first=8.0' b' 1515024000000000000\n' b'foo' b' comment="Empty tags with values",first=9.0,second=10.0' b',third=11.0' b' 1515110400000000000\n') with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') colnames = [ "first_tag", "second_tag", "third_tag", "comment", "first", "second", "third" ] cli.write_points( dataframe.loc[:, colnames], 'foo', tag_columns=["first_tag", "second_tag", "third_tag"]) self.assertEqual(m.last_request.body, expected)
def test_write_points_from_dataframe_fails_without_time_index(self): dataframe = pd.DataFrame( data=[["1", 1, 1.0], ["2", 2, 2.0]], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series") cli = DataFrameClient(database='db') cli.write_points({"foo": dataframe})
def exportToInfluxDB(serie, subnetItem, field): if config is None or config.InfluxDB is None: return None, "No InfluxDB config" try: client = DataFrameClient(config.InfluxDB['host'], config.InfluxDB['port'], config.InfluxDB['username'], config.InfluxDB['password'], config.InfluxDB['dbname']) client.write_points(serie,field, tags = {'enterprise': subnetItem['enterprise'], 'domain': subnetItem['domain'], 'zone': subnetItem['zone'], 'subnet':subnetItem['subnet']}) except Exception, e: return e
def random_shapeable_order(): # Retrieve random order df = randomorders.random_shapeable_orderbook() # Open connection and write to DB client = DataFrameClient(host, port, user, password, dbname) client.write_points(df, 'sbook') client.close() # Run optimization optimization() return {"status": "sucess"}
def forecast(times: List[str], values: List[float]): df = pandas.DataFrame(index=pandas.DatetimeIndex(times).round('5T'), data={'uncontr': values}) # Open connection and write to DB client = DataFrameClient(host, port, user, password, dbname) client.write_points(df, 'uncontr') client.close() # Run optimization optimization() return {"status": "sucess"}
def main(host='localhost', port=8086): logging.basicConfig(level=logging.INFO) """Instantiate the connection to the InfluxDB client.""" user = '******' password = '******' dbname = 'demo' protocol = 'json' client = DataFrameClient(host, port, user, password, dbname) logging.info("Create pandas DataFrame") today = datetime.datetime.today() date_list = get_week_list_by_date(today) df = pd.DataFrame(data=list(range(len(date_list))), index=date_list, columns=['W']) logging.info("Create database: " + dbname) client.create_database(dbname) logging.info("Write DataFrame to dsm_power table") client.write_points(df.copy(), 'dsm_power', tags={'device_id': 'II8583-Z5EKI-N9700'}, protocol=protocol) logging.info("Write DataFrame to electric_power table") client.write_points(df.copy(), 'electric_power', tags={'device_id': 'II8583-H9871-78D4F'}, protocol=protocol) logging.info("origin dataframe: {}".format(df)) logging.info("Read DataFrame from dsm_power table") fetch_df = client.query("select * from dsm_power")['dsm_power'] fetch_df.index = fetch_df.index.tz_localize(None) logging.info("fetch: {}".format(fetch_df)) logging.info("Read DataFrame from electric_power table") fetch_df = client.query("select * from electric_power")['electric_power'] fetch_df.index = fetch_df.index.tz_localize(None) logging.info("fetch: {}".format(fetch_df)) logging.info("get data by specfic time range") start_date = "2018-06-04" end_date = "2018-06-06" fetch_df = client.query("select * from dsm_power where time > '" + start_date + "' and time < '" + end_date + "'")['dsm_power'] fetch_df.index = fetch_df.index.tz_localize(None) logging.info("fetch: {}".format(fetch_df)) logging.info("Delete database: " + dbname) client.drop_database(dbname)
class BackfillLotus(object): def __init__(self): self.xdmod = XdMOD() self.client = DataFrameClient('influxdb1.jasmin.ac.uk', '8086', database='test') def today(self): return datetime.datetime.now().strftime('%Y-%m-%d') def get_metric(self, metric, start, end): param = self.xdmod.get_tsparam(metric, start_date=start, end_date=end) return param def get_daily(self, metric, start, end): # Gather the daily metric data for the period, which need to be done a month at a time curr_start = datetime.datetime.strptime(start,'%Y-%m-%d') curr_end = curr_start+datetime.timedelta(days=29) strf_end = curr_end.strftime('%Y-%m-%d') # get first dataframe data = [] # get data for each month and add to data frame while curr_end <= datetime.datetime.strptime(end,'%Y-%m-%d'): strf_start = curr_start.strftime('%Y-%m-%d') strf_end = curr_end.strftime('%Y-%m-%d') curr_df = self.get_metric(metric, strf_start, strf_end) curr_data = curr_df[metric] data.extend(curr_data) curr_start = curr_end+datetime.timedelta(days=1) curr_end = curr_start+datetime.timedelta(days=30) strf_start = curr_start.strftime('%Y-%m-%d') curr_df = self.get_metric(metric, strf_start, end) curr_data = curr_df[metric] data.extend(curr_data) return data def write_df_influxdb_daily(self,xdmod_metric,influx_metric,start,end): data = self.get_daily(xdmod_metric, start, end) df = pd.DataFrame(data=data, index=pd.date_range(start=start, end=end, freq='d'), columns=['gauge']) self.client.write_points(df, influx_metric, protocol='line') def write_df_influxdb_monthly(self,xdmod_metric,influx_metric,start,end): data = self.get_daily(xdmod_metric, start, end) df = pd.DataFrame(data=data, index=pd.date_range(start=start, end=end, freq='d'), columns=['gauge']) df_m = (df.groupby(pd.PeriodIndex(df.index, freq='m')).sum()) self.client.write_points(df_m, influx_metric, protocol='line')
class TimeSeriesClient(metaclass=Singleton): """ Time series object for interacting with the InfluxDB instance. Get/retrieve methods for time series data. """ def __init__(self): self.client = DataFrameClient(config.influx.host, config.influx.port, config.influx.user, config.influx.password, config.influx.name) self.protocol = 'json' self.trade_data_table = 'trade_data' def write_dataframe(self, dataframe, series, tags=None, field_columns=None, tag_columns=None, retention_policy=None): logger.debug('Wrote %s points to InfluxDB' % len(dataframe.index)) self.client.write_points(dataframe, series, tags, protocol=self.protocol, field_columns=field_columns, tag_columns=tag_columns, retention_policy=retention_policy) def get_last(self, exchange, symbol, measurement, **kwargs): """ Return most recently saved data point. """ _extra_filters = kwargs.get('extra_filters') _extra_sql = '' if _extra_filters: for key, val in _extra_filters.items(): _extra_sql += "and %s = '%s'" % (key, val) query_parameters = dict(measurement=measurement, exchange=exchange, symbol=symbol, extra_filters=_extra_sql) last_saved = self.client.query(""" select last(*) from %(measurement)s where exchange = '%(exchange)s' and symbol = '%(symbol)s' %(extra_filters)s """ % query_parameters) return_data = last_saved.get(measurement) if return_data is None: return_data = DataFrame() return return_data
def test_write_points_from_dataframe_fails_without_time_index(self): """Test failed write points from df without time index.""" dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/db/db/series", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo")
def write_habitslab_df_to_influx(pd_df, pId, studyName, dataType, userId): if pd_df is None: return # index of the dataframe pd_df['time'] = pd.to_datetime(pd_df['date']) pd_df = pd_df.set_index('time') pd_df['deviceId'] = "NL1" pd_df['pId'] = pId pd_df['studyName'] = studyName pd_df['type'] = dataType pd_df['userId'] = userId tags = {"deviceId": "NL1", "pId": pId, "studyId": studyName, "type": dataType, "userId": userId} dbConnDF = DataFrameClient(host='localhost', port=8086) dbConnDF.write_points(pd_df, 'habitsDB', tags=tags)
def test_dataframe_write_points_with_whitespace_in_column_names(self): """write_points should escape white space in column names.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column one", "column two", "column three"]) expected = ( b"foo column\\ one=\"1\",column\\ two=1i,column\\ three=1.0 0\n" b"foo column\\ one=\"2\",column\\ two=2i,column\\ three=2.0 " b"3600000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo') self.assertEqual(m.last_request.body, expected)
def test_write_points_from_dataframe_with_tag_escaped(self): """Test write points from df w/escaped tag in TestDataFrameClient.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame( data=[ ['blue orange', "1", 1, 'hot=cold'], # space, equal ['red,green', "2", 2, r'cold\fire'], # comma, backslash ['some', "2", 2, ''], # skip empty ['some', "2", 2, None], # skip None ['', "2", 2, None], # all tags empty ], index=pd.period_range(now, freq='H', periods=5), columns=["tag_one", "column_one", "column_two", "tag_three"] ) expected_escaped_tags = ( b"foo,tag_one=blue\\ orange,tag_three=hot\\=cold " b"column_one=\"1\",column_two=1i " b"0\n" b"foo,tag_one=red\\,green,tag_three=cold\\\\fire " b"column_one=\"2\",column_two=2i " b"3600000000000\n" b"foo,tag_one=some " b"column_one=\"2\",column_two=2i " b"7200000000000\n" b"foo,tag_one=some " b"column_one=\"2\",column_two=2i " b"10800000000000\n" b"foo " b"column_one=\"2\",column_two=2i " b"14400000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo', field_columns=['column_one', 'column_two'], tag_columns=['tag_one', 'tag_three']) self.assertEqual(m.last_request.body, expected_escaped_tags)
def test_write_points_from_dataframe_with_numeric_column_names(self): now = pd.Timestamp('1970-01-01 00:00+00:00') # df with numeric column names dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)]) expected = ( b'foo,hello=there 0=\"1\",1=1i,2=1.0 0\n' b'foo,hello=there 0=\"2\",1=2i,2=2.0 3600000000000\n' ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo", {"hello": "there"}) self.assertEqual(m.last_request.body, expected)
def test_write_points_from_dataframe_with_period_index(self): dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[pd.Period('1970-01-01'), pd.Period('1970-01-02')], columns=["column_one", "column_two", "column_three"]) expected = ( b"foo column_one=\"1\",column_three=1.0,column_two=1i 0\n" b"foo column_one=\"2\",column_three=2.0,column_two=2i " b"86400000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo") self.assertEqual(m.last_request.body, expected)
def test_write_points_from_dataframe_in_batches(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') self.assertTrue(cli.write_points(dataframe, "foo", batch_size=1))
def test_write_points_from_dataframe(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) expected = { 'database': 'db', 'points': [ {'time': '1970-01-01T00:00:00+00:00', 'fields': { 'column_two': 1, 'column_three': 1.0, 'column_one': '1'}, 'tags': {}, 'name': 'foo'}, {'time': '1970-01-01T01:00:00+00:00', 'fields': { 'column_two': 2, 'column_three': 2.0, 'column_one': '2'}, 'tags': {}, 'name': 'foo'}] } with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo') self.assertEqual(json.loads(m.last_request.body), expected) cli.write_points(dataframe, 'foo', tags=None) self.assertEqual(json.loads(m.last_request.body), expected)
def test_write_points_from_dataframe_with_numeric_precision(self): """Test write points from df with numeric precision.""" now = pd.Timestamp('1970-01-01 00:00+00:00') # df with numeric column names dataframe = pd.DataFrame(data=[["1", 1, 1.1111111111111], ["2", 2, 2.2222222222222]], index=[now, now + timedelta(hours=1)]) if numpy.lib.NumpyVersion(numpy.__version__) <= '1.13.3': expected_default_precision = ( b'foo,hello=there 0=\"1\",1=1i,2=1.11111111111 0\n' b'foo,hello=there 0=\"2\",1=2i,2=2.22222222222 3600000000000\n' ) else: expected_default_precision = ( b'foo,hello=there 0=\"1\",1=1i,2=1.1111111111111 0\n' b'foo,hello=there 0=\"2\",1=2i,2=2.2222222222222 3600000000000\n' # noqa E501 line too long ) expected_specified_precision = ( b'foo,hello=there 0=\"1\",1=1i,2=1.1111 0\n' b'foo,hello=there 0=\"2\",1=2i,2=2.2222 3600000000000\n' ) expected_full_precision = ( b'foo,hello=there 0=\"1\",1=1i,2=1.1111111111111 0\n' b'foo,hello=there 0=\"2\",1=2i,2=2.2222222222222 3600000000000\n' ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo", {"hello": "there"}) print(expected_default_precision) print(m.last_request.body) self.assertEqual(m.last_request.body, expected_default_precision) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo", {"hello": "there"}, numeric_precision=4) self.assertEqual(m.last_request.body, expected_specified_precision) cli = DataFrameClient(database='db') cli.write_points(dataframe, "foo", {"hello": "there"}, numeric_precision='full') self.assertEqual(m.last_request.body, expected_full_precision)
class DataSource(object): def __init__(self,host="localhost",port='8086',username="******",password="******",db_name=None,measurement=None): self.host = host self.port = port self.username = username self.password = password self.db_name = db_name self.measurement = measurement if self._check_influxdb_connected(host, port, username, password, db_name): self.client_api = DataFrameClient(host, port, username, password, db_name) 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_connected(self): return self._check_influxdb_connected(self.host,self.port,self.username,self.password,self.db_name) def _return(self): return self.host,self.port,self.username,self.password,self.db_name,self.measurement def query_analyzed(self, number_of_days =30): print "Get latest time series points" query_set = 'select value from %s order by time desc limit %s ;' % ( self.measurement, number_of_days * 24 * 60) result = self.client_api.query(query_set)[self.measurement] return result def query_all(self): print "Get latest time series points" query_set = 'select value from %s limit 10000;' % (self.measurement) result = self.client_api.query(query_set)[self.measurement] return result def update_db(self,data): result = False try: result = self.client_api.write_points(data,self.measurement) except Exception as e: print e.message return result
def test_write_points_from_dataframe_with_time_precision(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) points = { 'database': 'db', 'points': [ {'time': '1970-01-01T00:00:00+00:00', 'fields': { 'column_one': '1', 'column_three': 1.0, 'column_two': 1}, 'tags': {}, 'name': 'foo'}, {'time': '1970-01-01T01:00:00+00:00', 'fields': { 'column_one': '2', 'column_three': 2.0, 'column_two': 2}, 'tags': {}, 'name': 'foo'}] } cli = DataFrameClient(database='db') measurement = "foo" cli.write_points(dataframe, measurement, time_precision='s') points.update(precision='s') self.assertEqual(json.loads(m.last_request.body), points) cli.write_points(dataframe, measurement, time_precision='m') points.update(precision='m') self.assertEqual(json.loads(m.last_request.body), points) cli.write_points(dataframe, measurement, time_precision='u') points.update(precision='u') self.assertEqual(json.loads(m.last_request.body), points)
def test_write_points_from_dataframe_with_time_precision(self): now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[["1", 1, 1.0], ["2", 2, 2.0]], index=[now, now + timedelta(hours=1)], columns=["column_one", "column_two", "column_three"]) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') measurement = "foo" cli.write_points(dataframe, measurement, time_precision='h') self.assertEqual(m.last_request.qs['precision'], ['h']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\nfoo ' b'column_one="2",column_three=2.0,column_two=2i 1\n', m.last_request.body, ) cli.write_points(dataframe, measurement, time_precision='m') self.assertEqual(m.last_request.qs['precision'], ['m']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\nfoo ' b'column_one="2",column_three=2.0,column_two=2i 60\n', m.last_request.body, ) cli.write_points(dataframe, measurement, time_precision='s') self.assertEqual(m.last_request.qs['precision'], ['s']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\nfoo ' b'column_one="2",column_three=2.0,column_two=2i 3600\n', m.last_request.body, ) cli.write_points(dataframe, measurement, time_precision='ms') self.assertEqual(m.last_request.qs['precision'], ['ms']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\nfoo ' b'column_one="2",column_three=2.0,column_two=2i 3600000\n', m.last_request.body, ) cli.write_points(dataframe, measurement, time_precision='u') self.assertEqual(m.last_request.qs['precision'], ['u']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\nfoo ' b'column_one="2",column_three=2.0,column_two=2i 3600000000\n', m.last_request.body, ) cli.write_points(dataframe, measurement, time_precision='n') self.assertEqual(m.last_request.qs['precision'], ['n']) self.assertEqual( b'foo column_one="1",column_three=1.0,column_two=1i 0\n' b'foo column_one="2",column_three=2.0,column_two=2i ' b'3600000000000\n', m.last_request.body, )
pass try: del datei2['Kommentar'] except: pass datei2['Q-Resorber']=datei2['Q-Resorber']*1000 zeiti=pd.date_range(datumzeit, periods=len(datei2),freq='S') datei2.index=zeiti data = pd.DataFrame(datei2, index=zeiti) #tag=filename.replace('.csv','') client.write_points(data, messung,{'Filename': tag}) print 'Ok neu: %s'%(filename) zaehler=zaehler+1 except ValueError: e = sys.exc_info()[0] print "<p>Error: %s</p>%s" % (e,filename) #wenn vorhanden else: #print 'ist vorhanden' pass print 'OK'
pass try: del datei2['Kommentar'] except: pass try: datei2['Q-Resorber'] = datei2['Q-Resorber']*1000 except KeyError: pass zeiti = pd.date_range(datumzeit, periods=len(datei2), freq='S') datei2.index = zeiti data = pd.DataFrame(datei2, index=zeiti) # tag=filename.replace('.csv','') # print data.dtypes client.write_points(data, messung, {'Filename': tag, 'User': user}) print 'Ok neu: %s' % (filename) except: e = sys.exc_info()[0] print "<p>Error: %s</p>%s" % (e, filename) # wenn vorhanden else: # print 'ist vorhanden' pass print 'OK'
def test_write_points_from_dataframe_with_tag_cols_and_defaults(self): """Test default write points from df w/tag in TestDataFrameClient.""" now = pd.Timestamp('1970-01-01 00:00+00:00') dataframe = pd.DataFrame(data=[['blue', 1, "1", 1, 1.0, 'hot'], ['red', 0, "2", 2, 2.0, 'cold']], index=[now, now + timedelta(hours=1)], columns=["tag_one", "tag_two", "column_one", "column_two", "column_three", "tag_three"]) expected_tags_and_fields = ( b"foo,tag_one=blue " b"column_one=\"1\",column_two=1i " b"0\n" b"foo,tag_one=red " b"column_one=\"2\",column_two=2i " b"3600000000000\n" ) expected_tags_no_fields = ( b"foo,tag_one=blue,tag_two=1 " b"column_one=\"1\",column_two=1i,column_three=1.0," b"tag_three=\"hot\" 0\n" b"foo,tag_one=red,tag_two=0 " b"column_one=\"2\",column_two=2i,column_three=2.0," b"tag_three=\"cold\" 3600000000000\n" ) expected_fields_no_tags = ( b"foo,tag_one=blue,tag_three=hot,tag_two=1 " b"column_one=\"1\",column_two=1i,column_three=1.0 " b"0\n" b"foo,tag_one=red,tag_three=cold,tag_two=0 " b"column_one=\"2\",column_two=2i,column_three=2.0 " b"3600000000000\n" ) expected_no_tags_no_fields = ( b"foo " b"tag_one=\"blue\",tag_two=1i,column_one=\"1\"," b"column_two=1i,column_three=1.0,tag_three=\"hot\" " b"0\n" b"foo " b"tag_one=\"red\",tag_two=0i,column_one=\"2\"," b"column_two=2i,column_three=2.0,tag_three=\"cold\" " b"3600000000000\n" ) with requests_mock.Mocker() as m: m.register_uri(requests_mock.POST, "http://localhost:8086/write", status_code=204) cli = DataFrameClient(database='db') cli.write_points(dataframe, 'foo', field_columns=['column_one', 'column_two'], tag_columns=['tag_one']) self.assertEqual(m.last_request.body, expected_tags_and_fields) cli.write_points(dataframe, 'foo', tag_columns=['tag_one', 'tag_two']) self.assertEqual(m.last_request.body, expected_tags_no_fields) cli.write_points(dataframe, 'foo', field_columns=['column_one', 'column_two', 'column_three']) self.assertEqual(m.last_request.body, expected_fields_no_tags) cli.write_points(dataframe, 'foo') self.assertEqual(m.last_request.body, expected_no_tags_no_fields)
datei2 = datei2.fillna(0).replace([np.inf, -np.inf], np.nan) datei2 = datei2.fillna(0) try: del datei2['Zeit'] except: pass try: del datei2['Kommentar'] except: pass try: datei2['Q-Resorber']=datei2['Q-Resorber']*1000 except KeyError: pass zeiti=pd.date_range(datumzeit, periods=len(datei2),freq='S') datei2.index=zeiti data = pd.DataFrame(datei2, index=zeiti) data=data.astype('float64') #tag=filename.replace('.csv','') # print data.dtypes client.write_points(data, messung) print 'Ok neu: %s'%(filename) # e = sys.exc_info()[0] # print "<p>Error: %s</p>%s" % (e,filename)
from influxdb.exceptions import InfluxDBClientError client = DataFrameClient('localhost', 8086, 'root', 'root', 'hrs_experiment') try: print("Drop database") client.drop_database('hrs_experiment') except InfluxDBClientError: pass # Didn't already exist for dropping. print("Create database") client.create_database('hrs_experiment') write_times = [] for i in range(1, len(sys.argv)): print("Processing file: ", sys.argv[i], '...') station_id = 'Q' + os.path.basename(sys.argv[i]).split('_')[0] print("Using station ID: ", station_id, '...') df = pandas.read_csv(sys.argv[i], parse_dates=True, index_col=0, header = None) print("Creating JSON data...") print("Writing data...") start = time.time() for k, g in df.groupby(np.arange(len(df))//100): client.write_points(g, station_id) write_times.append(time.time() - start) print("Data written in {0} seconds".format(write_times[-1])) print("Sleeping for 30 seconds...") time.sleep(30) np.savetxt("influx_write_times.txt", np.array(write_times))
csvfilelist.append(os.path.join(dirpath, name)) data = pd.DataFrame() for csvfile in csvfilelist[1:2]: print(csvfile) with open(csvfile,'r') as f: #sensor_id;sensor_type;location;lat;lon;timestamp;P1;durP1;ratioP1;P2;durP2;ratioP2 #89;PPD42NS;47;48.827;9.129;2016-07-01T00:00:13.642743+00:00;24.59;13833;0.05;0.62;0;0.00 #89;PPD42NS;47;48.827;9.129;2016-07-01T00:00:44.365802+00:00;114.12;65580;0.22;0.62;0;0.00 data = pd.read_csv(f, delimiter=';') data.index=data.apply(lambda row: datetime.datetime.strptime(row.timestamp[:-6], "%Y-%m-%dT%H:%M:%S.%f"), axis=1) data=data.drop('timestamp',1) ifxc=DataFrameClient(ifxparm['host'],ifxparm['port'],ifxparm['user'],ifxparm['password'],ifxparm['db']) ifxc.write_points(data,'luftdaten.info',tags={'sensor_id':'0','sensor_type':0,'location':0}) #write_points(dataframe, measurement, tags=None, time_precision=None, database=None, retention_policy=None, batch_size=None) #ifxc.create_database(ifxparm['db']) #ldate = datetime.date.today() - datetime.timedelta(1) #for x in range(ndays): #dt = str(ldate - datetime.timedelta(x)) #url = "http://archive.madflex.de/{dt}/{dt}_ppd42ns_sensor_{sensor_id}.csv".format(dt=dt, sensor_id=sensor_id) #print(url) #r = requests.get(url) #data = data.append(pd.read_csv(StringIO(r.text), delimiter=';'))