def __init__(self, id_project=0):
if not id_project:
FileLogger.log(logging.ERROR, "'id_project' could not be empty!")
return
self.__id_project = id_project
self.__connexion = DB_connexion()
data = self.__connexion.Query_SQL_fetchone(prj.select_project_with_id,
[self.__id_project])
self.__project_ID = data[0]
self.__api_KEY = data[1]
def retrive_data_by_selectionId(project_ID, api_KEY, selectionId, id_selection,
categories_list,
element_reference_by_profil_dict,
element_reference_by_category_dict):
import pandas as pd
result_df = pd.DataFrame(columns=[
'id_apidae', 'id_selection', 'type_apidae', 'titre', 'profil_c2g',
'categorie_c2g', 'adresse1', 'adresse2', 'code_postal', 'ville',
'altitude', 'latitude', 'longitude', 'telephone', 'email', 'site_web',
'description_courte', 'description_detaillee', 'image', 'publics',
'tourisme_adapte', 'payant', 'animaux_acceptes', 'environnement',
'equipement', 'services', 'periode', 'activites', 'ouverture',
'date_debut', 'date_fin', 'typologie', 'bons_plans',
'dispositions_speciales', 'service_enfants', 'service_cyclistes',
'nouveaute_2020'
])
url = 'http://api.apidae-tourisme.com/api/v002/recherche/list-objets-touristiques?query={'
url += '"projetId":"' + project_ID + '",'
url += '"apiKey":"' + api_KEY + '",'
url += '"selectionIds":["' + selectionId + '"]}'
count = 100
try:
req = requests.get(url).json()
if "numFound" in req:
nb_object = int(req["numFound"])
else:
return result_df
i = 0
while count * i < nb_object:
result_df = result_df.append(
retrive_data_by_selectionId_by_cent(
project_ID, api_KEY, selectionId, id_selection,
categories_list, element_reference_by_profil_dict,
element_reference_by_category_dict, count * i, count))
i += 1
nb_data_retrieved = len(result_df.index)
if nb_object == nb_data_retrieved:
FileLogger.log(
logging.DEBUG,
f"{nb_object} data expected and {nb_data_retrieved} obtained for url {url} !!!"
)
else:
FileLogger.log(
logging.ERROR,
f"{nb_object} data expected and {nb_data_retrieved} obtained for url {url} !!!"
)
except ValueError:
print("problème d'extraction")
return result_df
def Execute(self):
"""Execution des traitements pour récupérer les selection d'un project_ID.
"""
try:
# Chargement de toutes les selections pour un project_ID.
dict_selection = self.__Read()
listUpdate_selection = []
listInsert_selection = []
listOfKeys = []
selection_df = apex.retrieve_selection_list(
self.__id_project, self.__project_ID, self.__api_KEY)
for _i, row in selection_df.iterrows():
aselection = selectionModel(
self.__id_project,
row["selection"],
row["description"],
)
currentKey = "{0}_#_{1}".format(aselection.id_project,
aselection.selection)
if currentKey in listOfKeys: # Si on a déjà traité cette clé.
continue
listOfKeys.append(currentKey)
if not currentKey in dict_selection:
listInsert_selection.append(aselection)
else:
listUpdate_selection.append(aselection)
del dict_selection[currentKey]
# Update
if listUpdate_selection:
FileLogger.log(
logging.DEBUG,
f"Selection list for project_ID: {self.__id_project} Update in progress..."
)
self.__Update(listUpdate_selection)
# Delete
if dict_selection:
FileLogger.log(
logging.DEBUG,
f"Selection list for project_ID: {self.__id_project} Delete in progress..."
)
self.__Delete(dict_selection)
# insert
if listInsert_selection:
FileLogger.log(
logging.DEBUG,
f"Selection list for project_ID: {self.__id_project} Insert in progress..."
)
self.__Create(listInsert_selection)
except Exception:
FileLogger.log(logging.ERROR, traceback.print_exc())
def __Create(self, selection=[]):
"""Insertion des sélections dans la table des selections
"""
try:
if not selection:
return
# Récupération des données dans la bdd
metadata = sqlalchemy.MetaData(bind=None)
tInfo_selection = sqlalchemy.Table(
'selection',
metadata,
autoload=True,
autoload_with=self.__connexion.engine())
lines = self.__connexion.engine().connect().execute(
tInfo_selection.insert(None),
[{
'id_project': self.__id_project,
'selection': aselection.selection,
'description': aselection.description,
} for aselection in selection])
nb_connexion = self.__connexion.number_connections()
FileLogger.log(
logging.DEBUG,
f"New instance created, number of connexion : {nb_connexion}")
FileLogger.log(
logging.DEBUG,
f"{lines.rowcount} selection(s) for project_ID: {self.__id_project} inserted!"
)
except Exception:
FileLogger.log(logging.ERROR, traceback.print_exc())
def __Delete(self, selection=[]):
"""Suppression de selection(s).
"""
try:
metadata = sqlalchemy.MetaData(bind=None)
tInfo_selection = sqlalchemy.Table(
'selection',
metadata,
autoload=True,
autoload_with=self.__connexion.engine())
lines = 0
for aselection in selection:
selection = re.split(r'_#_', aselection)[1]
query = tInfo_selection.delete(None).where(
sqlalchemy.and_(
tInfo_selection.c.id_project == self.__id_project,
tInfo_selection.c.selection == selection))
line = self.__connexion.engine().connect().execute(query)
nb_connexion = self.__connexion.number_connections()
FileLogger.log(
logging.DEBUG,
f"New instance created, number of connexion : {nb_connexion}"
)
lines += int(line.rowcount)
FileLogger.log(
logging.DEBUG,
f"{lines} selection(s) for project_ID: {self.__id_project} deleted!"
)
except Exception:
FileLogger.log(logging.ERROR, traceback.print_exc())
def __Read(self):
"""Lecture des selections pour un projet donné.
"""
try:
metadata = sqlalchemy.MetaData(bind=None)
tInfo_selection = sqlalchemy.Table(
'selection',
metadata,
autoload=True,
autoload_with=self.__connexion.engine())
dico_selection = {}
query = sqlalchemy.select([tInfo_selection]).where(
sqlalchemy.and_(
tInfo_selection.c.id_project == self.__id_project,
)).distinct()
result = self.__connexion.engine().connect().execute(query)
nb_connexion = self.__connexion.number_connections()
FileLogger.log(
logging.DEBUG,
f"New instance created, number of connexion : {nb_connexion}")
if result.rowcount == 0:
return dico_selection
for row in result:
aselection = selectionModel(row[tInfo_selection.c.id_project],
row[tInfo_selection.c.selection],
row[tInfo_selection.c.description])
key = "{0}_#_{1}".format(aselection.id_project,
aselection.selection)
if not key in dico_selection:
dico_selection[key] = aselection
return dico_selection
except Exception:
FileLogger.log(logging.ERROR, traceback.print_exc())
def __init__(self):
self.__dbname = os.getenv('DB_NAME')
self.__user = os.getenv('USER')
self.__password = os.getenv('PASSWORD')
self.__host = os.getenv('HOST')
self.__port = os.getenv('PORTE')
self.__db_connect_url = sqla_url.URL(drivername='postgresql+psycopg2',
username=self.__user,
password=self.__password,
host=self.__host,
port=self.__port,
database=self.__dbname)
try:
self.__conn = psycopg2.connect(dbname=self.__dbname,
user=self.__user,
password=self.__password,
host=self.__host,
port=self.__port)
self.__cur = self.__conn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
self.__engine = create_engine(self.__db_connect_url, echo=False)
nb_connexion = self.Query_SQL_fetchone(
f"SELECT sum(numbackends) FROM pg_stat_database WHERE datname='{self.__dbname}'"
)[0]
FileLogger.log(
logging.DEBUG,
f"New connection, cursor and engine created, number of connexion : {nb_connexion}"
)
except Error as e:
if e == 'TooManyConnections':
sleep(1)
self.__init__()
elif e == f'too many connections for role "{self.__user}"':
sleep(1)
self.__init__()
else:
FileLogger.log(logging.DEBUG,
f'Connection impossible !!! error {e}')
sys.exit()
def __Update(self, selection=[]):
"""Mise à jour des données dans la table selection
"""
try:
if selection == None:
return
metadata = sqlalchemy.MetaData(bind=None)
tInfo_selection = sqlalchemy.Table(
'selection',
metadata,
autoload=True,
autoload_with=self.__connexion.engine())
query = tInfo_selection.update(None).where(
sqlalchemy.and_(
tInfo_selection.c.id_project == int(self.__id_project),
tInfo_selection.c.selection == sqlalchemy.bindparam(
'c_selection'),
)).values(description=sqlalchemy.bindparam('description'), )
lines = self.__connexion.engine().connect().execute(
query, [{
'c_selection': str(aselection.selection),
'description': aselection.description,
} for aselection in selection])
nb_connexion = self.__connexion.number_connections()
FileLogger.log(
logging.DEBUG,
f"New instance created, number of connexion : {nb_connexion}")
FileLogger.log(
logging.DEBUG,
f"{lines.rowcount} selection(s) for project_ID: {self.__id_project} updated!"
)
except Exception:
FileLogger.log(logging.ERROR, traceback.print_exc())
def __del__(self):
del self.__connexion
FileLogger.log(logging.DEBUG,
"Destruction of Selection class instance")
pass
"""----------------------------
Creation date : 2020-07-20
Last update : 2020-07-21
----------------------------"""
import logging
from LoggerModule.FileLogger import FileLogger as FileLogger
from DB_Connexion import DB_connexion
import Table_project as prj
import app
if __name__ == "__main__":
connexion = DB_connexion()
project_list = connexion.Query_SQL_fetchall(prj.select_project_information)
del connexion
for project in project_list:
project_id = project[0]
app.asynchronous_selection_extract.apply_async(args=[project_id],
countdown=2)
FileLogger.log(logging.DEBUG,
f"Periodic Task executed for project {project_id}")
def query_database_for_list_of_filtered_locations(categories, profiles):
connexion = DB_connexion()
FileLogger.log(
logging.DEBUG,
f"{categories} categories and {profiles} profiles extraction start")
set_of_all_location = set()
list_of_location = connexion.Query_SQL_fetchall(apidae.select_apidae)
for location in list_of_location:
set_of_all_location.add(location[1])
nb_locations = len(set_of_all_location)
nb_locations_extracted = len(list_of_location)
FileLogger.log(
logging.DEBUG,
f"{nb_locations} different locations in set for {nb_locations_extracted} location extracted !!!"
)
set_of_location_id = set()
list_of_location = connexion.Query_SQL_fetchall(
apidae.select_apidae_with_categorie_list_edited_and_profil_list_edited,
[profiles, categories])
for location in list_of_location:
set_of_location_id.add(location[0])
try:
set_of_all_location.remove(location[0])
except:
FileLogger.log(
logging.ERROR,
f"{location[0]} no more in set of all locations !!!")
nb_locations_for_json = len(set_of_location_id)
nb_locations = len(set_of_all_location)
FileLogger.log(
logging.DEBUG,
f"1st step : {nb_locations_for_json} locations for json out of {nb_locations} different locations remaining in set for {nb_locations_extracted} location extracted !!!"
)
list_of_location = connexion.Query_SQL_fetchall(
apidae.select_apidae_with_categorie_list_edited_and_profil_list,
[profiles, categories])
for location in list_of_location:
set_of_location_id.add(location[0])
try:
set_of_all_location.remove(location[0])
except:
FileLogger.log(
logging.ERROR,
f"{location[0]} no more in set of all locations !!!")
nb_locations_for_json = len(set_of_location_id)
nb_locations = len(set_of_all_location)
FileLogger.log(
logging.DEBUG,
f"2nd step : {nb_locations_for_json} locations for json out of {nb_locations} different locations remaining in set for {nb_locations_extracted} location extracted !!!"
)
list_of_location = connexion.Query_SQL_fetchall(
apidae.select_apidae_with_categorie_list_and_profil_list_edited,
[profiles, categories])
for location in list_of_location:
set_of_location_id.add(location[0])
try:
set_of_all_location.remove(location[0])
except:
FileLogger.log(
logging.ERROR,
f"{location[0]} no more in set of all locations !!!")
nb_locations_for_json = len(set_of_location_id)
nb_locations = len(set_of_all_location)
FileLogger.log(
logging.DEBUG,
f"3rd step : {nb_locations_for_json} locations for json out of {nb_locations} different locations remaining in set for {nb_locations_extracted} location extracted !!!"
)
list_of_location = connexion.Query_SQL_fetchall(
apidae.select_apidae_with_categorie_list_and_profil_list,
[profiles, categories])
for location in list_of_location:
set_of_location_id.add(location[0])
try:
set_of_all_location.remove(location[0])
except:
FileLogger.log(
logging.ERROR,
f"{location[0]} no more in set of all locations !!!")
nb_locations_for_json = len(set_of_location_id)
nb_locations = len(set_of_all_location)
FileLogger.log(
logging.DEBUG,
f"4th step : {nb_locations_for_json} locations for json out of {nb_locations} different locations remaining in set for {nb_locations_extracted} location extracted !!!"
)
locations_list = []
nb_location = 0
for id_location in set_of_location_id:
data = connexion.Query_SQL_fetchone(
apidae.select_apidae_1_id_apidae_with_data_edited, [id_location])
dict_for_apidae, dict_for_geometry = functions.create_dict_for_lieu_validated(
data)
# liste properties, geometry
locations_list.append(dict_for_apidae)
# locations_list.append(dict_for_properties) # properties only
nb_location += 1
FileLogger.log(
logging.DEBUG,
f"{nb_locations_for_json} in set of location and {nb_location} locations extracted !!!"
)
del connexion
return nb_location, locations_list
def __del__(self):
self.__cur.close()
self.__conn.close()
self.__engine.dispose()
FileLogger.log(logging.DEBUG, f"Connexion closed !!!!")
def __geolocation(self):
self.__dict_id['adresse1'] = None
self.__dict_id['adresse2'] = None
self.__dict_id['ville'] = None
self.__dict_id['code_postal'] = None
self.__dict_id['altitude'] = None
self.__dict_id['longitude'] = None
self.__dict_id['latitude'] = None
if 'localisation' in self.__request_json:
# latitude, longitude
if 'geolocalisation' in self.__request_json['localisation']:
if 'geoJson' in self.__request_json['localisation']['geolocalisation']:
if 'coordinates' in self.__request_json['localisation']['geolocalisation']['geoJson']:
self.__dict_id['longitude'] = self.__request_json['localisation']['geolocalisation']['geoJson']['coordinates'][0]
self.__dict_id['latitude'] = self.__request_json['localisation']['geolocalisation']['geoJson']['coordinates'][1]
# adresse1, adresse2
if 'adresse' in self.__request_json['localisation']:
if 'adresse1' in self.__request_json['localisation']['adresse']:
self.__dict_id['adresse1'] = self.__request_json['localisation']['adresse']['adresse1']
if 'adresse2' in self.__request_json['localisation']['adresse']:
self.__dict_id['adresse2'] = self.__request_json['localisation']['adresse']['adresse2']
# ville
if 'adresse' in self.__request_json['localisation']:
if 'commune' in self.__request_json['localisation']['adresse']:
if 'codePostal' in self.__request_json['localisation']['adresse']:
self.__dict_id['code_postal'] = self.__request_json['localisation']['adresse']['codePostal']
if 'nom' in self.__request_json['localisation']['adresse']['commune']:
self.__dict_id['ville'] = self.__request_json['localisation']['adresse']['commune']['nom']
# altitude
if 'informations' in self.__request_json:
if 'structureGestion' in self.__request_json['informations']:
if 'geolocalisation' in self.__request_json['informations']['structureGestion']:
if 'altitude' in self.__request_json['informations']['structureGestion']['geolocalisation']:
self.__dict_id['altitude'] = self.__request_json['informations'][
'structureGestion']['geolocalisation']['altitude']
if self.__dict_id['altitude'] is None:
if 'localisation' in self.__request_json:
if 'geolocalisation' in self.__request_json['localisation']:
if 'altitude' in self.__request_json['localisation']['geolocalisation']:
self.__dict_id['altitude'] = self.__request_json['localisation']['geolocalisation']['altitude']
# latitude, longitude
if 'localisation' in self.__request_json:
if 'geolocalisation' in self.__request_json['localisation']:
if 'geoJson' in self.__request_json['localisation']['geolocalisation']:
if 'coordinates' in self.__request_json['localisation']['geolocalisation']['geoJson']:
self.__dict_id['longitude'] = self.__request_json['localisation']['geolocalisation']['geoJson']['coordinates'][0]
self.__dict_id['latitude'] = self.__request_json['localisation']['geolocalisation']['geoJson']['coordinates'][1]
if self.__dict_id['longitude'] is None or self.__dict_id['latitude'] is None:
# geolocator = Nominatim(
# timeout=10, user_agent="cooltogo_api_backend")
geolocator = BANFrance(
domain='api-adresse.data.gouv.fr', timeout=10)
address_to_geolocalize = ""
if self.__dict_id['adresse1'] is not None:
address_to_geolocalize += " " + self.__dict_id['adresse1']
if self.__dict_id['adresse2'] is not None:
address_to_geolocalize += " " + self.__dict_id['adresse2']
if self.__dict_id['code_postal'] is not None:
address_to_geolocalize += " " + self.__dict_id['code_postal']
if self.__dict_id['ville'] is not None:
address_to_geolocalize += " " + self.__dict_id['ville']
try:
geocode = RateLimiter(
geolocator.geocode, min_delay_seconds=2, max_retries=4, error_wait_seconds=10.0, swallow_exceptions=True, return_value_on_exception=None)
location = geocode(address_to_geolocalize)
if location is not None:
self.__dict_id['latitude'] = location.latitude
self.__dict_id['longitude'] = location.longitude
FileLogger.log(
logging.DEBUG, f"{address_to_geolocalize} resolved with latitute {location.latitude} and longitute {location.longitude}")
else:
FileLogger.log(
logging.DEBUG, f"{address_to_geolocalize} not resolved !!!!")
except (GeocoderTimedOut, GeocoderUnavailable, GeocoderQuotaExceeded) as e:
FileLogger.log(logging.ERROR, "Error: geocode failed on input %s with message %s" %
(address_to_geolocalize, str(e)))