def table_item_save(request): person_id = None if ('person_id' in request.POST) and request.POST['person_id'].isdigit(): person_id = int(request.POST['person_id']) user_login = request.POST[ 'user_login'] if 'user_login' in request.POST else None if not user_login: raise HTTPBadRequest('"user_login" is required parameter') if not person_id: users = DBSession.query(User).filter(User.login == user_login).all() if len(users) > 0: return { 'Result': 'Error', 'Message': u'Такой логин уже присутствует в системе' } with transaction.manager: if person_id: person = DBSession.query(Person) \ .options(joinedload('user')) \ .filter(Person.id == person_id) \ .all()[0] user = person.user else: person = Person() DBSession.add(person) user = User() DBSession.add(user) person.user = user for attr in request.POST: table_name, field = attr.split('_') if field == 'id': continue if table_name == 'person': setattr(person, field, request.POST[attr]) if table_name == 'user': setattr(user, field, request.POST[attr]) if 'user_active' in request.POST and request.POST['user_active']: user.active = True else: user.active = False if 'user_password' in request.POST and request.POST['user_password']: user.password = User.password_hash(request.POST['user_password']) DBSession.flush() DBSession.refresh(user) DBSession.refresh(person) person_json = person.as_json_dict('person_') user_json = user.as_json_dict('user_') item_json = person_json.copy() item_json.update(user_json) return {'Result': 'OK', 'Record': item_json}
def post(self): synonym_dict = dict(self.request.json) with transaction.manager: dbsession = DBSession() dbsession.query(Synonym).filter_by( id=synonym_dict['id']).update(synonym_dict) return synonym_dict
def taxon_tree(request): taxon_parent_id = request.matchdict['taxon_parent_id'] parent_id = None if taxon_parent_id != 'root': parent_id = int(taxon_parent_id) with transaction.manager: dbsession = DBSession() parent_taxon = dbsession.query(Taxon).filter_by(id=parent_id).first() children_taxons = dbsession.query(Taxon).filter_by( parent_id=parent_id).all() if taxon_parent_id == 'root': parent_taxon_json = {'id': 'root', 'name': 'Все таксоны'} else: parent_taxon_json = parent_taxon.as_json_dict() if taxon_parent_id == 'root': parent_taxon_json['id'] = 'root' children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_json(taxon)) parent_taxon_json['children'] = children_taxons_json return parent_taxon_json
def update_related_items(table, db_relative_field, relative_field_name, old_id_from_csv, new_corrected_id): session = DBSession() new_person = session.query(Person)\ .filter(Person.id == new_corrected_id)\ .one() count_items = session.query(table)\ .outerjoin(Person, db_relative_field == Person.id)\ .filter(db_relative_field == old_id_from_csv)\ .count() items = session.query(table)\ .outerjoin(Person, db_relative_field == Person.id)\ .filter(db_relative_field == old_id_from_csv) if count_items > 0: with transaction.manager: save_session = DBSession() for item in items: save_session.query(table).filter_by(id=item.id).update( {relative_field_name: new_person.id}) transaction.commit() session.close()
def table_view(request): can_i_edit = has_permission('edit', request.context, request) can_i_edit = isinstance(can_i_edit, ACLAllowed) user_id = authenticated_userid(request) dbsession = DBSession() card, user = None, None try: card = dbsession.query(Cards).filter_by(id=request.matchdict['id']).one() user = dbsession.query(User).filter_by(id=user_id).one() if can_i_edit else None result = card.as_json_dict() except NoResultFound: result = {'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено'} if not can_i_edit: # обнулим координаты перед показом result['lat'] = 0 result['lon'] = 0 if isinstance(has_permission('admin', request.context, request), ACLAllowed): is_editable = True else: is_editable = card.inserter == user.person_id if user else False dbsession.close() return {'data': result, 'editable': is_editable, 'success': True}
def taxon_filter(request): query_str = request.params['name'].encode('utf-8').decode('utf-8') start = int(request.params['start']) count = int(request.params['count']) # Нужно выдернуть номера id, названия таксонов и авторов (для синонимов) из таблиц таксонов и синонимов dbsession = DBSession() try: query_str_upper = query_str.upper() # ищем в таблице таксонов: aFilter = u"UPPER({0}) LIKE '%{1}%'".format('name', query_str_upper) tax_all = dbsession.query(Taxon.id, Taxon.name, Taxon.author).filter(aFilter).all() aFilter = u"UPPER({0}) LIKE '%{1}%'".format('russian_name', query_str_upper) rus_all = dbsession.query(Taxon.id, Taxon.russian_name, Taxon.author).filter(aFilter).all() # ищем в таблице синонимов: aFilter = u"UPPER({0}) LIKE '%{1}%'".format('synonym', query_str_upper) s_all = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author).filter(aFilter).all() all = [tax_all + s_all + rus_all][0] itemsPage = all[start:start + count] dbsession.close() except DBAPIError: dbsession.close() return {'success': False, 'msg': 'Ошибка подключения к БД'} rows = [] if all: rec_id = itertools.count() rows = [{'recId': rec_id.next(), 'id': id, 'name': name, 'author': author} for id, name, author in itemsPage] return {'items': rows, 'success': True, 'numRows': len(all), 'identity': 'id'}
def table_view(request): can_i_edit = has_permission('edit', request.context, request) can_i_edit = isinstance(can_i_edit, ACLAllowed) user_id = authenticated_userid(request) try: model = table_by_name(request.matchdict['table']) except KeyError: return {'success': False, 'msg': 'Ошибка: отсутствует таблица с указанным именем'} dbsession = DBSession() try: entity = dbsession.query(model).filter_by(id=request.matchdict['id']).one() user = dbsession.query(User).filter_by(id=user_id).one() if can_i_edit else None result = {'data': entity.as_json_dict(), 'success': True} except NoResultFound: result = {'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено'} if hasattr(entity, 'inserter'): if isinstance(has_permission('admin', request.context, request), ACLAllowed): is_editable = True else: is_editable = entity.inserter == user.person_id if user else False else: is_editable = True result['editable'] = is_editable dbsession.close() return result
def taxon_tree(request): taxon_parent_id = request.matchdict['taxon_parent_id'] parent_id = None if taxon_parent_id != 'root': parent_id = int(taxon_parent_id) with transaction.manager: dbsession = DBSession() parent_taxon = dbsession.query(Taxon).filter_by(id=parent_id).first() children_taxons = dbsession.query(Taxon).filter_by(parent_id=parent_id).all() if taxon_parent_id == 'root': parent_taxon_json = { 'id': 'root', 'name': 'Все таксоны' } else: parent_taxon_json = parent_taxon.as_json_dict() if taxon_parent_id == 'root': parent_taxon_json['id'] = 'root' children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_json(taxon)) parent_taxon_json['children'] = children_taxons_json return parent_taxon_json
def _get_squares_by_taxonlist(taxons, geomtype='geojson'): ''' Выбор квадратов из БД, на которые приходятся анн.списки таксонов из taxons='taxon_id1,taxon_id2,...'. Вернуть по запросу геометрию каждого квадрата в соответствии с типом geomtype = ['geojson', 'wkt'] ''' assert geomtype in ['geojson', 'wkt'] dbsession = DBSession() if '#' in taxons: if geomtype == 'geojson': all = dbsession.query(Squares.id, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).all() else: all = dbsession.query(Squares.id, sqlalchemy.func.st_astext(Squares.geom.RAW)).all() else: # Выбираем ключевые участки, где встречен таксон, а по ним --- id квадратов, которые приходятся на эти участки: subquery = TAXON_ID_QUERY % (", ".join([ str(num) for num in taxons]), TAXON_TYPES[len(TAXON_TYPES)-1]) qs = """ SELECT DISTINCT square_id from square_karea_association WHERE square_karea_association.key_area_id in (SELECT DISTINCT key_area.id FROM annotation INNER JOIN key_area ON annotation.key_area = key_area.id""" + ' AND annotation.species IN (' + subquery +'));' k_set = dbsession.query(Squares.id).from_statement(qs).all() k_set = [k[0] for k in k_set] if geomtype == 'geojson': all = dbsession.query(Squares.id, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).filter(Squares.id.in_(k_set)).all() else: all = dbsession.query(Squares.id, sqlalchemy.func.st_astext(Squares.geom.RAW)).filter(Squares.id.in_(k_set)).all() dbsession.close() return all
def add_from_file(associations_filename, shp_filename): ''' Добавить данные из shp-файла shp_filename. Первое поле аттрибутивной таблицы--идентификатор. Одновременно добавляются в таблицу связи данные из файла с разделителями associations_filename. Файл filename в формате csv (разделитель табуляция), колонки: square_id key_area_id ''' import transaction with transaction.manager: dbsession = DBSession() ogrData = ogr.Open(shp_filename) layer = ogrData.GetLayer(0) sq = layer.GetNextFeature() while sq is not None: id = sq.GetFieldAsString(0) geom = sq.GetGeometryRef() geom = geom.ExportToWkt() square = Squares(id=id, geom=WKTSpatialElement(geom, srid=3857)) dbsession.add(square) sq = layer.GetNextFeature() dbsession.flush() reader = csv.reader(open(associations_filename), delimiter='\t') reader.next() records = [line for line in reader] for id, key_area_id in records: # Определим ключевоq уч-к по его id key_a = dbsession.query(Key_area).filter_by(id=key_area_id).one() # Определим полигон по его id square = dbsession.query(Squares).filter_by(id=id).one() square.key_areas.append(key_a)
def points_text(request): # Есть querystring, содержащее строку вида 'nodes=taxon_id1,taxon_id2'). # Например, "nodes=taxon_1,taxon_5" # Это значит, что пользователь выбрал записи из таблицы taxon с id=1 и id=5. # Требуется вернуть карточки наблюдений соотв. таксонов # # Граничный случай, когда нужно выбрать все карточки: nodes="root_" dbsession = DBSession() try: taxons = request.params['nodes'] except KeyError: taxons = '' red_book_id = None if 'red_book' in request.params: red_book_id = int(request.params['red_book']) if red_book_id == -1: red_book_id = None can_i_edit = has_permission('edit', request.context, request) can_i_edit = isinstance(can_i_edit, ACLAllowed) if taxons: taxons = urllib.unquote(taxons) taxon_id = taxons.split(',') if 'root' in taxons: cards = dbsession.query(Cards, Taxon).join(Taxon).all() else: # Получим список видов-потомков выбранных таксонов и связанных с ними карточек subquery = TAXON_ID_QUERY % (", ".join([str(num) for num in taxon_id]), TAXON_TYPES[len(TAXON_TYPES) - 1]) qs = """ SELECT cards.id,cards.species,cards.lat,cards.lon, taxon.name FROM cards INNER JOIN taxon ON cards.species = taxon.id %s WHERE """ % ( 'INNER JOIN red_books_species ON cards.species = red_books_species.specie_id' if red_book_id else '') \ + ((' red_books_species.red_book_id = ' + str(red_book_id) + ' AND ') if red_book_id else '') \ + ' cards.species IN (' + subquery + ');' cards = dbsession.query(Cards, Taxon).from_statement(qs).all() points = [] for card, taxon in cards: id, spec_id, lat, lon = card.id, card.species, card.lat, card.lon name = taxon.name if lat and lon: if not can_i_edit: # настоящие координаты показывать нельзя # сдвинем координаты перед показом примерно на 10 км в случайном направлении lat = lat + (random() - random()) / 7 lon = lon + (random() - random()) / 4 points.append({'lat': lat, 'lon': lon, 'name': name, 'card_id': id, 'spec_id': spec_id}) else: points = {} dbsession.close() return {'points': points}
def anns_text(request): # Есть querystring, содержащее строку вида 'nodes=taxon_id1,taxon_id2'). # Например, "nodes=taxon_1,taxon_5" # Это значит, что пользователь выбрал записи из таблицы taxon с id=1 и id=5. # Требуется вернуть аннотированные списки соотв. таксонов # # Граничный случай, когда нужно выбрать все списки: nodes="root_" dbsession = DBSession() # Ключевые участки по квадрату: id = request.matchdict['id'] square = dbsession.query(Squares).filter_by(id=id).one() key_areas = [str(s.id) for s in square.key_areas] key_areas = ", ".join(key_areas) try: taxons_id = request.params['nodes'] except KeyError: taxons_id = '' can_i_edit = has_permission('edit', request.context, request) can_i_edit = isinstance(can_i_edit, ACLAllowed) if taxons_id: taxons_id = urllib.unquote(taxons_id) taxons_id = taxons_id.split(',') if "root" in taxons_id: anns = dbsession.query(Annotation,Taxon).join(Taxon).all() qs = """ SELECT annotation.id,annotation.species, taxon.name FROM annotation INNER JOIN taxon ON annotation.species = taxon.id """ + ' AND annotation.key_area IN ( %s ) ;' % (key_areas, ) anns = dbsession.query(Annotation, Taxon).from_statement(qs).all() else: # Получим список видов-потомков выбранных таксонов и связанных с ними аннотаций из ключевых участков квадрата id subquery = TAXON_ID_QUERY % (", ".join([ str(num) for num in taxons_id]), TAXON_TYPES[len(TAXON_TYPES)-1]) qs = """ SELECT annotation.id,annotation.species, taxon.name FROM annotation INNER JOIN taxon ON annotation.species = taxon.id """ + ' AND annotation.key_area IN ( %s ) ' % (key_areas, ) + ' AND annotation.species IN (' + subquery +');' anns = dbsession.query(Annotation, Taxon).from_statement(qs).all() squares = [] for ann, taxon in anns: id, spec_id= ann.id, ann.species name = taxon.name squares.append({'name': name, 'ann_id': id, 'spec_id': spec_id}) else: points = {} dbsession.close() return {'data': squares}
def inforesources_name(request): dbsession = DBSession() numRows = 0 inforesources = [] success = True if ('id' in request.params) and request.params['id'].isdigit(): id = int(request.params['id']) try: inforesources = dbsession.query(Inforesources.id, Inforesources.filename)\ .filter(Inforesources.id == id).all() numRows = 1 except DBAPIError: success = False else: start, count = helpers.get_paging_params(request.params) parsed_filename = helpers.get_parsed_search_attr( request.params, 'filename') filter_conditions = [] if parsed_filename: filter_conditions.append( Inforesources.filename.ilike(parsed_filename)) try: if (start is not None) and (count is not None): inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \ .filter(*filter_conditions) \ .order_by(Inforesources.filename) \ .slice(start, start + count) \ .all() numRows = dbsession.query(Inforesources) \ .filter(*filter_conditions) \ .count() else: inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \ .filter(*filter_conditions) \ .order_by(Inforesources.filename) \ .all() numRows = len(inforesources) except DBAPIError: success = False inforesources_json = [] for (id, name) in inforesources: inforesources_json.append({'id': id, 'filename': name}) dbsession.close() return { 'items': inforesources_json, 'success': success, 'numRows': numRows, 'identifier': 'id' }
def taxon_cbtree(request): path_name = 'path' if 'path' in request.params else 'basePath' hierarchical_path = request.params[path_name].replace('"', '') if hierarchical_path == '.': parent_id = None else: parent_id = int(str.split(str(hierarchical_path), '/')[-1]) dbsession = DBSession() parent_taxon = dbsession.query(Taxon).filter_by(id=parent_id).first() children_taxons = dbsession.query(Taxon).filter_by( parent_id=parent_id).order_by(Taxon.name).all() dbsession.close() if hierarchical_path == '.': block = { 'name': '.', 'path': hierarchical_path, 'directory': True, 'total': 1, 'status': 200, 'items': [{ 'name': '.', 'id': -1, 'path': hierarchical_path, 'directory': True }] } else: block = { 'name': parent_taxon.name, 'path': hierarchical_path, 'directory': True, 'total': 1, 'status': 200, 'items': [] } children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_node(hierarchical_path, taxon)) if hierarchical_path == '.': block['items'][0]['children'] = children_taxons_json else: block['items'] = children_taxons_json return block if block else children_taxons_json
def inforesources_name(request): dbsession = DBSession() numRows = 0 inforesources = [] success = True if ('id' in request.params) and request.params['id'].isdigit(): id = int(request.params['id']) try: inforesources = dbsession.query(Inforesources.id, Inforesources.filename)\ .filter(Inforesources.id == id).all() numRows = 1 except DBAPIError: success = False else: start, count = helpers.get_paging_params(request.params) parsed_filename = helpers.get_parsed_search_attr(request.params, 'filename') filter_conditions = [] if parsed_filename: filter_conditions.append(Inforesources.filename.ilike(parsed_filename)) try: if (start is not None) and (count is not None): inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \ .filter(*filter_conditions) \ .order_by(Inforesources.filename) \ .slice(start, start + count) \ .all() numRows = dbsession.query(Inforesources) \ .filter(*filter_conditions) \ .count() else: inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \ .filter(*filter_conditions) \ .order_by(Inforesources.filename) \ .all() numRows = len(inforesources) except DBAPIError: success = False inforesources_json = [] for (id, name) in inforesources: inforesources_json.append({'id': id, 'filename': name}) dbsession.close() return { 'items': inforesources_json, 'success': success, 'numRows': numRows, 'identifier': 'id' }
def table_browse_jtable(request): session = DBSession() table, table_name = helpers.get_table_by_name(request) sorting = request.GET[ 'jtSorting'] if 'jtSorting' in request.GET else 'id asc' rows_count = 0 items = [] success = True if ('id' in request.params) and request.params['id'].isdigit(): id = int(request.params['id']) try: items = session.query(table) \ .filter(table.id == id) \ .all() rows_count = 1 except DBAPIError: success = False else: start, count = helpers.get_jtable_paging_params(request.params) filter_conditions = _get_filter_conditions(request, table) try: if (start is not None) and (count is not None): items = session.query(table) \ .filter(or_(*filter_conditions)) \ .order_by(sorting) \ .slice(start, start+count) \ .all() rows_count = session.query(table) \ .filter(*filter_conditions) \ .count() else: items = session.query(table) \ .filter(or_(*filter_conditions)) \ .order_by(sorting) \ .all() rows_count = len(items) except DBAPIError: success = False session.close() items_json = [] for row in items: items_json.append(row.as_json_dict()) return { 'Result': 'OK' if success else False, 'Records': items_json, 'TotalRecordCount': rows_count }
def table_browse_jtable(request): session = DBSession() table, table_name = helpers.get_table_by_name(request) sorting = request.GET['jtSorting'] if 'jtSorting' in request.GET else 'id asc' rows_count = 0 items = [] success = True if ('id' in request.params) and request.params['id'].isdigit(): id = int(request.params['id']) try: items = session.query(table) \ .filter(table.id == id) \ .all() rows_count = 1 except DBAPIError: success = False else: start, count = helpers.get_jtable_paging_params(request.params) filter_conditions = _get_filter_conditions(request, table) try: if (start is not None) and (count is not None): items = session.query(table) \ .filter(or_(*filter_conditions)) \ .order_by(sorting) \ .slice(start, start+count) \ .all() rows_count = session.query(table) \ .filter(*filter_conditions) \ .count() else: items = session.query(table) \ .filter(or_(*filter_conditions)) \ .order_by(sorting) \ .all() rows_count = len(items) except DBAPIError: success = False session.close() items_json = [] for row in items: items_json.append(row.as_json_dict()) return { 'Result': 'OK' if success else False, 'Records': items_json, 'TotalRecordCount': rows_count }
def cards_jtable_browse(request): if not security.authenticated_userid(request): raise exc.HTTPForbidden() rows_count = 0 items = [] success = True observer = aliased(Person) inserter = aliased(Person) aliased_info = { 'observer': observer, 'inserter': inserter } start, count = helpers.get_jtable_paging_params(request.params) filter_conditions = _get_filter_conditions(request, aliased_info) sorting = _get_sorting_param(request, aliased_info) session = DBSession() try: items = session.query(inserter, func.count(Cards.id).label('cards_count')) \ .outerjoin(Cards, inserter.id == Cards.inserter) \ .filter(and_(*filter_conditions)) \ .group_by(inserter.id) \ .order_by(sorting) \ .slice(start, start+count) \ .all() rows_count = session.query(inserter, func.count(Cards.id).label('cards_count')) \ .outerjoin(Cards, inserter.id == Cards.inserter) \ .filter(and_(*filter_conditions)) \ .group_by(inserter.id) \ .count() except DBAPIError as err: print("DBAPIError error: {0}".format(err)) success = False session.close() items_json = [] for row in items: item_json = row[0].as_json_dict('inserter__') item_json['__cards_count'] = row[1] items_json.append(item_json) return { 'Result': 'OK' if success else False, 'Records': items_json, 'TotalRecordCount': rows_count }
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 verify_ids(): session = DBSession() for condition in id_verify_conditions: print '--------------------' print condition['main']['csv']['file'] print '--------------------' print '\n' for csv_row in data[condition['main']['csv']['file']]['records']: csv_id, entity_name = csv_row[condition['main']['csv']['id']],\ csv_row[condition['main']['csv']['name']] db_entities = session.query(condition['main']['db']['table'])\ .filter(eq(condition['main']['db']['name'], entity_name))\ .all() if len(db_entities) != 1: raise Exception() db_entity_id = db_entities[0].id print '\n' print entity_name for dependency in condition['dependency']: csv_dependency_records = data[dependency['csv']['id'][0]]['records'] count_dependency_records = 0 db_dependency_entities = session.query(dependency['db']['table']) if 'alias' in dependency['db']: alias = dependency['db']['alias'] db_dependency_entities = db_dependency_entities.outerjoin(alias, dependency['db']['joined'] == alias.id) db_dependency_entities = db_dependency_entities.filter(eq(dependency['db']['alias'].id, db_entity_id)) else: db_dependency_entities = db_dependency_entities.filter(eq(dependency['db']['id'], db_entity_id)) db_dependency_entities = db_dependency_entities.all() for csv_dep_record in csv_dependency_records: if csv_dep_record[dependency['csv']['id'][1]] == csv_id: count_dependency_records += 1 # if len(db_dependency_entities) == count_dependency_records: # print '' print condition['main']['csv']['file'] + ' -> ' + dependency['csv']['id'][0] print 'db: ' + str(len(db_dependency_entities)) print 'csv: ' + str(count_dependency_records)
def taxon_cbtree(request): path_name = 'path' if 'path' in request.params else 'basePath' hierarchical_path = request.params[path_name].replace('"', '') if hierarchical_path == '.': parent_id = None else: parent_id = int(str.split(str(hierarchical_path), '/')[-1]) dbsession = DBSession() parent_taxon = dbsession.query(Taxon).filter_by(id=parent_id).first() children_taxons = dbsession.query(Taxon).filter_by(parent_id=parent_id).order_by(Taxon.name).all() dbsession.close() if hierarchical_path == '.': block = { 'name': '.', 'path': hierarchical_path, 'directory': True, 'total': 1, 'status': 200, 'items': [{ 'name': '.', 'id': -1, 'path': hierarchical_path, 'directory': True }] } else: block = { 'name': parent_taxon.name, 'path': hierarchical_path, 'directory': True, 'total': 1, 'status': 200, 'items': [] } children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_node(hierarchical_path, taxon)) if hierarchical_path == '.': block['items'][0]['children'] = children_taxons_json else: block['items'] = children_taxons_json return block if block else children_taxons_json
def get_child_taxons_by_parent(request): parent_taxon_id = request.params['id'] is_full_data = ('isFullData' in request.params ) and request.params['isFullData'] == 'true' is_root_node_requsted = parent_taxon_id == '#' if is_root_node_requsted: parent_taxon_id = None else: parent_taxon_id = int(parent_taxon_id) dbsession = DBSession() children_taxons = dbsession.query(Taxon).filter_by( parent_id=parent_taxon_id).order_by(Taxon.name).all() dbsession.close() children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_jsTree_item(taxon, is_full_data)) if is_root_node_requsted: result = _get_root_jsTree_item() result['children'] = children_taxons_json else: result = children_taxons_json return result
def s_ka_association_download(request): dbsession = DBSession() try: all = dbsession.query(square_keyarea_association).all() except DBAPIError: result = {'success': False, 'msg': 'Ошибка подключения к БД'} names = ['square_id', 'key_area_id'] rows = [names, ] for row in all: data = [] for name in names: data.append(try_encode(getattr(row, name))) rows.append(data) fname = tempfile.mktemp() try: file = open(fname, 'w') writer = csv.writer(file, delimiter = '\t') writer.writerows(rows) file.close() file = open(fname, 'r') data = file.read() resname = 'square_karea_association.csv' finally: # в любом случае удаляем файл os.remove(fname) dbsession.close() return Response(content_type="application/octet-stream", content_disposition="attachment; filename=%s" % (resname, ), body=data)
def export_to_file(filename): from nextgisbio.utils.dump_to_file import dump dbsession = DBSession() redbook_species_db = dbsession.query(RedBook, RedBookSpecies, Taxon)\ .join(RedBookSpecies, RedBook.id == RedBookSpecies.red_book_id)\ .join(Taxon, RedBookSpecies.specie_id == Taxon.id)\ .order_by(RedBook.id, RedBookSpecies.specie_id)\ .all() dbsession.close() attribute_names = ['region', 'orig_name', 'lat_name', 'author', 'population', 'status', 'univ_status', 'year', 'bibl'] objects_for_dump = [ [ o[1].region, o[1].orig_name, o[2].name, o[1].author, o[1].population, o[1].status, o[1].univ_status, o[1].year, o[0].name ] for o in redbook_species_db ] dump(filename, attribute_names, objects_for_dump, is_array=True)
def s_ka_association_download(request): dbsession = DBSession() try: all = dbsession.query(square_keyarea_association).all() except DBAPIError: result = {'success': False, 'msg': 'Ошибка подключения к БД'} names = ['square_id', 'key_area_id'] rows = [ names, ] for row in all: data = [] for name in names: data.append(try_encode(getattr(row, name))) rows.append(data) fname = tempfile.mktemp() try: file = open(fname, 'w') writer = csv.writer(file, delimiter='\t') writer.writerows(rows) file.close() file = open(fname, 'r') data = file.read() resname = 'square_karea_association.csv' finally: # в любом случае удаляем файл os.remove(fname) dbsession.close() return Response(content_type="application/octet-stream", content_disposition="attachment; filename=%s" % (resname, ), body=data)
def get_child_taxons_by_parent(request): parent_taxon_id = request.params['id'] is_full_data = ('isFullData' in request.params) and request.params['isFullData'] == 'true' is_root_node_requsted = parent_taxon_id == '#' if is_root_node_requsted: parent_taxon_id = None else: parent_taxon_id = int(parent_taxon_id) dbsession = DBSession() children_taxons = dbsession.query(Taxon).filter_by(parent_id=parent_taxon_id).order_by(Taxon.name).all() dbsession.close() children_taxons_json = [] for taxon in children_taxons: children_taxons_json.append(_taxon_to_jsTree_item(taxon, is_full_data)) if is_root_node_requsted: result = _get_root_jsTree_item() result['children'] = children_taxons_json else: result = children_taxons_json return result
def redbook_filter(request): dbsession = DBSession() query_str = request.params['name'].encode('utf-8').decode('utf-8') start = int(request.params['start']) count = int(request.params['count']) try: query_str_upper = query_str.upper() aFilter = u"UPPER({0}) LIKE '%{1}%'".format('name', query_str_upper) order_by_clauses = [] order_by_clauses = dojo.parse_sort(request) red_books = dbsession.query(RedBook.id, RedBook.name)\ .filter(aFilter)\ .order_by(order_by_clauses)\ .all() itemsPage = red_books[start:start + count] except DBAPIError: return {'success': False, 'msg': 'Ошибка подключения к БД'} rows = [{'id': id, 'name': name} for id, name in itemsPage] dbsession.close() return { 'items': rows, 'success': True, 'numRows': len(itemsPage), 'identity': 'id' }
def parent_taxons(taxon_id): """ Возвращает родительские таксоны данного таксона. """ dbsession = DBSession() qs = """ WITH RECURSIVE subtree AS ( SELECT * FROM taxon WHERE id=%s UNION ALL SELECT t.* FROM taxon AS t, subtree AS st WHERE (st.parent_id = t.id) ) SELECT * FROM subtree ; """ % ( taxon_id, ) taxons = dbsession.query(Taxon).from_statement(qs).all() # Отсортируем таксоны так, чтобы на первом месте списка шли царства, на последнем -- виды. taxons.sort(key=lambda x: TAXON_TYPES.index(x.taxon_type)) return taxons
def redbook_filter(request): dbsession = DBSession() query_str = request.params['name'].encode('utf-8').decode('utf-8') start = int(request.params['start']) count = int(request.params['count']) try: query_str_upper = query_str.upper() aFilter = u"UPPER({0}) LIKE '%{1}%'".format('name', query_str_upper) order_by_clauses = [] order_by_clauses = dojo.parse_sort(request) red_books = dbsession.query(RedBook.id, RedBook.name)\ .filter(aFilter)\ .order_by(order_by_clauses)\ .all() itemsPage = red_books[start:start + count] except DBAPIError: return {'success': False, 'msg': 'Ошибка подключения к БД'} rows = [{'id': id, 'name': name} for id, name in itemsPage] dbsession.close() return {'items': rows, 'success': True, 'numRows': len(itemsPage), 'identity': 'id'}
def get_synonyms(request): sessions = DBSession() taxon_id = int(request.matchdict['taxon_id']) synonyms = sessions.query(Synonym).filter_by(species_id=taxon_id).all() synonyms_json = [synonym.as_json_dict() for synonym in synonyms] count_synonyms = len(synonyms_json) request.response.headerlist = [('Content-Range', '{0}-{1}/{2}'.format(0, count_synonyms, count_synonyms))] return synonyms_json
def square(request): dbsession = DBSession() id = request.matchdict['id'] square = dbsession.query(Squares).filter_by(id=id).one() key_areas = [{'id': s.id, 'name': s.name} for s in square.key_areas] dbsession.close() return {'id': square.id, 'key_areas': key_areas}
def remove_image(request): image_id = request.matchdict['image_id'] obj_type = request.matchdict['type'] with transaction.manager: dbSession = DBSession() dbSession.query(CardsImages).filter_by(image_id=image_id).delete() image = dbSession.query(Images).filter_by(id=image_id).one() if image.local and os.path.exists(image.local): os.remove(image.local) path_without_ext, extension = os.path.splitext(image.local)[0], os.path.splitext(image.local)[1] for key_size in THUMBNAIL_SIZES: os.remove('%s_%s%s' % (path_without_ext, key_size, extension)) dbSession.delete(image) return {'success': True}
def square(request): dbsession = DBSession() id = request.matchdict['id'] square = dbsession.query(Squares).filter_by(id=id).one() key_areas = [{'id': s.id, 'name': s.name} for s in square.key_areas] dbsession.close() return {'id': square.id, 'key_areas': key_areas }
def taxon_filter(request): query_str = request.params['name'].encode('utf-8').decode('utf-8') start = int(request.params['start']) count = int(request.params['count']) # Нужно выдернуть номера id, названия таксонов и авторов (для синонимов) из таблиц таксонов и синонимов dbsession = DBSession() try: query_str_upper = query_str.upper() # ищем в таблице таксонов: aFilter = u"UPPER({0}) LIKE '%{1}%'".format('name', query_str_upper) tax_all = dbsession.query(Taxon.id, Taxon.name, Taxon.author).filter(aFilter).all() aFilter = u"UPPER({0}) LIKE '%{1}%'".format('russian_name', query_str_upper) rus_all = dbsession.query(Taxon.id, Taxon.russian_name, Taxon.author).filter(aFilter).all() # ищем в таблице синонимов: aFilter = u"UPPER({0}) LIKE '%{1}%'".format('synonym', query_str_upper) s_all = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author).filter(aFilter).all() all = [tax_all + s_all + rus_all][0] itemsPage = all[start:start + count] dbsession.close() except DBAPIError: dbsession.close() return {'success': False, 'msg': 'Ошибка подключения к БД'} rows = [] if all: rec_id = itertools.count() rows = [{ 'recId': rec_id.next(), 'id': id, 'name': name, 'author': author } for id, name, author in itemsPage] return { 'items': rows, 'success': True, 'numRows': len(all), 'identity': 'id' }
def remove_image(request): image_id = request.matchdict['image_id'] obj_type = request.matchdict['type'] with transaction.manager: dbSession = DBSession() dbSession.query(CardsImages).filter_by(image_id=image_id).delete() image = dbSession.query(Images).filter_by(id=image_id).one() if image.local and os.path.exists(image.local): os.remove(image.local) path_without_ext, extension = os.path.splitext( image.local)[0], os.path.splitext(image.local)[1] for key_size in THUMBNAIL_SIZES: os.remove('%s_%s%s' % (path_without_ext, key_size, extension)) dbSession.delete(image) return {'success': True}
def export_to_file(filename): from nextgisbio.utils.dump_to_file import dump fieldnames = [ 'id', 'name', 'fullname', 'speciality', 'degree', 'organization', 'position', 'email', 'phone', 'address' ] dbsession = DBSession() dump(filename, fieldnames, dbsession.query(Person).order_by(Person.id).all())
def squares_text(request): dbsession = DBSession() all = dbsession.query(Squares, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).all() squares = [] for sq, geom in all: squares.append({'id': sq.id, 'geom': geom}) dbsession.close() return {'squares' : squares}
def direct_child(request): # Ext посылает запрос, содержащий строку вида 'node'='taxon_идентификатор') # например, 'node'='taxon_1', где id = id записи в таблице taxons # (об идентификаторах см. ниже, в цикле, # где в ответ на запрос выдаются дочерние узлы с идентификаторами) # Два граничных случая: # taxon == 'root': Корень дерева таксонов, childern=все записи из Kingdom # taxon.is_last_taxon == True: конец иерархии (это последний таксон) => leaf:=True node = request.params['node'] dbsession = DBSession() try: if node == 'root': childern = dbsession.query(Taxon).filter_by(parent_id=None).all() else: node = node.split('_') id = int(node[1]) childern = dbsession.query(Taxon).filter_by(parent_id=id).all() dbsession.close() except NoResultFound: dbsession.close() return { 'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено' } # Генерируем описания узлов для Ext.treepanel rows = [] for taxon in childern: node = {} # Ext хочет получать информацию из поля 'text' # Сформируем это поле из названия и автора author = taxon.author if taxon.author else '' is_last = taxon.is_last_taxon() node['id'] = 'taxon_' + str(taxon.id) node['leaf'] = is_last if is_last: node['text'] = "<b>%s</b> %s" % (taxon.name, author) else: node['text'] = "%s %s" % (taxon.name, author) rows.append(node) return rows
def table_item_save(request): session = DBSession() session.expire_on_commit = False if ('person_id' in request.POST) and request.POST['person_id'].isdigit(): person_id = int(request.POST['person_id']) person = session.query(Person) \ .options(joinedload('user')) \ .filter(Person.id == person_id) \ .all()[0] user = person.user else: person = Person() user = User() session.add(user) person.user = user for attr in request.POST: table_name, field = attr.split('_') if field == 'id': continue if table_name == 'person': setattr(person, field, request.POST[attr]) if table_name == 'user': setattr(user, field, request.POST[attr]) if 'user_active' in request.POST and request.POST['user_active']: user.active = True else: user.active = False if 'user_password' in request.POST and request.POST['user_password']: user.password = User.password_hash(request.POST['user_password']) session.add(person) try: transaction.commit() except IntegrityError: transaction.abort() return { 'Result': 'Error', 'Message': u'Такой логин уже присутствует в системе' } person_json = person.as_json_dict('person_') user_json = user.as_json_dict('user_') item_json = person_json.copy() item_json.update(user_json) session.close() return { 'Result': 'OK', 'Record': item_json }
def _get_squares_by_taxonlist(taxons, geomtype='geojson'): ''' Выбор квадратов из БД, на которые приходятся анн.списки таксонов из taxons='taxon_id1,taxon_id2,...'. Вернуть по запросу геометрию каждого квадрата в соответствии с типом geomtype = ['geojson', 'wkt'] ''' assert geomtype in ['geojson', 'wkt'] dbsession = DBSession() if 'root' in taxons: if geomtype == 'geojson': all = dbsession.query( Squares.id, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).all() else: all = dbsession.query(Squares.id, sqlalchemy.func.st_astext( Squares.geom.RAW)).all() else: # Выбираем ключевые участки, где встречен таксон, а по ним --- id квадратов, которые приходятся на эти участки: subquery = TAXON_ID_QUERY % (", ".join( [str(num) for num in taxons]), TAXON_TYPES[len(TAXON_TYPES) - 1]) qs = """ SELECT DISTINCT square_id from square_karea_association WHERE square_karea_association.key_area_id in (SELECT DISTINCT key_area.id FROM annotation INNER JOIN key_area ON annotation.key_area = key_area.id""" + ' AND annotation.species IN (' + subquery + '));' k_set = dbsession.query(Squares.id).from_statement(qs).all() k_set = [k[0] for k in k_set] if geomtype == 'geojson': all = dbsession.query( Squares.id, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).filter( Squares.id.in_(k_set)).all() else: all = dbsession.query(Squares.id, sqlalchemy.func.st_astext( Squares.geom.RAW)).filter( Squares.id.in_(k_set)).all() dbsession.close() return all
def direct_child(request): # Ext посылает запрос, содержащий строку вида 'node'='taxon_идентификатор') # например, 'node'='taxon_1', где id = id записи в таблице taxons # (об идентификаторах см. ниже, в цикле, # где в ответ на запрос выдаются дочерние узлы с идентификаторами) # Два граничных случая: # taxon == 'root': Корень дерева таксонов, childern=все записи из Kingdom # taxon.is_last_taxon == True: конец иерархии (это последний таксон) => leaf:=True node = request.params['node'] dbsession = DBSession() try: if node == 'root': childern = dbsession.query(Taxon).filter_by(parent_id=None).all() else: node = node.split('_') id = int(node[1]) childern = dbsession.query(Taxon).filter_by(parent_id=id).all() dbsession.close() except NoResultFound: dbsession.close() return {'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено'} # Генерируем описания узлов для Ext.treepanel rows = [] for taxon in childern: node = {} # Ext хочет получать информацию из поля 'text' # Сформируем это поле из названия и автора author = taxon.author if taxon.author else '' is_last = taxon.is_last_taxon() node['id'] = 'taxon_' + str(taxon.id) node['leaf'] = is_last if is_last: node['text'] = "<b>%s</b> %s" % (taxon.name, author) else: node['text'] = "%s %s" % (taxon.name, author) rows.append(node) return rows
def delete_anlist(request): annotation_id = request.matchdict['id'] success = True try: with transaction.manager: dbsession = DBSession() annotation = dbsession.query(Annotation).filter_by(id=annotation_id).one() dbsession.delete(annotation) except: success = False return {'success': success}
def __parent_in_list__(self, list): ''' Возвращает истину, если данный таксон является дочерним таксоном от одного из таксонов, чьи названия перечисленны в списке list ''' dbsession = DBSession() target = dbsession.query(Taxon).filter(Taxon.name.in_(list)).all() target_ids = [t.id for t in target] for id in target_ids: if self.child_of(id): return True return False
def taxon_type(request): taxon_id = request.matchdict['id'] with transaction.manager: dbsession = DBSession() p = dbsession.query(Taxon).filter(Taxon.id == taxon_id).one() types = {'mammalia': p.is_mammalia(), 'aves': p.is_aves(), 'plantae': p.is_plantae(), 'ara': p.is_ara(), 'arthropoda': p.is_arthropoda(), 'moss': p.is_moss(), 'lichenes': p.is_lichenes()} return types
def delete_card(request): card_id = request.matchdict['id'] success = True try: with transaction.manager: dbsession = DBSession() card = dbsession.query(Cards).filter_by(id=card_id).one() dbsession.delete(card) except: success = False return {'success': success}
def squares_text(request): dbsession = DBSession() all = dbsession.query(Squares, sqlalchemy.func.st_asgeojson( Squares.geom.RAW)).all() squares = [] for sq, geom in all: squares.append({'id': sq.id, 'geom': geom}) dbsession.close() return {'squares': squares}
def get_synonyms(request): sessions = DBSession() taxon_id = int(request.matchdict['taxon_id']) synonyms = sessions.query(Synonym).filter_by(species_id=taxon_id).all() synonyms_json = [synonym.as_json_dict() for synonym in synonyms] count_synonyms = len(synonyms_json) request.response.headerlist = [ ('Content-Range', '{0}-{1}/{2}'.format(0, count_synonyms, count_synonyms)) ] return synonyms_json
def species_by_taxon(taxon_ids): """ Возвращает список видов (species), которые являются потомками таксонов, указанных в списке taxon_ids """ dbsession = DBSession() qs = TAXON_ALL_QUERY % (", ".join([str(num) for num in taxon_ids]), TAXON_TYPES[len(TAXON_TYPES) - 1]) + ";" taxons = ( dbsession.query(Taxon.id, Taxon.taxon_type, Taxon.name, Taxon.author, Taxon.source).from_statement(qs).all() ) taxons = [{"id": t[0], "taxon_type": t[1], "name": t[2], "author": t[3], "source": t[4]} for t in taxons] return taxons
def export_to_file(filename): fieldnames = ['card_id', 'image_id'] with open(filename, 'wb') as file: writer = csv_utf.UnicodeWriter(file) writer.writerow(fieldnames) session = DBSession() images = [[cards_image.card_id, cards_image.image_id] for cards_image in session.query(CardsImages).order_by( CardsImages.card_id, CardsImages.image_id).all()] session.close() writer.writerows(images)
def karea_ann(request): dbsession = DBSession() id = request.matchdict['id'] karea = dbsession.query(Key_area).filter_by(id=id).one() annotations = [] for ann in karea.annotations: annotations.append({'id': ann.id, 'name': ann.species_link.name, 'species': ann.species}) dbsession.close() return {'data': annotations}
def upload_image(request): filename = request.POST['file'].filename input_file = request.POST['file'].file obj_id = request.matchdict['id'] obj_type = request.matchdict['type'] path_to_images = os.path.join(os.path.dirname(nextgisbio.__file__), 'static/data/images') date_now = datetime.datetime.now().strftime('%Y-%m-%d') path_to_images_now = os.path.join(path_to_images, date_now) if not os.path.exists(path_to_images_now): os.mkdir(path_to_images_now) # from http://stackoverflow.com/questions/2782229/most-lightweight-way-to-create-a-random-string-and-a-random-hexadecimal-number random_file_name = str(uuid.uuid4()) base_file_path = os.path.join(path_to_images_now, '.'.join([random_file_name, 'jpg'])) with open(base_file_path, 'wb') as output_file: shutil.copyfileobj(input_file, output_file) for key_size in THUMBNAIL_SIZES: try: im = Image.open(base_file_path) im.thumbnail(THUMBNAIL_SIZES[key_size], Image.BICUBIC) im.save(os.path.join( path_to_images_now, '.'.join([random_file_name + '_' + key_size, 'jpg'])), 'JPEG', quality=70) except IOError: print "cannot create thumbnail for '%s'" % base_file_path with transaction.manager: dbSession = DBSession() image = Images() image.name = filename image.url = '/static/data/images/%s/%s.jpg' % (date_now, random_file_name) image.size = os.path.getsize(base_file_path) image.local = base_file_path dbSession.add(image) if obj_type == 'card': card_image = CardsImages() card_image.image = image card_image.card = dbSession.query(Cards).filter_by(id=obj_id).one() dbSession.add(card_image) photo_json = image.as_json_dict() return photo_json
def __parent_in_list__(self, list): """ Возвращает истину, если данный таксон является дочерним таксоном от одного из таксонов, чьи названия перечисленны в списке list """ dbsession = DBSession() target = dbsession.query(Taxon).filter(Taxon.name.in_(list)).all() target_ids = [t.id for t in target] for id in target_ids: if self.child_of(id): return True return False
def export_to_file(filename): fieldnames = ['id', 'name', 'description', 'url', 'local', 'size'] with open(filename, 'wb') as file: writer = csv_utf.UnicodeWriter(file) writer.writerow(fieldnames) session = DBSession() images = [[ image.id, image.name, image.description, image.url, image.local, image.size ] for image in session.query(Images).order_by(Images.id).all()] session.close() writer.writerows(images)
def persons_jtable_browse(request): session = DBSession() rows_count = 0 items = [] success = True start, count = helpers.get_jtable_paging_params(request.params) filter_conditions = _get_filter_conditions(request) sorting = _get_sorting_param(request) try: items = session.query(Person, User) \ .join(User) \ .filter(or_(*filter_conditions)) \ .order_by(sorting) \ .slice(start, start + count) \ .all() rows_count = session.query(Person) \ .filter(*filter_conditions) \ .count() except DBAPIError: success = False session.close() items_json = [] for row in items: person = row[0].as_json_dict('person_') user = row[1].as_json_dict('user_') item_json = person.copy() item_json.update(user) items_json.append(item_json) return { 'Result': 'OK' if success else False, 'Records': items_json, 'TotalRecordCount': rows_count }