def rev_data(estacion, fecha, contaminante, tipo):
     oztool = ContIOTools()
     conexion = SqlCont()
     conn = conexion.getPostgresConn()
     cur = conn.cursor()
     tempData = pd.read_sql_query(
         """SELECT COUNT(fecha) FROM {0} WHERE fecha = '{1}' AND id_est = '{2}' AND  id_tipo_pronostico = {3}; """
         .format(contaminante, fecha, estacion, tipo), conn)
     return tempData['count'][0]
    def buildClass(allData, estation, contaminant, delta):
        """
        Function so that every date in the allData table is added n hours and get the value that belongs to it.

        :param allData: is data extracted  the function readData
        :type allData: DataFrame.
        :param estation: station from wich the data will be taken.
        :type estation: string.
        :param contaminant: contaminant from wich the data will be taken.
        :type contaminant: string.
        :param delta : number of hours added to the original.
        :type delta: int, float
        :return: build
        :rtype: DataFrame
        """
        oztool = ContIOTools()
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        #conexion database
        tableContaminant = oztool.findTable(contaminant)
        # name the contaminant in the database
        fechas = allData['fecha']
        build = pd.DataFrame(fechas, columns=['fecha'])
        cont = len(build.index) - 1
        values = np.ones((cont + 1, 1)) * -1
        #building the DataFrame, the first column is the date
        x = 0
        for xv in estation:
            name = tableContaminant + '_' + xv + '_delta'
            #name the column in the DataFrame
            while x <= cont:
                #Construction of the second column having the pollutant value
                time = build.ix[x, 'fecha']
                time = time + timedelta(hours=delta)
                #You add the delta to the date
                sql = """SELECT val FROM {0} WHERE id_est ='{1}' AND fecha ='{2}';""".format(
                    tableContaminant, xv, time)
                cur.execute(sql)
                temp = cur.fetchall()
                tempValue = np.array(temp)
                if len(tempValue) != 0:
                    #If the query is empty it puts -1 in the value of the column
                    values[x, 0] = tempValue[0, 0]
                    x = x + 1
                else:
                    values[x, 0] = np.nan
                    x = x + 1
                temBuild = pd.DataFrame(values, columns=[name])
            x = 0
            build[name] = temBuild
        conn.commit()
        cur.close()
        #The connection to the database is closed
        build.dropna()
        return build.fillna(value=-1)
    def readData_corr(startDate, endDate, estations, contaminant):
        """
        Function to extract information from the database.

        :param startDate: range of data wit wich the vaues of tue query are extracted.
        :type startDate: timedata
        :param endDate : range of data wit wich the vaues of tue query are extracted.
        :type endDate: timedata
        :param estations: set the stations to get the values.
        :type estations : list with the stations
        :return : allData  all data that was taken from the database.
        :rtype: DataFrame
        """
        oztool = ContIOTools()
        tables_contaminants = oztool.getTables()
        cont = oztool.findTable(contaminant)
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        #conexion for the database
        allData = pd.read_sql_query(
            """SELECT fecha FROM {0} WHERE id_est ='{1}' AND fecha >= '{2}' AND fecha <= '{3}' ORDER BY fecha ASC;"""
            .format(cont, estations[0], startDate, endDate), conn)
        #query the dates in the gives range
        numberows = len(
            allData.index)  #Numbers the data given by the previous query
        for x in estations:
            for y in tables_contaminants:
                name = y + '_' + x.lower()
                #name the column in the DataFrame
                tempDataValues = pd.read_sql_query(
                    """SELECT fecha, val  as {0} FROM {1} WHERE id_est = '{2}' AND fecha >= '{3}' AND fecha <= '{4}'ORDER BY fecha ASC;"""
                    .format(name, y, x, startDate, endDate), conn)
                #query the values in the gives rangefrom Utilites.FormatData import FormatData as fdfrom Utilites.FormatData import FormatData as fd
                if tempDataValues.empty:
                    #if the query is empty fill it will -1
                    tempData = pd.DataFrame(np.ones((numberows, 1)) * -1,
                                            columns=[y + '_' + x.lower()])
                    #allData[y + '_' + x.lower()]= tempData;
                    #pass;
                elif not tempDataValues.empty:
                    allData = allData.merge(tempDataValues,
                                            how='left',
                                            on='fecha')
                    #allData[name]=tempDataValues[y+'_'+x.lower()];
        conn.commit()
        cur.close()
        #The connection to the database is closed
        #allData=allData.dropna(how = 'any');
        #return allData.fillna(value=-1);
        return allData
    def buildClass2(allData, estation, contaminant, delta, startDate, endDate):
        """
        Function so that every date in the allData table is added n hours and get the value that belongs to it.

        :param allData: is data extracted  the function readData
        :type allData: DataFrame.
        :param estation: station from wich the data will be taken.
        :type estation: string.
        :param contaminant: contaminant from wich the data will be taken.
        :type contaminant: string.
        :param delta : number of hours added to the original.
        :type delta: int, float
        :return: build
        :rtype: DataFrame
        """
        oztool = ContIOTools()
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        #conexion database
        tableContaminant = oztool.findTable(contaminant)
        # name the contaminant in the database
        fechas = allData['fecha']
        build = pd.DataFrame(fechas, columns=['fecha'])
        cont = len(build.index)
        values = np.ones((cont, 1)) * -1
        start = datetime.strptime(startDate, '%Y/%m/%d')
        end = datetime.strptime(endDate, '%Y/%m/%d')
        startDelta = start + timedelta(hours=delta)
        #You add the delta to the date
        endDelta = end + timedelta(hours=delta)
        #You add the delta to the date
        for xv in estation:
            name = tableContaminant + '_' + xv + '_delta'
            #name the column in the DataFrame
            sql = pd.read_sql_query(
                """SELECT fecha, val FROM {0} WHERE id_est ='{1}' AND fecha >= '{2}' AND fecha <= '{3}' ORDER BY fecha ASC;"""
                .format(tableContaminant, xv, startDelta, endDelta), conn)
            valPredic = sql['val']
            if valPredic.empty:
                temBuild = pd.DataFrame(values, columns=[name])
                build[name] = temBuild
            else:
                build[name] = valPredic
        conn.commit()
        cur.close()
        #The connection to the database is closed
        return build
    def get_climatology(fechaInicio, fechaFinal, estacion):
        """
        function to bring the climatology data stored in the database

        :param fechaInicio: range of data wit wich the vaues of tue query are extracted.
        :type fechaInicio: datetime
        :param fechaFinal: range of data wit wich the vaues of tue query are extracted.
        :type fechaFinal: datetime
        :param estacion: set the stations to get the values
        :type estacion: String
        :return:  all data that was taken from the database.
        """
        oztool = ContIOTools()
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        allData = pd.DataFrame()
        fechaMeta = fechaInicio
        while fechaMeta <= fechaFinal:
            month_init = fechaMeta.month
            hour_init = datetime.strptime(
                str(fechaMeta.hour) + ':00:00', '%H:%M:%S')
            tempData = pd.read_sql_query(
                """SELECT hora FROM climatologia WHERE id_est = '{0}' AND mes = {1} AND hora = '{2}' AND id_tabla = '{3}';"""
                .format(estacion, month_init, hour_init, 'cont_otres'), conn)
            contaminants = oztool.getTables()
            for xs in contaminants:
                temp = pd.read_sql_query(
                    """SELECT hora, val FROM climatologia WHERE id_est = '{0}' AND mes = {1} AND hora = '{2}' AND id_tabla = '{3}';"""
                    .format(estacion, month_init, hour_init, xs), conn)
                temp.rename(columns={
                    'fecha': 'fecha',
                    'val': xs + '_' + estacion.lower()
                },
                            inplace=True)
                if temp.empty:
                    pass
                else:
                    tempData = tempData.merge(temp, how='left', on='hora')
            conn.commit()
            cur.close()
            allData = pd.concat([allData, tempData], axis=0)
            fechaMeta = fechaMeta + timedelta(hours=1)
        allData = allData.reset_index(drop=True)
        return allData
    def get_forecast(nameContaminant, estacion, tipo):
        """
        function that brings from the database the last prediction saved

        :param nameContaminant: name of the pollutant
        :type nameContaminant: SyntaxWarning
        :param estacion: name of the weather station
        :type estacion:  String
        :return: DataFrame
        """
        oztool = ContIOTools()
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        nameTable = nameContaminant
        tempDataValues = pd.read_sql_query(
            """SELECT * FROM {0} WHERE id_est = '{1}' AND id_tipo_pronostico = {2} ORDER BY fecha DESC LIMIT 1; """
            .format(nameTable, estacion, tipo), conn)
        conn.commit()
        cur.close()
        return tempDataValues
    def saveData(estacion, fecha, Valor, contaminant, tipo):
        """
        function to save the prediction in the database

        :param estacion: name of the station to which the prediction belongs
        :type estacion: string
        :param fecha: prediction date
        :type fecha: date
        :param valor: prediction value
        :type valor: float32
        """
        table = contaminant
        oztool = ContIOTools()
        conexion = SqlCont()
        conn = conexion.getPostgresConn()
        cur = conn.cursor()
        #conexion database
        sql = """INSERT INTO {0}(fecha,val,id_est, id_tipo_pronostico) VALUES (\'{1}\',{2},\'{3}\',{4});""".format(
            table, fecha, Valor[0], estacion, tipo)
        cur.execute(sql)
        conn.commit()
        cur.close()