We have a database in SQL Server which I want ultimately migrate to Influxdb. The data is mainly time series.
As a starting point I want to keep both databases for few months while we get confident with Influxdb and then migrate completely.
I build the following code:
import pypyodbc
import pandas as pd
import datetime
import time
import argparse
from influxdb import DataFrameClient
cnxn = pypyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
"Server=example\SQLEXPRESS;"
"Database=example;"
"uid=example;pwd=example")
cursor = cnxn.cursor()
df = pd.read_sql_query('SELECT TOP (1000) * FROM [something].[dbo].[something]', cnxn)
print(df)
client = DataFrameClient('localhost', 8086)
def convert_timestamps(timestamp):
return int(time.mktime(datetime.datetime.strptime(timestamp, '%y%m%d%H%M%S').timetuple()))
df['dptimestamp'] = df['dptimestamp'].apply(convert_timestamps)
df['dptimestamp'] = pd.DatetimeIndex(df['dptimestamp'])
df.index = df['dptimestamp']
print(df.index)
print(type(df['dptimestamp']))
print(df)
client.write_points(df, tag_columns={'devicedescription': df['devicedescription']}, database='Occupancy_system', measurement='dpvalue',time_precision='s')
I am using the pypyodbc in combination with pandas to query the data into a dataframe and them trying to save to Influxdb using client.write_points.
Overall I cant make to work and searching online I can see the influx dataframe method is not stable to write back to Influx.
the error I get with the above code is:
raise InfluxDBClientError(err_msg, response.status_code)
influxdb.exceptions.InfluxDBClientError: 400: {"error":"unable to parse following by the dataframe
open question:
is there a telegraph plugin to query SQL database and write the data?
is there any other way to achieve my task to your experience
Influxdb version 1.8
Thanks alot