Exemple #1
0
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
Exemple #4
0
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
Exemple #5
0
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
Exemple #10
0
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
Exemple #15
0
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
Exemple #18
0
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 []
Exemple #19
0
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
Exemple #20
0
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