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