Python DBSession.query方法代码示例

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def link_regions_to_fed(args):

    #try:
    db_session = DBSession()
    transaction.manager.begin()

    # 1. Get all federals
    fds = db_session.query(FederalDistrict).all()

    # 2. Get all regions
    regions = db_session.query(Region).all()

    # 3. Read csv file with federals (for get original fed id)
    csv_fds = get_from_csv(path.join(BASE_PATH, 'federal_districts.csv'))

    # 4. Read updated csv file with regions and federal ids
    csv_regions = get_from_csv(path.join(BASE_PATH, 'regions.csv'))

    # 5. Update regions in DB
    for region in regions:
        # get fed_id from csv by region_code
        orig_fed_id = next(ifilter(lambda x: x['region_code'] == str(region.region_code), csv_regions))['fed_id']
        # get original federal from csv
        orig_fed = next(ifilter(lambda x: x['id'] == orig_fed_id, csv_fds))
        # find federal in db by short_name
        db_fed = next(ifilter(lambda fed: fed.short_name == unicode(orig_fed['short_name'], 'utf8'), fds))
        # update db region
        region.federal_dist = db_fed

    transaction.manager.commit()
    db_session.close()

    print ('Region was linked with federal districts')

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
    def run(cls, env):
        return  # disabled by request of Sergey

        db_session = DBSession()
        transaction.manager.begin()

        LogEntry.info('InternalUpdateReactor started!', component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())

        fs_resources = db_session.query(FoclStruct).all()

        for fs in fs_resources:

            # get const obj
            try:
                const_obj = db_session.query(ConstructObject).filter(ConstructObject.resource_id == fs.id).one()
            except:
                LogEntry.info('Error on update const obj with id = ' + str(fs.id),
                              component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())
                continue

            # update from project vector layers
            const_obj.fosc_plan = cls.get_feat_count_for_layer(fs, 'fosc')
            const_obj.cross_plan = cls.get_feat_count_for_layer(fs, 'optical_cross')
            const_obj.spec_trans_plan = cls.get_feat_count_for_layer(fs, 'special_transition')


        db_session.flush()
        LogEntry.info('InternalUpdateReactor finished!', component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())

        transaction.manager.commit()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def _get_years():
    # get min max from db
    db_session = DBSession()
    ucn_proj = db_session.query(Project).filter(Project.keyname==UCN_PROJECT_KEYNAME).one()

    min_start_date = db_session.query(func.min(ConstructObject.start_build_date)).filter(ConstructObject.project == ucn_proj).scalar()
    max_end_date = db_session.query(func.max(ConstructObject.end_build_date)).filter(ConstructObject.project == ucn_proj).scalar()

    # if null, set def values
    min_start_year = min_start_date.year if min_start_date else 2015
    max_end_year = max_end_date.year if max_end_date else 2020

    # check min max
    if min_start_year > max_end_year:
        min_start_year, max_end_year = max_end_year, min_start_year

    # create range
    years = list(range(min_start_year, max_end_year+1))

    # current and selected years
    current_year = date.today().year
    selected_year = current_year if current_year in years else years[0]

    years_view_model = [{'year': x, 'selected': x == selected_year} for x in years]
    return years_view_model

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
    def load_domain_dicts(cls, force=False, federal=True, region=True, district=True):
        print 'Loading domain dicts...'
        from ..compulink_admin.model import Region, District
        from csv import DictReader


        if (region and not federal) or (district and not region):
            print('Not consist params!')
            return

        db_session = DBSession()
        db_session.autoflush = False
        with transaction.manager:

            if ((db_session.query(FederalDistrict).count() > 0 and federal) or
               (db_session.query(Region).count() > 0 and region) or
               (db_session.query(District).count() > 0 and district)) and not force:
                print '     Domain dictionary already existings! Returning...'
                return

            with open(path.join(BASE_PATH, 'federal_districts.csv')) as fed_csv, \
                 open(path.join(BASE_PATH, 'regions.csv')) as reg_csv, \
                 open(path.join(BASE_PATH, 'districts.csv')) as dist_csv:

                fed_reader = DictReader(fed_csv)
                reg_reader = DictReader(reg_csv)
                dist_reader = DictReader(dist_csv)


                feds = {}
                if federal:
                    for fed_row in fed_reader:
                        federal_dist = FederalDistrict()
                        federal_dist.name = fed_row['name']
                        federal_dist.short_name = fed_row['short_name']
                        federal_dist.persist()
                        feds[fed_row['id']] = federal_dist


                regs = {}
                if region:
                    for reg_row in reg_reader:
                        region = Region()
                        region.name = reg_row['name']
                        region.short_name = reg_row['short_name']
                        region.region_code = reg_row['region_code']
                        region.persist()
                        regs[reg_row['id']] = region

                if district:
                    for dist_row in dist_reader:
                        district = District()
                        district.name = dist_row['name']
                        district.short_name = dist_row['short_name']
                        district.region = regs[dist_row['region_id']]

                        district.persist()


            db_session.flush()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def get_child_resx_by_parent(request):
    if request.user.keyname == 'guest':
        raise HTTPForbidden()

    parent_resource_id = request.params.get('id', None)
    if parent_resource_id is None:
        raise HTTPBadRequest('Set "id" param!')
    else:
        parent_resource_id = parent_resource_id.replace('res_', '')
    is_root_node_requsted = parent_resource_id == '#'

    type_filter = request.params.get('type', None)

    dbsession = DBSession()
    if is_root_node_requsted:
        parent_resource_id = dbsession.query(Resource).filter(Resource.parent==None).all()[0].id

    parent_resource = dbsession.query(Resource).get(parent_resource_id)
    children = parent_resource.children

    suitable_types = [
        ResourceGroup.identity,
        FoclProject.identity,
        ]
    if type_filter == 'vols' or not type_filter:
        suitable_types.append(FoclStruct.identity)
    if type_filter == 'sit' or not type_filter:
        suitable_types.append(SituationPlan.identity)

    if not request.user.is_administrator:
        allowed_res_list = _get_user_resources_tree(request.user)

    child_resources_json = []
    for child_resource in children:
        if child_resource.identity in suitable_types:
            # remove system folders
            if child_resource.identity == ResourceGroup.identity and child_resource.keyname == DICTIONARY_GROUP_KEYNAME:
                continue
            # check permissions
            if not request.user.is_administrator and child_resource.id not in allowed_res_list:
                continue
            is_need_checkbox = child_resource.identity in (FoclProject.identity, SituationPlan.identity, FoclStruct.identity)
            has_children = child_resource.identity in (ResourceGroup.identity, FoclProject.identity)
            child_resources_json.append({
                'id': 'res_' + str(child_resource.id),
                'text': child_resource.display_name,
                'children': has_children,
                'has_children': has_children,
                'icon': child_resource.identity,
                'res_type': child_resource.identity,
                'a_attr': {'chb': is_need_checkbox}
            })

            if not is_need_checkbox:
                child_resources_json[-1]['state'] = {'disabled': True}

    dbsession.close()

    return Response(json.dumps(child_resources_json))

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def get_federal_districts_layer(request):
    if request.user.keyname == 'guest':
        raise HTTPForbidden()

    project_filter = request.params.get('project_filter', None)
    if project_filter is None:
        raise HTTPBadRequest('Set "project_filter" param!')

    # --- attribute parts
    dbsession = DBSession()
    # all FD
    fds = dbsession.query(FederalDistrict).order_by(FederalDistrict.name).all()
    # filter by rights
    allowed_res_ids = None
    if not request.user.is_administrator:
        allowed_res_ids = get_user_writable_focls(request.user)
    # filter by struct
    project_res_ids = None
    if project_filter and project_filter != 'root':
        project_res_ids = get_project_focls(project_filter)

    fd_colors = {}
    for fd in fds:
        co_query = dbsession.query(ConstructionStatusReport, ConstructObject)\
        .outerjoin(ConstructObject, ConstructObject.resource_id == ConstructionStatusReport.focl_res_id)

        if allowed_res_ids is not None:
            co_query = co_query.filter(ConstructionStatusReport.focl_res_id.in_(allowed_res_ids))
        if project_res_ids is not None:
            co_query = co_query.filter(ConstructionStatusReport.focl_res_id.in_(project_res_ids))
        # all regions in fd
        regions_ids = get_child_regions_ids(fd.id)
        co_query = co_query.filter(ConstructionStatusReport.region.in_(regions_ids))

        construct_objects = co_query.all()

        fd_colors[fd.id] = get_color_for_co(construct_objects)

    # --- geometry part
    geom_dict_resource = dbsession.query(Resource).filter(Resource.keyname == FEDERAL_KEYNAME).one()
    query = geom_dict_resource.feature_query()
    query.geom()
    result = ColorizeProxy(query())

    # --- merge result
    result.colorize(lambda feat: fd_colors[feat['properties']['fed_id']] if feat['properties']['fed_id'] in fd_colors.keys() else COLOR_GRAY)

    # --- return
    content_disposition = (b'attachment; filename=%s.geojson'
                           % FEDERAL_KEYNAME)
    return Response(
        geojson.dumps(result, ensure_ascii=False, cls=ComplexEncoder),
        content_type=b'application/json',
        charset='utf-8',
        content_disposition=content_disposition)\

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def fill_construct_obj_12_10(args):

    db_session = DBSession()
    transaction.manager.begin()

    # remove all existing ConstructObjects
    db_session.query(ConstructObject).delete()

    region_dict = get_regions_from_resource(as_dict=True)
    district_dict = get_districts_from_resource(as_dict=True)

    # fill
    resources = db_session.query(FoclStruct)

    for focl_struct in resources:
        co = ConstructObject()
        co.name = focl_struct.display_name
        co.resource = focl_struct
        co.external_id = focl_struct.external_id

        # try to get region
        if focl_struct.region:
            if focl_struct.region in region_dict.keys():
                name = region_dict[focl_struct.region]
                try:
                    region = db_session.query(Region).filter(Region.name == name).one()
                    co.region = region
                except:
                    print 'Region name not found in regions table! Resource %s, region name = %s' % (focl_struct.id, name)
            else:
                print 'Region id not found in layer! Resource %s' % focl_struct.id

        # try to get district
        if focl_struct.district:
            if focl_struct.district in district_dict.keys():
                name = district_dict[focl_struct.district]
                try:
                    dist_query = db_session.query(District).filter(District.name == name)
                    if co.region:
                        dist_query = dist_query.filter(District.region==co.region)
                    dist = dist_query.one()
                    co.district = dist
                except:
                    print 'District name not found in district table! Resource %s, district name = %s' % (focl_struct.id, name)
            else:
                print 'District id not found in layer! Resource %s' % focl_struct.id

        #try to get project
        co.project = ModelsUtils.get_project_by_resource(focl_struct)

        co.persist()

    transaction.manager.commit()
    db_session.close()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
    def run(cls, env):

        ngw_session = NgwSession()
        ms_session = MsSqlSession()

        transaction.manager.begin()

        LogEntry.info('ExternalUpdateReactor started!', component=COMP_ID, group=ExternalUpdateReactor.identity, append_dt=datetime.now())

        # get mssql conn info
        enabled_sett = env.compulink_mssql_bridge.settings.get('enable', 'false').lower()
        mssql_enable = enabled_sett in ('true', 'yes', '1')

        if not mssql_enable:
            LogEntry.info('MSSQL disabled in config! Returning...', component=COMP_ID, group=ExternalUpdateReactor.identity, append_dt=datetime.now())
            return

        # get all external ids
        fs_external_ids = ngw_session.query(ConstructObject.external_id).filter(ConstructObject.external_id != None, ConstructObject.external_id != '').all()
        fs_external_ids = [r for (r,) in fs_external_ids]

        # get info from mssql
        CompulinkMssqlBridgeComponent.configure_db_conn(env.compulink_mssql_bridge.settings.get('conn_str', 'no'))
        ms_rows = ms_session.query(MssqConstObject)\
            .filter(MssqConstObject.ObjectID.in_(fs_external_ids))\
            .options(joinedload_all(MssqConstObject.Work3), joinedload_all(MssqConstObject.Work4)).all()

        for ms_row in ms_rows:
            # get const obj
            try:
                const_obj = ngw_session.query(ConstructObject).filter(ConstructObject.external_id == str(ms_row.ObjectID)).one()
            except:
                LogEntry.info('Error on update const obj with ext id = ' + str(ms_row.ObjectID),
                              component=COMP_ID, group=ExternalUpdateReactor.identity, append_dt=datetime.now())
                continue

            # update from mssql
            const_obj.cabling_plan = ms_row.PreliminaryLineLength     #new requ TODO: check!
            const_obj.access_point_plan = ms_row.AccessPointAmount              #new requ
            const_obj.subcontr_name = ms_row.Work3.SubContractor.ContractorName if ms_row.Work3 and ms_row.Work3.SubContractor else None
            const_obj.start_build_date = ms_row.Work3.AgreementStartDateWork if ms_row.Work3 else None
            const_obj.end_build_date = ms_row.Work3.AgreementFinishDateWork if ms_row.Work3 else None
            const_obj.start_deliver_date = ms_row.Work4.AgreementStartDateWork if ms_row.Work4 else None
            const_obj.end_deliver_date = ms_row.Work4.AgreementFinishDateWork if ms_row.Work4 else None

        ngw_session.flush()
        LogEntry.info('ExternalUpdateReactor finished!', component=COMP_ID, group=ExternalUpdateReactor.identity, append_dt=datetime.now())

        transaction.manager.commit()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def get_extent_by_resource_id(resource_id):
    session = DBSession()
    resource = session.query(Resource).filter(Resource.id == resource_id).first()

    extent = None
    for res in resource.children:
        if res.identity != VectorLayer.identity or (res.keyname and res.keyname.startswith('real_')):
            continue

        table_info = TableInfo.from_layer(res)
        table_info.setup_metadata(tablename=res._tablename)

        columns = [db.func.st_astext(db.func.st_extent(db.text('geom')).label('box'))]
        query = sql.select(columns=columns, from_obj=table_info.table)
        extent_str = session.connection().scalar(query)

        if extent_str:
            if not extent:
                extent = loads(extent_str).bounds
            else:
                new_extent = loads(extent_str).bounds
                extent = extent_union(extent, new_extent)

    session.close()

    return extent_buff(extent, 2000)

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def get_district_extent(request):
    dist_id = request.matchdict["id"]
    if dist_id is None:
        return Response("[]")

    db_session = DBSession()

    try:
        distr_res = (
            db_session.query(Resource)
            .filter(Resource.keyname == Layers.DISTRICTS, Resource.cls == VectorLayer.identity)
            .one()
        )
    except NoResultFound:
        raise exc.HTTPInternalServerError("Публичная карта не настроена! Обратитесь к администратору сервера")

    try:
        query = distr_res.feature_query()
        query.geom()
        query.filter_by(id=int(dist_id))
        for feat in query():
            extent = feat.geom.bounds
            break
    except:
        raise exc.HTTPInternalServerError("Район с заданныи id не найден! Обратитесь к администратору сервера")

    resp = {"extent": extent}

    return Response(json.dumps(resp))

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def append_picket_layer_to_wfs(args):
    db_session = DBSession()

    transaction.manager.begin()

    fs_resources = db_session.query(FoclStruct).all()

    # get struct from
    template_path = os.path.join(BASE_PATH, 'layers_templates/')

    vl_name = 'picket'

    for fs in fs_resources:
        # get picket layer
        picket_layer = [res for res in fs.children if res.keyname and vl_name in res.keyname]

        if picket_layer:
            # get wfs service
            wfs_services = [res for res in fs.children if res.cls == WfsServiceResource.identity]

            if wfs_services:
                for wfs_service in wfs_services:
                    already_added = bool([lyr for lyr in wfs_service.layers if lyr.keyname == vl_name])
                    if not already_added:
                        ModelsUtils.append_lyr_to_wfs(wfs_service, picket_layer[0], vl_name)
                        print 'Focl struct %s. Added to wfs service: %s' % (fs.display_name, vl_name)
                    else:
                        print 'Focl struct %s. Layer %s already added' % (fs.display_name, vl_name)
                    #wfs_service.persist()



    transaction.manager.commit()
    db_session.close()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def append_accepted_part_layer(args):
    db_session = DBSession()

    transaction.manager.begin()

    fs_resources = db_session.query(FoclStruct).all()

    # get struct from
    additional_template_path = os.path.join(BASE_PATH, 'additional_layers_templates/')

    vl_name = 'accepted_part'

    for fs in fs_resources:
        fs_children_keys = [res.keyname for res in fs.children if res.keyname]

        # check exists
        found = [res_key for res_key in fs_children_keys if vl_name in res_key]
        if not found:
            try:
                with codecs.open(os.path.join(additional_template_path, vl_name + '.json'), encoding='utf-8') as json_file:
                    json_layer_struct = json.load(json_file, encoding='utf-8')
                    vector_layer = ModelsUtils.create_vector_layer(fs, json_layer_struct, vl_name)
                    mapserver_style = ModelsUtils.set_default_style(vector_layer, vl_name, 'default')
            except Exception, ex:
                print 'Error on append layer %s to %s: %s' % (vl_name, fs.display_name, ex.message)
                return
            print 'Focl struct %s. Added: %s' % (fs.display_name, vl_name)

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def editor_delete_geom(request):
    if request.user.keyname == 'guest':
        raise HTTPForbidden()
    if request.method != 'DELETE':
        return error_response(u'Метод не поддерживается! Необходим DELETE')
    try:
        deletes = request.json_body

        db_session = DBSession()
        transaction.manager.begin()

        for del_feat in deletes:
            res = db_session.query(VectorLayer)\
                .options(joinedload_all('parent'))\
                .filter(VectorLayer.id == del_feat['layer'])\
                .first()
            if not res:
                return error_response(u'Редактируемый слой не найден')
            parent_res = res.parent
            if not parent_res:
                return error_response(u'Редактируемый слой некорректный (Слой вне объекта строительства)')
            if not (request.user.is_administrator or parent_res.has_permission(FoclStructScope.edit_data, request.user)):
                return error_response(u'У вас недостаточно прав для редактирования данных')

            if IWritableFeatureLayer.providedBy(res):
                res.feature_delete(del_feat['id'])
            else:
                return error_response(u'Ресурс не поддерживает работу с геометриями')

        transaction.manager.commit()
    except Exception as ex:
        return error_response(ex.message)

    return success_response()

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def _get_layers_styles_items(request, resource_id):
    layers_styles = []
    dbsession = DBSession()

    resource = dbsession.query(Resource).filter(Resource.id == resource_id).first()

    editable_layers_styles = get_editable_layers_styles(request)
    player_layers_styles = get_playable_layers_styles(request)

    for child_resource in resource.children:
        if child_resource.identity != VectorLayer.identity:
            continue
        if len(child_resource.keyname) < (GUID_LENGTH + 1):
            continue
        layer_keyname_without_guid = child_resource.keyname[0:-(GUID_LENGTH + 1)]
        if layer_keyname_without_guid not in editable_layers_styles:
            continue
        layers_styles.append({
            'resource': child_resource,
            'layer_keyname': layer_keyname_without_guid,
            'editor_styles': editable_layers_styles[layer_keyname_without_guid],
            'player_styles': player_layers_styles[layer_keyname_without_guid]
        })

    dbsession.close()

    return layers_styles

# 需要导入模块: from nextgisweb import DBSession [as 别名]
# 或者: from nextgisweb.DBSession import query [as 别名]
def _get_values_for_display(request, permission=FoclStructScope.edit_data):
    resource_id = int(request.GET['resource_id'])
    dbsession = DBSession()
    resource = dbsession.query(Resource).filter(Resource.id == resource_id).first()

    # checks
    if request.user.keyname == 'guest':
        raise HTTPForbidden()

    if not(request.user.is_administrator or resource.has_permission(permission, request.user)):
        raise HTTPForbidden()

    extent3857 = get_extent_by_resource_id(resource_id)
    extent4326 = _extent_3857_to_4326(extent3857)

    focl_layers = get_focl_layers_list()
    sit_plan_layers_type = get_sit_plan_layers_list()

    layers_styles = _get_layers_styles_items(request, resource_id)
    editable_layers_view_model = _create_editable_layers_view_model(layers_styles)
    playable_layers_view_model = _create_playable_layers_view_model(layers_styles)

    values = dict(
        resource_display_name=resource.display_name,
        show_header=True,
        focl_layers_type=focl_layers['focl'],
        objects_layers_type=focl_layers['objects'],
        real_layers_type=focl_layers['real'],
        sit_plan_layers_type=sit_plan_layers_type,
        extent=extent4326,
        editable_layers_info=editable_layers_view_model,
        playable_layers_info=playable_layers_view_model
    )

    return values