Requêtes avancées (optionnel)

Note

Cette partie du TP est plus avancée. Elle n'est pas obligatoire, mais elle vous permettra de mieux comprendre les avantages de SQL et d'approfondir votre maîtrise du langage. En fonction de votre avancement, il est peut-être préfrable de faire ces exercices après avoir discuté et commencé la phase 3 en groupe.

Maintenant que vous disposez de la base de données complète, vous allez pouvoir écrire des requêtes SQL plus complexes. Ajoutez ces fonctions dans mobility/models/airport.py.

Important : l'objectif est de réaliser l'ensemble du traitement directement en SQL, en une seule requête, plutôt que de faire une requête simple puis traiter le résultat en Python. En effet, le moteur SQL est optimisé pour effectuer ces opérations efficacement, même sur de grandes quantités de données. Récupérer toutes les lignes en Python pour ensuite les trier ou les filtrer est beaucoup plus lent et consomme inutilement de la mémoire.

Avertissement

La base de données contient des aéroports dont les coordonnées (latitude_deg, longitude_deg) sont NULL. Si vous ne les filtrez pas, SQLite placera ces lignes en tête du résultat lors d'un ORDER BY croissant (les NULL sont considérés comme plus petits que toute valeur), et la version Python lèvera une TypeError en tentant d'effectuer des opérations arithmétiques sur None. Pensez à filtrer ces entrées avec IS NOT NULL en SQL, ou avec un if ... is None: continue en Python.

Pour vous en convaincre, vous pouvez utiliser le décorateur suivant pour mesurer le temps d'exécution de vos fonctions :

import time
import functools

def measure_time(func):
    @functools.wraps(func)
    def wrapper(*args, **kwargs):
        start = time.time()
        result = func(*args, **kwargs)
        end = time.time()
        print(f"\n{'='*50}\n⏱  {func.__name__} : {end - start:.4f} secondes\n{'='*50}\n", flush=True)
        return result
    return wrapper

Il suffit ensuite d'ajouter @measure_time au-dessus de la fonction que vous souhaitez mesurer :

@measure_time
def get_airports_by_country():
    ...

Comparez le temps d'exécution entre une version qui fait le travail en SQL et une version qui récupère toutes les données puis les traite en Python.

mobility/models/airport.py

def get_airports_by_country():
  # TODO
  # Retourne la liste des aéroports avec le nom du pays correspondant
  # Les résultats doivent être triés par ordre alphabétique du nom du pays,
  # puis par latitude décroissante au sein de chaque pays

def get_closest_airport(iata_code: str):
  # TODO
  # Retourne l'aéroport le plus proche de l'aéroport dont le code IATA est "iata_code"
  # Utilisez la distance euclidienne simplifiée :
  # d = (lat1 - lat2)² + (lon1 - lon2)²
  # Attention : certains aéroports n'ont pas de coordonnées dans la DB,
  # filtrez-les avec IS NOT NULL

def get_busiest_airports():
  # TODO
  # Retourne les 10 aéroports avec le plus grand nombre de vols au départ,
  # avec le nom du pays correspondant.
  # Utilisez la table "flight" (colonne iata_departure) et un COUNT + GROUP BY.

mobility/models/airport.py

Solution (Essayez d'abord par vous-même)

Version SQL (recommandée)

def get_airports_by_country():
   db = get_db()
   return db.execute(
      'SELECT airport.iata_code, airport.name, airport.latitude_deg, '
      'airport.longitude_deg, country.name AS country_name '
      'FROM airport '
      'JOIN country ON airport.iso_country = country.iso_country '
      'ORDER BY country.name, airport.latitude_deg DESC'
   ).fetchall()

def get_closest_airport(iata_code: str):
   db = get_db()
   return db.execute(
      'SELECT a2.iata_code, a2.name, a2.latitude_deg, a2.longitude_deg, a2.iso_country, '
      '(a1.latitude_deg - a2.latitude_deg) * (a1.latitude_deg - a2.latitude_deg) + '
      '(a1.longitude_deg - a2.longitude_deg) * (a1.longitude_deg - a2.longitude_deg) AS distance '
      'FROM airport a1, airport a2 '
      'WHERE a1.iata_code = ? AND a2.iata_code != a1.iata_code '
      'AND a2.latitude_deg IS NOT NULL AND a2.longitude_deg IS NOT NULL '
      'ORDER BY distance '
      'LIMIT 1',
      (iata_code,)
   ).fetchone()

def get_busiest_airports():
   db = get_db()
   return db.execute(
      'SELECT airport.iata_code, airport.name, country.name AS country_name, '
      'COUNT(flight.flight_id) AS nb_flights '
      'FROM airport '
      'JOIN country ON airport.iso_country = country.iso_country '
      'JOIN flight ON airport.iata_code = flight.iata_departure '
      'GROUP BY airport.iata_code '
      'ORDER BY nb_flights DESC '
      'LIMIT 10'
   ).fetchall()

Version Python naïve (pour comparaison de performance — uniquement pour get_closest_airport et get_busiest_airports )

def get_closest_airport_python(iata_code: str):
   db = get_db()
   airports = db.execute('SELECT * FROM airport').fetchall()

   origin = None
   for airport in airports:
      if airport["iata_code"] == iata_code:
         origin = airport
         break

   if origin is None:
      return None

   closest = None
   min_distance = float("inf")
   for airport in airports:
      if airport["iata_code"] == iata_code:
         continue
      if airport["latitude_deg"] is None or airport["longitude_deg"] is None:
         continue
      distance = ((origin["latitude_deg"] - airport["latitude_deg"]) ** 2 +
                  (origin["longitude_deg"] - airport["longitude_deg"]) ** 2)
      if distance < min_distance:
         min_distance = distance
         closest = airport

   return closest

def get_busiest_airports_python():
   db = get_db()
   flights = db.execute('SELECT * FROM flight').fetchall()
   airports = db.execute('SELECT * FROM airport').fetchall()
   countries = db.execute('SELECT * FROM country').fetchall()

   country_names = {c['iso_country']: c['name'] for c in countries}
   airport_info = {a['iata_code']: a for a in airports}

   counts = {}
   for f in flights:
      iata = f['iata_departure']
      counts[iata] = counts.get(iata, 0) + 1

   result = []
   for iata, nb in counts.items():
      if iata in airport_info:
         a = airport_info[iata]
         result.append({
            'iata_code': iata,
            'name': a['name'],
            'country_name': country_names.get(a['iso_country'], '?'),
            'nb_flights': nb
         })

   result.sort(key=lambda x: x['nb_flights'], reverse=True)
   return result[:10]

Ajoutez ensuite les routes correspondantes dans mobility/airport.py et les templates nécessaires pour afficher les résultats de ces requêtes.

mobility/airport.py

@bp.route('/airports_by_country')
def airports_by_country():
   # TODO
   # Affiche la liste des aéroports triés par pays puis par latitude

@bp.route('/closest_airport/<iata_code>')
def closest_airport(iata_code):
   # TODO
   # Affiche l'aéroport le plus proche de l'aéroport dont le code IATA est "iata_code"

@bp.route('/busiest_airports')
def busiest_airports():
   # TODO
   # Affiche le top 10 des aéroports les plus fréquentés

Note

N'oubliez pas de mettre à jour la ligne d'import en tête de mobility/airport.py pour y inclure les nouvelles fonctions :

from mobility.models.airport import get_airport_list, search_airport_by_iata_code, get_airports_by_country, get_closest_airport

mobility/airport.py

Solution (Essayez d'abord par vous-même)
from mobility.models.airport import get_airports_by_country, get_closest_airport

@bp.route('/airports_by_country')
def airports_by_country():
   airports = get_airports_by_country()
   return render_template("airports_by_country.html", airports=airports)

@bp.route('/closest_airport/<iata_code>')
def closest_airport(iata_code):
   airport = get_closest_airport(iata_code)
   return render_template("closest.html", iata_code=iata_code, airport=airport)

Créez ensuite les templates nécessaires pour afficher les résultats.

mobility/templates/airports_by_country.html

Solution (Essayez d'abord par vous-même)
{% extends "base.html" %}
{% block content %}

<h2>Airports by country</h2>
<table>
    <tr>
        <th>IATA code</th>
        <th>Name</th>
        <th>Latitude</th>
        <th>Longitude</th>
        <th>Country</th>
    </tr>
    {% for airport in airports %}
    <tr>
        <td>{{ airport['iata_code'] }}</td>
        <td>{{ airport['name'] }}</td>
        <td>{{ airport['latitude_deg'] }}</td>
        <td>{{ airport['longitude_deg'] }}</td>
        <td>{{ airport['country_name'] }}</td>
    </tr>
    {% endfor %}
</table>

{% endblock %}

mobility/templates/closest.html

Solution (Essayez d'abord par vous-même)
{% extends "base.html" %}
{% block content %}

<h2>Closest airport to {{ iata_code }}</h2>
{% if airport %}
<table>
    <tr>
        <th>IATA code</th>
        <th>Name</th>
        <th>Latitude</th>
        <th>Longitude</th>
        <th>Country</th>
    </tr>
    <tr>
        <td>{{ airport['iata_code'] }}</td>
        <td>{{ airport['name'] }}</td>
        <td>{{ airport['latitude_deg'] }}</td>
        <td>{{ airport['longitude_deg'] }}</td>
        <td>{{ airport['iso_country'] }}</td>
    </tr>
</table>
{% else %}
<p>No airport found.</p>
{% endif %}

{% endblock %}

Créez le template mobility/templates/busiest_airports.html pour afficher le classement :

mobility/templates/busiest_airports.html

Solution (Essayez d'abord par vous-même)
{% extends "base.html" %}
{% block content %}

<h2>Top 10 des aéroports les plus fréquentés</h2>
<table>
    <tr>
        <th>Rang</th>
        <th>IATA code</th>
        <th>Name</th>
        <th>Country</th>
        <th>Departures</th>
    </tr>
    {% for airport in airports %}
    <tr>
        <td>{{ loop.index }}</td>
        <td>{{ airport['iata_code'] }}</td>
        <td>{{ airport['name'] }}</td>
        <td>{{ airport['country_name'] }}</td>
        <td>{{ airport['nb_flights'] }}</td>
    </tr>
    {% endfor %}
</table>

{% endblock %}

Comparer les performances SQL et Python

Ajoutez les routes dédiées pour les versions Python naïve et pour l'aéroport le plus fréquenté dans mobility/airport.py. Commencez par mettre à jour la ligne d'import pour inclure toutes les nouvelles fonctions :

from mobility.models.airport import (get_airport_list, search_airport_by_iata_code,
                                     get_airports_by_country, get_closest_airport,
                                     get_closest_airport_python,
                                     get_busiest_airports, get_busiest_airports_python)

Ajoutez ensuite les trois nouvelles routes :

@bp.route('/closest_airport_python/<iata_code>')
def closest_airport_python_view(iata_code):
    airport = get_closest_airport_python(iata_code)
    return render_template("closest.html", iata_code=iata_code, airport=airport)

@bp.route('/busiest_airports')
def busiest_airports():
    airports = get_busiest_airports()
    return render_template("busiest_airports.html", airports=airports)

@bp.route('/busiest_airports_python')
def busiest_airports_python_view():
    airports = get_busiest_airports_python()
    return render_template("busiest_airports.html", airports=airports)

Décorez les fonctions avec @measure_time dans mobility/models/airport.py, puis lancez le serveur :

flask --app=mobility --debug run

Ouvrez votre navigateur et comparez les deux versions côte à côte :

Aéroport le plus proche

Top 10 des aéroports les plus fréquentés

Les deux URLs affichent le même résultat, mais le terminal affichera les temps d'exécution mesurés par @measure_time. La différence est particulièrement visible sur busiest_airports, qui traite 121 000 vols : SQL laisse le moteur agréger les données en C, tandis que Python charge toutes les lignes en mémoire et les parcourt une par une en Python pur.

Note

Les chiffres exacts dépendent de votre machine. Sur un Intel Core i7-1255U (12ᵉ génération) :

Fonction

Version SQL

Version Python

get_closest_airport

~0.002 s

~0.018 s

get_busiest_airports

~0.065 s

~0.24 s

La vitesse du CPU influencera les deux temps. Mais c'est surtout la taille de la base de données qui creuse l'écart : SQLite traite les données en interne et ne retourne à Python que le résultat final, tandis que la version naïve transfère l'intégralité des tables en mémoire Python avant de les traiter. Pour de très grands volumes, cette approche peut simplement devenir infaisable.

Continuez en lisant Continuez à développer!.