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
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")
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")
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")
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")
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
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")
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")