def find_or_create_site(db: DatabaseSession, identifier: str, details: dict) -> Any: """ Select encounter site by *identifier*, or insert it if it doesn't exist. """ LOG.debug(f"Looking up site «{identifier}»") site = db.fetch_row( """ select site_id as id, identifier from warehouse.site where identifier = %s """, (identifier, )) if site: LOG.info(f"Found site {site.id} «{site.identifier}»") else: LOG.debug(f"Site «{identifier}» not found, adding") data = { "identifier": identifier, "details": Json(details), } site = db.fetch_row( """ insert into warehouse.site (identifier, details) values (%(identifier)s, %(details)s) returning site_id as id, identifier """, data) LOG.info(f"Created site {site.id} «{site.identifier}»") return site
def find_or_create_target(db: DatabaseSession, identifier: str, control: bool) -> Any: """ Select presence_absence test target by *identifier*, or insert it if it doesn't exist. """ LOG.debug(f"Looking up target «{identifier}»") target = db.fetch_row( """ select target_id as id, identifier from warehouse.target where identifier = %s """, (identifier, )) if target: LOG.info(f"Found target {target.id} «{target.identifier}»") else: LOG.debug(f"Target «{identifier}» not found, adding") data = {"identifier": identifier, "control": control} target = db.fetch_row( """ insert into warehouse.target (identifier, control) values (%(identifier)s, %(control)s) returning target_id as id, identifier """, data) LOG.info(f"Created target {target.id} «{target.identifier}»") return target
def update_sample(db: DatabaseSession, identifier: str = None, collection_identifier: str = None, additional_details: dict = None) -> Any: """ Find sample by *identifier* and update with any *additional_details*. The provided *additional_details* are merged (at the top-level only) into the existing sample details, if any. Raises an :class:`SampleNotFoundError` if there is no sample known by *identifier*. """ if identifier: LOG.debug(f"Looking up sample with identifier «{identifier}»") sample = db.fetch_row(""" select sample_id as id, identifier, details from warehouse.sample where identifier = %s for update """, (identifier,)) elif collection_identifier: LOG.debug(f"Looking up sample with collection_identifier «{collection_identifier}»") sample = db.fetch_row(""" select sample_id as id, collection_identifier as identifier, details from warehouse.sample where collection_identifier = %s for update """, (collection_identifier,)) if not sample: LOG.error(f"No sample found with identifier «{identifier}» or collection identifier «{collection_identifier}»") raise SampleNotFoundError(identifier) LOG.info(f"Found sample {sample.id} «{sample.identifier}»") if additional_details: LOG.info(f"Updating sample {sample.id} «{sample.identifier}» details") update_details_nwgc_id(sample, additional_details) sample = db.fetch_row(""" update warehouse.sample set details = coalesce(details, '{}') || %s where sample_id = %s returning sample_id as id, identifier """, (Json(additional_details), sample.id)) assert sample.id, "Updating details affected no rows!" return sample
def update_sample( db: DatabaseSession, sample, encounter_id: Optional[int] = None) -> Optional[MinimalSampleRecord]: """ Update sample's encounter_id. """ LOG.debug( f"Updating sample {sample.id}, linked to encounter {encounter_id}") if sample.encounter_id: assert sample.encounter_id == encounter_id, \ f"Sample {sample.id} already linked to another encounter {sample.encounter_id}" return None sample = db.fetch_row( """ update warehouse.sample set encounter_id = %s where sample_id = %s returning sample_id as id, identifier """, (encounter_id, sample.id)) assert sample.id, "Updating encounter_id affected no rows!" LOG.info(f"Updated sample {sample.id}") return sample
def find_sample(db: DatabaseSession, identifier: str, for_update=True) -> Any: """ Find sample by *identifier* and return sample. """ LOG.debug(f"Looking up sample «{identifier}»") query_ending = "" if for_update: query_ending = "for update" sample = db.fetch_row( """ select sample_id as id, identifier, encounter_id from warehouse.sample where identifier = %s or collection_identifier = %s """ + query_ending, ( identifier, identifier, )) if not sample: LOG.info(f"No sample with identifier «{identifier}» found") return None LOG.info(f"Found sample {sample.id} «{sample.identifier}»") return sample
def upsert_sample(db: DatabaseSession, collection_identifier: str, encounter_id: int, details: dict) -> Any: """ Upsert collected sample by its *collection_identifier*. The provided *details* are merged (at the top-level only) into the existing sample details, if any. """ LOG.debug(f"Upserting sample collection «{collection_identifier}»") data = { "collection_identifier": collection_identifier, "encounter_id": encounter_id, "details": Json(details), } sample = db.fetch_row( """ insert into warehouse.sample (collection_identifier, encounter_id, details) values (%(collection_identifier)s, %(encounter_id)s, %(details)s) on conflict (collection_identifier) do update set encounter_id = excluded.encounter_id, details = coalesce(sample.details, '{}') || %(details)s returning sample_id as id, identifier, collection_identifier, encounter_id """, data) assert sample.id, "Upsert affected no rows!" LOG.info( f"Upserted sample {sample.id} with collection identifier «{sample.collection_identifier}»" ) return sample
def upsert_individual(db: DatabaseSession, identifier: str, sex: str = None) -> Any: """ Upsert individual by their *identifier*. """ LOG.debug(f"Upserting individual «{identifier}»") data = { "identifier": identifier, "sex": sex, } individual = db.fetch_row( """ insert into warehouse.individual (identifier, sex) values (%(identifier)s, %(sex)s) on conflict (identifier) do update set sex = excluded.sex returning individual_id as id, identifier """, data) assert individual.id, "Upsert affected no rows!" LOG.info(f"Upserted individual {individual.id} «{individual.identifier}»") return individual
def find_or_create_sequence_read_set( db: DatabaseSession, document: dict, sample: MinimalSampleRecord) -> SequenceReadSetRecord: """ Find sequence read set given a *sample* and consensus genome record *document*, inserting the sequence read set if it does not exist. Return the sequence read set. """ urls = document['metadata']['urls'] LOG.debug( dedent(f""" Looking up sequence read set with sample ID «{sample.id}» and urls {urls} """)) sequence_read_set: SequenceReadSetRecord = db.fetch_row( """ select sequence_read_set_id as id, sample_id, urls from warehouse.sequence_read_set where sample_id = %s and urls @> %s and %s @> urls """, (sample.id, urls, urls)) if sequence_read_set: LOG.info(f"Found sequence read set {sequence_read_set.id}") else: LOG.debug( dedent(f""" Sequence read set not found for sample id «{sample.id}» and urls {urls} """)) data = { "sample_id": sample.id, "urls": urls, } sequence_read_set = db.fetch_row( """ insert into warehouse.sequence_read_set (sample_id, urls) values (%(sample_id)s, %(urls)s) returning sequence_read_set_id as id, sample_id, urls """, data) LOG.info(f"Created sequence read set {sequence_read_set.id}") return sequence_read_set
def find_kit(db: DatabaseSession, identifier: str) -> KitRecord: """ Look for kit using *identifier* within the database """ kit: KitRecord = db.fetch_row( """ select kit_id as id, identifier, encounter_id, rdt_sample_id, utm_sample_id from warehouse.kit where identifier = %s for update """, (identifier, )) return kit
def find_or_create_individual(db: DatabaseSession, identifier: str, sex: str, details: dict = None) -> Any: """ Select indinvidual by *identifier*, or insert it if it doesn't exist. """ LOG.debug(f"Looking up individual «{identifier}»") individual = db.fetch_row( """ select individual_id as id, identifier from warehouse.individual where identifier = %s """, (identifier, )) if individual: LOG.info(f"Found individual {individual.id} «{individual.identifier}»") else: LOG.debug(f"individual «{identifier}» not found, adding") data = { "identifier": identifier, "sex": sex, "details": Json(details), } individual = db.fetch_row( """ insert into warehouse.individual (identifier, sex, details) values (%(identifier)s, %(sex)s, %(details)s) returning individual_id as id, identifier """, data) LOG.info( f"Created individual {individual.id} «{individual.identifier}»") return individual
def upsert_presence_absence(db: DatabaseSession, identifier: str, sample_id: int, target_id: int, present: bool, details: dict) -> Any: """ Upsert presence_absence by its *identifier*. Confirmed with Samplify that their numeric identifier for each test is stable and persistent. """ LOG.debug(f"Upserting presence_absence «{identifier}»") data = { "identifier": identifier, "sample_id": sample_id, "target_id": target_id, "present": present, "details": Json(details) } presence_absence = db.fetch_row( """ insert into warehouse.presence_absence ( identifier, sample_id, target_id, present, details) values ( %(identifier)s, %(sample_id)s, %(target_id)s, %(present)s, %(details)s) on conflict (identifier) do update set sample_id = excluded.sample_id, target_id = excluded.target_id, present = excluded.present, details = coalesce(presence_absence.details, '{}') || excluded.details returning presence_absence_id as id, identifier """, data) assert presence_absence.id, "Upsert affected no rows!" LOG.info(f"Upserted presence_absence {presence_absence.id} \ «{presence_absence.identifier}»") return presence_absence
def upsert_encounter(db: DatabaseSession, identifier: str, encountered: str, individual_id: int, site_id: int, age: Optional[str], details: dict) -> Any: """ Upsert encounter by its *identifier*. """ LOG.debug(f"Upserting encounter «{identifier}»") data = { "identifier": identifier, "encountered": encountered, "individual_id": individual_id, "site_id": site_id, "age": age, "details": Json(details), } encounter = db.fetch_row( """ insert into warehouse.encounter ( identifier, individual_id, site_id, encountered, age, details) values ( %(identifier)s, %(individual_id)s, %(site_id)s, %(encountered)s::timestamp with time zone, %(age)s, %(details)s) on conflict (identifier) do update set individual_id = excluded.individual_id, site_id = excluded.site_id, encountered = excluded.encountered, age = excluded.age, details = excluded.details returning encounter_id as id, identifier """, data) assert encounter.id, "Upsert affected no rows!" LOG.info(f"Upserted encounter {encounter.id} «{encounter.identifier}»") return encounter
def insert_fhir_bundle(db: DatabaseSession, bundle: dict) -> None: """ Insert FHIR bundles into the receiving area of the database. """ LOG.debug(f"Inserting FHIR bundle «{bundle['id']}»") fhir = db.fetch_row(""" insert into receiving.fhir(document) values (%s) returning fhir_id as id """, (Json(bundle),)) assert fhir.id, "Insert affected no rows!" LOG.info(f"Inserted FHIR document {fhir.id} «{bundle['id']}»")
def insert_sequence_read_set(db: DatabaseSession, sample_id: int, urls: list): """ Insert sequencing read set directly into warehouse.sequence_read_set, with the *sample_id* and *urls*. """ LOG.debug(f"Inserting sequence read set for sample {sample_id}") data = {"sample_id": sample_id, "urls": urls} sequence_read_set = db.fetch_row( """ insert into warehouse.sequence_read_set (sample_id, urls) values (%(sample_id)s, %(urls)s) returning sequence_read_set_id as id """, data) assert sequence_read_set.id, "Insert failed" return sequence_read_set
def find_location(db: DatabaseSession, scale: str, identifier: str) -> Any: """ Find a location by *scale* and *identifier*. """ LOG.debug(f"Looking up location {(scale, identifier)}") location = db.fetch_row( """ select location_id as id, scale, identifier, hierarchy from warehouse.location where (scale, identifier) = (%s, %s) """, (scale, identifier)) if not location: LOG.error(f"No location for {(scale, identifier)}") return None LOG.info(f"Found location {location.id} as {(scale, identifier)}") return location
def find_sample(db: DatabaseSession, nwgc_id: str) -> Optional[int]: """ Find sample within warehouse that has *nwgc_id* in the sample details. """ LOG.debug(f"Looking up sample with NWGC ID: «{nwgc_id}»") sample = db.fetch_row( """ select sample_id as id from warehouse.sample where details @> '{"nwgc_id": [%s]}' """, (int(nwgc_id), )) if not sample: LOG.error(f"No sample with NWGC ID «{nwgc_id}» found") return None LOG.info(f"Found sample {sample.id}") return sample.id
def set_use_create(use, description): """ Create a new identifier set use. \b <use> is the name of the new use. <description> is a comment explaining the purpose of the use. """ session = DatabaseSession() with session: use, = session.fetch_row(""" insert into warehouse.identifier_set_use (use, description) values (%s, %s) returning use """, (use, description)) click.echo( "Created identifier set use " + click.style(use, bold = True))
def find_organism(db: DatabaseSession, lineage: str) -> Optional[OrganismRecord]: """ Find organism by *lineage* and return organism. """ LOG.debug(f"Looking up organism «{lineage}»") organism = db.fetch_row( """ select organism_id as id, lineage from warehouse.organism where lineage ~ %s """, (lineage, )) if not organism: LOG.error(f"No organism with lineage «{lineage}» found") return None LOG.info(f"Found organism {organism.id} «{organism.lineage}»") return organism
def find_sample_by_id(db: DatabaseSession, sample_id: int) -> Any: """ Find sample by *sample_id* and return sample. """ LOG.debug(f"Looking up sample «{sample_id}»") sample = db.fetch_row( """ select sample_id as id, identifier, encounter_id from warehouse.sample where sample_id = %s for update """, (sample_id, )) if not sample: LOG.error(f"No sample with id «{sample_id}» found") return None LOG.info(f"Found sample {sample.id} «{sample.identifier}»") return sample
def upsert_genome(db: DatabaseSession, sequence_read_set: SequenceReadSetRecord, organism: OrganismRecord, document: dict) -> GenomeRecord: """ Upsert consensus genomes with the given *sequence_read_set*, *organism*, and consensus genome *document*. """ LOG.debug( dedent(f""" Upserting genome with sequence read set {sequence_read_set.id}, organism {organism.id} «{organism.lineage}»""")) data = { "sample_id": sequence_read_set.sample_id, "organism_id": organism.id, "sequence_read_set_id": sequence_read_set.id, "additional_details": Json(document['summary_stats']) } genome: GenomeRecord = db.fetch_row( """ insert into warehouse.consensus_genome (sample_id, organism_id, sequence_read_set_id, details) values (%(sample_id)s, %(organism_id)s, %(sequence_read_set_id)s, %(additional_details)s) on conflict (sample_id, organism_id, sequence_read_set_id) do update set details = %(additional_details)s returning consensus_genome_id as id, sample_id, organism_id, sequence_read_set_id """, data) assert genome.id, "Upsert affected no rows!" LOG.info( dedent(f""" Upserted genome {genome.id} with sample ID «{genome.sample_id}», organism ID «{genome.organism_id}», and sequence read set ID «{genome.sequence_read_set_id}» """)) return genome
def set_create(name, description): """ Create a new identifier set. \b <name> is the name of the new set. <description> is a comment explaining the purpose of the set. """ session = DatabaseSession() with session: identifier_set_id, = session.fetch_row(""" insert into warehouse.identifier_set (name, description) values (%s, %s) returning identifier_set_id """, (name, description)) click.echo( "Created identifier set " + click.style(name, bold = True) + f" (#{identifier_set_id})")
def find_encounter(db: DatabaseSession, identifier: str) -> Any: """ Given an *identifier* find the corresponding encounter within the relational warehouse. """ LOG.debug(f"Looking up encounter with identifier «{identifier}»") encounter = db.fetch_row( """ select encounter_id as id, encounter.identifier as identifier, site.identifier as site from warehouse.encounter join warehouse.site using (site_id) where encounter.identifier = %s """, (identifier, )) if not encounter: return None LOG.info(f"Found encounter «{encounter.id}»") return encounter
def upsert_location(db: DatabaseSession, scale: str, identifier: str, hierarchy: str) -> Any: """ Upserts a location by its *scale* and *identifier*. If *hierarchy* is None, it will be set to the location's `scale => identifier`. Otherwise, the location's `scale => identifier` will be appended to the *hierarchy*. On update, new hierarchy and existing hierarchy are concatenated, with new hierarchy taking precedence if there is overlap of keys. """ LOG.debug(f"Upserting location {(scale, identifier)}") # Always includes the new location's own scale => identifier in hierarchy location_hierarchy = f"{scale} => {identifier}".lower() if hierarchy is None: hierarchy = location_hierarchy else: hierarchy = hierarchy + "," + location_hierarchy location = db.fetch_row( """ insert into warehouse.location (scale, identifier, hierarchy) values (%s, %s, %s) on conflict (scale, identifier) do update set hierarchy = coalesce(location.hierarchy, '') || excluded.hierarchy returning location_id as id, scale, identifier, hierarchy """, (scale, identifier, hierarchy)) assert location.id, "Upsert affected no rows!" LOG.info( f"Upserted location {location.id} as {(location.scale,location.identifier)}" ) return location
def set_create(name, use, description): """ Create a new identifier set. \b <name> is the name of the new set. <use> is the use classification for this set, valid values can be found using `id3c identifier set-use ls` command. <description> is a comment explaining the purpose of the set. """ session = DatabaseSession() with session: identifier_set_id, = session.fetch_row(""" insert into warehouse.identifier_set (name, use, description) values (%s, %s, %s) returning identifier_set_id """, (name, use, description)) click.echo( "Created identifier set " + click.style(name, bold = True) + f" (#{identifier_set_id})")
def upsert_genomic_sequence(db: DatabaseSession, genome: GenomeRecord, masked_consensus: dict) -> Any: """ Upsert genomic sequence given a *genome* record and some information from a given *masked_consensus*. """ sequence_identifier = "".join([ masked_consensus['sequence_identifier'], "-", str(genome.sequence_read_set_id) ]) LOG.info(f"Upserting genomic sequence «{sequence_identifier}»") data = { "identifier": sequence_identifier, "segment": masked_consensus['sequence_segment'], "seq": masked_consensus['genomic_sequence'], "genome_id": genome.id, } genomic_sequence = db.fetch_row( """ insert into warehouse.genomic_sequence (identifier, segment, seq, consensus_genome_id) values (%(identifier)s, %(segment)s, %(seq)s, %(genome_id)s) on conflict (identifier) do update set seq = excluded.seq, segment = excluded.segment returning genomic_sequence_id as id, identifier, segment, seq, consensus_genome_id """, data) assert genomic_sequence.consensus_genome_id == genome.id, \ "Provided sequence identifier was not unique, matched a sequence linked to another consensus genome!" assert genomic_sequence.id, "Upsert affected no rows!" LOG.info(f"Upserted genomic sequence {genomic_sequence.id}»") return genomic_sequence
def find_sample(db: DatabaseSession, identifier: str) -> Optional[SampleRecord]: """ Given an *identifier* find the corresponding sample within the database. """ LOG.debug(f"Looking up sample with identifier «{identifier}»") sample = db.fetch_row( """ select sample_id as id, identifier, encounter_id, details ->> 'sample_type' as type from warehouse.sample where sample.identifier = %s """, (identifier, )) if not sample: return None LOG.info(f"Found sample «{sample.id}»") return sample
def find_sample_origin_by_barcode(db: DatabaseSession, barcode: str) -> Optional[str]: """ Given an SFS *barcode* return the `sample_origin` found in sample.details """ sample = db.fetch_row( """ select details ->> 'sample_origin' as sample_origin from warehouse.sample join warehouse.identifier on sample.identifier = identifier.uuid::text where barcode = %s """, (barcode, )) if not sample: LOG.error(f"No sample with barcode «{barcode}» found.") return None if not sample.sample_origin: LOG.warning( f"Sample with barcode «{barcode}» did not have sample_origin in details" ) return None return sample.sample_origin
def upsert_kit_with_sample(db: DatabaseSession, identifier: str, sample: SampleRecord, additional_details: dict) -> Tuple[KitRecord, str]: """ Upsert kit by its *identifier* to include link to a sample. An existing kit has its *sample_id* updated and the provided *additional_details* are merged (at the top level only into) the existing kit details, if any. """ LOG.debug(f"Upserting kit «{identifier}»") data = { "identifier": identifier, "sample_id": sample.id, "additional_details": Json(additional_details) } if sample.type == 'utm': sample_type = "utm_sample_id" elif sample.type == 'rdt': sample_type = "rdt_sample_id" # Look for existing kit kit = find_kit(db, identifier) # Nothing found → create if not kit: LOG.info("Creating new kit") status = "created" kit = db.fetch_row( sql.SQL(""" insert into warehouse.kit (identifier, {}, details) values(%(identifier)s, %(sample_id)s, %(additional_details)s) returning kit_id as id, identifier, encounter_id, {} """).format(sql.Identifier(sample_type), sql.Identifier(sample_type)), data) # Found kit → update else: status = "updated" kit_sample_id = getattr(kit, sample_type) # Warn if kit is already linked to a different sample! if (kit_sample_id and (sample.id != kit_sample_id)): LOG.warning( f"Kit «{kit.id}» already linked to another " + f"{sample_type} «{kit_sample_id}», linking with «{sample.id}» instead" ) kit = db.fetch_row( sql.SQL(""" update warehouse.kit set {} = %(sample_id)s, details = coalesce(details, {}) || %(additional_details)s where kit_id = %(kit_id)s returning kit_id as id, identifier, encounter_id, {} """).format(sql.Identifier(sample_type), sql.Literal(Json({})), sql.Identifier(sample_type)), { **data, "kit_id": kit.id }) assert kit.id, "Upsert affected no rows!" LOG.info(f"Upserted kit {kit.id} with identifier «{kit.identifier}» " + f"linked to {sample_type} «{getattr(kit, sample_type)}»") return kit, status
def process_diagnostic_report_bundle_entry(db: DatabaseSession, bundle: Bundle, entry: BundleEntry): """ Given an DiagnosticReport resource *entry* from a given *bundle*, processes the relevant information into the database. """ resource, resource_type = resource_and_resource_type(entry) if resource_type != 'DiagnosticReport': return LOG.debug(f"Processing DiagnosticReport Resource «{entry.fullUrl}».") for reference in resource.specimen: barcode = None if not reference.identifier: specimen = reference.resolved(Specimen) barcode = identifier(specimen, f"{INTERNAL_SYSTEM}/sample").strip() elif matching_system(reference.identifier, INTERNAL_SYSTEM): barcode = reference.identifier.value.strip() if not barcode: continue LOG.debug(f"Looking up collected specimen barcode «{barcode}»") specimen_identifier = find_identifier(db, barcode) if not specimen_identifier: LOG.warning(f"Skipping collected specimen with unknown barcode «{barcode}»") continue # By default, assume that the incoming barcode is for a collection identifier is_collection_identifier = True try: assert specimen_identifier.set_name in EXPECTED_COLLECTION_IDENTIFIER_SETS, \ f"Specimen with unexpected «{specimen_identifier.set_name}» barcode «{barcode}»" except AssertionError: assert specimen_identifier.set_name in EXPECTED_SAMPLE_IDENTIFIER_SETS, \ f"Specimen with unexpected «{specimen_identifier.set_name}» barcode «{barcode}»" is_collection_identifier = False sample = find_sample(db, specimen_identifier.uuid) if not is_collection_identifier and not sample: raise SampleNotFoundError("No sample with identifier «{specimen_identifier.uuid}» found.") # Sometimes the Ellume samples come in faster than the specimen manifest # is updated. In this case, create a new collection identifier that will # be filled in later. if not sample: LOG.debug(f"Creating sample with collection identifier «{specimen_identifier.uuid}»") sample = db.fetch_row(""" insert into warehouse.sample (collection_identifier) values (%s) returning sample_id as id, collection_identifier """, (str(specimen_identifier.uuid),)) LOG.info(f"Created sample {sample.id} with collection identifier «{sample.collection_identifier}»") process_presence_absence_tests(db, resource, sample.id, barcode)
def upsert_sample(db: DatabaseSession, identifier: str, collection_identifier: Optional[str], collection_date: Optional[str], additional_details: dict) -> Tuple[Any, str]: """ Upsert sample by its *identifier* and/or *collection_identifier*. An existing sample has its *identifier*, *collection_identifier*, *collection_date* updated, and the provided *additional_details* are merged (at the top-level only) into the existing sample details, if any. Raises an exception if there is more than one matching sample. """ data = { "identifier": identifier, "collection_identifier": collection_identifier, "collection_date": collection_date, "additional_details": Json(additional_details), } # Look for existing sample(s) with db.cursor() as cursor: cursor.execute(""" select sample_id as id, identifier, collection_identifier, encounter_id from warehouse.sample where identifier = %(identifier)s or collection_identifier = %(collection_identifier)s for update """, data) samples = list(cursor) # Nothing found → create if not samples: LOG.info("Creating new sample") status = 'created' sample = db.fetch_row(""" insert into warehouse.sample (identifier, collection_identifier, collected, details) values (%(identifier)s, %(collection_identifier)s, date_or_null(%(collection_date)s), %(additional_details)s) returning sample_id as id, identifier, collection_identifier, encounter_id """, data) # One found → update elif len(samples) == 1: status = 'updated' sample = samples[0] LOG.info(f"Updating existing sample {sample.id}") sample = db.fetch_row(""" update warehouse.sample set identifier = %(identifier)s, collection_identifier = %(collection_identifier)s, collected = date_or_null(%(collection_date)s), details = coalesce(details, '{}') || %(additional_details)s where sample_id = %(sample_id)s returning sample_id as id, identifier, collection_identifier, encounter_id """, { **data, "sample_id": sample.id }) assert sample.id, "Update affected no rows!" # More than one found → error else: raise Exception(f"More than one sample matching sample and/or collection barcodes: {samples}") return sample, status