def test_reject_suprious_values(self): # Create an empty database tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3') dbpath = tmpfile.name del(tmpfile) db = Database(dbpath) # Create a file with test data in it testfile = tempfile.NamedTemporaryFile(suffix='.csv', delete=False) testfile.write("""Ref, Date, Time, Wind 1, Wind 2, Direction, Irradiance Wm-2, Batt V BB,12-01-2016,10:00:00,1,2,W,0.10,4.72 BB,12-01-2016,10:01:00,1000000,2,N,0.20,4.72 BB,12-01-2016,10:02:00,1,1000000,E,0.30,4.70 BB,12-01-2016,10:03:00,1,2,E,1000000,4.80 BB,12-01-2016,10:04:00,1,2,E,0.30,4.90 """) testfile.close() # Add the test data to the test database db.add([testfile.name]) c = db._conn.cursor() c.execute("""SELECT * FROM event""") events = c.fetchall() print('EVENTS:', events) self.assertEqual(len(events), 1) del(db) os.remove(dbpath) os.remove(testfile.name)
def remove_data(args): d = Database(args.database_path) c = d._conn.cursor() filt = generate_filter(args, c) # populate the temp tables tmp_event_rids, tmp_raw_data_rids filt.select_events() filt.select_raw_data() c.execute("""SELECT rid FROM tmp_event_rids""") event_rowids = c.fetchall() c.execute("""SELECT rid FROM tmp_raw_data_rids""") raw_data_rowids = c.fetchall() if confirmation('Remove %d events and %d raw_date records (y/N)? ' % ( len(event_rowids), len(raw_data_rowids))): c.execute(""" DELETE FROM event WHERE EXISTS ( SELECT 1 FROM tmp_event_rids t WHERE t.rid = event.rowid ) """) c.execute(""" DELETE FROM raw_data WHERE EXISTS ( SELECT 1 FROM tmp_raw_data_rids t WHERE t.rid = raw_data.rowid ) """) c.execute(""" SELECT id, path FROM input_file WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE e.file_id = input_file.id ) AND NOT EXISTS ( SELECT 1 FROM raw_data r WHERE r.file_id = input_file.id )""") for r in c.fetchall(): print('Removing input file %s as it no longer has events / raw_data' % r[1]) c.execute(""" DELETE FROM input_file WHERE NOT EXISTS ( SELECT 1 FROM event e WHERE e.file_id = input_file.id ) AND NOT EXISTS ( SELECT 1 FROM raw_data r WHERE r.file_id = input_file.id )""") d.commit()
def setUpClass(cls): # Create an empty database tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3') cls._path = tmpfile.name del (tmpfile) cls._db = Database(cls._path) # Create a file with test data in it cls._testfile = tempfile.NamedTemporaryFile(suffix='.csv', delete=False) cls._testfile.write( """Ref, Date, Time, Wind 1, Wind 2, Direction, Irradiance Wm-2, Batt V BB,12-01-2016,19:34:10,1,2,W,0.10,4.72 BB,12-01-2016,19:34:11,1,2,N,0.20,4.72 BB,12-01-2016,19:34:15,1,2,E,0.30,4.70 BB,12-01-2016,19:34:16,1,2,S,0.40,4.72 BB,12-01-2016,19:34:17,1,2,SW,0.50,4.72 BB,13-01-2016,19:34:10,1,2,W,0.10,4.72 BB,13-01-2016,19:34:11,1,2,N,0.20,4.72 BB,13-01-2016,19:34:15,1,2,E,0.30,4.70 BB,13-01-2016,19:34:16,1,2,S,0.40,4.72 BB,13-01-2016,19:34:17,1,2,SW,0.50,4.72 """) cls._testfile.close() # Add the test data to the test database cls._db.add([cls._testfile.name])
def show_files(args): d = Database(args.database_path) def want(path, patterns): if len(patterns) == 0: return True dirname, basename = os.path.split(path) for pat in patterns: if fnmatch.fnmatch(basename, pat): return True return False print(args.files) print('%3s %-50s %19s %9s %9s' % ( 'ID', 'PATH', 'IMPORTED', 'RECORDS', 'ERRORS')) print(' '.join(['-' * l for l in [3, 50, 19, 9, 9]])) for r in d.list_input_files(): if want(r['path'], args.files): print('%3d %-50s %19s %9d %9d' % ( r['id'], r['path'], r['import_date'][:19], r['records'], r['errors']))
def test_open_existing_database(self): d, path = self.make_me_a_new_database() # Insert a marker record so we're not just getting a new DB after re-opening c = d._conn.cursor() c.execute("""CREATE TABLE test_open_existing_database (id INT)""") del (d) self.assertTrue(os.path.exists(path)) d = Database(path) self.assertTrue(d.schema_exists()) # Check we have our marker table c = d._conn.cursor() c.execute(""" SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'test_open_existing_database' """) self.assertEqual(len(c.fetchall()), 1) del (d) os.remove(path)
def test_open_existing_database(self): d, path = self.make_me_a_new_database() # Insert a marker record so we're not just getting a new DB after re-opening c = d._conn.cursor() c.execute("""CREATE TABLE test_open_existing_database (id INT)""") del(d) self.assertTrue(os.path.exists(path)) d = Database(path) self.assertTrue(d.schema_exists()) # Check we have our marker table c = d._conn.cursor() c.execute(""" SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'test_open_existing_database' """) self.assertEqual(len(c.fetchall()), 1) del(d) os.remove(path)
def calibrate(args): log.debug('calibrate(%s)' % args.ref) d = Database(args.database_path) c = d._conn.cursor() c.execute("""SELECT 1 FROM calibration WHERE ref = ?""", (args.ref,)) if len(c.fetchall()) > 0: log.debug('calibrate() updating existing record for ref %s' % args.ref) c.execute(""" UPDATE calibration SET anemometer_1_factor = ?, anemometer_2_factor = ?, max_windspeed_ms = ?, irradiance_factor = ?, max_irradiance = ? WHERE ref = ? """, (args.anemometer_1_factor, args.anemometer_2_factor, args.max_windspeed_ms, args.irradiance_factor, args.max_irradiance, args.ref)) else: log.debug('calibrate() inserting new record for ref %s' % args.ref) c.execute(""" INSERT INTO calibration ( ref, anemometer_1_factor, anemometer_2_factor, max_windspeed_ms, irradiance_factor, max_irradiance ) VALUES ( ?, ?, ?, ?, ?, ? ) """, (args.ref, args.anemometer_1_factor, args.anemometer_2_factor, args.max_windspeed_ms, args.irradiance_factor, args.max_irradiance)) d.commit()
def make_me_a_new_database(self): tmpfile = tempfile.NamedTemporaryFile(suffix='.sqlite3') path = tmpfile.name del (tmpfile) d = Database(path) return d, path
def add_files(args): d = Database(args.database_path) d.add(args.files)
def database_info(args): d = Database(args.database_path) info = d.info() for k in ['Database file', 'Size', 'Number of files added', 'Number of records']: print('%-30s%s' % (k + ':', info[k]))
def database_reset(args): d = Database(args.database_path) if confirmation(): d.reset() else: log.warning("Database reset ABORTED because confirmation not given")