0

I wrote a python function for updating two tables of SQLite.db file with measurements from a Sensor. I store data every 15min on my Raspberry Pi.

Because of the limited hard disk space of the SD-Card (16GB), i need to delete the data dynamic. It is obviously that i need an algorithm which deletes the data after the first day (4 rows/hour), first week (1row/hour), first month(1row/2hour) and past month (1row/4hour). Finally all data, which are older then one year, should be deleted.

Here is the function for updating the data:

def database(save_to_db):        #save_to_db is a list with humidity[0] OR temperature[0] and integers of year[1], month[2], day[3], time[4]
conn = sqlite3.connect('database.db')                    #connect to database "database.db"
cur  = conn.cursor()                                     #create a cursor
#______Which measure is determined? ('%' = humidity, 'C' = temperature)_____
if '%' in save_to_db[0]:
#______Insert a row with index, humidity, date, time
    cur.execute(('''INSERT INTO hum VALUES (
                 NULL, ?,?,?,?,?)''')
                (save_to_db[0], save_to_db[1], save_to_db[2], save_to_db[3], save_to_db[4]))
elif 'C' in save_to_db[0]:
#______Insert a row with index, temperature, date, time
    cur.execute(('''INSERT INTO tem VALUES (
                 NULL, ?,?,?,?,?)'''),
                (save_to_db[0], save_to_db[1], save_to_db[2], save_to_db[3], save_to_db[4]))   
conn.commit()                                           #save the determined rows                                     
conn.close()                                            #close connection to DB

Now i want to query if a new day has begun. If this is true a new function should be called for deleting the data. It is not really dynamic, but i think it should be enough to delete the data one time a day. I have no idea how i can do this.

I am new to python and SQLite.

Niclas Resse
  • 31
  • 1
  • 9
  • There's a lot there... what specific step are you stuck on? – glibdud Feb 10 '17 at 13:28
  • First i want to call a function like 'delete_data_daily()'. It should be called if a new day has begin. If this works I want to delete the rows like this: today: 96 measurments (row) last week: 24 measurements (row) last month: 12 measurements (row) last year: 6 measurements (row) – Niclas Resse Feb 10 '17 at 13:38
  • But again, which part are you stuck on? How to trigger the function? How to write it? If both, those are two very different problems that probably deserve separate questions (and the latter at least could benefit from you showing what you've attempted). – glibdud Feb 10 '17 at 13:49
  • I stuck on the query to call the function. The query should be like this: if save_to_db[3] != (last row of table column [3]): then call function "delete_data_daily(save_to_db[3])" i dont know how i can query the day of the last row. – Niclas Resse Feb 10 '17 at 13:54

0 Answers0