Python DBSession.query方法代码示例

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
    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)

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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'}

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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}

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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}

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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}

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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'
    }

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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
    }

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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
    }

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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)

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
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

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
    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

# 需要导入模块: from nextgisbio.models import DBSession [as 别名]
# 或者: from nextgisbio.models.DBSession import query [as 别名]
    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)