Exemple #1
0
def get_translation_id(strn, lang=statmart_default_language):
    cache_key = lang + " " + strn
    if cache_key in translation_id_cache:
        return translation_id_cache[cache_key]
    if len(
            lang
    ) > 2:  # Since 'lang' is used outside of the prepared statement, we need to protect against SQL injection
        raise Exception(
            "Bad language: '%s' - this value must be a maximum of two characters."
        )
    with us.get_db_connection() as cursor:
        query = "SELECT id FROM translation WHERE " + lang + " = %s;"
        count = cursor.execute(query, [strn])
        if count == 1:
            id = list(cursor)[0][0]
            translation_id_cache[cache_key] = id
            return id
        if count > 1:
            raise Exception(
                "There are multiple instances of the phrase '%s' in the database. This case is not currently supported."
            )
        query = "INSERT INTO translation (" + lang + ") VALUES (%s);"
        cursor.execute(query, [strn.strip()])
        cursor.execute("SELECT LAST_INSERT_ID();")
        id = list(cursor)[0][0]
        translation_id_cache[cache_key] = id
        cursor.execute("COMMIT")
        return id
Exemple #2
0
def get_series_info_map_json(config):
    df = ""
    with us.get_db_connection() as cursor:
        query = """
                SELECT identifier, originalsource, proximatesource
                FROM series
                WHERE series.identifier like '%s\_%s\_%s'
                """ % (config["prefix"], "%", config["suffix"])
        df = psql.frame_query(query, con=cursor.connection)
        df = df.set_index("identifier")
    return df.to_json(orient="index")
Exemple #3
0
def get_series_info_map_json(config):
    df = ""
    with us.get_db_connection() as cursor:
        query = """
                SELECT identifier, originalsource, proximatesource
                FROM series
                WHERE series.identifier like '%s\_%s\_%s'
                """ % (config["prefix"], "%", config["suffix"])
        df = psql.frame_query(query, con=cursor.connection)
        df = df.set_index("identifier")
    return df.to_json(orient="index")
Exemple #4
0
def get_iso3_map_json(config):
    df = ""
    with us.get_db_connection() as cursor:
        query = """
                SELECT L.iso3, L.countryname, O.series, COUNT(O.dateid) as numberyears
                FROM observation as O
                INNER JOIN location as L ON O.locationid = L.id
                WHERE O.series like '%s\_%s\_%s'
                GROUP BY L.iso3
                """ % (config["prefix"], "%", config["suffix"])
        df = psql.frame_query(query, con=cursor.connection)
        df = df.set_index("iso3")
    return df.to_json(orient="index")
Exemple #5
0
def get_iso3_map_json(config):
    df = ""
    with us.get_db_connection() as cursor:
        query = """
                SELECT L.iso3, L.countryname, O.series, COUNT(O.dateid) as numberyears
                FROM observation as O
                INNER JOIN location as L ON O.locationid = L.id
                WHERE O.series like '%s\_%s\_%s'
                GROUP BY L.iso3
                """ % (config["prefix"], "%", config["suffix"])
        df = psql.frame_query(query, con=cursor.connection)
        df = df.set_index("iso3")
    return df.to_json(orient="index")
Exemple #6
0
def get_translation_id(strn, lang=statmart_default_language):
    cache_key = lang + " " + strn
    if cache_key in translation_id_cache:
        return translation_id_cache[cache_key]
    if len(lang) > 2: # Since 'lang' is used outside of the prepared statement, we need to protect against SQL injection
        raise Exception("Bad language: '%s' - this value must be a maximum of two characters.")
    with us.get_db_connection() as cursor:
        query = "SELECT id FROM translation WHERE " + lang + " = %s;" 
        count = cursor.execute(query, [strn])
        if count == 1:
            id = list(cursor)[0][0]
            translation_id_cache[cache_key] = id
            return id
        if count > 1:
            raise Exception("There are multiple instances of the phrase '%s' in the database. This case is not currently supported.")
        query = "INSERT INTO translation (" + lang + ") VALUES (%s);"
        cursor.execute(query, [strn.strip()])
        cursor.execute("SELECT LAST_INSERT_ID();")
        id = list(cursor)[0][0]
        translation_id_cache[cache_key] = id
        cursor.execute("COMMIT")
        return id
Exemple #7
0
def standard_load_from_data_list(data_list, observation_fields="series, locationid, dateid, value, source, note"):
    with us.get_db_connection() as cursor:
        for (series_key, iso3, meta, data) in data_list:
            cursor.execute("SELECT ID FROM location WHERE iso3 = %s AND divisionname IS NULL AND city IS NULL", [iso3])
            locationid = list(cursor)[0][0]
            
            cursor.execute("DELETE FROM series WHERE identifier = %s", series_key)
            cursor.execute("DELETE FROM observation WHERE series = %s", series_key)
            
            series_fields = "identifier, dataset, file, filehash, category, type, name, " + \
                            "description, originalsource, proximatesource, originalsourcelink, proximatesourcelink"
            insert_series = "INSERT INTO series (" + series_fields +") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" 
            values = [series_key]
            for field in series_fields.split(",")[1:]:
                field = field.strip()
                if field in meta:
                    if pd.isnull(meta[field]):
                        values.append(None)
                    else:
                        values.append(meta[field])
                else:
                    values.append(None)
            cursor.execute(insert_series, values)

            for indx in data.index:
                record = data.ix[indx];
                values = []
                valplaces = []
                for field in observation_fields.split(","):
                    valplaces.append("%s")
                    field = field.strip()
                    if field == "dateid": #TODO: this needs to be revisited; it assumes that "year" is the only date value
                        cursor.execute("SELECT ID FROM date WHERE year = %s AND month IS NULL AND day IS NULL", [str(record["year"])])
                        dateid = list(cursor)[0][0]        
                        values.append(dateid)      
                    
                    if field == "description":
                        values.append(get_translated_field(data, record, "description"))
                
                    if field == "locationid": #TODO: this needs to be revisited
                        values.append(locationid)   
                        
                    if field == "note":        
                        values.append(get_translated_field(data, record, "note"))
                
                    if field == "series":
                        values.append(series_key)
                        
                    if field == "source":               
                        values.append(get_translated_field(data, record, "source"))     
                
                    if field == "value":
                        values.append(str(record["value"]).strip())
                                
                insert_observation = "INSERT INTO observation(" + observation_fields + ") VALUES (" + ", ".join(valplaces) + ")"
                # this logging can be very chatty
                if verbose:
                    us.log(insert_observation)
                    us.log(values)
                cursor.execute(insert_observation, values)
            cursor.execute("COMMIT")
Exemple #8
0
def standard_load_from_data_list(
        data_list,
        observation_fields="series, locationid, dateid, value, source, note"):
    with us.get_db_connection() as cursor:
        for (series_key, iso3, meta, data) in data_list:
            cursor.execute(
                "SELECT ID FROM location WHERE iso3 = %s AND divisionname IS NULL AND city IS NULL",
                [iso3])
            locationid = list(cursor)[0][0]

            cursor.execute("DELETE FROM series WHERE identifier = %s",
                           series_key)
            cursor.execute("DELETE FROM observation WHERE series = %s",
                           series_key)

            series_fields = "identifier, dataset, file, filehash, category, type, name, " + \
                            "description, originalsource, proximatesource, originalsourcelink, proximatesourcelink"
            insert_series = "INSERT INTO series (" + series_fields + ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            values = [series_key]
            for field in series_fields.split(",")[1:]:
                field = field.strip()
                if field in meta:
                    if pd.isnull(meta[field]):
                        values.append(None)
                    else:
                        values.append(meta[field])
                else:
                    values.append(None)
            cursor.execute(insert_series, values)

            for indx in data.index:
                record = data.ix[indx]
                values = []
                valplaces = []
                for field in observation_fields.split(","):
                    valplaces.append("%s")
                    field = field.strip()
                    if field == "dateid":  #TODO: this needs to be revisited; it assumes that "year" is the only date value
                        cursor.execute(
                            "SELECT ID FROM date WHERE year = %s AND month IS NULL AND day IS NULL",
                            [str(record["year"])])
                        dateid = list(cursor)[0][0]
                        values.append(dateid)

                    if field == "description":
                        values.append(
                            get_translated_field(data, record, "description"))

                    if field == "locationid":  #TODO: this needs to be revisited
                        values.append(locationid)

                    if field == "note":
                        values.append(
                            get_translated_field(data, record, "note"))

                    if field == "series":
                        values.append(series_key)

                    if field == "source":
                        values.append(
                            get_translated_field(data, record, "source"))

                    if field == "value":
                        values.append(str(record["value"]).strip())

                insert_observation = "INSERT INTO observation(" + observation_fields + ") VALUES (" + ", ".join(
                    valplaces) + ")"
                # this logging can be very chatty
                if verbose:
                    us.log(insert_observation)
                    us.log(values)
                cursor.execute(insert_observation, values)
            cursor.execute("COMMIT")