def record_charging(self, vin, charging_status, charge_date, level, latitude, longitude): conn = get_db() if charging_status == "InProgress": try: in_progress = conn.execute( "SELECT stop_at FROM battery WHERE VIN=? ORDER BY start_at DESC limit 1", (vin, )).fetchone()[0] is None except TypeError: in_progress = False if not in_progress: conn.execute( "INSERT INTO battery(start_at,start_level,VIN) VALUES(?,?,?)", (charge_date, level, vin)) conn.commit() else: try: start_at, stop_at, start_level = conn.execute( "SELECT start_at, stop_at, start_level from battery WHERE VIN=? ORDER BY start_at " "DESC limit 1", (vin, )).fetchone() in_progress = stop_at is None if in_progress: co2_per_kw = Ecomix.get_co2_per_kw(start_at, charge_date, latitude, longitude) kw = (level - start_level ) / 100 * self.vehicles_list.get_car_by_vin( vin).battery_power conn.execute( "UPDATE battery set stop_at=?, end_level=?, co2=?, kw=? WHERE start_at=? and VIN=?", (charge_date, level, co2_per_kw, kw, start_at, vin)) conn.commit() except TypeError: logger.debug("battery table is empty") conn.close()
def download_song_posters(app): """ Download poster image from `image_url` """ WEB_PATH = path.join(path.dirname(path.abspath('__main__')), 'web') DOWNLOAD_PATH = WEB_PATH + url_for('static', filename='images') db = get_db(app) cursor = db.cursor() if not path.exists(DOWNLOAD_PATH): mkdir(DOWNLOAD_PATH) urls = PosterImage(cursor).select_urls() for url in urls: url = url[0] local_url = None try: print("Downloading image: ", url) local_url = download(url, out=DOWNLOAD_PATH) local_url = path.basename(local_url) PosterImage(cursor).update(url, local_url) db.commit() except Exception as error: print("Failed to download poster image: ", url) print(error) db.close()
def record_position(self, vin, mileage, latitude, longitude, date, level, level_fuel, moving): conn = get_db() if mileage == 0: # fix a bug of the api logger.error("The api return a wrong mileage for %s : %f", vin, mileage) else: if conn.execute("SELECT Timestamp from position where Timestamp=?", (date, )).fetchone() is None: temp = get_temp(latitude, longitude, self.weather_api) if level_fuel == 0: # fix fuel level not provided when car is off try: level_fuel = conn.execute( "SELECT level_fuel FROM position WHERE level_fuel>0 AND VIN=? ORDER BY Timestamp DESC " "LIMIT 1", (vin, )).fetchone()[0] logger.info( "level_fuel fixed with last real value %f for %s", level_fuel, vin) except TypeError: level_fuel = None logger.info("level_fuel unfixed for %s", vin) conn.execute( "INSERT INTO position(Timestamp,VIN,longitude,latitude,mileage,level,level_fuel,moving," "temperature) VALUES(?,?,?,?,?,?,?,?,?)", (date, vin, longitude, latitude, mileage, level, level_fuel, moving, temp)) conn.commit() logger.info("new position recorded for %s", vin) clean_position(conn) else: logger.debug("position already saved")
def fetch_page(page_number): collection = db.get_db()['inventory'] query = db.build_query(request.json) batch = collection.find(query).limit(RESULT_PER_PAGE).skip( (int(page_number) - 1) * RESULT_PER_PAGE) batch_cnt = collection.count_documents(query) return render_template('results.html', data=batch, page_cnt=batch_cnt, pages=range(math.ceil(batch_cnt / RESULT_PER_PAGE)), cur_page=int(page_number))
def fetch_login(): user_requested = list(db.get_db()['user'].find( {'email': request.form['email']}))[0] hashed_pwd = blake2b(str.encode(request.form['password']), digest_size=10) if user_requested is not None and user_requested[ 'password'] == hashed_pwd.hexdigest(): session['logged_in_user'] = { '_id': str(user_requested['_id']), 'name': user_requested['name'], 'email': user_requested['email'] } return json.dumps({'success': True}) return json.dumps({'success': False})
def save_rankings_to_db(ranking, app): """ Save ranking in song_rankings table """ db = get_db(app) try: cursor = db.cursor() for rank in ranking: song_name = rank.name artist_name = rank.artist youtube_id = rank.youtube_id source = rank.source song_rank = rank.ranking week = rank.week genre = rank.genre artist_ids = [] for artist in artist_name: artist_id = Artist(artist, 'singer') \ .insert(cursor).id artist_ids.append(artist_id) song_id = Song(song_name, None, None, None, None, youtube_id, artist_ids=artist_ids).insert(cursor).id genre_id = Genre(genre).insert(cursor).id SongGenre(song_id, genre_id).insert(cursor) for artist_id in artist_ids: SongArtist(song_id, artist_id).insert(cursor) SongRankings( song_id, artist_id, source, song_rank, week, ).insert(cursor) except IOError as error: print('Error while inserting new ranking ', error) finally: db.commit() db.close()
def get_chargings(min=None, max=None): conn = get_db() if min is not None: if max is not None: res = conn.execute( "select * from battery WHERE start_at>=? and start_at<=?", (min, max)).fetchall() else: res = conn.execute("select * from battery WHERE start_at>=?", (min, )).fetchall() elif max is not None: res = conn.execute("select * from battery WHERE start_at<=?", (max, )).fetchall() else: res = conn.execute("select * from battery").fetchall() return tuple(map(dict, res))
def get_trips() -> List[Trip]: conn = get_db() res = conn.execute( 'SELECT * FROM position ORDER BY Timestamp').fetchall() trips = [] if len(res) > 1: start = res[0] end = res[1] tr = Trip() #res = list(map(dict,res)) for x in range(0, len(res) - 2): next_el = res[x + 2] if end["mileage"] - start["mileage"] == 0 or \ (end["Timestamp"] - start["Timestamp"]).total_seconds() / 3600 > 3: start = end tr = Trip() else: distance = next_el["mileage"] - end["mileage"] # km duration = (next_el["Timestamp"] - end["Timestamp"]).total_seconds() / 3600 if ( distance == 0 and duration > 0.08 ) or duration > 2: # check the speed to handle missing point tr.distance = end["mileage"] - start["mileage"] # km if tr.distance > 0: tr.start_at = start["Timestamp"] tr.end_at = end["Timestamp"] tr.add_points(end["longitude"], end["latitude"]) tr.duration = ( end["Timestamp"] - start["Timestamp"]).total_seconds() / 3600 tr.speed_average = tr.distance / tr.duration diff_level = start["level"] - end["level"] tr.consumption = diff_level / 100 * BATTERY_POWER # kw tr.consumption_km = 100 * tr.consumption / tr.distance # kw/100 km # logger.debug( # f"Trip: {start['Timestamp']} {tr.distance:.1f}km {tr.duration:.2f}h {tr.speed_average:.2f} km/h {tr.consumption:.2f} kw {tr.consumption_km:.2f}kw/100km") # filter bad value if tr.consumption_km < 70: trips.append(tr) start = next_el tr = Trip() else: tr.add_points(end["longitude"], end["latitude"]) end = next_el return trips
def get_recorded_position(): from geojson import Feature, Point, FeatureCollection from geojson import dumps as geo_dumps conn = get_db() res = conn.execute('SELECT * FROM position ORDER BY Timestamp') features_list = [] for row in res: feature = Feature(geometry=Point( (row["longitude"], row["latitude"])), properties={ "vin": row["vin"], "date": row["Timestamp"], "mileage": row["mileage"], "level": row["level"] }) features_list.append(feature) feature_collection = FeatureCollection(features_list) conn.close() return geo_dumps(feature_collection, sort_keys=True)
def get_recorded_position(): conn = get_db() res = conn.execute('SELECT * FROM position ORDER BY Timestamp') features_list = [] for row in res: if row["longitude"] is None or row["latitude"] is None: continue feature = Feature(geometry=Point( (row["longitude"], row["latitude"])), properties={ "vin": row["vin"], "date": row["Timestamp"].strftime("%x %X"), "mileage": row["mileage"], "level": row["level"], "level_fuel": row["level_fuel"] }) features_list.append(feature) feature_collection = FeatureCollection(features_list) conn.close() return geo_dumps(feature_collection, sort_keys=True)
def post_register(): """process user registration form information. logs user in if successful Returns: json: success status and error message if failed """ # check if user with this email already exist # TODO: change error message after adding password retrieval function user_requested = list(db.get_db()['user'].find( {'email': request.form['email']})) if user_requested: return json.dumps({ 'success': False, 'message': "User already exist. Please contact site manager for password reset." }) # check access code if current_app.config['ACCESS_CODE'] != request.form['access-code']: return json.dumps({ 'success': False, 'message': "Wrong access code. Please try again." }) hashed_pwd = blake2b(str.encode(request.form['password']), digest_size=10) new_user_info = { "name": request.form['full-name'], "email": request.form['email'], "password": hashed_pwd.hexdigest(), "affiliation": request.form['affiliation'], "title": request.form['title'] } new_user_id = db.insert_new_user(new_user_info) return json.dumps({'success': True})
def record_info(self, vin, status: psac.models.status.Status): longitude = status.last_position.geometry.coordinates[0] latitude = status.last_position.geometry.coordinates[1] date = status.last_position.properties.updated_at mileage = status.timed_odometer.mileage level = status.energy[0].level charging_status = status.energy[0].charging.status moving = status.kinetic.moving conn = get_db() if mileage == 0: # fix a bug of the api logger.error( f"The api return a wrong mileage for {vin} : {mileage}") else: if conn.execute("SELECT Timestamp from position where Timestamp=?", (date, )).fetchone() is None: temp = None if self.weather_api is not None: try: weather_rep = requests.get( "https://api.openweathermap.org/data/2.5/onecall", params={ "lat": latitude, "lon": longitude, "exclude": "minutely,hourly,daily,alerts", "appid": "f8ee4124ea074950b696fd3e956a7069", "units": "metric" }) temp = weather_rep.json()["current"]["temp"] logger.debug(f"Temperature :{temp}c") except Exception as e: logger.error( f"Unable to get temperature from openweathermap :{e}" ) conn.execute( "INSERT INTO position(Timestamp,VIN,longitude,latitude,mileage,level, moving, temperature) VALUES(?,?,?,?,?,?,?,?)", (date, vin, longitude, latitude, mileage, level, moving, temp)) conn.commit() logger.info(f"new position recorded for {vin}") res = conn.execute( "SELECT Timestamp,mileage,level from position ORDER BY Timestamp DESC LIMIT 3;" ).fetchall() # Clean DB if len(res) == 3: if res[0]["mileage"] == res[1]["mileage"] == res[2][ "mileage"]: if res[0]["level"] == res[1]["level"] == res[2][ "level"]: logger.debug("Delete duplicate line") conn.execute( "DELETE FROM position where Timestamp=?;", (res[1]["Timestamp"], )) conn.commit() else: logger.debug("position already saved") # todo handle battery status charge_date = status.energy[0].updated_at if charging_status == "InProgress": try: in_progress = conn.execute( "SELECT stop_at FROM battery WHERE VIN=? ORDER BY start_at DESC limit 1", (vin, )).fetchone()[0] is None except TypeError: in_progress = False if not in_progress: res = conn.execute( "INSERT INTO battery(start_at,start_level,VIN) VALUES(?,?,?)", (charge_date, level, vin)) conn.commit() else: try: start_at, stop_at, start_level = conn.execute( "SELECT start_at, stop_at, start_level from battery WHERE VIN=? ORDER BY start_at " "DESC limit 1", (vin, )).fetchone() in_progress = stop_at is None if in_progress: co2_per_kw = Ecomix.get_co2_per_kw(start_at, charge_date, latitude, longitude) kw = (level - start_level) / 100 * BATTERY_POWER res = conn.execute( "UPDATE battery set stop_at=?, end_level=?, co2=?, kw=? WHERE start_at=? and VIN=?", (charge_date, level, co2_per_kw, kw, start_at, vin)) conn.commit() except TypeError: logger.debug("battery table is empty") except: logger.debug("Error when saving status " + traceback.format_exc()) conn.close()
def run_scrapers(app): """ Returns list of songs after running scrapers. """ song_count = 0 db = get_db(app) try: dj = DjpunjabScraper() for song in dj.parse(): if song: print("Got a song {}".format(song)) print('--------------------') song_count += 1 save_song_to_db(song, db) except Exception as e: print("DjpunjabScraper failed: ", e) try: jo = DjjohalScraper() for song in jo.parse(): if song: print("Got a song {}".format(song)) print('--------------------') song_count += 1 save_song_to_db(song, db) except Exception as e: print("DjjohalScraper failed: ", e) db.close() try: jt = JattjugadScraper() for song in jt.parse(): if song: print("Got a song {}".format(song)) print('---------------------') song_count += 1 save_song_to_db(song, db) except Exception as e: print("JattjugadScraper failed: ", e) try: mr = MrjattScraper() for song in mr.parse(): if song: print("Got a song {}".format(song)) print('---------------------') song_count += 1 save_song_to_db(song, db) except Exception as e: print("MrjattScraper failed: ", e) print('******************************') print('** SAVED {} SONGS TO DB ***'.format(song_count)) print('******************************')
def get_trips(vehicles_list: Cars) -> Dict[str, Trips]: conn = get_db() vehicles = conn.execute( "SELECT DISTINCT vin FROM position;").fetchall() trips_by_vin = {} for vin in vehicles: trips = Trips() vin = vin[0] res = conn.execute( 'SELECT * FROM position WHERE VIN=? ORDER BY Timestamp', (vin, )).fetchall() if len(res) > 1: car = vehicles_list.get_car_by_vin(vin) assert car is not None trip_parser = TripParser(car) start = res[0] end = res[1] tr = Trip() # for debugging use this line res = list(map(dict,res)) for x in range(0, len(res) - 2): logger.debugv("%s mileage:%.1f level:%s level_fuel:%s", res[x]['Timestamp'], res[x]['mileage'], res[x]['level'], res[x]['level_fuel']) next_el = res[x + 2] distance = end["mileage"] - start["mileage"] duration = (end["Timestamp"] - start["Timestamp"]).total_seconds() / 3600 try: speed_average = distance / duration except ZeroDivisionError: speed_average = 0 restart_trip = False if trip_parser.is_refuel(start, end, distance): restart_trip = True elif speed_average < 0.2 and duration > 0.05: restart_trip = True logger.debugv("low speed detected") if restart_trip: start = end tr = Trip() logger.debugv( "restart trip at %s mileage:%.1f level:%s level_fuel:%s", start['Timestamp'], start['mileage'], start['level'], start['level_fuel']) else: distance = next_el["mileage"] - end["mileage"] # km duration = (next_el["Timestamp"] - end["Timestamp"]).total_seconds() / 3600 try: speed_average = distance / duration except ZeroDivisionError: speed_average = 0 end_trip = False if trip_parser.is_refuel(end, next_el, distance): end_trip = True elif speed_average < 0.2 and duration > 0.05: # (distance == 0 and duration > 0.08) or duration > 2 or # check the speed to handle missing point end_trip = True logger.debugv("low speed detected") elif duration > 2: end_trip = True logger.debugv("too much time detected") elif x == len(res) - 3: # last record detected # think if add point is needed end = next_el end_trip = True logger.debugv("last position found") if end_trip: logger.debugv( "stop trip at %s mileage:%.1f level:%s level_fuel:%s", end['Timestamp'], end['mileage'], end['level'], end['level_fuel']) tr.distance = end["mileage"] - start[ "mileage"] # km if tr.distance > 0: tr.start_at = start["Timestamp"] tr.end_at = end["Timestamp"] tr.add_points(end["longitude"], end["latitude"]) if end["temperature"] is not None and start[ "temperature"] is not None: tr.add_temperature(end["temperature"]) tr.duration = ( end["Timestamp"] - start["Timestamp"]).total_seconds() / 3600 tr.speed_average = tr.distance / tr.duration diff_level, diff_level_fuel = trip_parser.get_level_consumption( start, end) tr.car = car if diff_level != 0: tr.set_consumption(diff_level) # kw if diff_level_fuel != 0: tr.set_fuel_consumption(diff_level_fuel) tr.mileage = end["mileage"] logger.debugv( "Trip: %s -> %s %.1fkm %.2fh %.0fkm/h %.2fkWh %.2fkWh/100km %.2fL " "%.2fL/100km %.1fkm", tr.start_at, tr.end_at, tr.distance, tr.duration, tr.speed_average, tr.consumption, tr.consumption_km, tr.consumption_fuel, tr.consumption_fuel_km, tr.mileage) # filter bad value trips.check_and_append(tr) start = next_el tr = Trip() else: tr.add_points(end["longitude"], end["latitude"]) end = next_el trips_by_vin[vin] = trips return trips_by_vin