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
