def dump_data(): dir_name = 'nextgisbio/initial_data/csv/' + time.strftime("%Y_%m_%d_%H_%M_%S") if not os.path.exists(dir_name): os.makedirs(dir_name) else: raise Exception('Directory for exported csv files already exists') get_path_name = lambda name: os.path.join(dir_name, name) with transaction.manager: Annotation.export_to_file(get_path_name('annotation.csv')) Cards.export_to_file(get_path_name('cards.csv')) Person.export_to_file(get_path_name('person.csv')) Taxa_scheme.export_to_file(get_path_name('taxa_scheme.csv')) Museum.export_to_file(get_path_name('museum.csv')) Coord_type.export_to_file(get_path_name('coord_type.csv')) Anthr_press.export_to_file(get_path_name('anthr_press.csv')) Vitality.export_to_file(get_path_name('vitality.csv')) Abundance.export_to_file(get_path_name('abundance.csv')) Footprint.export_to_file(get_path_name('footprint.csv')) Pheno.export_to_file(get_path_name('pheno.csv')) Inforesources.export_to_file(get_path_name('inforesources.csv')) Legend.export_to_file(get_path_name('legend.csv')) Area_type.export_to_file(get_path_name('area_type.csv')) Key_area.export_to_file(get_path_name('key_area.csv')) RedBook.export_to_file(get_path_name('redbooks.csv')) User.export_to_file(get_path_name('user.csv')) Squares.export_to_file(get_path_name('square_karea_association.csv')) Taxon.export_to_file(get_path_name('taxon.csv')) Synonym.export_to_file(get_path_name('synonym.csv')) Images.export_to_file(get_path_name('images.csv')) CardsImages.export_to_file(get_path_name('cards_images.csv')) return dir_name
def create_taxon(request): new_data = dict(request.POST) dbsession = DBSession() taxon = Taxon() for k, v in new_data.items(): if v == '': v = None if hasattr(taxon, k): setattr(taxon, k, v) dbsession.add(taxon) dbsession.flush() dbsession.refresh(taxon) return {'item': taxon.as_json_dict()}
def get_taxon_path(request): taxon_id = request.matchdict['id'] taxons = Taxon.parent_taxons(taxon_id) path = [t.id for t in taxons] return {'success': True, 'path': path}
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 gen_sql(records): with transaction.manager: for r in records: id = None for t in 'Kingdom', 'Phylum', 'Class', 'Order', 'Family', 'Genus', 'Species': try: print t, r[t] taxon = DBSession.query(Taxon).filter_by(taxon_type = t, name = r[t], parent_id = id).one() print taxon except NoResultFound: taxon = Taxon(taxon_type = t, name = r[t], parent_id = id, author=r[t+'_author'], source=r[t+'_source']) DBSession.add(taxon) DBSession.flush() id = taxon.id print taxon.id, taxon.taxon_type, taxon.name, taxon.parent_id
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 get_taxon(request): id = int(request.matchdict['id']) parent_taxons = Taxon.parent_taxons(id)
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 main(argv=sys.argv): if len(argv) != 2 and len(argv) != 3: usage(argv) config_uri = argv[1] setup_logging(config_uri) settings = get_appsettings(config_uri) engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) md5_pass = False if len(argv) == 3 and argv[2] == '--md5-pass': md5_pass = True Base.metadata.drop_all(engine) Base.metadata.create_all(engine) # Заполним таблицы данными: # Таксоны taxons_file = 'nextgisbio/initial_data/csv/taxon.csv' Taxon.add_from_file(taxons_file) synonym_file = 'nextgisbio/initial_data/csv/synonym.csv' Synonym.add_from_file(synonym_file) # Справочники person_file = 'nextgisbio/initial_data/csv/person.csv' Person.add_from_file(person_file) taxa_file = 'nextgisbio/initial_data/csv/taxa_scheme.csv' Taxa_scheme.add_from_file(taxa_file) museum_file = 'nextgisbio/initial_data/csv/museum.csv' Museum.add_from_file(museum_file) coord_type_file = 'nextgisbio/initial_data/csv/coord_type.csv' Coord_type.add_from_file(coord_type_file) ant_file = 'nextgisbio/initial_data/csv/anthr_press.csv' Anthr_press.add_from_file(ant_file) vital_file = 'nextgisbio/initial_data/csv/vitality.csv' Vitality.add_from_file(vital_file) abundance_file = 'nextgisbio/initial_data/csv/abundance.csv' Abundance.add_from_file(abundance_file) footprint_file = 'nextgisbio/initial_data/csv/footprint.csv' Footprint.add_from_file(footprint_file) pheno_file = 'nextgisbio/initial_data/csv/pheno.csv' Pheno.add_from_file(pheno_file) infores_file = 'nextgisbio/initial_data/csv/inforesources.csv' Inforesources.add_from_file(infores_file) area_type_file = 'nextgisbio/initial_data/csv/area_type.csv' Area_type.add_from_file(area_type_file) legend_file = 'nextgisbio/initial_data/csv/legend.csv' Legend.add_from_file(legend_file) key_area_file = 'nextgisbio/initial_data/csv/key_area.csv' Key_area.add_from_file(key_area_file) # Нужно добавить шейпы и заполнить данными таблицу # связей (square_keyarea_association) многие-ко-многим между Squares и Key_area shp_file = 'nextgisbio/initial_data/shp/key_areas_25km.shp' association_file = 'nextgisbio/initial_data/csv/square_karea_association.csv' Squares.add_from_file(association_file, shp_file) # Карточки и аннотации cards_file = 'nextgisbio/initial_data/csv/cards.csv' Cards.add_from_file(cards_file) ann_file = 'nextgisbio/initial_data/csv/annotation.csv' Annotation.add_from_file(ann_file) # Пользователи users_file = 'nextgisbio/initial_data/csv/user.csv' User.add_from_file(users_file, md5_pass) red_books_csv = 'nextgisbio/initial_data/csv/redbooks.csv' RedBook.import_from_csv(red_books_csv) images_csv = 'nextgisbio/initial_data/csv/images.csv' Images.import_from_csv(images_csv) cards_images_csv = 'nextgisbio/initial_data/csv/cards_images.csv' CardsImages.import_from_csv(cards_images_csv)
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