def fetch(cursor, sampling_event_id, locations=None): if not sampling_event_id: return None stmt = '''SELECT sampling_events.id, doc, doc_accuracy, location_id, proxy_location_id, individual_id FROM sampling_events WHERE sampling_events.id = %s''' cursor.execute( stmt, (sampling_event_id,)) sampling_event = None for (sampling_event_id, doc, doc_accuracy, location_id, proxy_location_id, individual_id) in cursor: sampling_event = SamplingEvent(str(sampling_event_id), doc=doc, doc_accuracy=doc_accuracy, individual_id=individual_id) if location_id: sampling_event.location_id = str(location_id) sampling_event.public_location_id = str(location_id) if proxy_location_id: sampling_event.proxy_location_id = str(proxy_location_id) sampling_event.public_location_id = str(proxy_location_id) if not sampling_event: return sampling_event sampling_event.attrs = SamplingEventFetch.fetch_attrs(cursor, sampling_event_id) sampling_event.event_sets = SamplingEventFetch.fetch_event_sets(cursor, sampling_event_id) if sampling_event.location_id: location = LocationFetch.fetch(cursor, sampling_event.location_id) if locations is not None: if sampling_event.location_id not in locations: locations[sampling_event.location_id] = location else: sampling_event.location = location if sampling_event.proxy_location_id: proxy_location = LocationFetch.fetch(cursor, sampling_event.proxy_location_id) if locations is not None: if sampling_event.proxy_location_id not in locations: locations[sampling_event.proxy_location_id] = proxy_location else: sampling_event.proxy_location = proxy_location return sampling_event
def get(self, partner_id): with self._connection: with self._connection.cursor() as cursor: cursor.execute( '''SELECT DISTINCT location_id FROM location_attrs JOIN attrs ON attrs.id = location_attrs.attr_id WHERE attr_type = %s AND attr_value = %s''', ( 'partner_name', partner_id, )) locations = Locations() locations.locations = [] locations.count = 0 locs = [] for (location_id, ) in cursor: locs.append(location_id) for location_id in locs: location = LocationFetch.fetch(cursor, location_id) locations.locations.append(location) locations.count = locations.count + 1 return locations
def check_for_duplicate(cursor, location, location_id): stmt = '''SELECT id, ST_X(location) as latitude, ST_Y(location) as longitude, accuracy, curated_name, curation_method, country FROM locations WHERE location = ST_SetSRID(ST_MakePoint(%s, %s), 4326)''' cursor.execute(stmt, ( location.latitude, location.longitude, )) existing_locations = [] for (loc_id, latitude, longitude, accuracy, curated_name, curation_method, country) in cursor: if location_id is None or loc_id != location_id: existing_locations.append(loc_id) for existing_id in existing_locations: existing_location = LocationFetch.fetch(cursor, existing_id) if existing_location.attrs: for existing_ident in existing_location.attrs: for ident in location.attrs: if ident.attr_type == existing_ident.attr_type and\ ident.attr_value == existing_ident.attr_value and\ ident.study_name == existing_ident.study_name: raise DuplicateKeyException( "Error updating location - duplicate with {}". format(existing_location))
def get(self, latitude, longitude): with self._connection: with self._connection.cursor() as cursor: cursor.execute( "SELECT id FROM locations WHERE ST_X(location) = %s AND ST_Y(location) = %s", ( latitude, longitude, )) locations = Locations() locations.locations = [] locations.count = 0 ids = [] for (location_id, ) in cursor: ids.append(str(location_id)) for location_id in ids: location = LocationFetch.fetch(cursor, location_id) locations.locations.append(location) locations.count = locations.count + 1 if len(locations.locations) == 0: raise MissingKeyException("GPS location not found {}, {}".format( latitude, longitude)) return locations
def get(self, location_id): location = None with self._connection: with self._connection.cursor() as cursor: location = LocationFetch.fetch(cursor, location_id) return location
def get(self, study_code=None, start=None, count=None, orderby='location'): result = Locations() with self._connection: with self._connection.cursor() as cursor: query_body = ' FROM locations l' args = () if study_code or orderby == 'study_name': query_body = query_body + ''' LEFT JOIN location_attrs li ON li.location_id = l.id JOIN attrs a ON li.attr_id = a.id LEFT JOIN studies s ON s.id = a.study_id''' if study_code: query_body = query_body + " WHERE study_code = %s" args = (study_code[:4], ) count_args = args count_query = 'SELECT COUNT(DISTINCT l.id) ' + query_body if orderby: query_body = query_body + " ORDER BY " + orderby + ", l.id" if not (start is None and count is None): query_body = query_body + ' LIMIT %s OFFSET %s' args = args + (count, start) if orderby: cursor.execute( 'SELECT DISTINCT l.id, ' + orderby + query_body, args) else: cursor.execute( 'SELECT DISTINCT l.id, l.curated_name ' + query_body, args) locations = [] for (location_id, ignored) in cursor: with self._connection.cursor() as lcursor: location = LocationFetch.fetch(lcursor, location_id) locations.append(location) if not (start is None and count is None): cursor.execute(count_query, count_args) result.count = cursor.fetchone()[0] else: result.count = len(locations) result.locations = locations return result
def check_location_details(cursor, location_id, location): current_location = None if location_id: current_location = LocationFetch.fetch(cursor, location_id) cli = current_location.attrs #It's OK if the location id is set but the location object not filled in if location: idents = location.attrs if location != current_location: raise NestedEditException( "Implied location edit not allowed for {}".format( location_id)) return current_location
def post(self, location): with self._connection: with self._connection.cursor() as cursor: LocationEdit.check_for_duplicate(cursor, location, None) uuid_val = uuid.uuid4() stmt = '''INSERT INTO locations (id, location, accuracy, curated_name, curation_method, country, notes) VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 4326), %s, %s, %s, %s, %s)''' args = (uuid_val, location.latitude, location.longitude, location.accuracy, location.curated_name, location.curation_method, location.country, location.notes) cursor.execute(stmt, args) LocationEdit.add_attrs(cursor, uuid_val, location) location = LocationFetch.fetch(cursor, uuid_val) return location
def get(self, location_id, start, count): with self._connection: with self._connection.cursor() as cursor: locations = {} try: location = LocationFetch.fetch(cursor, location_id) except MissingKeyException as mke: raise mke fields = '''SELECT sampling_events.id''' query_body = ''' FROM sampling_events WHERE location_id = %s OR proxy_location_id = %s''' args = ( location_id, location_id, ) count_args = args count_query = 'SELECT COUNT(sampling_events.id) ' + query_body query_body = query_body + ''' ORDER BY doc, id''' if not (start is None and count is None): query_body = query_body + ' LIMIT %s OFFSET %s' args = args + (count, start) sampling_events = SamplingEvents(sampling_events=[], count=0) stmt = fields + query_body cursor.execute(stmt, args) samp_ids = [] for samp_id in cursor: samp_ids.append(samp_id) locations = {} sampling_events.sampling_events = [] for samp_id in samp_ids: event = SamplingEventFetch.fetch(cursor, samp_id, locations) sampling_events.sampling_events.append(event) sampling_events.locations = locations if not (start is None and count is None): cursor.execute(count_query, count_args) sampling_events.count = cursor.fetchone()[0] else: sampling_events.count = len( sampling_events.sampling_events) sampling_events.attr_types = [] for samp_id in samp_ids: col_query = '''select distinct attr_type from sampling_event_attrs se JOIN attrs a ON se.attr_id=a.id WHERE sampling_event_id = %s''' cursor.execute(col_query, (samp_id, )) for (attr_type, ) in cursor: if attr_type not in sampling_events.attr_types: sampling_events.attr_types.append(attr_type) return sampling_events
def get(self, location_id, start, count): with self._connection: with self._connection.cursor() as cursor: try: location = LocationFetch.fetch(cursor, location_id) except MissingKeyException as mke: raise mke fields = '''SELECT os.id, study_name, sampling_event_id, days_in_culture, partner_species''' query_body = ''' FROM original_samples os JOIN sampling_events se ON se.id = os.sampling_event_id LEFT JOIN partner_species_identifiers psi ON psi.id = os.partner_species_id LEFT JOIN studies s ON s.id = os.study_id WHERE location_id = %s OR proxy_location_id = %s''' args = ( location_id, location_id, ) count_args = args count_query = 'SELECT COUNT(os.id) ' + query_body query_body = query_body + ''' ORDER BY study_name, os.id''' if not (start is None and count is None): query_body = query_body + ' LIMIT %s OFFSET %s' args = args + (count, start) original_samples = OriginalSamples(original_samples=[], count=0) stmt = fields + query_body cursor.execute(stmt, args) original_samples.original_samples, original_samples.sampling_events = OriginalSampleFetch.load_original_samples( cursor, True) if not (start is None and count is None): cursor.execute(count_query, count_args) original_samples.count = cursor.fetchone()[0] else: original_samples.count = len( original_samples.original_samples) original_samples.attr_types = [] col_query = '''select distinct attr_type from original_sample_attrs osa JOIN attrs a ON osa.attr_id=a.id JOIN original_samples os ON os.id = osa.original_sample_id JOIN sampling_events se ON se.id = os.sampling_event_id WHERE se.location_id = %s OR se.proxy_location_id = %s''' cursor.execute(col_query, (location_id, location_id)) for (attr_type, ) in cursor: original_samples.attr_types.append(attr_type) return original_samples