Python program adatrögzítő SQL-be

Sziasztok!

 

Ti hogyan raknátok be egy adatrögzítő eredményeit egy SQL DB-be Python programmal?

Van esetleg valakinek erre bevált profi megoldása?

 

 

Az adatok kb.  így jönnek soros porton keresztül:

[]
['DHTxx', 'test!']
[]
[]
['Humidity:', '44.80%', 'Temperature:', '24.70°C', '76.46°F', 'Heat', 'index:', '24.40°C', '75.91°F']
[]
['Humidity:', '44.80%', 'Temperature:', '24.70°C', '76.46°F', 'Heat', 'index:', '24.40°C', '75.91°F']
[]
['Humidity:', '44.80%', 'Temperature:', '24.70°C', '76.46°F', 'Heat', 'index:', '24.40°C', '75.91°F']
[]
['Humidity:', '44.80%', 'Temperature:', '24.70°C', '76.46°F', 'Heat', 'index:', '24.40°C', '75.91°F']

Sajnos persze lehet adatvesztés, korrupt betűk stb..

 

Jelenleg ez a kis script-et tákoltam össze. Kiírja az adatokat, de nem tudom hogy lehet ezt szépen átrakni egy DB-be, hogy

- megmaradjon a két tizedes  + a mértékegységek is

- Hiba esetén csak dobja el a sort, ne rögzítsen semmit.

- User/Pass login a DB-be.

 

#!/usr/bin/env python3
import serial
import time

ser = serial.Serial('/dev/ttyUSB0', 9600, timeout=1)
ser.flushInput()
humidity = 0
temperature = 0
heatIndex = 0

while True:
ser_bytes = ser.readline()
#decoded_bytes = float(ser_bytes[0:len(ser_bytes)-2].decode("utf-8"))
decoded_bytes = ser_bytes[0:len(ser_bytes)-2].decode("utf-8")
#print(decoded_bytes)
list = decoded_bytes.split()
print(list)
time.sleep(5)
#humidity = list[0:2]
#temperature = list[3]
#heatIndex = list[6]
#print('Páratartalom:' + humidity)
#print('Hőmérséklet:' + temperature)
#print('HeatIndex:' + heatIndex)

Később majd a DB-ből szeretnénk web-es chartot rajzolni az adatokból.

Hozzászólások

Szia!

Először is nem hagyhatom ki a szokásos stackoverflow választ: 

Nem jó amit akarsz, ilyen adatokat kimondottan idősoros adatok tárolására való DB-be szoktak rakni (TSDB). (A valóság pedig az, hogy sokszor bőven jó ezt SQL-ben is, szóval ezzel nincs para.)

Hogy konstruktív legyek:

először is készíts egy adatbázis sémát amibe be tudod ezt illeszteni: vedd külön a számértéket a mértékegységtől, gondold át, hogy kell-e egyeltalán mértékegység (ha mindig csak celsiusban jön hőmérséklet, tök felesleges elrakni a mértékegységet, max odaírod az oszlop leírásába, hogy ezek itt celsius adatok)

Aztán a beérkező sorra érdemes lehet egy reguláris kifejezést írni, ez két okból is hasznos:

- ha a sor hibás, vagy nem tartalmaz minden adatot, akkor eleve megy a kukába, tehát ezzel már nem kell foglalkozni

- egyből ki tudod nyerni az értékeket változókba, amit aztán rögtön írhatsz a DB-be.

Végül, attól függően, hogy milyen SQL az az SQL, válassz egy python library-t amivel tudod kezelni, PostgreSQL-hez pl. ott a psycopg

Időpontot (timestampet) honnan párosítassz a mért adatokhoz? Jön valami dedikált deviceról, vagy a gépről, ahol a szkript fut?

Azért a konkrét projekttől nagyon függ a megoldás, pl ha több szenzorról gyűjtessz adatot, akkor kell valami szenzor ID, ha több locationről jön az adat, akkor át kell gondolni, hogy melyik időzónában van a timestamp, mennyire kritikus, hogy minden méréshez meglegyen minden adat, vagy fontosabb, hogy a részleges adatból is elmentődjön ami használható. Legyen e dedikálkt táblája a méréstípusoknak (esetleg mert másik szenzorról másmilyen adatok jönnek be), vagy akarod e generalizálni az adatstruktúrát: lásd tidy data

Ilyesmikre gondolok. 

Szerkesztve: 2020. 06. 09., k - 12:49

DB schema az első, ahogy ezt előttem is jelezték.

Mivel a Celsius / Fahrenheit egyértelműen konvertálható, elég csak az egyiket tárolni.

Én ezt tárolnám be:

  • time: timestamp vagy datetime
  • humidity: float
  • temperature: float
  • heat: float

Persze lehet csicsázni...

 

Python kód (write only módban, nincs tesztelve, mysql backenddel)

sqlconnect = {"host": db_host, "user": db_user, "password": db_pwd, "database": db_database}



def on_message(...):
	try:
		...
		sql = "INSERT INTO %s ('time', 'humidity', 'temperature', 'heat') VALUES('%s', '%f', '%f', '%f')" % ("tableName", timeStr, humidity, temperature, heat)

		db = my.connect(**sqlconnect)
		cursor = db.cursor()
		cursor.execute(sql)
	except my.Error as e:
		...
	finally:
		if (db.is_connected()):
			cursor.close()
			db.close()

 

 

Szerkesztve: 2020. 06. 09., k - 15:42

Hello. 

Én is éppen ilyesmivel szórakozom. :-)

SQLite adatbázist ajánlom, mert egyszerű pythonból kezelni, és ehhez tényleg nem kell semmi extra az adatbázismotortól. Én egyenlőre még .txt fájlban gyűjtöm az adatokat (csak a láthatóság miatt, hogy egyszerűen lehessen rápillantani bárhonnan) de ha lesz egy kis időm átirányítom az adatbázisba. Amit már elkészítettem, teszteltem csak az éles adatok nem oda mennek, egyenlőre manuálisan töltöm bele az adatokat.

Adatbázisban most csak számokat fogsz tárolni, a megfelelő formátumokban. Mértékegységet az oszlopok nevében lehet megadni csicsázás képpen. Aztán persze kiíratásnál lehet bármit utána írni...

SQLite adatbázis generálás, és írás bele, fájlból:

import sqlite3
from sqlite3 import Error
from readlines import read_data

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e) 
    return conn

def insertmany_data(conn, data):
    sql = '''INSERT INTO bme280_data (datetime_int, temperature, pressure, humidity)
             VALUES (strftime('%s', ?), ?, ?, ?);'''
    cur = conn.cursor()
    cur.executemany(sql, data)
    return cur.lastrowid


if __name__ == '__main__':
    database = r"home_temp.db"
    wtf = read_data('weather_BME280_sensor_data.txt')
    print(wtf[0], "...", "\n", "...", len(wtf), "...", "\n", "...",  wtf[-1])
    sql = '''INSERT INTO bme280_data (datetime_int, temperature, pressure, humidity)
             VALUES (strftime('%s', ?), ?, ?, ?);'''
    # create a database connection
    conn = sqlite3.connect("home_temp.db")
    cur = conn.cursor()

    cur.executemany(sql, wtf)
    conn.commit()
    conn.close()

Egyéb infok: #https://www.sqlitetutorial.net/sqlite-python/

Szerkesztve: 2020. 06. 09., k - 16:05

Sehogy. Erre valo barmelyik timeseries DB, mint Influxdb, Prometheus, Graphite, etc. (Influxdb ajanlott, mert a Prometheus nem rendelkezik reliable storage-el hosszu tavra; ha kell az adatok hosszu tavu tarolsa). Esetleg barmelyik nyers noSQl columnar store. De semmi esetre sem relacios adatbazis.

WARNING: https://www.influxdata.com/products/editions/  influxdb cluster kepes verzioja,

nem ingyenes . Ill mint lathato `nyilt core rendszer` flagje van, nem teljesen nyilt .

 

Egy 9600 baundos soros portos cummohoz, meg szinte barmi jo, semmi fancy nem kell.

Relacios adatbazsi is jo!
Csak, ne feledd az idexet az ido belyegen...

Amit nem lehet megirni assemblyben, azt nem lehet megirni.

Nem beszéltem clusteringrol. Reliable storage megoldásról beszéltem. Azt sajnos maga a Prometheus mögött álló team is elismeri, hogy olyat ők nem tudnak nyújtani. 

A 9600-as baudot elpazarolni sql-re meg igazan butaság lenne, ha van egyszerubb megoldás ami még sávszélességet is spórol. Mondjuk graphite. 

Az biztos hogy timeseriest sql-be rakni menő dolog, csak értelme nincs. :)

"Nem beszéltem clusteringrol. Reliable storage megoldásról beszéltem. Azt sajnos maga a Prometheus mögött álló team is elismeri, hogy olyat ők nem tudnak nyújtani. "

Talan resetnel tobb hetes adat sorkat dobal el ?
Nem hinnem.

Amije nincs prometheusnak az a beepitett  cluster / HA classikus ertelemben.

Storage reszt teheted HA -va es mesz Active-passive fele.

Illetve futatathatsz tobb peldanyt ami ugyan abbol az adatforrasbol taplalkozik (active-active).
Nem feltetlen lesz zsir ugyanaz mindegyik peldany adata, de mindegyik ~ perckent vett
adat sort fog tartalmazni, csak nem feltetlen ugyan azokbol nanosecundumokbol.

Szugseg esten lekerdezeskor ossze is fesulheted.

Ha egy node nem bir ell az osszes scrapperel, szet lehet dobni tobb nodra,
ami szinten `explicit` user config vagy automalizacio, nem beepitett feature .

prometheus szinten nincs felkeszulve nagyon nagy adat sorokra,
tipikusan 2~4 hetes  ,  ~10 sec felbontassal meg elbirja .

 

"Az biztos hogy timeseriest sql-be rakni menő dolog, csak értelme nincs. :)"

Azt hittem manapsag minden trendi ami nem SQL, meg ha hulyeseg is . ;-)

Ha valami kicsi egyszerure kene, talan:
https://oss.oetiker.ch/rrdtool/ (letezik python binding)

 

Amit nem lehet megirni assemblyben, azt nem lehet megirni.

Egyetertunk, de gondolom a kerdezo nem akar storage HA-t csinalni Prometheus ala. 

Egy sime egynodos Influxdb viszont hosszu tavra megoldja a tarolast a columnar storage-ben. Sajna nalunk volt mar, hogy a kethetes adatot sem tudtuk visszakerdezni a Prometheusbol, mert egyszeruen szetcseszodott valahogy. Torles segitett, csak igy nullarol kezdhettuk adott pillanatban gyujteni a logokat.

Na basszus a jo regi kis megbizhato rrd-t meg el is felejti az ember a timeseries miatt. Bahh, meg en is oregszem. :D

A lenyeg, hogy megint nem tudjuk mi a cel, csak azt tudjuk hogy o az adott eszkozokkel akarjha elerni akar jo az ugy akar nem. :D

Mi mondjuk pont az elastic-ot hasznaljuk mindenhol erre, de pssszt... :D

Mondjuk azert erdemes odafigyelni arra, hogy az elastic nem kimondottan timeseries-re van, ugye a storage engine a lucene a reverse indexing-re van (nem eveletlenul full-text search db-nek hivjak), azaz inkabb document store (persze van columnar resze is...de ez nem az). Persze lehet hasznalni es mondjuk aggregaciora messze jobb, mint a graphite, de ha nincs mas oka (ML, stb.) akkor en inkabb valamilyen columnar noSQL-t valasztanek, mert az pont erre jo/lett kitalalva.

Az is igaz, hogy en az ES-hez retek jobban ugyhogy valoszinuleg akkor is azt hasznalnam, ha tudom mas jobb lenne. :D

Nekem most egy ilyenem van:

rrdtool.create( rrdFile,
'--step', str( 300 ),
'--no-overwrite',
'DS:temperature:GAUGE:'+str(measure_interval*2)+':-273:5000',
'DS:humidity:GAUGE:'+str(measure_interval*2)+':0:100',
'RRA:AVERAGE:0.5:1:576', # 48h of 5min data
'RRA:AVERAGE:0.5:12:1440',# 1440 * 1 hour blocks = 60 days
'RRA:AVERAGE:0.5:288:1825', # 1824 * 1 day = 5 years
'RRA:AVERAGE:0.5:8640:600' # 600 * 1 Month = 50 years
)

Az egyedi értékeket ez nem is menti le, hanem csak az átlagokat állítgatja?

ha kicsit többet akarsz Pythonnal a jovoben adatbazisozni, szvsz megeri az SQLAlchemyt hasznalni

“The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it."
"Senkit nem hagyunk az ereszcsatornán!"

en is timeseries db-t javasolnek. ezt hasznalom itthon ilyesmire: https://graphiteapp.org/ . akar pythonnal is feedelheted, de van sok mas tool, pl collectd, amivel egyutt tud mukodni.