import sqlite3 as sl class SQLite(): def __init__(self, database): self.client = sl.connect(database) self.client.execute(""" CREATE TABLE IF NOT EXISTS KPI( kpi_id INTEGER PRIMARY KEY AUTOINCREMENT, kpiDescription TEXT, device_id INTEGER, kpi_sample_type INTEGER ); """) def insert_KPI(self,kpiDescription,device_id,kpi_sample_type): c = self.client.cursor() c.execute("SELECT kpi_id FROM KPI WHERE device_id is ? AND kpi_sample_type is ?",(device_id,kpi_sample_type)) data=c.fetchone() if data is None: c.execute("INSERT INTO KPI (kpiDescription, device_id,kpi_sample_type) VALUES (?,?,?)", (kpiDescription,device_id,kpi_sample_type)) self.client.commit() return c.lastrowid else: return data[0] def delete_KPI(self,device_id,kpi_sample_type): c = self.client.cursor() c.execute("SELECT kpi_id FROM KPI WHERE device_id is ? AND kpi_sample_type is ?",(device_id,kpi_sample_type)) data=c.fetchone() if data is None: return False else: c.execute("DELETE FROM KPI WHERE device_id is ? AND kpi_sample_type is ?",(device_id,kpi_sample_type)) self.client.commit() return True def delete_kpid_id(self,kpi_id): c = self.client.cursor() c.execute("SELECT * FROM KPI WHERE kpi_id is ?",(kpi_id,)) data=c.fetchone() if data is None: return False else: c.execute("DELETE FROM KPI WHERE kpi_id is ?",(kpi_id,)) self.client.commit() return True def get_KPI(self,kpi_id): data = self.client.execute("SELECT * FROM KPI WHERE kpi_id is ?",(kpi_id,)) return data.fetchone() def get_KPIS(self): data = self.client.execute("SELECT * FROM KPI") for row in data: print(row)