openMaps.py

#!/usr/bin/python3
import json
import sys
import urllib
import time as sleepTimer
import mariadb
import requests
from requests.exceptions import HTTPError
from datetime import datetime, time

GOOGLE_URL = "https://maps.googleapis.com/maps/api/distancematrix/json"
GOOGLE_API_KEY_FRED = ""
GOOGLE_API_KEY_CHRIS = ""

RUSHHOUR_MORNING_START = time(6)
RUSHHOUR_MORNING_END = time(9)

RUSHHOUR_EVENING_START = time(16)
RUSHHOUR_EVENING_END = time(19)

DB_USER = ""
DB_PASSWORD = ""
DB_SERVERNAME = ""
DB_PORT = 3307
DB_DATABASE = "Stauvorhersage"
def get_google_maps_db_id():
    # Connect to MariaDB Platform
    try:
        with mariadb.connect(
                user=DB_USER,
                password=DB_PASSWORD,
                host=DB_SERVERNAME,
                port=DB_PORT,
                database=DB_DATABASE
        ) as conn:

            cursor = conn.cursor()
            cursor.execute('SELECT ID FROM Stauvorhersage.Datenquelle AS q WHERE q.Name="Google Maps"')
            for (id) in cursor:
                print(id)
                quelle_db_id = id[0]
        return quelle_db_id

    except mariadb.Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)

def get_origin_and_destinationlist():
    origin = None
    destinationList = []
    # Connect to MariaDB Platform
    try:
        with mariadb.connect(
                user=DB_USER,
                password=DB_PASSWORD,
                host=DB_SERVERNAME,
                port=DB_PORT,
                database=DB_DATABASE
        ) as conn:

            cursor = conn.cursor()
            cursor.execute(
                'SELECT ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode  FROM Stauvorhersage.Ort AS o WHERE o.ID="1"')
            for (ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode) in cursor:
                print([ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode])
                origin = [ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode]

            cursor.execute(
                'SELECT ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode FROM Stauvorhersage.Ort AS o WHERE o.ID>"1"')
            for (ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode) in cursor:
                print([ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode])
                destinationList.append([ID, Name, Strasse, Hausnummer, PLZ, Ort, PlusCode])

            print(destinationList)

        return origin, destinationList

    except mariadb.Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)

def get_google_matrix(origins, destination, api_key):
    try:
        response = requests.get(
            GOOGLE_URL,
            params={"unites": "metric",
                    "origins": origins,
                    "destinations": destination,
                    "departure_time": "now",
                    "key": api_key},
        )
        response.raise_for_status()
    except HTTPError as http_error:
        raise Exception(f"HTTP Error occurred: {http_error}")
    except Exception as error:
        raise Exception(f"Other Error occurred: {error}")
    else:
        print("Success: " + str(response.status_code))

    return response.json()


def in_between(now, start, end):
    if start <= now < end:
        return True
    else:
        return False


def run_maps(quelle_db_id, origin, destinationList, api_key):
    originrequest = ""
    if origin[6] == "":
        originrequest = origin[2] + " " + origin[3] + "," + origin[4] + "," + origin[5]
    else:
        originrequest = origin[5]
        originrequest = urllib.parse.quote_plus(originrequest)

    for destination in destinationList:
        destinationrequest = ""
        if destination[6] == "":
            destinationrequest = destination[2] + " " + destination[3] + "," + destination[4] + "," + destination[5]
        else:
            destinationrequest = destination[6]

        destinationrequest = urllib.parse.quote_plus(destinationrequest)
        print("Abfrage - Von: " + str(origin[1] + " Nach: " + str(destination[1])))
        api_query = "https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=" + originrequest + "&destinations=" + destinationrequest + "&departure_time=now&key=" + api_key
        print(api_query)
        with urllib.request.urlopen(api_query) as url:
            try:
                data = json.loads(url.read().decode())
                print(data)
                # data = json.loads('{"destination_addresses": ["C7XC+37 Bochum, Germany"], "origin_addresses": ["8XG9+X4 Heiligenhaus, Germany"], "rows": [{"elements": [{"distance": {"text": "44.6 km", "value": 44648}, "duration": {"text": "40 mins", "value": 2397}, "duration_in_traffic": {"text": "38 mins", "value": 2290}, "status": "OK"}]}], "status": "OK"}')

                print("Von: " + data['origin_addresses'][0])
                print("Nach: " + data['destination_addresses'][0])
                print("Entfernung: " + data['rows'][0]['elements'][0]['distance']['text'])
                print("Dauer: " + data['rows'][0]['elements'][0]['duration']['text'])
                print("Dauer mit Verkehr: " + data['rows'][0]['elements'][0]['duration_in_traffic']['text'])
                status = data['status']
                print("Status: " + status)
                if (status == "OK"):
                    try:
                        with mariadb.connect(
                            user=DB_USER,
                            password=DB_PASSWORD,
                            host=DB_SERVERNAME,
                            port=DB_PORT,
                            database=DB_DATABASE
                        ) as conn:
                            cursor = conn.cursor()
                            query = "INSERT INTO Streckenvorhersage (StartortID, ZielortID, Entfernung, Dauer, Datenquelle) VALUES  (%d, %d, %d, %d, %d)" % (
                                origin[0], destination[0], data['rows'][0]['elements'][0]['distance']['value'],
                                data['rows'][0]['elements'][0]['duration_in_traffic']['value'], quelle_db_id)
                            print(query)
                            cursor.execute(query)
                            conn.commit()
                    except mariadb.Error as e:
                        print(f"Error connecting to MariaDB Platform: {e}")
            except:
                print(f"Error while quering Google API")


if __name__ == "__main__":
    origin = None
    destinationList = []
    quelle_maps_db_id = get_google_maps_db_id()
    origin, destinationList = get_origin_and_destinationlist()

    while True:
        run_maps(quelle_maps_db_id, origin, destinationList[:6], GOOGLE_API_KEY_FRED) # immer 6 pro key
        run_maps(quelle_maps_db_id, origin, destinationList[6:], GOOGLE_API_KEY_CHRIS)

        if in_between(datetime.now().time(), RUSHHOUR_MORNING_START, RUSHHOUR_MORNING_END) or in_between(
                datetime.now().time(), RUSHHOUR_EVENING_START, RUSHHOUR_EVENING_END):
            sleepTimer.sleep(60 * 5)
        else:
            sleepTimer.sleep(60 * 30)