def create_locality(parameters):
'''
'''
params = {'name': parameters['name'], 'region_id': parameters['region_id']}
query = """
INSERT INTO `locality`(
name,
region_id
)
VALUES(
:name,
:region_id
);
"""
logger.debug("creating locality...")
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception("error to create locality")
return False
return True
def consult_region_byid(id_region):
'''
Retorna o estado (region) por id e país.
'''
params = {'id': id_region}
query = """
SELECT
id,
name,
code,
default_timezone,
country_id
FROM `region`
WHERE
id = :id;
"""
try:
MYSQL.execute(query=query, params=params)
except:
logger.exception("error to consult countries in databases")
return False
if MYSQL.result:
return {
'id': MYSQL.result[0]['id'],
'name': MYSQL.result[0]['name'],
'code': MYSQL.result[0]['code'],
'default_timezone': MYSQL.result[0]['default_timezone'],
'country_id': MYSQL.result[0]['country_id']
}
return False
def update_region(parameters):
'''
'''
params = {
'id': parameters['id'],
'name': parameters['name'],
'code': parameters['code'],
'default_timezone': parameters['default_timezone'],
'country_id': parameters['country_id']
}
query = 'UPDATE `region` SET `name` = :name, \
`code` = :code, \
`default_timezone` = :default_timezone, \
`country_id` = :country_id \
WHERE id = :id'
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception("error to update region")
return False
logger.info("region updated with successfully!")
return True
def exist_wfday(locality_id, date, conn=None):
'''
'''
params = {'locality_id': locality_id, 'date': date}
query = """
SELECT *
FROM weather_forecasting_day
WHERE
locality_id = :locality_id
AND date = :date;
"""
if conn:
try:
MYSQL.session_execute(
query=query,
params=params,
connection=conn,
)
except:
return False
else:
try:
MYSQL.execute(query=query, params=params)
except:
return False
if MYSQL.result:
return True
return True
def consult_lag(locality_id, conn=None):
'''
'''
params = {'locality_id': locality_id}
query = """
SELECT
lag
FROM weather_forecasting_day
WHERE
locality_id = :locality_id
"""
if conn:
try:
MYSQL.session_execute(
query=query,
params=params,
connection=conn,
)
except:
return False
else:
try:
MYSQL.execute(query=query, params=params)
except:
return False
if MYSQL.result:
return MYSQL.result
def consult_lag(locality_id):
'''
'''
params = {
'locality_id': locality_id
}
query = """
SELECT
lag
FROM weather_forecasting_day
WHERE
locality_id = :locality_id
"""
try:
MYSQL.execute(
query=query,
params=params
)
except:
return False
if MYSQL.result:
return MYSQL.result
def get_all_regions(country_id):
"""Retorna todos os estados por país"""
params = {'country_id': country_id}
query = """
SELECT
id,
name,
code,
default_timezone,
country_id
FROM `region`
WHERE
country_id = :country_id
"""
try:
MYSQL.execute(query=query, params=params)
except:
logger.exception("error to consult regions in databases")
return False
if MYSQL.result:
return MYSQL.result
return False
def update_wfhour(parameters):
'''
+----------------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| atmospheric_pressure | int(11) | NO | | NULL | |
| wind | int(11) | NO | | NULL | |
| temp | int(11) | NO | | NULL | |
| relative_humidity | int(11) | NO | | NULL | |
| last_update | text | YES | | NULL | |
| weather | text | NO | | NULL | |
| locality_id | int(11) | NO | MUL | NULL | |
| date_time | datetime | NO | | NULL | |
+----------------------+----------+------+-----+---------+----------------+
RETURN:
bool
'''
params = {
'id': parameters['id'],
'atmospheric_pressure': parameters['atmospheric_pressure'],
'wind': parameters['wind'],
'temp': parameters['temp'],
'relative_humidity': parameters['relative_humidity'],
'last_update': parameters['last_update'],
'weather': parameters['weather'],
'locality_id': parameters['locality_id'],
'date_time': parameters['date_time']
}
query = 'UPDATE `weather_forecasting_hour` SET `atmospheric_pressure` = :atmospheric_pressure, \
`wind` = :wind, \
`temp` = :temp, \
`relative_humidity` = :relative_humidity, \
`last_update` = :last_update, \
`weather` = :weather, \
`locality_id` = :locality_id, \
`date_time` = :date_time \
WHERE id = :id'
try:
MYSQL.execute(
query=query,
params=params,
get_results=False,
check_for_empty_result=False
)
except:
logger.exception("error to update weather forecasting hour")
return False
logger.debug("weather forecasting hour updated with successfully!")
return True
def update_wfday(parameters):
'''
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| weather | text | NO | | NULL | |
| precipitation | int(11) | NO | | NULL | |
| date | date | NO | | NULL | |
| max | int(11) | NO | | NULL | |
| min | int(11) | NO | | NULL | |
| locality_id | int(11) | NO | MUL | NULL | |
| lag | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+----------------+
RETURN:
bool
'''
params = {
'id': parameters['id'],
'precipitation': parameters['precipitation'],
'_date': parameters['date'],
'_max': parameters['max'],
'_min': parameters['min'],
'weather': parameters['weather'],
'locality_id': parameters['locality_id']
}
query = 'UPDATE `weather_forecasting_day` SET `precipitation` = :precipitation, \
`date` = :_date, \
`max` = :_max, \
`min` = :_min, \
`weather` = :weather, \
`locality_id` = :locality_id \
WHERE id = :id'
try:
MYSQL.execute(
query=query,
params=params,
get_results=False,
check_for_empty_result=False
)
except:
logger.exception("error to update weather forecasting hour")
return False
logger.debug("weather forecasting hour updated with successfully!")
return True
def update_wfday(parameters, conn=None):
'''
'''
params = {
'id': parameters['id'],
'locality_id': parameters['locality_id'],
'precipitation': parameters['precipitation'],
'weather': parameters['weather'],
'_date': parameters['date'],
'_max': parameters['max'],
'_min': parameters['min'],
'lag': parameters['lag'],
}
query = 'UPDATE `weather_forecasting_day` SET weather = :weather, \
precipitation = :precipitation, \
`max` = :_max, \
`min` = :_min, \
`date` = :_date, \
locality_id = :locality_id, \
lag = :lag \
WHERE id = :id'
if conn:
try:
MYSQL.session_execute(query=query,
params=params,
connection=conn,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to update weather forecasting: [{}]".format(parameters))
return False
else:
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to update weather forecasting: [{}]".format(parameters))
return False
logger.info('weather forecasting day updated with successfully!')
return True
def get_wf_hour_byid(id):
'''
'''
params = {
'id': id
}
query = """
SELECT
id,
atmospheric_pressure,
wind,
temp,
relative_humidity,
last_update,
weather,
locality_id,
date_time
FROM `weather_forecasting_hour`
WHERE
id = :id
"""
try:
MYSQL.execute(
query=query,
params=params
)
except:
logger.exception("error to consult weather forecasting hour by id")
return False
if MYSQL.result:
return {
'id': MYSQL.result[0]['id'],
'atmospheric_pressure': MYSQL.result[0]['atmospheric_pressure'],
'wind': MYSQL.result[0]['wind'],
'temp': MYSQL.result[0]['temp'],
'relative_humidity': MYSQL.result[0]['relative_humidity'],
'last_update': MYSQL.result[0]['last_update'],
'weather': MYSQL.result[0]['weather'],
'locality_id': MYSQL.result[0]['locality_id'],
'date_time': MYSQL.result[0]['date_time']
}
return False
def get_wf_day_byid(id):
'''
'''
params = {
'id': id
}
query = """
SELECT
id,
weather,
precipitation,
date,
max,
min,
locality_id,
lag
FROM `weather_forecasting_day`
WHERE
id = :id
"""
try:
MYSQL.execute(
query=query,
params=params
)
except:
logger.exception("error to consult weather forecasting day by id")
return False
if MYSQL.result:
return {
'id': MYSQL.result[0]['id'],
'weather': MYSQL.result[0]['weather'],
'precipitation': MYSQL.result[0]['precipitation'],
'date': MYSQL.result[0]['date'],
'max': MYSQL.result[0]['max'],
'min': MYSQL.result[0]['min'],
'weather': MYSQL.result[0]['weather'],
'locality_id': MYSQL.result[0]['locality_id'],
'lag': MYSQL.result[0]['lag']
}
return False
def get_wf_hour(locality_id):
'''
'''
params = {
'locality_id': locality_id
}
query = """
SELECT
wf.id,
wf.atmospheric_pressure,
wf.wind,
wf.temp,
wf.relative_humidity,
wf.last_update,
wf.weather,
wf.locality_id,
wf.date_time,
l.`name` as locality_name,
r.`name` as region_name,
r.`code`
FROM `weather_forecasting_hour` wf
JOIN locality l ON l.id = wf.locality_id
JOIN region r ON r.id = l.region_id
WHERE
l.id = :locality_id
ORDER BY
wf.date_time;
"""
try:
MYSQL.execute(
query=query,
params=params
)
except:
logger.exception("error to consult weather forecasting hour by locality_id")
return False
if MYSQL.result:
return MYSQL.result
return False
def get_wf_day(locality_id):
'''
'''
params = {
'locality_id': locality_id
}
query = """
SELECT
wf.id,
wf.weather,
wf.precipitation,
wf.`date`,
wf.`max`,
wf.`min`,
wf.locality_id,
wf.lag,
l.`name` as locality_name,
r.`name` as region_name,
r.`code`
FROM `weather_forecasting_day` wf
JOIN locality l ON l.id = wf.locality_id
JOIN region r ON r.id = l.region_id
WHERE
l.id = :locality_id
ORDER BY
wf.`date`;
"""
try:
MYSQL.execute(
query=query,
params=params
)
except:
logger.exception("error to consult weather forecasting day by locality_id")
return False
if MYSQL.result:
return MYSQL.result
return False
def consult_wfday(locality_id, date, conn=None):
'''
'''
params = {'locality_id': locality_id, 'date': date}
query = """
SELECT *
FROM weather_forecasting_day
WHERE
locality_id = :locality_id
AND date = :date;
"""
if conn:
try:
MYSQL.session_execute(
query=query,
params=params,
connection=conn,
)
except:
return False
else:
try:
MYSQL.execute(query=query, params=params)
except:
return False
if MYSQL.result:
return {
'id': MYSQL.result[0]['id'],
'weather': MYSQL.result[0]['weather'],
'precipitation': MYSQL.result[0]['precipitation'],
'date': MYSQL.result[0]['date'],
'max': MYSQL.result[0]['max'],
'min': MYSQL.result[0]['min'],
'locality_id': MYSQL.result[0]['locality_id'],
'lag': MYSQL.result[0]['lag']
}
def delete_wfday(id):
'''
'''
params = {
'id': id
}
query = 'DELETE FROM `weather_forecasting_day` WHERE id = :id'
try:
MYSQL.execute(
query=query,
params=params,
get_results=False,
check_for_empty_result=False
)
except:
logger.exception("error to delete weather forecasting day")
return False
logger.debug("weather forecasting day deleted with successfully!")
return True
def create_region(parameters):
'''
'''
params = {
'name': parameters['name'],
'code': parameters['code'],
'default_timezone': parameters['default_timezone'],
'country_id': parameters['country_id']
}
query = """
INSERT INTO `region`(
name,
code,
default_timezone,
country_id
)
VALUES(
:name,
:code,
:default_timezone,
:country_id
);
"""
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception("error to create region")
return False
logger.info("region created with successfully!")
return True
def consult_locality_byregionid(region_id):
"""Retorna todos os estados por país"""
params = {'region_id': region_id}
query = """
SELECT
id,
name
FROM `locality`
WHERE
region_id = :region_id
"""
try:
MYSQL.execute(query=query, params=params)
except:
logger.exception("error to consult locality in databases")
return []
if MYSQL.result:
return MYSQL.result
logger.warning('failed consult cities by region id')
return []
def create_wfhour(parameters, conn=None):
'''
'''
params = {
'locality_id': parameters['locality_id'],
'temp': parameters['temp'],
'weather': parameters['weather'],
'last_update': parameters['last_update'],
'wind': parameters['wind'],
'atmospheric_pressure': parameters['atmospheric_pressure'],
'relative_humidity': parameters['relative_humidity'],
'date_time': parameters['date_time']
}
if exist_wfhour(params['locality_id'], params['date_time'], conn):
logger.info(
'weather forecasting hour alread exist: [{0}] '.format(parameters))
return True
query = """
INSERT INTO `weather_forecasting_hour`(
atmospheric_pressure,
wind,
temp,
relative_humidity,
last_update,
weather,
locality_id,
date_time
)
VALUES(
:atmospheric_pressure,
:wind,
:temp,
:relative_humidity,
:last_update,
:weather,
:locality_id,
:date_time
);
"""
if conn:
try:
MYSQL.session_execute(query=query,
params=params,
connection=conn,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to create weather forecasting: [{}]".format(parameters))
return False
else:
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to create weather forecasting: [{}]".format(parameters))
return False
logger.info('weather forecasting hour created with successfully!')
return True
def create_wfday(parameters, conn=None):
'''
'''
params = {
'locality_id': parameters['locality_id'],
'precipitation': parameters['precipitation'],
'weather': parameters['weather'],
'date': parameters['date'],
'max': parameters['max'],
'min': parameters['min'],
'lag': parameters['lag'],
}
if exist_wfday(params['locality_id'], params['date'], conn):
return True
query = """
INSERT INTO `weather_forecasting_day`(
weather,
precipitation,
`date`,
`max`,
`min`,
locality_id,
lag
)
VALUES(
:weather,
:precipitation,
:date,
:max,
:min,
:locality_id,
:lag
);
"""
if conn:
try:
MYSQL.session_execute(query=query,
params=params,
connection=conn,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to create weather forecasting: [{}]".format(parameters))
return False
else:
try:
MYSQL.execute(query=query,
params=params,
get_results=False,
check_for_empty_result=False)
except:
logger.exception(
"error to create weather forecasting: [{}]".format(parameters))
return False
logger.info('weather forecasting day created with successfully!')
return True
def create_wfhour(parameters):
'''
+----------------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| atmospheric_pressure | int(11) | NO | | NULL | |
| wind | int(11) | NO | | NULL | |
| temp | int(11) | NO | | NULL | |
| relative_humidity | int(11) | NO | | NULL | |
| last_update | text | YES | | NULL | |
| weather | text | NO | | NULL | |
| locality_id | int(11) | NO | MUL | NULL | |
| date_time | datetime | NO | | NULL | |
+----------------------+----------+------+-----+---------+----------------+
RETURN:
bool
'''
params = {
'atmospheric_pressure': parameters['atmospheric_pressure'],
'wind': parameters['wind'],
'temp': parameters['temp'],
'relative_humidity': parameters['relative_humidity'],
'last_update': parameters['last_update'],
'weather': parameters['weather'],
'locality_id': parameters['locality_id'],
'date_time': parameters['date_time']
}
query = """
INSERT INTO `weather_forecasting_hour`(
atmospheric_pressure,
wind,
temp,
relative_humidity,
last_update,
weather,
locality_id,
date_time
)
VALUES(
:atmospheric_pressure,
:wind,
:temp,
:relative_humidity,
:last_update,
:weather,
:locality_id,
:date_time
);
"""
try:
MYSQL.execute(
query=query,
params=params,
get_results=False,
check_for_empty_result=False
)
except:
logger.exception("error to create weather forecasting hour")
return False
logger.info("weather forecasting hour created with successfully!")
return True
def create_wfday(parameters):
'''
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| weather | text | NO | | NULL | |
| precipitation | int(11) | NO | | NULL | |
| date | date | NO | | NULL | |
| max | int(11) | NO | | NULL | |
| min | int(11) | NO | | NULL | |
| locality_id | int(11) | NO | MUL | NULL | |
| lag | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+----------------+
RETURN:
bool
'''
params = {
'precipitation': parameters['precipitation'],
'_date': parameters['date'],
'_max': parameters['max'],
'_min': parameters['min'],
'weather': parameters['weather'],
'locality_id': parameters['locality_id'],
'lag': parameters['lag']
}
query = """
INSERT INTO `weather_forecasting_day`(
precipitation,
`date`,
`max`,
`min`,
weather,
locality_id,
lag
)
VALUES(
:precipitation,
:_date,
:_max,
:_min,
:weather,
:locality_id,
:lag
);
"""
try:
MYSQL.execute(
query=query,
params=params,
get_results=False,
check_for_empty_result=False
)
except:
logger.exception("error to create weather forecasting day")
return False
logger.info("weather forecasting day created with successfully!")
return True