Source Code for Module FlightReportManager.DataStoreController

  1  ''' 
  2  Created on Jun 29, 2016 
  3   
  4  @author: johanneskinzig 
  5  ''' 
  6   
  7  import sqlite3 
  8  import os 
  9   
 10  # TODO: possibly add sqlcipher component???? 
 11   
 12 -class DataStoreController(): 
 13      '''Handle the sqlite DataBase''' 
 14   
 15 -    def __init__(self, sqlitedatafile): 
 16          '''open sqlite db file''' 
 17          self.sqlitedatafile = sqlitedatafile 
 18          # check if file exists and set a flag 
 19          if os.path.isfile(self.sqlitedatafile): 
 20              db_exists = True 
 21          else: 
 22              db_exists = False 
 23          # try to open the DB file; it will be opened when it exists, otherwise it will be created 
 24          self.connectToDB() 
 25          # only create tables if file_exists_flag == False 
 26          if db_exists == False: 
 27              self.createTable() 
 28       
 29 -    def connectToDB(self): 
 30          ''' Open sqlite datafile for CRUD operations 
 31          ''' 
 32          try: 
 33              self.connection = sqlite3.connect(self.sqlitedatafile) 
 34              self.db_cursor = self.connection.cursor() 
 35          except: 
 36              print("Error reading/creating DB") # we need to be ready for python3 
 37           
 38 -    def commitCloseDB(self): 
 39          ''' Commit and close DB file 
 40          ''' 
 41          # commit and close 
 42          self.connection.commit() 
 43          self.connection.close() 
 44           
 45 -    def createTable(self): 
 46          ''' 
 47          Create tables, only called when DB is  not existing yet 
 48          ''' 
 49          print "Creating tables..." 
 50          # Create table and columns for flight data 
 51          self.db_cursor.execute('''CREATE TABLE `flights` ( 
 52          `FlightID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
 53          `event_name`    TEXT NOT NULL, 
 54          `city_nearby`    TEXT, 
 55          `pilot_location`    TEXT, 
 56          `date_time`    TEXT, 
 57          `total_distance`    REAL, 
 58          `max_altitude`    REAL, 
 59          `avg_speed`    REAL, 
 60          `flight_duration`    REAL, 
 61          `controller_type` TEXT, 
 62          `drone_type`    TEXT, 
 63          `battery_usage`    INTEGER, 
 64          `raw_data_file_name`    TEXT NOT NULL UNIQUE 
 65          );''') 
 66          # commit and close 
 67          self.commitCloseDB 
 68          print "Done creating tables" 
 69           
 70       
 71 -    def insertIntoTable(self, event_name, data_as_string): 
 72          """Insert new data into table (when importing only)""" 
 73          self.connectToDB() 
 74          self.db_cursor.execute("insert into flights (event_name, city_nearby, pilot_location, date_time, total_distance, max_altitude, avg_speed, flight_duration, controller_type, drone_type, battery_usage, raw_data_file_name) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (event_name, data_as_string[0].decode('utf-8'), data_as_string[1], data_as_string[2], data_as_string[3], data_as_string[4], data_as_string[5], data_as_string[6], data_as_string[7], data_as_string[8], data_as_string[9], data_as_string[10])) 
 75          self.commitCloseDB() 
 76           
 77 -    def updatRowNameInTable(self, flightid, new_name): 
 78          ''' 
 79          Update name of flight in table 
 80          ''' 
 81          self.connectToDB() 
 82          self.db_cursor.execute("UPDATE flights SET event_name=? WHERE FlightID=?", (new_name, flightid)) 
 83          self.commitCloseDB() 
 84           
 85 -    def deleteRowFromTable(self, flightid): 
 86          """Delete selected row from table""" 
 87          self.connectToDB() 
 88          self.db_cursor.execute("DELETE from flights WHERE FlightID=?", (flightid, )) 
 89          self.commitCloseDB() 
 90           
 91 -    def updateRowCityInTable(self, flightid, newCity): 
 92          """Update city""" 
 93          self.connectToDB() 
 94          self.db_cursor.execute("UPDATE flights SET city_nearby=? WHERE FlightID=?", (newCity.decode('utf-8'), flightid)) 
 95          self.commitCloseDB()         
 96       
 97 -    def readDataFromTable(self): 
 98          ''' Read whole data from table --> aim: display in GUI 
 99          ''' 
100          # connect to DB 
101          self.connectToDB() 
102          self.db_cursor.execute("SELECT * FROM flights") 
103          # get the rows! 
104          rows = self.db_cursor.fetchall() 
105          # close connection 
106          self.commitCloseDB()         
107          return rows 
108       
109 -    def getRowFromID(self, flightid): 
110          ''' 
111          Returns the row by a given flight id 
112          ''' 
113          # connect to DB 
114          self.connectToDB() 
115          self.db_cursor.execute("SELECT * FROM flights WHERE FlightID=?", (flightid, )) 
116          # get the row! -- only one row because FlightID is primary key and therefore unique 
117          row = self.db_cursor.fetchone() 
118          # close connection 
119          self.commitCloseDB() 
120          return row 
121           
122               
123  if __name__ == '__main__': 
124      print "Init, start!" 
125      # generate test db 
126      mydb = DataStoreController('test-gui.db') 
127       
128      #from DroneDataConversion import BebopFlightDataManager  
129       
130      #filepath = "DroneDataFiles/BebopThirdFlight_2016_06_26.json" 
131      #print("File: " + filepath) 
132      #testFlightManager = BebopFlightDataManager(filepath) 
133      #mydb.insertIntoTable(testFlightManager.diagnostic_information_raw()) 
134      #print "Done writing Data to table" 
135       
136      test = mydb.readDataFromTable() 
137      for row in test: 
138          print row 
139       
140      mydb.updatRowNameInTable(1, "super1") 
141