Danke an @tim für die inputs. Ich habe etwas weiter gearbeitet und eine (hoffentlich) verbesserte Version erstellt.
Die Zeit wird nun in UTC in der DB abgelegt und die Daten werden in sichererer (und schnellerer) weise in der DB gespeichert (wobei das Speichern in der DB schon vorher nicht den grössten Teil der Zeit beanspruchte).
Die neue Version nimmt die Ausführungszeiten der einzelnen Arbeitsschritte auf und zeigt diese am Ende an.
#!/usr/bin/python3
# transfer the data from the querx sesor into the mariadb / mysql-DB
# procedure:
# - get the time of the latest data in the database as a Unix Timestamp
# - forge a request string for the querx http interface and get the data in xml format
# - convert the data from xml to SQL and store it in the SQL DB
#
# by Marco Tedaldi, m.tedaldi aequator.ch 12.2021
# 20220113 MTE time is stored in UTC in DB
# 20220112 MTE switched from using mysql to mariadb
# split parsing and storing into two separate actions
# using .executemany to store all data in one go instead of using many separate SQL statements
# 20220104 MTE added collection and display of timing information
import sys
import mariadb # mariadb
import http.client # to get the data from the querx
import xml.etree.ElementTree as ET # to parse the XML from the querx
from datetime import datetime # to conver the timestamps to datetime for the DB
import time # to measure execution time
import math
# access information for the Querx (copied from https://www.egnite.de/support/tutorials/tutorial-zugriff-auf-querx-messdaten-mit-python/)
querx_address = "192.0.2.24" # name or address of the Querx Sensor
querx_http_port = 80
xml_url = "/tpl/document.cgi?tpl/j/datalogger.tpl&format=xml"
# databse access information
conn_params= {
"host" : "localhost",
"user" : "username",
"password" : "verysecurepassword",
"database" : "phys_data",
}
db_table = "ClimateData"
#copied from https://stackoverflow.com/questions/17973278/python-decimal-engineering-notation-for-mili-10e-3-and-micro-10e-6
# function to display values in engineering or technical notation
def eng_string(x, format='%s', si=False):
'''
Returns float/int value <x> formatted in a simplified engineering format -
using an exponent that is a multiple of 3.
format: printf-style string used to format the value before the exponent.
si: if true, use SI suffix for exponent, e.g. k instead of e3, n instead of
e-9 etc.
E.g. with format='%.2f':
1.23e-08 => 12.30e-9
123 => 123.00
1230.0 => 1.23e3
-1230000.0 => -1.23e6
and with si=True:
1230.0 => 1.23k
-1230000.0 => -1.23M
'''
sign = ''
if x < 0:
x = -x
sign = '-'
exp = int(math.floor(math.log10(x)))
exp3 = exp - (exp % 3)
x3 = round(x / (10 ** exp3), 2)
if si and exp3 >= -24 and exp3 <= 24 and exp3 != 0:
exp3_text = 'yzafpnum kMGTPEZY'[exp3 // 3 + 8]
elif exp3 == 0:
exp3_text = ''
else:
exp3_text = 'e%s' % exp3
return ('%s'+format+'%s') % (sign, x3, exp3_text)
# function to get the timestamp of the last entry
def get_lastTS(cursor):
try:
cursor.execute("SELECT UNIX_TIMESTAMP(MAX(Time)) FROM ClimateData")
result = cursor.fetchone()
ts = int(result[0])
except:
print("could not get timestamp from database, using 1.1.1970 00:00")
ts = 0
return(ts)
# copied from https://www.egnite.de/support/tutorials/tutorial-zugriff-auf-querx-messdaten-mit-python/
# adapted to get historical values
def get_xml(address, port, url):
try:
# Python 3.x: httplib => http.client
conn = http.client.HTTPConnection(address, port)
conn.request("GET", url)
res = conn.getresponse()
xml = res.read()
return(xml)
except Exception as e:
print("Error: " + str(e))
sys.exit(1)
# main program
if __name__ == "__main__":
runtime = [time.monotonic()]
print('connecting to SQL database')
db = mariadb.connect(**conn_params)
cursor = db.cursor()
runtime.append(time.monotonic())
print('connected to SQL database')
print('getting date and time of last entry')
lastdata = get_lastTS(cursor)
runtime.append(time.monotonic())
date = datetime.fromtimestamp(lastdata).strftime('%d.%m.%Y %H:%M:%S')
print('timestamp of last data: ' + str(lastdata) + " (" + date + ")")
xml_url = xml_url + "&start=" + str(lastdata) + "&step=300"
print("Getting data from http://" + querx_address + xml_url)
climatedata = get_xml(querx_address, querx_http_port, xml_url)
runtime.append(time.monotonic())
print("Data received, now starting to parse")
root = ET.fromstring(climatedata)
sensorname = root.findtext('hostname')
data = root.find('data')
print("Number of records = " + str(len(data) - 1))
datalist=[]
query = 'INSERT INTO ' + str(db_table) + ' VALUES (?,?'
for rnr, record in enumerate(data):
if rnr == 0:
for entry in record:
query = query + ',?'
query = query + ')'
else:
datatuple = ()
dtime = datetime.utcfromtimestamp(int(record.get('timestamp'))).strftime('%Y-%m-%d %H:%M:%S')
row = [ str(sensorname), dtime]
for entry in record:
row.append(str(entry.get('value')))
datatuple = tuple(row)
datalist.append(datatuple)
print('Records processed: ' + str(rnr), end='\r')
runtime.append(time.monotonic())
print("\nWriting data do DB")
cursor.executemany(query, datalist)
db.commit()
runtime.append(time.monotonic())
cursor.close()
db.close()
runtime.append(time.monotonic())
print('\n')
print("Runtimes:")
print("Total Runtime = " +
eng_string(runtime[5]-runtime[0], si=True) + "s")
print("Connecting to DB took " +
eng_string(runtime[1]-runtime[0], si=True) + "s")
print("Getting Timestamp of last entry took " +
eng_string(runtime[2]-runtime[1], si=True) + "s")
print("Getting Data from Querx took " +
eng_string(runtime[3]-runtime[2], si=True) + "s")
print("Parsing and converting the data took " +
eng_string(runtime[4]-runtime[3], si=True) + "s")
print("Storing the data to DB took " +
eng_string(runtime[5]-runtime[4], si=True) + "s")
print("Disconnecting from DB took " +
eng_string(runtime[6]-runtime[5], si=True) + "s")