def insert_coordinates(license_plate, travel_date, lat, lon): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() cursor.execute("INSERT INTO public.\"Posizioni_Mezzi\"(mezzo, data_viaggio, lat, lon) " "VALUES ('{0}', '{1}', {2}, {3})".format(license_plate, travel_date, lat, lon)) connection.commit() connection.close()
def sign_up(name, surname, email, username, user_password): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() cursor.execute("INSERT INTO public.\"Utenti\"(username, email, nome, cognome, password) VALUES (" "'{0}','{1}','{2}','{3}','{4}')".format(username, email, name, surname, user_password)) connection.commit() connection.close()
def insert_movement(id_fitness, id_mezzo, lat, lon, luogo=''): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() cursor.execute("INSERT INTO public.\"Spostamenti\"(id_fitness, id_mezzo, luogo, lat, lon) " "VALUES ('{0}', {1}, '{2}', {3}, {4})".format(id_fitness, id_mezzo, luogo, lat, lon)) connection.commit() connection.close()
def insert_route(lat_ori, lon_ori, lat_dest, lon_dest, distanza, durata, origine='', destinazione=''): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() cursor.execute("INSERT INTO public.\"Itinerari\"(origine, lat_origine, lon_origine, destinazione," " lat_destinazione, lon_destinazione, distanza, durata) VALUES ('{0}',{1},{2},'{3}',{4},{5},{6},{7})" "".format(origine, lat_ori, lon_ori, destinazione, lat_dest, lon_dest, distanza, durata)) connection.commit() connection.close()
def insert_request(lat_departure, lon_departure, lat_arrival, lon_arrival, time_departure, time_arrival, request_user, departure='', arrival=''): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() cursor.execute("INSERT INTO public.\"Richieste\"(origine, lat_origine, lon_origine, destinazione, " "lat_destinazione, lon_destinazione, ora_partenza, ora_arrivo, utente) " "VALUES('{0}', {1}, {2}, '{3}', {4}, {5}, '{6}', '{7}', '{8}') RETURNING id".format(departure, lat_departure, lon_departure, arrival, lat_arrival, lon_arrival, time_departure, time_arrival, request_user)) request_inserted_id = cursor.fetchone()[0] connection.commit() connection.close() return request_inserted_id
def get_coordiantes(license_plate, travel_date): connection = DBConnection(host, port, database, user, password).connect() cursor = connection.cursor() date_from = "{0} 00:00:00".format(travel_date) date_to = "{0} 23:59:59".format(travel_date) cursor.execute("SELECT lat, lon FROM public.\"Posizioni_Mezzi\" WHERE mezzo = '{0}' AND " "(data_viaggio, data_viaggio) OVERLAPS ('{1}', '{2}')".format(license_plate, date_from, date_to)) data = cursor.fetchall() coordinates = [] for row in data: coordinates.append([row[0], row[1]]) connection.commit() connection.close() return coordinates