Python DBSession.close方法代码示例

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

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

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

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

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

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

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

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

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