def get(self):
response = Studies([],0)
with self._connection:
with self._connection.cursor() as cursor:
#, curated_name, accuracy, country, partner_name
# ST_X(location) as latitude, ST_Y(location) as longitude
stmt = '''select study_code from locations l
join location_attrs li ON li.location_id = l.id
JOIN attrs a ON a.id = li.attr_id
JOIN studies s ON a.study_id = s.id
group by location, study_code
having count(location) > 1 ORDER BY study_code;'''
cursor.execute(stmt)
studies = []
for (study_name,) in cursor:
studies.append(study_name)
for study_id in studies:
study = StudyFetch.fetch(cursor, study_id)
response.studies.append(study)
response.count = response.count + 1
return response
def get(self):
response = Studies([], 0)
with self._connection:
with self._connection.cursor() as cursor:
stmt = '''select distinct study_name from partner_species_identifiers
LEFT JOIN taxonomy_identifiers ON taxonomy_identifiers.partner_species_id = partner_species_identifiers.id
JOIN studies ON studies.id=study_id
WHERE taxonomy_id IS NULL ORDER BY study_name'''
cursor.execute(stmt)
studies = []
for (study_name, ) in cursor:
studies.append(study_name)
for study_id in studies:
study = StudyFetch.fetch(cursor, study_id)
response.studies.append(study)
response.count = response.count + 1
return response
def get(self, include_country):
response = Studies([],0)
with self._connection:
with self._connection.cursor() as cursor:
stmt = '''select distinct study_code, accuracy FROM sampling_events
LEFT JOIN studies ON studies.id = study_id
LEFT JOIN locations ON locations.id = location_id
WHERE location_id IS NULL OR locations.accuracy = 'country';'''
cursor.execute(stmt)
studies = []
for (study_name, accuracy) in cursor:
if accuracy and accuracy == 'country':
if include_country:
studies.append(study_name)
else:
studies.append(study_name)
for study_id in studies:
study = StudyFetch.fetch(cursor, study_id)
response.studies.append(study)
response.count = response.count + 1
return response
def get(self):
response = Studies([], 0)
with self._connection:
with self._connection.cursor() as cursor:
# , curated_name, accuracy, country, partner_name
stmt = '''select distinct studies.study_name AS study_id FROM sampling_events
LEFT JOIN studies ON studies.id = sampling_events.study_id
where curated_name is NULL or accuracy IS NULL OR country IS NULL ORDER BY study_id;'''
cursor.execute(stmt)
studies = []
for (study_name, ) in cursor:
studies.append(study_name)
for study_id in studies:
study = StudyFetch.fetch(cursor, study_id)
response.studies.append(study)
response.count = response.count + 1
return response
def get(self, study_id):
study = None
with self._connection:
with self._connection.cursor() as cursor:
study = StudyFetch.fetch(cursor, study_id)
return study
def run_command(self, cursor, study_id, study):
cursor.execute(
'''SELECT id, study_name, study_code FROM studies WHERE study_code = %s''',
(study_id[:4], ))
result = cursor.fetchone()
study_uuid = None
if result:
study_uuid = result[0]
else:
raise MissingKeyException("No study {}".format(study_id))
stmt = '''UPDATE studies
SET study_name = %s
WHERE id = %s'''
args = (study.name, study_uuid)
try:
cursor.execute(stmt, args)
stmt = '''SELECT id, study_id, partner_species FROM partner_species_identifiers WHERE
study_id = %s'''
cursor.execute(stmt, (study_uuid, ))
ps_ids = []
for (psid, stud_id, partner_species) in cursor:
ps = PartnerSpecies([], partner_species=partner_species)
ps_ids.append(psid)
for psid in ps_ids:
cursor.execute(
'''DELETE FROM taxonomy_identifiers WHERE
partner_species_id = %s''', (psid, ))
#The partner_species really relates to the sampling event not the study
#so can't just blow away and rebuild
for species in study.partner_species:
stmt = '''SELECT id, study_id, partner_species FROM partner_species_identifiers WHERE
study_id = %s AND partner_species = %s'''
cursor.execute(stmt, (
study_uuid,
species.partner_species,
))
result = cursor.fetchone()
psid = None
if result:
psid = result[0]
else:
psid = uuid.uuid4()
cursor.execute(
'''INSERT INTO partner_species_identifiers (id, study_id,
partner_species) VALUES (%s, %s, %s)''',
(psid, study_uuid, species.partner_species))
for taxa in species.taxa:
cursor.execute(
'''INSERT INTO taxonomy_identifiers (taxonomy_id,
partner_species_id) VALUES (%s, %s)''',
(taxa.taxonomy_id, psid))
except psycopg2.IntegrityError as err:
raise IntegrityException(
"Error updating study {}".format(study)) from err
updated_study = StudyFetch.fetch(cursor, study_id)
return updated_study