import requests import mysql.connector from time import sleep # Connexion à MySQL conn = mysql.connector.connect( host="localhost", user="root", password="_z7b6CJc", database="immo_new" ) cursor = conn.cursor() # Fonction pour récupérer les quartiers d'une ville depuis OSM def get_districts_from_osm(city_name): overpass_url = "https://overpass-api.de/api/interpreter" query = f""" [out:json]; area["name"="{city_name}"]->.searchArea; ( way["place"="neighbourhood"](area.searchArea); relation["place"="neighbourhood"](area.searchArea); ); out center; """ try: response = requests.get(overpass_url, params={'data': query}) data = response.json() districts = [] for element in data['elements']: if 'tags' in element and 'name' in element['tags']: districts.append({ 'name': element['tags']['name'], 'lat': element['center']['lat'], 'lon': element['center']['lon'] }) return districts except Exception as e: print(f"Erreur pour {city_name}: {e}") return [] grandes_villes = [ {"nom": "Paris", "insee_code": "75056"}, {"nom": "Marseille", "insee_code": "13055"}, {"nom": "Lyon", "insee_code": "69123"}, # Lyon (commune), pas un arrondissement {"nom": "Toulouse", "insee_code": "31555"}, {"nom": "Nice", "insee_code": "06088"}, {"nom": "Nantes", "insee_code": "44109"}, {"nom": "Montpellier", "insee_code": "34172"}, {"nom": "Strasbourg", "insee_code": "67482"}, {"nom": "Bordeaux", "insee_code": "33063"}, {"nom": "Lille", "insee_code": "59350"}, {"nom": "Rennes", "insee_code": "35238"}, {"nom": "Reims", "insee_code": "51454"}, {"nom": "Le Havre", "insee_code": "76351"}, {"nom": "Saint-Étienne", "insee_code": "42218"}, {"nom": "Toulon", "insee_code": "83137"}, {"nom": "Grenoble", "insee_code": "38185"}, {"nom": "Dijon", "insee_code": "21231"}, {"nom": "Nîmes", "insee_code": "30189"}, {"nom": "Angers", "insee_code": "49007"}, {"nom": "Villeurbanne", "insee_code": "69266"}, {"nom": "Saint-Denis", "insee_code": "93066"}, {"nom": "Le Mans", "insee_code": "72181"}, {"nom": "Aix-en-Provence", "insee_code": "13001"}, {"nom": "Brest", "insee_code": "29019"}, {"nom": "Limoges", "insee_code": "87085"}, {"nom": "Tours", "insee_code": "37261"}, {"nom": "Amiens", "insee_code": "80021"}, {"nom": "Perpignan", "insee_code": "66136"}, {"nom": "Metz", "insee_code": "57463"}, {"nom": "Besançon", "insee_code": "25056"}, {"nom": "Boulogne-Billancourt", "insee_code": "92012"}, {"nom": "Orléans", "insee_code": "45234"}, {"nom": "Mulhouse", "insee_code": "68224"}, {"nom": "Rouen", "insee_code": "76540"}, {"nom": "Saint-Paul", "insee_code": "97450"}, # La Réunion {"nom": "Caen", "insee_code": "14118"}, {"nom": "Argenteuil", "insee_code": "95018"}, {"nom": "Montreuil", "insee_code": "93048"}, {"nom": "Nancy", "insee_code": "54395"}, {"nom": "Roubaix", "insee_code": "59512"}, {"nom": "Tourcoing", "insee_code": "59599"}, {"nom": "Nanterre", "insee_code": "92050"}, {"nom": "Avignon", "insee_code": "84007"}, {"nom": "Vitry-sur-Seine", "insee_code": "94081"}, {"nom": "Créteil", "insee_code": "94028"}, {"nom": "Dunkerque", "insee_code": "59183"}, {"nom": "Poitiers", "insee_code": "86194"}, {"nom": "Asnières-sur-Seine", "insee_code": "92004"}, {"nom": "Courbevoie", "insee_code": "92026"}, {"nom": "Versailles", "insee_code": "78646"}, {"nom": "Colombes", "insee_code": "92025"}, {"nom": "Fort-de-France", "insee_code": "97209"}, # Martinique {"nom": "Aulnay-sous-Bois", "insee_code": "93005"}, {"nom": "Rueil-Malmaison", "insee_code": "92063"}, {"nom": "Saint-Pierre", "insee_code": "97416"}, # La Réunion {"nom": "Pau", "insee_code": "64445"}, {"nom": "La Rochelle", "insee_code": "17300"}, {"nom": "Saint-Maur-des-Fossés", "insee_code": "94068"}, {"nom": "Calais", "insee_code": "62193"}, {"nom": "Antony", "insee_code": "92002"}, {"nom": "Cayenne", "insee_code": "97302"}, # Guyane {"nom": "Clermont-Ferrand", "insee_code": "63113"}, {"nom": "Le Tampon", "insee_code": "97422"}, # La Réunion {"nom": "Béziers", "insee_code": "34032"}, {"nom": "Aubervilliers", "insee_code": "93001"}, {"nom": "Colmar", "insee_code": "68066"}, {"nom": "Quimper", "insee_code": "29232"}, {"nom": "Valence", "insee_code": "26362"}, {"nom": "Champigny-sur-Marne", "insee_code": "94017"}, {"nom": "Vénissieux", "insee_code": "69259"}, {"nom": "Cergy", "insee_code": "95127"}, {"nom": "Saint-Nazaire", "insee_code": "44184"}, {"nom": "Issy-les-Moulineaux", "insee_code": "92050"}, {"nom": "Noisy-le-Grand", "insee_code": "93051"}, {"nom": "Villeneuve-Saint-Georges", "insee_code": "94078"}, {"nom": "Courcouronnes", "insee_code": "91182"}, {"nom": "Antibes", "insee_code": "06004"}, {"nom": "Les Abymes", "insee_code": "97101"}, # Guadeloupe {"nom": "Pessin", "insee_code": "97118"}, # Guadeloupe (exemple, à remplacer par une ville plus grande si nécessaire) {"nom": "Pontoise", "insee_code": "95500"}, {"nom": "Évry-Courcouronnes", "insee_code": "91228"}, {"nom": "Bourgoin-Jallieu", "insee_code": "38053"}, {"nom": "La Seyne-sur-Mer", "insee_code": "83126"}, {"nom": "Istres", "insee_code": "13047"}, {"nom": "Villejuif", "insee_code": "94076"}, {"nom": "Saint-Quentin", "insee_code": "02691"}, {"nom": "Beauvais", "insee_code": "60057"}, {"nom": "Hyères", "insee_code": "83069"}, {"nom": "Cholet", "insee_code": "49099"}, {"nom": "Meaux", "insee_code": "77284"}, {"nom": "Narbonne", "insee_code": "11262"}, {"nom": "Mamoudzou", "insee_code": "97611"}, # Mayotte {"nom": "Chelles", "insee_code": "77108"}, {"nom": "La Roche-sur-Yon", "insee_code": "85191"}, {"nom": "Cannes", "insee_code": "06029"}, {"nom": "Mantes-la-Jolie", "insee_code": "78367"}, {"nom": "Châtenay-Malabry", "insee_code": "92019"}, {"nom": "Sarcelles", "insee_code": "95585"}, {"nom": "Le Cannet", "insee_code": "06030"}, {"nom": "Mâcon", "insee_code": "71270"}, {"nom": "Elancourt", "insee_code": "78208"}, {"nom": "Talence", "insee_code": "33522"}, {"nom": "Trappes", "insee_code": "78621"}, {"nom": "Suresnes", "insee_code": "92073"}, {"nom": "Lorient", "insee_code": "56121"}, {"nom": "Savigny-sur-Orge", "insee_code": "91589"}, {"nom": "Saint-Ouen", "insee_code": "93070"}, {"nom": "Cachan", "insee_code": "94016"}, {"nom": "Le Blanc-Mesnil", "insee_code": "93007"}, {"nom": "Bayonne", "insee_code": "64102"}, {"nom": "Viry-Châtillon", "insee_code": "91687"}, {"nom": "Noisy-le-Sec", "insee_code": "93053"}, {"nom": "Vannes", "insee_code": "56260"}, {"nom": "Bobigny", "insee_code": "93008"}, {"nom": "Les Mureaux", "insee_code": "78440"}, {"nom": "Colomiers", "insee_code": "31149"}, {"nom": "Athis-Mons", "insee_code": "91027"}, {"nom": "Pantin", "insee_code": "93055"}, {"nom": "Épinay-sur-Seine", "insee_code": "93031"}, {"nom": "Le Port", "insee_code": "97420"}, # La Réunion {"nom": "Saint-André", "insee_code": "97409"}, # La Réunion {"nom": "Rosny-sous-Bois", "insee_code": "93064"}, {"nom": "Saint-Médard-en-Jalles", "insee_code": "33446"}, {"nom": "Garges-lès-Gonesse", "insee_code": "95270"}, {"nom": "Drancy", "insee_code": "93029"}, {"nom": "Maubeuge", "insee_code": "59392"}, {"nom": "Sotteville-lès-Rouen", "insee_code": "76681"}, {"nom": "Franconville", "insee_code": "95200"}, {"nom": "Fontenay-sous-Bois", "insee_code": "94033"}, {"nom": "Échirolles", "insee_code": "38151"}, {"nom": "Cavaillon", "insee_code": "84035"}, {"nom": "Montigny-lès-Cormeilles", "insee_code": "95425"}, {"nom": "Massy", "insee_code": "91377"}, {"nom": "Vaulx-en-Velin", "insee_code": "69256"}, {"nom": "Grasse", "insee_code": "06069"}, {"nom": "Aubagne", "insee_code": "13005"}, {"nom": "Villepinte", "insee_code": "93078"}, {"nom": "Bagneux", "insee_code": "92007"}, {"nom": "Saint-Brieuc", "insee_code": "22278"}, {"nom": "Champagne-au-Mont-d'Or", "insee_code": "69040"}, {"nom": "Sainte-Geneviève-des-Bois", "insee_code": "91510"}, {"nom": "Conflans-Sainte-Honorine", "insee_code": "78172"}, {"nom": "Saint-Herblain", "insee_code": "44162"}, {"nom": "Livry-Gargan", "insee_code": "93046"}, {"nom": "Sannois", "insee_code": "95582"}, {"nom": "Plaisir", "insee_code": "78490"}, {"nom": "Oyonnax", "insee_code": "01283"}, {"nom": "Osny", "insee_code": "95453"}, {"nom": "Colombier", "insee_code": "38120"}, {"nom": "Halluin", "insee_code": "59280"}, {"nom": "Illkirch-Graffenstaden", "insee_code": "67213"}, {"nom": "Wattrelos", "insee_code": "59650"}, {"nom": "Châtillon", "insee_code": "92020"}, {"nom": "La Ciotat", "insee_code": "13028"}, {"nom": "Montigny-lès-Metz", "insee_code": "57480"}, {"nom": "Bondy", "insee_code": "93010"}, {"nom": "Villefranche-sur-Saône", "insee_code": "69264"}, {"nom": "La Valette-du-Var", "insee_code": "83142"}, {"nom": "Cagnes-sur-Mer", "insee_code": "06027"}, {"nom": "Fréjus", "insee_code": "83061"}, {"nom": "Six-Fours-les-Plages", "insee_code": "83129"}, {"nom": "Villiers-sur-Marne", "insee_code": "94079"}, {"nom": "Brive-la-Gaillarde", "insee_code": "19031"}, {"nom": "Vaulx-en-Velin", "insee_code": "69256"}, # Doublon, à remplacer par une autre ville si nécessaire {"nom": "Clamart", "insee_code": "92023"}, {"nom": "Saint-Leu-la-Forêt", "insee_code": "95583"}, {"nom": "Gonesse", "insee_code": "95277"}, {"nom": "Yerres", "insee_code": "91691"}, {"nom": "Pierrefitte-sur-Seine", "insee_code": "93059"}, {"nom": "Mont-Saint-Aignan", "insee_code": "76457"}, {"nom": "Saint-Benoît", "insee_code": "97411"}, # La Réunion {"nom": "Fos-sur-Mer", "insee_code": "13039"}, {"nom": "Saint-Chamond", "insee_code": "42207"}, {"nom": "L'Haÿ-les-Roses", "insee_code": "94038"}, {"nom": "Sainte-Marie", "insee_code": "97438"}, # La Réunion {"nom": "Sartrouville", "insee_code": "78586"}, {"nom": "Évreux", "insee_code": "27229"}, {"nom": "Bagnols-sur-Cèze", "insee_code": "30028"}, {"nom": "Butterworth", "insee_code": "98813"}, # Exemple à remplacer par une ville métropolitaine {"nom": "Cavaillon", "insee_code": "84035"}, # Doublon, à remplacer {"nom": "Villemomble", "insee_code": "93077"}, {"nom": "Saint-Gratien", "insee_code": "95566"}, {"nom": "Choisy-le-Roi", "insee_code": "94022"}, {"nom": "Herblay-sur-Seine", "insee_code": "95306"}, {"nom": "Saint-Cyr-l'École", "insee_code": "78545"}, {"nom": "Goussainville", "insee_code": "95272"}, {"nom": "Le Chesnay-Rocquencourt", "insee_code": "78158"}, {"nom": "Cugnaux", "insee_code": "31157"}, {"nom": "Brunoy", "insee_code": "91114"}, {"nom": "Sevran", "insee_code": "93071"}, {"nom": "Vigneux-sur-Seine", "insee_code": "91713"}, {"nom": "Les Ulis", "insee_code": "91683"}, {"nom": "Saint-Germain-en-Laye", "insee_code": "78551"}, {"nom": "Morsang-sur-Orge", "insee_code": "91430"}, {"nom": "Fleury-les-Aubrais", "insee_code": "45140"}, {"nom": "Tremblay-en-France", "insee_code": "93073"}, {"nom": "Villeparisis", "insee_code": "77514"}, {"nom": "Vauréal", "insee_code": "95648"}, {"nom": "Saint-Michel-sur-Orge", "insee_code": "91570"}, {"nom": "Chilly-Mazarin", "insee_code": "91161"}, {"nom": "Carrières-sur-Seine", "insee_code": "78124"}, {"nom": "Gennevilliers", "insee_code": "92042"}, {"nom": "Sannois", "insee_code": "95582"}, # Doublon, à remplacer {"nom": "Morsang-sur-Orge", "insee_code": "91430"}, # Doublon, à remplacer ] # Boucle sur les grandes villes for ville in grandes_villes: print(f"Récupération des quartiers de {ville['nom']}...") districts = get_districts_from_osm(ville['nom']) for district in districts: try: cursor.execute(""" INSERT INTO districts ( district_name, insee_code, latitude, longitude, avg_price_per_sqm_apartment, avg_price_per_sqm_house, number_of_transactions, data_year ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """, ( district['name'], ville['insee_code'], district['lat'], district['lon'], None, # avg_price_per_sqm_apartment (à remplir plus tard) None, # avg_price_per_sqm_house None, # number_of_transactions 2023 # data_year (par défaut) )) conn.commit() except Exception as e: print(f"Erreur lors de l'insertion de {district['name']}: {e}") sleep(1) # Éviter de surcharger l'API Overpass conn.close() print("Import terminé !")