def run_command(self, cursor, sampling_event_id, sampling_event): stmt = '''SELECT id FROM sampling_events WHERE id = %s''' cursor.execute(stmt, (sampling_event_id, )) existing_sampling_event = None for (sampling_event_id, ) in cursor: existing_sampling_event = SamplingEvent(sampling_event_id) if not existing_sampling_event: raise MissingKeyException( "Could not find sampling_event to update {}".format( sampling_event_id)) SamplingEventEdit.check_date(sampling_event) SamplingEventEdit.check_location_details(cursor, sampling_event.location_id, sampling_event.location) SamplingEventEdit.check_location_details( cursor, sampling_event.proxy_location_id, sampling_event.proxy_location) stmt = '''UPDATE sampling_events SET doc = %s, doc_accuracy = %s, location_id = %s, proxy_location_id = %s, individual_id = %s WHERE id = %s''' args = (sampling_event.doc, sampling_event.doc_accuracy, sampling_event.location_id, sampling_event.proxy_location_id, sampling_event.individual_id, sampling_event_id) try: cursor.execute(stmt, args) rc = cursor.rowcount cursor.execute( 'DELETE FROM sampling_event_attrs WHERE sampling_event_id = %s', (sampling_event_id, )) SamplingEventEdit.add_attrs(cursor, sampling_event_id, sampling_event) except psycopg2.IntegrityError as err: raise DuplicateKeyException( "Error updating sampling_event {}".format( sampling_event)) from err except DuplicateKeyException as err: raise err sampling_event = SamplingEventFetch.fetch(cursor, sampling_event_id) if rc != 1: raise MissingKeyException( "Error updating sampling_event {}".format(sampling_event_id)) return sampling_event
def run_command(self, cursor, derivative_sample_id, derivative_sample): stmt = '''SELECT id FROM derivative_samples WHERE id = %s''' cursor.execute(stmt, (derivative_sample_id, )) existing_derivative_sample = None for (derivative_sample_id, ) in cursor: existing_derivative_sample = DerivativeSample(derivative_sample_id) if not existing_derivative_sample: raise MissingKeyException( "Could not find derivative_sample to update {}".format( derivative_sample_id)) stmt = '''UPDATE derivative_samples SET original_sample_id = %s, dna_prep = %s, parent_derivative_sample_id = %s WHERE id = %s''' args = (derivative_sample.original_sample_id, derivative_sample.dna_prep, derivative_sample.parent_derivative_sample_id, derivative_sample_id) try: cursor.execute(stmt, args) rc = cursor.rowcount cursor.execute( 'DELETE FROM derivative_sample_attrs WHERE derivative_sample_id = %s', (derivative_sample_id, )) DerivativeSampleEdit.add_attrs(cursor, derivative_sample_id, derivative_sample) except psycopg2.IntegrityError as err: raise DuplicateKeyException( "Error updating derivative_sample {}".format( derivative_sample)) from err except DuplicateKeyException as err: raise err derivative_sample = DerivativeSampleFetch.fetch( cursor, derivative_sample_id) if rc != 1: raise MissingKeyException( "Error updating derivative_sample {}".format( derivative_sample_id)) return derivative_sample
def get(self, study_name, start, count): with self._connection: with self._connection.cursor() as cursor: study_id = OriginalSampleEdit.fetch_study_id( cursor, study_name, False) if not study_id: raise MissingKeyException("No study {}".format(study_name)) fields = '''SELECT DISTINCT ds.id, original_sample_id, dna_prep, s.study_name ''' query_body = '''FROM derivative_samples ds JOIN original_samples os ON os.id = ds.original_sample_id LEFT JOIN studies s ON s.id = os.study_id WHERE os.study_id = %s''' args = (study_id, ) count_args = args count_query = 'SELECT COUNT(ds.id) ' + query_body query_body = query_body + ''' ORDER BY s.study_name, id''' if not (start is None and count is None): query_body = query_body + ' LIMIT %s OFFSET %s' args = args + (count, start) stmt = fields + query_body cursor.execute(stmt, args) derivative_samples = DerivativeSamples(derivative_samples=[], count=0) cursor.execute(stmt, args) derivative_samples.derivative_samples, derivative_samples.original_samples = DerivativeSampleFetch.load_derivative_samples( cursor, True) derivative_samples.count = len( derivative_samples.derivative_samples) if not (start is None and count is None): cursor.execute(count_query, count_args) derivative_samples.count = cursor.fetchone()[0] derivative_samples.attr_types = [] col_query = '''select distinct attr_type from derivative_sample_attrs dsa JOIN attrs a ON a.id=dsa.attr_id JOIN derivative_samples ds ON ds.id = dsa.derivative_sample_id JOIN original_samples os ON os.id = ds.original_sample_id WHERE os.study_id = %s''' cursor.execute(col_query, (study_id, )) for (attr_type, ) in cursor: derivative_samples.attr_types.append(attr_type) return derivative_samples
def delete(self, event_set_name, note_name): ret = None with self._connection: with self._connection.cursor() as cursor: event_set_id = EventSetFetch.fetch_event_set_id( cursor, event_set_name) stmt = '''SELECT note_name FROM event_set_notes WHERE event_set_id = %s AND note_name = %s''' cursor.execute(stmt, (event_set_id, note_name)) res = cursor.fetchone() if not res: raise MissingKeyException( "No such event set note {} {}".format( event_set_name, note_name)) stmt = '''DELETE FROM event_set_notes WHERE event_set_id = %s AND note_name = %s''' cursor.execute(stmt, (event_set_id, note_name)) ret = EventSetFetch.fetch(cursor, event_set_id, 0, 0) return ret
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 run_command(self, cursor, assay_datum_id, assay_datum): stmt = '''SELECT id FROM assay_data WHERE id = %s''' cursor.execute(stmt, (assay_datum_id, )) existing_assay_datum = None for (assay_datum_id, ) in cursor: existing_assay_datum = AssayDatum(assay_datum_id) if not existing_assay_datum: raise MissingKeyException( "Could not find assay_datum to update {}".format( assay_datum_id)) stmt = '''UPDATE assay_data SET derivative_sample_id = %s, ebi_run_acc = %s WHERE id = %s''' args = (assay_datum.derivative_sample_id, assay_datum.ebi_run_acc, assay_datum_id) try: cursor.execute(stmt, args) rc = cursor.rowcount cursor.execute( 'DELETE FROM assay_datum_attrs WHERE assay_datum_id = %s', (assay_datum_id, )) AssayDatumEdit.add_attrs(cursor, assay_datum_id, assay_datum) except psycopg2.IntegrityError as err: raise DuplicateKeyException( "Error updating assay_datum {}".format(assay_datum)) from err except DuplicateKeyException as err: raise err assay_datum = AssayDatumFetch.fetch(cursor, assay_datum_id) if rc != 1: raise MissingKeyException( "Error updating assay_datum {}".format(assay_datum_id)) return assay_datum
def put(self, study_id, study): if not study_id: raise MissingKeyException("No study {}".format(study_id)) with self._connection: with self._connection.cursor() as cursor: return self.run_command(cursor, study_id, study)
def add_sampling_event(cursor, event_set_id, sampling_event): if sampling_event: if event_set_id is None or sampling_event.sampling_event_id is None: raise MissingKeyException( "Invalid add_sampling_event {} {}".format( event_set_id, sampling_event)) stmt = '''INSERT INTO event_set_members (event_set_id, sampling_event_id) VALUES (%s, %s)''' cursor.execute(stmt, (event_set_id, sampling_event.sampling_event_id))
def merge(self, into, merged): ret = None with self._connection: with self._connection.cursor() as cursor: individual1 = IndividualFetch.fetch(cursor, into) if not individual1: raise MissingKeyException("No individual {}".format(into)) if individual1.individual_id == merged: return individual1 individual2 = IndividualFetch.fetch(cursor, merged) if not individual2: raise MissingKeyException("No individual {}".format(merged)) if individual2.attrs: for new_ident in individual2.attrs: found = False for existing_ident in individual1.attrs: if new_ident == existing_ident: found = True if not found: new_ident_value = True individual1.attrs.append(new_ident) stmt = '''UPDATE sampling_events SET individual_id = %s WHERE individual_id = %s''' cursor.execute(stmt, (individual1.individual_id, individual2.individual_id)) delete = IndividualDelete(self._connection) delete.delete(individual2.individual_id) put = IndividualPut(self._connection) ret = put.run_command(cursor, individual1.individual_id, individual1) return ret
def get(self, study_name, start, count): with self._connection: with self._connection.cursor() as cursor: study_id = OriginalSampleEdit.fetch_study_id(cursor, study_name, False) if not study_id: raise MissingKeyException("No study {}".format(study_name)) fields = '''SELECT original_samples.id, study_name, sampling_event_id, days_in_culture, partner_species''' query_body = ''' FROM original_samples LEFT JOIN sampling_events se ON se.id = original_samples.sampling_event_id LEFT JOIN studies s ON s.id = original_samples.study_id LEFT JOIN partner_species_identifiers psi ON psi.id = original_samples.partner_species_id WHERE s.id = %s''' args = (study_id,) count_args = args count_query = 'SELECT COUNT(original_samples.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) 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_samples os JOIN original_sample_attrs ose ON ose.original_sample_id=os.id JOIN attrs a ON a.id=ose.attr_id WHERE os.study_id = %s''' cursor.execute(col_query, (study_id,)) for (attr_type,) in cursor: original_samples.attr_types.append(attr_type) return original_samples
def get(self, assay_datum_id): with self._connection: with self._connection.cursor() as cursor: assay_datum = AssayDatumFetch.fetch(cursor, assay_datum_id) if not assay_datum: raise MissingKeyException( "No assay_datum {}".format(assay_datum_id)) return assay_datum
def run_command(self, cursor, individual_id, individual): stmt = '''SELECT id FROM individuals WHERE id = %s''' cursor.execute(stmt, (individual_id, )) existing_individual = None for (individual_id, ) in cursor: existing_individual = Individual(individual_id) if not existing_individual: raise MissingKeyException( "Error updating individual {}".format(individual_id)) stmt = '''UPDATE individuals SET id = %s WHERE id = %s''' args = (individual_id, individual_id) try: cursor.execute(stmt, args) rc = cursor.rowcount IndividualEdit.delete_attrs(cursor, individual_id) IndividualEdit.check_for_duplicate(cursor, individual, individual_id) IndividualEdit.add_attrs(cursor, individual_id, individual) except psycopg2.IntegrityError as err: raise DuplicateKeyException( "Error updating individual {}".format(individual)) from err individual = IndividualFetch.fetch(cursor, individual_id) if rc != 1: raise MissingKeyException( "Error updating individual {}".format(individual_id)) # return individual
def run_command(self, cursor, individual_id): IndividualEdit.delete_attrs(cursor, individual_id) stmt = '''DELETE FROM individuals WHERE id = %s''' cursor.execute(stmt, (individual_id, )) rc = cursor.rowcount if rc != 1: raise MissingKeyException( "Error deleting individual {}".format(individual_id))
def get(self, original_sample_id): with self._connection: with self._connection.cursor() as cursor: original_sample = OriginalSampleFetch.fetch( cursor, original_sample_id) if not original_sample: raise MissingKeyException( "No original_sample {}".format(original_sample_id)) return original_sample
def fetch_event_set_id(cursor, event_set_name): stmt = '''SELECT id FROM event_sets WHERE event_set_name = %s''' cursor.execute(stmt, (event_set_name, )) res = cursor.fetchone() if not res: raise MissingKeyException( "No such event set {}".format(event_set_name)) return res[0]
def get(self, derivative_sample_id): with self._connection: with self._connection.cursor() as cursor: derivative_sample = DerivativeSampleFetch.fetch( cursor, derivative_sample_id) if not derivative_sample: raise MissingKeyException( "No derivative_sample {}".format(derivative_sample_id)) return derivative_sample
def get(self, sampling_event_id): with self._connection: with self._connection.cursor() as cursor: sampling_event = SamplingEventFetch.fetch( cursor, sampling_event_id) if not sampling_event: raise MissingKeyException( "No sampling_event {}".format(sampling_event_id)) return sampling_event
def run_command(self, cursor, derivative_sample_id): stmt = '''SELECT original_sample_id, parent_derivative_sample_id FROM derivative_samples WHERE id = %s''' cursor.execute(stmt, (derivative_sample_id,)) original_sample_id = None parent_derivative_sample_id = None for (orig_sample_id, parent_deriv_sample_id) in cursor: original_sample_id = orig_sample_id parent_derivative_sample_id = parent_deriv_sample_id try: stmt = '''SELECT id FROM derivative_samples WHERE parent_derivative_sample_id = %s''' cursor.execute(stmt, (derivative_sample_id,)) for (sample_id,) in cursor: if parent_derivative_sample_id: stmt = '''UPDATE derivative_samples SET parent_derivative_sample_id = %s WHERE id = %s''' cursor.execute(stmt, (parent_derivative_sample_id, sample_id,)) elif original_sample_id: stmt = '''UPDATE derivative_samples SET original_sample_id = %s, parent_derivative_sample_id = NULL WHERE id = %s''' cursor.execute(stmt, (original_sample_id, sample_id,)) else: stmt = '''UPDATE derivative_samples SET parent_derivative_sample_id = NULL WHERE id = %s''' cursor.execute(stmt, (sample_id,)) except psycopg2.ProgrammingError as err: pass stmt = '''DELETE FROM derivative_sample_attrs WHERE derivative_sample_id = %s''' cursor.execute(stmt, (derivative_sample_id,)) stmt = '''DELETE FROM derivative_samples WHERE id = %s''' cursor.execute(stmt, (derivative_sample_id,)) rc = cursor.rowcount if rc != 1: raise MissingKeyException("Error deleting derivative_sample {}".format(derivative_sample_id))
def run_command(self, cursor, location_id): stmt = '''DELETE FROM location_attrs WHERE location_id = %s''' cursor.execute(stmt, (location_id, )) stmt = '''DELETE FROM locations WHERE id = %s''' cursor.execute(stmt, (location_id, )) rc = cursor.rowcount if rc != 1: raise MissingKeyException( "Error deleting location {}".format(location_id))
def run_command(self, cursor, assay_datum_id): stmt = '''DELETE FROM assay_datum_attrs WHERE assay_datum_id = %s''' cursor.execute(stmt, (assay_datum_id, )) stmt = '''DELETE FROM assay_data WHERE id = %s''' cursor.execute(stmt, (assay_datum_id, )) rc = cursor.rowcount if rc != 1: raise MissingKeyException( "Error deleting assay_datum {}".format(assay_datum_id))
def fetch(cursor, study_id): if not study_id: return None 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)) study = Study(name=result[1], code=result[2]) stmt = '''SELECT id, study_id, partner_species FROM partner_species_identifiers WHERE study_id = %s''' cursor.execute(stmt, (study_uuid, )) study.partner_species = [] ps_id_map = {} for (psid, study_uuid, partner_species) in cursor: ps = PartnerSpecies([], partner_species=partner_species) ps_id_map[partner_species] = psid study.partner_species.append(ps) for ps in study.partner_species: psid = ps_id_map[ps.partner_species] stmt = '''SELECT taxonomies.id, taxonomies.rank, taxonomies.name FROM taxonomy_identifiers JOIN taxonomies ON taxonomies.id = taxonomy_identifiers.taxonomy_id WHERE partner_species_id = %s''' cursor.execute(stmt, (psid, )) for (tid, rank, name) in cursor: taxa = Taxonomy(tid, name=name, rank=rank) ps.taxa.append(taxa) get = LocationsGet(cursor.connection) locs = get.get(study_id) study.locations = locs return study
def run_command(self, cursor, sampling_event_id): stmt = '''SELECT individual_id FROM sampling_events WHERE id = %s''' cursor.execute( stmt, (sampling_event_id,)) individual_id = None for (individual_id, ) in cursor: pass individual_ids = 0 if individual_id: stmt = '''SELECT COUNT(individual_id) FROM sampling_events WHERE individual_id = %s''' cursor.execute( stmt, (individual_id,)) individual_ids = cursor.fetchone()[0] stmt = '''UPDATE original_samples SET sampling_event_id = NULL WHERE sampling_event_id = %s''' cursor.execute( stmt, (sampling_event_id,)) stmt = '''DELETE FROM sampling_event_attrs WHERE sampling_event_id = %s''' cursor.execute( stmt, (sampling_event_id,)) stmt = '''DELETE FROM event_set_members WHERE sampling_event_id = %s''' cursor.execute( stmt, (sampling_event_id,)) stmt = '''DELETE FROM sampling_events WHERE id = %s''' cursor.execute( stmt, (sampling_event_id,)) rc = cursor.rowcount if individual_ids == 1: i_delete = IndividualDelete(self._connection) i_delete.run_command(cursor, individual_id) if rc != 1: raise MissingKeyException("Error deleting sampling_event {}".format(sampling_event_id))
def fetch(cursor, location_id): if not location_id: return None stmt = '''SELECT id, ST_X(location) as latitude, ST_Y(location) as longitude, accuracy, curated_name, curation_method, country, notes FROM locations WHERE id = %s''' cursor.execute(stmt, (location_id, )) location = None for (location_id, latitude, longitude, accuracy, curated_name, curation_method, country, notes) in cursor: location = Location(str(location_id), latitude, longitude, accuracy, curated_name, curation_method, country, notes) stmt = '''SELECT DISTINCT attr_type, attr_value, attr_source, studies.study_name FROM location_attrs JOIN attrs a ON a.id = location_attrs.attr_id LEFT JOIN studies ON a.study_id = studies.id WHERE location_id = %s AND attr_type = %s''' cursor.execute(stmt, (location_id, 'partner_name')) if not location: raise MissingKeyException("No location {}".format(location_id)) location.attrs = [] for (name, value, source, study) in cursor: ident = Attr(attr_type=name, attr_value=value, attr_source=source, study_name=study) location.attrs.append(ident) if len(location.attrs) == 0: location.attrs = None return location
def delete(self, event_set_name, sampling_event_id): resp = None with self._connection: with self._connection.cursor() as cursor: event_set_id = EventSetFetch.fetch_event_set_id( cursor, event_set_name) stmt = '''DELETE FROM event_set_members WHERE event_set_id = %s AND sampling_event_id = %s''' cursor.execute(stmt, (event_set_id, sampling_event_id)) if cursor.rowcount != 1: raise MissingKeyException( 'Sampling event not found in event set {}'.format( event_set_name)) resp = EventSetFetch.fetch(cursor, event_set_id, 0, 0) return resp
def run_command(self, cursor, original_sample_id): stmt = '''UPDATE derivative_samples SET original_sample_id = NULL WHERE original_sample_id = %s''' cursor.execute(stmt, (original_sample_id, )) stmt = '''DELETE FROM original_sample_attrs WHERE original_sample_id = %s''' cursor.execute(stmt, (original_sample_id, )) stmt = '''DELETE FROM original_samples WHERE id = %s''' cursor.execute(stmt, (original_sample_id, )) rc = cursor.rowcount OriginalSampleEdit.clean_up_taxonomies(cursor) if rc != 1: raise MissingKeyException( "Error deleting original_sample {}".format(original_sample_id))
def put(self, event_set_name, note_id, note): ret = None with self._connection: with self._connection.cursor() as cursor: event_set_id = EventSetFetch.fetch_event_set_id(cursor,event_set_name) stmt = '''UPDATE event_set_notes SET note_text = %s, note_name = %s WHERE event_set_id = %s AND note_name = %s''' try: cursor.execute(stmt, (note.note_text, note.note_name, event_set_id, note_id)) except psycopg2.IntegrityError as err: raise DuplicateKeyException("Error updating event set note id from {} to {} in {}".format(note_id, note.note_name, event_set_id )) from err if cursor.rowcount != 1: raise MissingKeyException('No note {} in event set {}'.format(note.note_name, event_set_name)) ret = EventSetFetch.fetch(cursor, event_set_id, 0, 0) return ret
def get(self, country_id): with self._connection: with self._connection.cursor() as cursor: stmt = '''SELECT english, alpha2, alpha3 FROM countries WHERE ''' if len(country_id) == 2: stmt = stmt + ''' LOWER(alpha2) =%s''' elif len(country_id) == 3: stmt = stmt + ''' LOWER(alpha3) =%s''' else: stmt = stmt + ''' LOWER(english) =%s''' cursor.execute(stmt, (country_id.casefold(), )) country = None for (english, alpha2, alpha3) in cursor: country = Country(english, alpha2, alpha3) if not country: raise MissingKeyException( "No country {}".format(country_id)) return country
def fetch(cursor, individual_id): if not individual_id: return None stmt = '''SELECT id FROM individuals WHERE id = %s''' cursor.execute(stmt, (individual_id, )) individual = None for (individual_id, ) in cursor: individual = Individual(str(individual_id)) stmt = '''SELECT DISTINCT attr_type, attr_value, attr_source, studies.study_name FROM individual_attrs JOIN attrs a ON a.id = individual_attrs.attr_id LEFT JOIN studies ON a.study_id = studies.id WHERE individual_id = %s''' cursor.execute(stmt, (individual_id, )) if not individual: raise MissingKeyException("No individual {}".format(individual_id)) individual.attrs = [] for (name, value, source, study) in cursor: ident = Attr(attr_type=name, attr_value=value, attr_source=source, study_name=study) individual.attrs.append(ident) if len(individual.attrs) == 0: individual.attrs = None return individual
def merge(self, into, merged): ret = None with self._connection: with self._connection.cursor() as cursor: original_sample1 = OriginalSampleFetch.fetch(cursor, into) if not original_sample1: raise MissingKeyException( "No original_sample {}".format(into)) if original_sample1.original_sample_id == merged: return original_sample1 original_sample2 = OriginalSampleFetch.fetch(cursor, merged) if not original_sample2: raise MissingKeyException( "No original_sample {}".format(merged)) if original_sample1.study_name: if original_sample2.study_name: if original_sample1.study_name[:4] == '0000': original_sample1.study_name = original_sample2.study_name elif original_sample2.study_name[:4] == '0000': pass elif original_sample1.study_name != original_sample2.study_name: msg = 'Incompatible study_name {} {}'.format( original_sample1.study_name, original_sample2.study_name) raise IncompatibleException(msg) else: original_sample1.study_name = original_sample2.study_name if original_sample1.days_in_culture: if original_sample2.days_in_culture: if original_sample1.days_in_culture != original_sample2.days_in_culture: msg = 'Incompatible days_in_culture {} {}'.format( original_sample1.days_in_culture, original_sample2.days_in_culture) raise IncompatibleException(msg) else: original_sample1.days_in_culture = original_sample2.days_in_culture if original_sample1.partner_species: if original_sample2.partner_species: if original_sample1.partner_species != original_sample2.partner_species: msg = 'Incompatible partner_species {} {}'.format( original_sample1.partner_species, original_sample2.partner_species) raise IncompatibleException(msg) else: original_sample1.partner_species = original_sample2.partner_species if original_sample2.attrs: for new_ident in original_sample2.attrs: found = False for existing_ident in original_sample1.attrs: if new_ident == existing_ident: found = True if not found: new_ident_value = True original_sample1.attrs.append(new_ident) if original_sample1.sampling_event_id: if original_sample2.sampling_event_id: merge = SamplingEventMerge(self._connection) merged_se = merge.run_command( cursor, original_sample1.sampling_event_id, original_sample2.sampling_event_id) original_sample1.sampling_event_id = merged_se.sampling_event_id original_sample2.sampling_event_id = None else: original_sample1.sampling_event_id = original_sample2.sampling_event_id stmt = '''UPDATE derivative_samples SET original_sample_id = %s WHERE original_sample_id = %s''' cursor.execute(stmt, (original_sample1.original_sample_id, original_sample2.original_sample_id)) delete = OriginalSampleDelete(self._connection) delete.run_command(cursor, original_sample2.original_sample_id) put = OriginalSamplePut(self._connection) ret = put.run_command(cursor, original_sample1.original_sample_id, original_sample1) return ret
def get(self, study_name, start, count): with self._connection: with self._connection.cursor() as cursor: study_id = SamplingEventEdit.fetch_study_id( cursor, study_name, False) if not study_id: raise MissingKeyException("No study {}".format(study_name)) fields = '''SELECT sampling_events.id ''' query_body = ''' FROM sampling_events LEFT JOIN original_samples os ON os.sampling_event_id = sampling_events.id LEFT JOIN studies ON studies.id = os.study_id WHERE os.study_id = %s''' args = (study_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 = [] col_query = '''select distinct attr_type from sampling_event_attrs se JOIN attrs a ON se.sampling_event_id=a.id WHERE a.study_id = %s''' cursor.execute(col_query, (study_id, )) for (attr_type, ) in cursor: sampling_events.attr_types.append(attr_type) return sampling_events