Python program adatrögzítő SQL-be

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. 

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()

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/

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.

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

Mellékszál, de küldjed ide (is): https://iotguru.cloud és kapsz belőle szép grafikonokat, mint ez: https://iotguru.cloud/field/gy8rYTk1GxtKm6cgAM0R5w/temperature

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.