75 lines
2.7 KiB
Python
75 lines
2.7 KiB
Python
|
import sqlite3
|
||
|
|
import configfile
|
||
|
|
from os import listdir
|
||
|
|
from os.path import isfile, join
|
||
|
|
from os import remove
|
||
|
|
from json import loads
|
||
|
|
import threading
|
||
|
|
import time
|
||
|
|
|
||
|
|
class Sqlite3_DB():
|
||
|
|
def __init__(self): #constructor to create database connection
|
||
|
|
|
||
|
|
conf = configfile.Configuration()
|
||
|
|
self.dbname=conf.db_file_name
|
||
|
|
self.conn = sqlite3.connect(self.dbname)
|
||
|
|
self.cursor = self.conn.cursor()
|
||
|
|
self.tablename = conf.table_name
|
||
|
|
self.days= conf.days_archive
|
||
|
|
try: #create table if still not exists
|
||
|
|
self.cursor.execute("CREATE TABLE %s(time_date datetime,sensor_data json)"%(conf.table_name))
|
||
|
|
self.conn.commit()
|
||
|
|
except:
|
||
|
|
self.tablecreated=1;
|
||
|
|
self.path=conf.path
|
||
|
|
self.count = 0;
|
||
|
|
|
||
|
|
#function to count the number of rows in SQL database
|
||
|
|
def sql_count(self):
|
||
|
|
self.cursor.execute("SELECT COUNT(sensor_data) FROM %s;"%(self.tablename))
|
||
|
|
numberOfRows = self.cursor.fetchone()[0]
|
||
|
|
return numberOfRows
|
||
|
|
|
||
|
|
#function to select the oldest files of the database
|
||
|
|
def select_oldest(self,number):
|
||
|
|
oldest_data= list()
|
||
|
|
for row in self.cursor.execute("SELECT sensor_data FROM %s LIMIT %d;"%(self.tablename,number)): #DESC FOR NEWES, ASC FOR OLDEST
|
||
|
|
oldest_data.append(row);
|
||
|
|
return(oldest_data)
|
||
|
|
|
||
|
|
#function to select the newest files of the database
|
||
|
|
def select_newest(self,number):
|
||
|
|
newest_data= list()
|
||
|
|
for row in self.cursor.execute("SELECT sensor_data FROM %s ORDER BY rowid DESC LIMIT %d;"%(self.tablename,number)): #DESC FOR NEWES, ASC FOR OLDEST
|
||
|
|
newest_data.append(row);
|
||
|
|
return(newest_data)
|
||
|
|
|
||
|
|
|
||
|
|
def remove_oldests_from_db(self,number):#remove number of old lines from DB
|
||
|
|
while(self.conn.in_transaction):#wait for previous transactions to be over
|
||
|
|
continue;
|
||
|
|
try:
|
||
|
|
self.cursor.execute("DELETE FROM %s LIMIT %d;"%(self.tablename,number)) # remove by order the oldest ones
|
||
|
|
i=0
|
||
|
|
self.conn.commit()
|
||
|
|
while(self.conn.in_transaction):
|
||
|
|
time.sleep(0.0001)
|
||
|
|
|
||
|
|
except:
|
||
|
|
print("Error on Deleting")
|
||
|
|
return;
|
||
|
|
|
||
|
|
def remove_newest_from_db(self,number):#remove number of new lines from DB
|
||
|
|
while(self.conn.in_transaction):#wait for previous transactions to be over
|
||
|
|
continue;
|
||
|
|
try:
|
||
|
|
self.cursor.execute("DELETE FROM %s ORDER BY rowid DESC LIMIT %d;"%(self.tablename,number)) # remove by order the newest ones
|
||
|
|
i=0
|
||
|
|
self.conn.commit()
|
||
|
|
while(self.conn.in_transaction):
|
||
|
|
time.sleep(0.001)
|
||
|
|
|
||
|
|
except:
|
||
|
|
print("Error on Deleting")
|
||
|
|
return;
|