def species_name(request): with transaction.manager: dbsession = DBSession() species_types = { 'mammalia': MAMMALIA, 'aves': AVES, 'plantae': PLANTAE, 'ara': ARA, 'arthropoda': ARTHROPODA, 'moss': MOSS, 'lichenes': LICHENES } rows = [] rec_id = itertools.count() try: for sp in species_types.keys(): slist = species_types[sp] target = dbsession.query(Taxon).filter( Taxon.name.in_(slist)).all() target_ids = [t.id for t in target] tax_all = Taxon.species_by_taxon(target_ids) rows = rows + [{ 'recId': rec_id.next(), 'id': row['id'], 'name': row['name'], 'author': row['author'], 'source': row['source'], 'organism': sp, 'synonim': False } for row in tax_all] # соберем синонимы: syn = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author, Synonym.source).filter( Synonym.species_id.in_([ row['id'] for row in tax_all ])).all() rows = rows + [{ 'recId': rec_id.next(), 'id': row[0], 'name': row[1], 'author': row[2], 'source': row[3], 'organism': sp, 'synonim': True } for row in syn] except DBAPIError: result = {'success': False, 'msg': 'Ошибка подключения к БД'} return {'data': rows, 'success': True, 'totalCount': len(rows)}
def species_name(request): with transaction.manager: dbsession = DBSession() species_types = {'mammalia': MAMMALIA, 'aves': AVES, 'plantae': PLANTAE, 'ara': ARA, 'arthropoda': ARTHROPODA, 'moss': MOSS, 'lichenes': LICHENES} rows = [] rec_id = itertools.count() try: for sp in species_types.keys(): slist = species_types[sp] target = dbsession.query(Taxon).filter(Taxon.name.in_(slist)).all() target_ids = [t.id for t in target] tax_all = Taxon.species_by_taxon(target_ids) rows = rows + [ {'recId': rec_id.next(), 'id': row['id'], 'name': row['name'], 'author': row['author'], 'source': row['source'], 'organism': sp, 'synonim': False } for row in tax_all] # соберем синонимы: syn = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author, Synonym.source).filter( Synonym.species_id.in_([row['id'] for row in tax_all])).all() rows = rows + [ {'recId': rec_id.next(), 'id': row[0], 'name': row[1], 'author': row[2], 'source': row[3], 'organism': sp, 'synonim': True } for row in syn] except DBAPIError: result = {'success': False, 'msg': 'Ошибка подключения к БД'} return {'data': rows, 'success': True, 'totalCount': len(rows)}
def as_join_list(taxon_list=None, header=True): ''' Выбрать аннотированные списки таксонов, перечисленных в taxon_list Все идентификаторы заменить на соотв. значения из таблиц-справочников. Вернуть выборку в виде списка значений. Если taxon_list=None, выбрать все карточки. header: добавлять ли в начало списка строку заголовков. ''' dbsession = DBSession() if taxon_list: # выдать списки-потомки определенных таксонов species = Taxon.species_by_taxon(taxon_list) species_id = [t['id'] for t in species] else: # выдать все списки species = dbsession.query(Taxon).all() species_id = [t.id for t in species if t.is_last_taxon()] qs = ''' SELECT annotation.id, taxon.name as species, key_area.name as key_area, person_1.name as identifier, person_2.name as collecter, inforesources.filename as biblioref, original_name, location, lon, lat, biotop, difference, substrat, status, frequency, quantity, annotation, infosourse, year, month, day, exposure FROM annotation LEFT OUTER JOIN taxon ON annotation.species = taxon.id LEFT OUTER JOIN key_area ON annotation.key_area = key_area.id LEFT OUTER JOIN person as person_1 ON annotation.identifier = person_1.id LEFT OUTER JOIN person as person_2 ON annotation.collecter = person_2.id LEFT OUTER JOIN inforesources ON annotation.biblioref = inforesources.id WHERE annotation.species IN (%s) ''' % ", ".join([str(num) for num in species_id]) anlists = dbsession.query(Annotation).from_statement(qs).all() dbsession.close() names = [ 'id', 'species', 'key_area', 'identifier', 'collecter', 'biblioref', 'original_name', 'location', 'lon', 'lat', 'biotop', 'difference', 'substrat', 'status', 'frequency', 'quantity', 'annotation', 'infosourse', 'year', 'month', 'day', 'exposure' ] result = [names] for anlist in anlists: row = [anlist.__getattribute__(attr) for attr in names] result.append(row) return result
def as_join_list(taxon_list=None, header=True): ''' Выбрать карточки наблюдений таксонов, перечисленных в taxon_list Все идентификаторы заменить на соотв. значения из таблиц-справочников. Вернуть выборку в виде списка значений. Если taxon_list=None, выбрать все карточки. header: добавлять ли в начало списка строку заголовков. ''' dbsession = DBSession() qs = ''' SELECT cards.id, taxon.name as species, cards.original_name, person.name as inserter, person_1.name as observer, person_2.name as identifier, taxa_scheme.taxa_scheme, museum.museum, anthr_press.anthr_press, vitality.vitality, abundance.abundance, footprint.footprint, pheno.pheno, inforesources.filename as inforesources, cards.year, cards.month, cards.day, cards.time, cards.habitat, cards.substrat, cards.limit_fact, cards.protection, cards.area, cards.quantity, cards.unknown_age, cards.unknown_sex, cards.males, cards.females, cards.ad, cards.sad, cards.juv, cards.pull, cards.egs, cards.publications, cards.notes, cards.location, cards.lon, cards.lat, coord_type.coord_type FROM cards LEFT OUTER JOIN taxon ON cards.species = taxon.id LEFT OUTER JOIN person ON cards.inserter = person.id LEFT OUTER JOIN person as person_1 ON cards.observer = person_1.id LEFT OUTER JOIN person as person_2 ON cards.identifier = person_2.id LEFT OUTER JOIN taxa_scheme ON cards.taxa_scheme = taxa_scheme.id LEFT OUTER JOIN museum ON cards.museum = museum.id LEFT OUTER JOIN anthr_press ON cards.anthr_press = anthr_press.id LEFT OUTER JOIN vitality ON cards.vitality = vitality.id LEFT OUTER JOIN abundance ON cards.abundance = abundance.id LEFT OUTER JOIN footprint ON cards.footprint = footprint.id LEFT OUTER JOIN pheno ON cards.pheno = pheno.id LEFT OUTER JOIN inforesources ON cards.inforesources = inforesources.id LEFT OUTER JOIN coord_type ON cards.coord_type = coord_type.id ''' if taxon_list: species = Taxon.species_by_taxon(taxon_list) species_id = [t['id'] for t in species] qs_where = ' WHERE cards.species IN (%s)' % ", ".join([str(num) for num in species_id]) qs += qs_where cards = dbsession.query(Cards).from_statement(qs).all() dbsession.close() names = ['id', 'species', 'original_name', 'inserter', 'observer', 'identifier', 'taxa_scheme', 'museum', 'anthr_press', 'vitality', 'abundance', 'footprint', 'pheno', 'inforesources', 'year', 'month', 'day', 'time', 'habitat', 'substrat', 'limit_fact', 'protection', 'area', 'quantity', 'unknown_age', 'unknown_sex', 'males', 'females', 'ad', 'sad', 'juv', 'pull', 'egs', 'publications', 'notes', 'location', 'lon', 'lat', 'coord_type'] result = [names] for card in cards: row = [card.__getattribute__(attr) for attr in names] result.append(row) return result
def as_join_list(taxon_list=None, header=True): ''' Выбрать карточки наблюдений таксонов, перечисленных в taxon_list Все идентификаторы заменить на соотв. значения из таблиц-справочников. Вернуть выборку в виде списка значений. Если taxon_list=None, выбрать все карточки. header: добавлять ли в начало списка строку заголовков. ''' dbsession = DBSession() qs = ''' SELECT cards.id, taxon.name as species, cards.original_name, person.name as inserter, person_1.name as observer, person_2.name as identifier, taxa_scheme.taxa_scheme, museum.museum, anthr_press.anthr_press, vitality.vitality, abundance.abundance, footprint.footprint, pheno.pheno, inforesources.filename as inforesources, cards.year, cards.month, cards.day, cards.time, cards.habitat, cards.substrat, cards.limit_fact, cards.protection, cards.area, cards.quantity, cards.unknown_age, cards.unknown_sex, cards.males, cards.females, cards.ad, cards.sad, cards.juv, cards.pull, cards.egs, cards.publications, cards.notes, cards.location, cards.lon, cards.lat, coord_type.coord_type FROM cards LEFT OUTER JOIN taxon ON cards.species = taxon.id LEFT OUTER JOIN person ON cards.inserter = person.id LEFT OUTER JOIN person as person_1 ON cards.observer = person_1.id LEFT OUTER JOIN person as person_2 ON cards.identifier = person_2.id LEFT OUTER JOIN taxa_scheme ON cards.taxa_scheme = taxa_scheme.id LEFT OUTER JOIN museum ON cards.museum = museum.id LEFT OUTER JOIN anthr_press ON cards.anthr_press = anthr_press.id LEFT OUTER JOIN vitality ON cards.vitality = vitality.id LEFT OUTER JOIN abundance ON cards.abundance = abundance.id LEFT OUTER JOIN footprint ON cards.footprint = footprint.id LEFT OUTER JOIN pheno ON cards.pheno = pheno.id LEFT OUTER JOIN inforesources ON cards.inforesources = inforesources.id LEFT OUTER JOIN coord_type ON cards.coord_type = coord_type.id ''' if taxon_list: species = Taxon.species_by_taxon(taxon_list) species_id = [t['id'] for t in species] qs_where = ' WHERE cards.species IN (%s)' % ", ".join( [str(num) for num in species_id]) qs += qs_where cards = dbsession.query(Cards).from_statement(qs).all() dbsession.close() names = [ 'id', 'species', 'original_name', 'inserter', 'observer', 'identifier', 'taxa_scheme', 'museum', 'anthr_press', 'vitality', 'abundance', 'footprint', 'pheno', 'inforesources', 'year', 'month', 'day', 'time', 'habitat', 'substrat', 'limit_fact', 'protection', 'area', 'quantity', 'unknown_age', 'unknown_sex', 'males', 'females', 'ad', 'sad', 'juv', 'pull', 'egs', 'publications', 'notes', 'location', 'lon', 'lat', 'coord_type' ] result = [names] for card in cards: row = [card.__getattribute__(attr) for attr in names] result.append(row) return result