def load (self, context): if self.uid == -1: return # Is it already loaded? Convenience check for client calls # don't need to verify loads from the cache. if context.config.use_cache: try: if self.journal_title: # Already loaded return except AttributeError: # Note already loaded, so continue pass cursor = context.get_cursor() cursor.execute(""" SELECT journal_title, abbreviation, issn, nlm_id FROM medline_journals WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) for k, v in row.items(): self.set(k, v) if context.config.use_cache: context.cache_set('%s:%s' % (self.ALT_CACHE_KEY, self.issn), self)
def summary_set_from_concept (context, concept_id, concept_type): """ For a given concept_id with a particular concept_type, return the set of summary/study pairs for that concept. """ if concept_type in ('exposure', 'outcome'): table_name = concept_type + 's' elif concept_type == 'species': table_name = concept_type else: return None pairs = [] try: cursor = context.get_cursor() query = """ SELECT summary_concepts.*, %s.study_id FROM %s, summary_concepts WHERE %s.uid = summary_concepts.study_concept_id AND %s.concept_id = """ % (table_name, table_name, table_name, table_name) cursor.execute(query + " %s ", int(concept_id)) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) for row in cursor.fetchall(): row = dtuple.DatabaseTuple(desc, row) summary = Summary(context, row['summary_id']) st = study.Study(context, row['study_id']) pairs.append((summary, st)) except Exception, e: context.logger.error(e) return None
def load(self, context): if self.uid == -1: return # Is it already loaded? Convenience check for client calls # don't need to verify loads from the cache. if context.config.use_cache: try: if getattr(self, self.CACHE_CHECK_KEY): # Already loaded return except AttributeError: # Note already loaded, so continue pass cursor = context.get_cursor() cursor.execute( """ SELECT reference, comments FROM human_studies WHERE uid = %s """, self.uid, ) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() row = dtuple.DatabaseTuple(desc, row) self.reference = row["reference"] self.comments = row["comments"] if context.config.use_cache: context.cache_set("%s:%s" % (self.CACHE_KEY, self.uid), self)
def summary_set_from_concept(context, concept_id, concept_type): """ For a given concept_id with a particular concept_type, return the set of summary/study pairs for that concept. """ if concept_type in ('exposure', 'outcome'): table_name = concept_type + 's' elif concept_type == 'species': table_name = concept_type else: return None pairs = [] try: cursor = context.get_cursor() query = """ SELECT summary_concepts.*, %s.study_id FROM %s, summary_concepts WHERE %s.uid = summary_concepts.study_concept_id AND %s.concept_id = """ % (table_name, table_name, table_name, table_name) cursor.execute(query + " %s ", int(concept_id)) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) for row in cursor.fetchall(): row = dtuple.DatabaseTuple(desc, row) summary = Summary(context, row['summary_id']) st = study.Study(context, row['study_id']) pairs.append((summary, st)) except Exception, e: context.logger.error(e) return None
def records_by_year (context, year, term_map={}): cursor = context.get_cursor() queued_records = [] year_terms = term_map['pubdate'] year_clause = ' OR '.join(['queued_record_metadata.term_id=%s' % term.uid for term in year_terms]) try: select_clause = """ SELECT queued_records.uid FROM queued_records, queued_record_metadata, studies WHERE queued_records.uid = queued_record_metadata.queued_record_id AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) """ % year_clause cursor.execute(select_clause + """ AND SUBSTRING(queued_record_metadata.value, 1, 4) LIKE %s """, str(year) + '%' ) rows = cursor.fetchall() for row in rows: queued_record = QueuedRecord(context, row[0]) queued_records.append(queued_record) except Exception, e: context.logger.error('Records by year: %s', e)
def save(self, context): cursor = context.get_cursor() try: if self.uid == -1: cursor.execute( """ INSERT INTO human_studies (reference, comments) VALUES (%s, %s) """, (self.reference, self.comments)) self.uid = self.get_new_uid(context) context.logger.info('HumanStudy created with uid %s', self.uid) else: cursor.execute( """ UPDATE human_studies SET reference = %s, comments = %s WHERE uid = %s """, (self.reference, self.comments, self.uid)) context.logger.info('HumanStudy %s updates', self.uid) except: print traceback.print_exc() raise Error, 'Duplicate reference' if context.config.use_cache: context.cache_set('%s:%s' % (self.CACHE_KEY, self.uid), self)
def load(self, context): if self.uid == -1: return # Is it already loaded? Convenience check for client calls # don't need to verify loads from the cache. if context.config.use_cache: try: if getattr(self, self.CACHE_CHECK_KEY): # Already loaded return except AttributeError: # Note already loaded, so continue pass cursor = context.get_cursor() cursor.execute( """ SELECT reference, comments FROM human_studies WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() row = dtuple.DatabaseTuple(desc, row) self.reference = row['reference'] self.comments = row['comments'] if context.config.use_cache: context.cache_set('%s:%s' % (self.CACHE_KEY, self.uid), self)
def save(self, context): cursor = context.get_cursor() try: if self.uid == -1: cursor.execute( """ INSERT INTO human_studies (reference, comments) VALUES (%s, %s) """, (self.reference, self.comments), ) self.uid = self.get_new_uid(context) context.logger.info("HumanStudy created with uid %s", self.uid) else: cursor.execute( """ UPDATE human_studies SET reference = %s, comments = %s WHERE uid = %s """, (self.reference, self.comments, self.uid), ) context.logger.info("HumanStudy %s updates", self.uid) except: print traceback.print_exc() raise Error, "Duplicate reference" if context.config.use_cache: context.cache_set("%s:%s" % (self.CACHE_KEY, self.uid), self)
def records_by_author (context, author): cursor = context.get_cursor() queued_records = [] source_catalog = context.get_source_catalog() complete_mapping = source_catalog.get_complete_mapping() term_list = complete_mapping['author'] term_clause = ' OR '.join(['queued_record_metadata.term_id=%s' % term.uid for term in term_list]) try: select_clause = """ SELECT queued_records.uid FROM queued_records, queued_record_metadata, studies WHERE queued_records.uid = queued_record_metadata.queued_record_id AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) """ % term_clause cursor.execute(select_clause + """ AND value LIKE %s """, str(author) + '%' ) rows = cursor.fetchall() for row in rows: queued_record = QueuedRecord(context, row[0]) queued_records.append(queued_record) except Exception, e: context.logger.error('Records by author: %s', e)
def load_from_issn (self, context, issn): if not issn: return if context.config.use_cache: j = context.cache_get('%s:%s' % (self.ALT_CACHE_KEY, issn)) if j \ and j.journal_title: for att in ('uid', 'journal_title', 'abbreviation', 'issn', 'eissn', 'iso_abbr', 'nlm_id'): self.set(att, getattr(j, att)) return cursor = context.get_cursor() cursor.execute(""" SELECT uid, journal_title, abbreviation, nlm_id FROM medline_journals WHERE issn = %s """, issn) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() if rows: row = rows[0] row = dtuple.DatabaseTuple(desc, row) for k, v in row.items(): self.set(k, v) self.issn = issn if context.config.use_cache: context.cache_set('%s:%s' % (self.CACHE_KEY, self.uid), self) context.cache_set('%s:%s' % (self.ALT_CACHE_KEY, self.issn), self)
def save (self, context): cursor = context.get_cursor() if self.uid == -1: cursor.execute(""" INSERT INTO categories (uid, name, concept_types) VALUES (NULL, %s, %s) """, (self.name, self.get_types(shorthand=True))) cursor.execute(""" SELECT LAST_INSERT_ID() AS new_uid """) row = cursor.fetchone() self.uid = row[0] for group in self.groups: group.category_id = self.uid group.save(context) else: cursor.execute(""" UPDATE categories SET name = %s, concept_types = %s WHERE uid = %s """, (self.name, self.get_types(shorthand=True), self.uid))
def save(self, context): cursor = context.get_cursor() if self.uid == -1: cursor.execute( """ INSERT INTO category_concepts (uid, category_id, concept_id, is_default, is_broad) VALUES (NULL, %s, %s, %s, %s) """, (self.category_id, self.concept_id, int( self.is_default), int(self.is_broad))) cursor.execute(""" SELECT LAST_INSERT_ID() AS new_uid """) row = cursor.fetchone() self.uid = row[0] else: cursor.execute( """ UPDATE category_concepts SET is_default = %s, is_broad = %s WHERE uid = %s """, (int(self.is_default), int(self.is_broad), self.uid))
def save(self, context): cursor = context.get_cursor() if self.uid == -1: cursor.execute( """ INSERT INTO categories (uid, name, concept_types) VALUES (NULL, %s, %s) """, (self.name, self.get_types(shorthand=True))) cursor.execute(""" SELECT LAST_INSERT_ID() AS new_uid """) row = cursor.fetchone() self.uid = row[0] for group in self.groups: group.category_id = self.uid group.save(context) else: cursor.execute( """ UPDATE categories SET name = %s, concept_types = %s WHERE uid = %s """, (self.name, self.get_types(shorthand=True), self.uid))
def find_concepts (context, search_term): cursor = context.get_cursor() concepts = {} if isinstance(search_term, types.IntType): cursor.execute(""" SELECT umls_terms.umls_concept_id, term, preferred_name, umls_source_id FROM umls_terms, umls_concepts, umls_concepts_sources WHERE umls_concepts.umls_concept_id = %s AND umls_concepts.umls_concept_id = umls_terms.umls_concept_id AND umls_concepts_sources.umls_concept_id = umls_concepts.umls_concept_id GROUP BY umls_concepts.umls_concept_id ORDER BY term, preferred_name """, search_term) else: # Assumes search_term is text if search_term \ and len(search_term) > 0: query_term = search_term.strip().replace(' ', '% ') + '%' cursor.execute(""" SELECT umls_terms.umls_concept_id, term, preferred_name, umls_source_id FROM umls_terms, umls_concepts, umls_concepts_sources WHERE term LIKE %s AND umls_concepts.umls_concept_id = umls_terms.umls_concept_id AND umls_concepts_sources.umls_concept_id = umls_concepts.umls_concept_id GROUP BY umls_concepts.umls_concept_id ORDER BY term, preferred_name """, query_term) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) if not concepts.has_key((row['umls_concept_id'], row['umls_source_id'])): concept = Concept(uid=row['umls_concept_id']) concept.concept_source_id = row['umls_source_id'] concept.term = row['preferred_name'] concept.synonyms.append(row['term']) concepts[(concept.uid, concept.concept_source_id)] = concept else: concept = concepts[(row['umls_concept_id'], row['umls_source_id'])] if not row['term'] in concept.synonyms: concept.synonyms.append(row['term']) concepts[(concept.uid, concept.concept_source_id)] = concept if not isinstance(search_term, types.IntType): # Try to bump up coarse "relevance" of exact matches concepts_ranked = concepts.values() for concept in concepts_ranked: if concept.term.lower() == search_term.lower()\ or search_term.lower() in [syn.lower() for syn in concept.synonyms]: concepts_ranked.remove(concept) concepts_ranked.insert(0, concept) return concepts_ranked return concepts.values()
def load(self, context): cursor = context.get_cursor() if self.uid == -1: if not self.concept_id == -1 \ and not self.category_id == -1: cursor.execute( """ SELECT * FROM category_concepts WHERE category_id = %s AND concept_id = %s """, (self.category_id, self.concept_id)) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() if row: row = dtuple.DatabaseTuple(desc, row) self.uid = row['uid'] self.is_broad = row['is_broad'] self.is_default = row['is_default'] else: self.logger.debug('No matched rows') return else: self.logger.debug('Not enough info') return else: cursor.execute( """ SELECT * FROM category_concepts WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() if row: row = dtuple.DatabaseTuple(desc, row) self.concept_id = row['concept_id'] self.is_broad = row['is_broad'] self.is_default = row['is_default'] cursor.execute( """ SELECT * FROM category_concept_groups WHERE category_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) self.groups.append(row['category_group_id']) self.concept = Concept(context, self.concept_id)
def load (self, context): cursor = context.get_cursor() if self.uid == -1: if not self.concept_id == -1 \ and not self.category_id == -1: cursor.execute(""" SELECT * FROM category_concepts WHERE category_id = %s AND concept_id = %s """, (self.category_id, self.concept_id)) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() if row: row = dtuple.DatabaseTuple(desc, row) self.uid = row['uid'] self.is_broad = row['is_broad'] self.is_default = row['is_default'] else: self.logger.debug('No matched rows') return else: self.logger.debug('Not enough info') return else: cursor.execute(""" SELECT * FROM category_concepts WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() if row: row = dtuple.DatabaseTuple(desc, row) self.concept_id = row['concept_id'] self.is_broad = row['is_broad'] self.is_default = row['is_default'] cursor.execute(""" SELECT * FROM category_concept_groups WHERE category_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) self.groups.append(row['category_group_id']) self.concept = Concept(context, self.concept_id)
def remove_concept (self, context, concept): cursor = context.get_cursor() for c in self.concepts: if concept.uid == c.uid: self.concepts.remove(c) try: cursor.execute(""" DELETE FROM category_concepts WHERE uid = %s """, concept.uid) except Exception, e: self.logger.error('Could not remove concept %s (%s)', concept.uid, e)
def load(self, context, load_synonyms=True): if self.uid == -1: return # Is it already loaded? Convenience check for client calls # don't need to verify loads from the cache. if context.config.use_cache: try: if self.term: # Already loaded return except AttributeError: # Note already loaded, so continue pass cursor = context.get_cursor() cursor.execute( """ SELECT umls_concepts.preferred_name, umls_concepts_sources.umls_source_id, umls_concepts_sources.umls_source_code FROM umls_concepts, umls_concepts_sources WHERE umls_concepts_sources.umls_concept_id = umls_concepts.umls_concept_id AND umls_concepts.umls_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) self.term = row['preferred_name'] self.sources.append( (row['umls_source_id'], row['umls_source_code'])) self.concept_source_id = row['umls_source_id'] self.concept_source_code = row['umls_source_code'] if load_synonyms: # NOTE: Is there any value in using umls_term_id? It's ignored here. cursor.execute( """ SELECT term FROM umls_terms WHERE umls_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) synonym = row['term'] if not synonym in self.synonyms: self.synonyms.append(synonym)
def load(self, context, load_concepts=False): if self.uid == -1: return cursor = context.get_cursor() cursor.execute( """ SELECT * FROM categories WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() row = dtuple.DatabaseTuple(desc, row) self.name = row['name'] self.set_types(row['concept_types']) cursor.execute( """ SELECT * FROM category_groups WHERE category_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) group = CategoryGroup(uid=row['uid'], category_id=self.uid, name=row['name']) self.add_group(group) if load_concepts: cursor.execute( """ SELECT * FROM category_concepts WHERE category_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) cat_concept = CategoryConcept(uid=row['uid'], category_id=self.uid, concept_id=row['concept_id']) cat_concept.is_broad = row['is_broad'] cat_concept.is_default = row['is_default'] cat_concept.load(context) self.add_concept(cat_concept)
def remove_concept(self, context, concept): cursor = context.get_cursor() for c in self.concepts: if concept.uid == c.uid: self.concepts.remove(c) try: cursor.execute( """ DELETE FROM category_concepts WHERE uid = %s """, concept.uid) except Exception, e: self.logger.error('Could not remove concept %s (%s)', concept.uid, e)
def load (self, context, load_synonyms=True): if self.uid == -1: return # Is it already loaded? Convenience check for client calls # don't need to verify loads from the cache. if context.config.use_cache: try: if self.term: # Already loaded return except AttributeError: # Note already loaded, so continue pass cursor = context.get_cursor() cursor.execute(""" SELECT umls_concepts.preferred_name, umls_concepts_sources.umls_source_id, umls_concepts_sources.umls_source_code FROM umls_concepts, umls_concepts_sources WHERE umls_concepts_sources.umls_concept_id = umls_concepts.umls_concept_id AND umls_concepts.umls_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) self.term = row['preferred_name'] self.sources.append((row['umls_source_id'], row['umls_source_code'])) self.concept_source_id = row['umls_source_id'] self.concept_source_code = row['umls_source_code'] if load_synonyms: # NOTE: Is there any value in using umls_term_id? It's ignored here. cursor.execute(""" SELECT term FROM umls_terms WHERE umls_concept_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) synonym = row['term'] if not synonym in self.synonyms: self.synonyms.append(synonym)
def load (self, context, load_concepts=False): if self.uid == -1: return cursor = context.get_cursor() cursor.execute(""" SELECT * FROM categories WHERE uid = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) row = cursor.fetchone() row = dtuple.DatabaseTuple(desc, row) self.name = row['name'] self.set_types(row['concept_types']) cursor.execute(""" SELECT * FROM category_groups WHERE category_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) group = CategoryGroup(uid=row['uid'], category_id=self.uid, name=row['name']) self.add_group(group) if load_concepts: cursor.execute(""" SELECT * FROM category_concepts WHERE category_id = %s """, self.uid) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) cat_concept = CategoryConcept(uid=row['uid'], category_id=self.uid, concept_id=row['concept_id']) cat_concept.is_broad = row['is_broad'] cat_concept.is_default = row['is_default'] cat_concept.load(context) self.add_concept(cat_concept)
def load_categories(context): cursor = context.get_cursor() categories = [] cursor.execute(""" SELECT uid FROM categories ORDER BY name """) rows = cursor.fetchall() for row in rows: category = Category(uid=row[0]) category.load(context) categories.append(category) return categories
def load_categories (context): cursor = context.get_cursor() categories = [] cursor.execute(""" SELECT uid FROM categories ORDER BY name """) rows = cursor.fetchall() for row in rows: category = Category(uid=row[0]) category.load(context) categories.append(category) return categories
def save (self, context, update_all=False): # NOTE: For now, do not allow creation of arbitrary concepts if self.uid == -1: return cursor = context.get_cursor() # NOTE: For now, only allow update of preferred_name cursor.execute(""" UPDATE umls_concepts SET preferred_name = %s WHERE umls_concept_id = %s """, (self.term, self.uid)) if context.config.use_cache: context.cache_set('%s:%s' % (self.CACHE_KEY, self.uid), self)
def save (self, context): cursor = context.get_cursor() if self.uid == -1: cursor.execute(""" INSERT INTO category_groups (uid, category_id, name) VALUES (NULL, %s, %s) """, (self.category_id, self.name)) else: cursor.execute(""" UPDATE category_groups SET name = %s WHERE uid = %s """, (self.name, self.uid))
def save(self, context, update_all=False): # NOTE: For now, do not allow creation of arbitrary concepts if self.uid == -1: return cursor = context.get_cursor() # NOTE: For now, only allow update of preferred_name cursor.execute( """ UPDATE umls_concepts SET preferred_name = %s WHERE umls_concept_id = %s """, (self.term, self.uid)) if context.config.use_cache: context.cache_set('%s:%s' % (self.CACHE_KEY, self.uid), self)
def records_by_heading_index (context): cursor = context.get_cursor() cursor.execute(""" SELECT term, COUNT(term) as termcount, COUNT(DISTINCT reference_id) AS idcount FROM reference_mesh GROUP BY term HAVING COUNT(term) > 4 ORDER BY idcount DESC, term """) results = [] rows = cursor.fetchall() for row in rows: if row == None: break results.append((row[0], row[1], row[2])) return results
def get_studies(context): studies = [] cursor = context.get_cursor() cursor.execute(""" SELECT uid FROM human_studies ORDER BY reference """) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) hs = HumanStudy(context, row['uid']) studies.append(hs) return studies
def save(self, context): cursor = context.get_cursor() if self.uid == -1: cursor.execute( """ INSERT INTO category_groups (uid, category_id, name) VALUES (NULL, %s, %s) """, (self.category_id, self.name)) else: cursor.execute( """ UPDATE category_groups SET name = %s WHERE uid = %s """, (self.name, self.uid))
def records_by_methodology_index (context): cursor = context.get_cursor() cursor.execute(""" SELECT study_type_id, COUNT(study_type_id) as the_count FROM methodologies, studies, queued_records WHERE methodologies.study_id = studies.uid AND studies.record_id = queued_records.uid AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 GROUP BY study_type_id """) results = [] rows = cursor.fetchall() for row in rows: results.append((row[0], row[1])) return results
def get_studies(context): studies = [] cursor = context.get_cursor() cursor.execute( """ SELECT uid FROM human_studies ORDER BY reference """ ) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) hs = HumanStudy(context, row["uid"]) studies.append(hs) return studies
def delete(self, context): cursor = context.get_cursor() try: # First, delete from summary_human_refs cursor.execute( """ DELETE FROM summary_human_refs WHERE human_study_id = %s """, self.uid) cursor.execute( """ DELETE FROM human_studies WHERE uid = %s """, self.uid) if context.config.use_cache: context.cache_delete('%s:%s' % (self.CACHE_KEY, self.uid)) except Exception, e: context.logger.error(e)
def find_references(context, token=''): if not token: return get_studies(context) studies = [] cursor = context.get_cursor() cursor.execute( """ SELECT uid FROM human_studies WHERE reference LIKE %s ORDER BY reference """, '%s%s' % (token, '%')) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) hs = HumanStudy(context, row['uid']) studies.append(hs) return studies
def get_summaries_from_study (context, study): """ For a given Study, return a list of all relevant Summary objects. """ summaries = [] try: cursor = context.get_cursor() for m in study.methodologies: cursor.execute(""" SELECT uid FROM summaries WHERE methodology_id = %s ORDER BY uid """, m.uid) rows = cursor.fetchall() for row in rows: s = Summary(context, row[0]) summaries.append(s) except Exception, e: context.logger.error(e)
def delete (self, context): cursor = context.get_cursor() try: # Get rid of all human studies referenced from this study cursor.execute(""" DELETE FROM summary_human_refs WHERE summary_id = %s """, self.uid) cursor.execute(""" DELETE FROM summary_concepts WHERE summary_id = %s """, self.uid) cursor.execute(""" DELETE FROM summaries WHERE uid = %s """, self.uid) self.cache_delete(context) except Exception, e: context.logger.error(e)
def get_summaries_from_study(context, study): """ For a given Study, return a list of all relevant Summary objects. """ summaries = [] try: cursor = context.get_cursor() for m in study.methodologies: cursor.execute( """ SELECT uid FROM summaries WHERE methodology_id = %s ORDER BY uid """, m.uid) rows = cursor.fetchall() for row in rows: s = Summary(context, row[0]) summaries.append(s) except Exception, e: context.logger.error(e)
def records_by_heading (context, term): results = [] # GROUP BY reference_id because some terms repeat w/diff qualifiers cursor = context.get_cursor() cursor.execute(""" SELECT reference_id FROM reference_mesh WHERE term = %s GROUP BY reference_id """, term) id_rows = cursor.fetchall() for id_row in id_rows: cursor.execute(""" SELECT authors, title, source, pubmed_id FROM sentinel_studies WHERE reference_id = %s """, id_row[0]) rows = cursor.fetchall() for row in rows: results.append((row[0], row[1], row[2], row[3])) return results
def records_by_concept_index (context, concept): cursor = context.get_cursor() results = [] table_name = concept_tables[concept] select_clause = """ SELECT COUNT(concept_id) AS the_count, concept_id, term FROM %s, studies, queued_records WHERE %s.study_id = studies.uid AND studies.record_id = queued_records.uid AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 GROUP BY concept_id ORDER BY term """ % (table_name, table_name) cursor.execute(select_clause) rows = cursor.fetchall() results.extend([(r[0], r[1], r[2]) for r in rows]) return results
def records_by_methodology (context, methodology_id): queued_records = [] cursor = context.get_cursor() try: cursor.execute(""" SELECT queued_records.uid FROM queued_records, studies, methodologies WHERE queued_records.uid = studies.record_id AND studies.uid = methodologies.study_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND methodologies.study_type_id = %s """, methodology_id ) rows = cursor.fetchall() for row in rows: queued_record = QueuedRecord(context, row[0]) queued_records.append(queued_record) except Exception, e: context.logger.error('Records by methodology: %s', e)
def find_references(context, token=""): if not token: return get_studies(context) studies = [] cursor = context.get_cursor() cursor.execute( """ SELECT uid FROM human_studies WHERE reference LIKE %s ORDER BY reference """, "%s%s" % (token, "%"), ) fields = [d[0] for d in cursor.description] desc = dtuple.TupleDescriptor([[f] for f in fields]) rows = cursor.fetchall() for row in rows: row = dtuple.DatabaseTuple(desc, row) hs = HumanStudy(context, row["uid"]) studies.append(hs) return studies
def records_by_journal (context, issn, term_map={}): journal_title = '' queued_records = [] issn_terms = term_map['issn'] issn_clause = ' OR '.join(['queued_record_metadata.term_id=%s' % term.uid for term in issn_terms]) cursor = context.get_cursor() cursor.execute(""" SELECT journal_title FROM medline_journals WHERE issn = %s """, issn) try: rows = cursor.fetchall() if len(rows) != 1: raise Exception('Journal %s not found' % issn) journal_title = rows[0][0] select_clause = """ SELECT queued_records.uid FROM queued_records, queued_record_metadata, studies WHERE queued_record_metadata.queued_record_id = queued_records.uid AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) """ % issn_clause cursor.execute(select_clause + """ AND queued_record_metadata.value = %s """, issn ) rows = cursor.fetchall() for row in rows: queued_record = QueuedRecord(context, row[0]) queued_records.append(queued_record) except Exception, e: context.logger.error('Records by journal: %s', e)
def records_by_year_index (context, term_map={}): cursor = context.get_cursor() results = [] year_terms = term_map['pubdate'] year_clause = ' OR '.join(['term_id=%s' % term.uid for term in year_terms]) select_clause = """ SELECT COUNT(*) AS the_count, SUBSTRING(value, 1, 4) AS the_year FROM queued_record_metadata, queued_records, studies WHERE queued_record_metadata.queued_record_id = queued_records.uid AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) GROUP BY SUBSTRING(value, 1, 4) ORDER BY value DESC """ % year_clause cursor.execute(select_clause) rows = cursor.fetchall() for row in rows: results.append((row[0], row[1])) return results
def delete(self, context): cursor = context.get_cursor() try: # First, delete from summary_human_refs cursor.execute( """ DELETE FROM summary_human_refs WHERE human_study_id = %s """, self.uid, ) cursor.execute( """ DELETE FROM human_studies WHERE uid = %s """, self.uid, ) if context.config.use_cache: context.cache_delete("%s:%s" % (self.CACHE_KEY, self.uid)) except Exception, e: context.logger.error(e)
def records_by_journal_index (context, term_map={}): cursor = context.get_cursor() results = [] issn_terms = term_map['issn'] issn_clause = ' OR '.join(['term_id=%s' % term.uid for term in issn_terms]) select_clause = """ SELECT COUNT(*) AS the_count, value, journal_title, abbreviation FROM queued_record_metadata, queued_records, medline_journals, studies WHERE queued_record_metadata.queued_record_id = queued_records.uid AND queued_record_metadata.value = medline_journals.issn AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) GROUP BY value ORDER BY journal_title """ % issn_clause cursor.execute(select_clause) rows = cursor.fetchall() for row in rows: results.append((row[0], row[1], row[2], row[3])) return results
def add_synonym (self, context, term): cursor = context.get_cursor() # If a synonym does not yet exist, add it here, starting at id 20,000,000 # (5,000,000+ and 10,000,000+ are already in use from ITIS faux-merge) if not term in self.synonyms: cursor.execute(""" SELECT MAX(umls_term_id) AS max_id FROM umls_terms """) row = cursor.fetchone() current_max = row[0] if current_max < 20000000: new_max = 20000001 else: new_max = current_max + 1 cursor.execute(""" INSERT INTO umls_terms (umls_term_id, term, umls_concept_id) VALUES (%s, %s, %s) """, (new_max, term, self.uid))
def records_by_concept (context, concept, concept_id): cursor = context.get_cursor() queued_records = [] table_name = concept_tables[concept] try: select_clause = """ SELECT queued_records.uid FROM queued_records, studies, %s WHERE %s.study_id = studies.uid AND studies.record_id = queued_records.uid AND %s.concept_id = %s AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 """ % (table_name, table_name, table_name, concept_id) cursor.execute(select_clause) rows = cursor.fetchall() for row in rows: queued_record = QueuedRecord(context, row[0]) queued_records.append(queued_record) except Exception, e: context.logger.error('Records by concept: %s', e)
def records_by_author_index (context): cursor = context.get_cursor() results = [] source_catalog = context.get_source_catalog() complete_mapping = source_catalog.get_complete_mapping() term_list = complete_mapping['author'] term_clause = ' OR '.join(['term_id=%s' % term.uid for term in term_list]) select_clause = """ SELECT COUNT(*) AS the_count, value FROM queued_record_metadata, queued_records, studies WHERE queued_record_metadata.queued_record_id = queued_records.uid AND queued_records.uid = studies.record_id AND queued_records.status = 2 AND studies.article_type >= 2 AND studies.article_type < 8 AND (%s) GROUP BY value ORDER BY value """ % term_clause cursor.execute(select_clause) rows = cursor.fetchall() results.extend([(r[0], r[1]) for r in rows]) return results