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_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 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=';'))
