def guess_model(self): from skylines.model import Flight, AircraftModel # first try to find the reg number in the database if self.registration is not None: glider_reg = self.registration result = DBSession.query(Flight) \ .filter(func.upper(Flight.registration) == func.upper(glider_reg)) \ .order_by(desc(Flight.id)) \ .first() if result and result.model_id: return result.model_id # try to find another flight with the same logger and use it's aircraft type if (self.logger_id is not None and self.logger_manufacturer_id is not None): logger_id = self.logger_id logger_manufacturer_id = self.logger_manufacturer_id result = DBSession.query(Flight).outerjoin(IGCFile) \ .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \ .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \ .filter(Flight.model_id == None) \ .order_by(desc(Flight.id)) if self.logger_manufacturer_id.startswith('X'): result = result.filter(Flight.pilot == self.owner) result = result.first() if result and result.model_id: return result.model_id if self.model is not None: glider_type = self.model.lower() # otherwise, try to guess the glider model by the glider type igc header text_fragments = ['%{}%'.format(v) for v in re.sub(r'[^a-z]', ' ', glider_type).split()] digit_fragments = ['%{}%'.format(v) for v in re.sub(r'[^0-9]', ' ', glider_type).split()] if not text_fragments and not digit_fragments: return None glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type) result = DBSession \ .query(AircraftModel) \ .filter(and_( func.regexp_replace(func.lower(AircraftModel.name), '[^a-z]', ' ').like(func.any(text_fragments)), func.regexp_replace(func.lower(AircraftModel.name), '[^0-9]', ' ').like(func.all(digit_fragments)))) \ .order_by(func.levenshtein(func.regexp_replace(func.lower(AircraftModel.name), '[^a-z0-9]', ''), glider_type_clean)) if result.first(): return result.first().id # nothing found return None
def match_query(session, query, latest=None, **kwargs): """Match ESGF results against :class:`clef.model.Path` Matches the results of :func:`find_checksum_id` with the :class:`Path` table. If `latest` is True the checksums will be matched, otherwise only the file name is used in order to spot outdated versions that have been removed from ESGF. Args: latest (bool): Match the checksums (True) or filenames (False) **kwargs: See :func:`esgf_query` Returns: Joined result of :class:`clef.model.Path` and :func:`find_checksum_id` """ values = find_checksum_id(query, latest=latest, **kwargs) if latest is True: # Exact match on checksum return (values.outerjoin( Checksum, or_(Checksum.md5 == values.c.checksum, Checksum.sha256 == values.c.checksum)).outerjoin(Path)) else: # Match on file name #return values.outerjoin(Path, Path.path.like('%/'+values.c.title)) return values.outerjoin( Path, func.regexp_replace(Path.path, '^.*/', '') == values.c.title)
def find_local_path(session, subq): """Find the filesystem paths of ESGF matches Converts the results of :func:`match_query` to local filesystem paths, either to the file itself or to the containing dataset. Args: subq: result of func:`esgf_query` Returns: Iterable of strings with the paths to either paths or datasets """ return (session.query( func.regexp_replace(subq.c.esgf_paths_path, '[^//]*$', '') ).select_from(subq).filter(subq.c.esgf_paths_file_id != None).filter( sa.not_( sa.and_( subq.c.esgf_paths_path.like( '/g/data/rr3/publications/CMIP5/%'), sa.not_( subq.c.esgf_paths_path.like( '/g/data/rr3/publications/CMIP5/%/files/%')))) ).filter( sa.not_( sa.and_( subq.c.esgf_paths_path.like( '/g/data/fs38/publications/CMIP6/%'), sa.not_( subq.c.esgf_paths_path.like( '/g/data/fs38/publications/CMIP6/%/files/%'))))). distinct())
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) return jsonify( profiles_schema.dump( matching_profiles(query).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' '), 1 # How many words in the name )))))
def invalid_query(self, session, **kw): return session.query( func.regexp_replace(Config.key, u'\D', '', u'g').label('id'), func.nullif(Config.value, u'__gone__').label('target'))\ .filter(Config.key.like(u'__Source_%%__'))\ .filter( session.query(orm.aliased(Config)) .filter_by(key=func.format(u'__Source_%s__', Config.value)).exists())\ .order_by('id', 'target')
def __getitem__(self, project): try: return self.request.db.query(Project).filter( Project.normalized_name == func.lower( func.regexp_replace(project, "_", "-", "ig") ) ).one() except NoResultFound: raise KeyError from None
def get_items(self, **kwargs): event_id = kwargs.get('event_id') lookup_item = kwargs.get('q') deployment = getattr(g, 'deployment', None) if event_id is None: event = getattr(g, 'event', None) else: event = Event.query.filter_by(id=event_id).one() if deployment: deployment_id = deployment.id else: deployment_id = None if event and event.participant_set_id: participant_set_id = event.participant_set_id else: participant_set_id = None full_name_lat_query = ParticipantFullNameTranslations.lateral( 'full_name') first_name_lat_query = ParticipantFirstNameTranslations.lateral( 'first_name') other_names_lat_query = ParticipantOtherNamesTranslations.lateral( 'other_names') last_name_lat_query = ParticipantLastNameTranslations.lateral( 'last_name') queryset = Participant.query.select_from(Participant).join( Participant.participant_set).outerjoin( full_name_lat_query, true()).outerjoin(first_name_lat_query, true()).outerjoin( other_names_lat_query, true()).outerjoin(last_name_lat_query, true()).filter( Participant.participant_set_id == participant_set_id, ParticipantSet.deployment_id == deployment_id, ParticipantSet.id == participant_set_id) if lookup_item: queryset = queryset.filter( or_( text('full_name.value ILIKE :name'), func.btrim( func.regexp_replace( func.concat_ws( ' ', text('first_name.value'), text('other_names.value'), text('last_name.value'), ), r'\s+', ' ', 'g')).ilike(f'%{lookup_item}%'), Participant.participant_id.ilike( bindparam('pid')))).params(name=f'%{lookup_item}%', pid=f'{lookup_item}%') return queryset
def apply_wildcard(cls, session, domain_name): qry = session.query(DomainProfile) # Convert stored * to LIKE operator % replace_percent = func.regexp_replace(DomainProfile.name, '^\*', '%') # Since the pattern is stored in the database, # we swap the usual order of comparison qry = qry.filter(literal(domain_name).ilike(replace_percent)) # Order by length, so that we return the most-specific name qry = qry.order_by(func.length(DomainProfile.name)) return qry.first()
class Node(Base): __tablename__ = "node" id = Column(Integer, primary_key=True, autoincrement=False) path = Column(String(500), nullable=False, index=True) # To find the descendants of this node, we look for nodes whose path # starts with this node's path. descendants = relationship( "Node", viewonly=True, order_by=path, primaryjoin=remote(foreign(path)).like(path.concat(".%")), ) # Finding the ancestors is a little bit trickier. We need to create a fake # secondary table since this behaves like a many-to-many join. secondary = select( id.label("id"), func.unnest( cast( func.string_to_array(func.regexp_replace(path, r"\.?\d+$", ""), "."), ARRAY(Integer), )).label("ancestor_id"), ).alias() ancestors = relationship( "Node", viewonly=True, secondary=secondary, primaryjoin=id == secondary.c.id, secondaryjoin=secondary.c.ancestor_id == id, order_by=path, ) @property def depth(self): return len(self.path.split(".")) - 1 def __repr__(self): return "Node(id={})".format(self.id) def __str__(self): root_depth = self.depth s = [str(self.id)] s.extend(((n.depth - root_depth) * " " + str(n.id)) for n in self.descendants) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent.path + "." + str(self.id) for n in self.descendants: n.path = new_path + n.path[len(self.path):] self.path = new_path
class Node(Base): __tablename__ = 'node' id = Column(Integer, primary_key=True, autoincrement=False) path = Column(String(500), nullable=False, index=True) # 想要找到这个node的后代,我们需要搜索以当前node的path为path前缀的node descendants = relationship('Node', viewonly=True, order_by=path, primaryjoin=remote(foreign(path)).like( path.concat(".%"))) # 想要找到这个node的祖先有点复杂。 # 我们需要创建一个伪secondary表,因为这个行为有些像many-to-many secondary = select([ id.label('id'), func.unnest( cast( func.string_to_array(func.regexp_replace(path, r"\.?\d+$", ""), "."), ARRAY(Integer))).label('ancestor_id') ]).alias() ancestor = relationship("Node", viewonly=True, secondary=secondary, primaryjoin=id == secondary.c.id, secondaryjoin=secondary.c.ancestor_id == id, order_by=path) @property def depth(self): return len(self.path.split(".")) - 1 def __repr__(self): return "Node(id={})".format(self.id) def __str__(self): root_depth = self.depth s = [str(self.id)] s.extend(((n.depth - root_depth) * " " + str(n.id)) for n in self.descendants) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent + "." + str(self.id) for n in self.descendants: n.path = new_path + n.path[len(self.path):] self.path = new_path
def new(site_id=None): form = survey_form() form.site.choices = [(s.id, s.name) for s in site.query.order_by( cast(func.nullif(func.regexp_replace(site.name, "\\D", "", "g"), ""), DECIMAL)).all()] if form.validate_on_submit(): new_survey = survey(site_id=form.site.data, date=form.date.data, crew=form.crew.data, time_in=form.time_in.data, time_out=form.time_out.data, surveyed=form.precentage_surveyed.data, method=form.method.data, ac1=form.ac1.data, ac2=form.ac2.data, ac3=form.ac3.data, egg1=form.egg1.data, egg2=form.egg2.data, egg3=form.egg3.data, chick02=form.chick02.data, chick39=form.chick39.data, chick1017=form.chick1017.data, fledgling=form.fledgling.data, ef1=form.ef1.data, ef2=form.ef2.data, ef3=form.ef3.data, ef4=form.ef4.data, scrape=form.scrape.data, pveg=form.primary_vegitation.data, perc_pveg=form.precentage_primary_vegitation.data, size_type=form.size_type.data, cwdn1=form.cwdn1.data, cwdn2=form.cwdn2.data, cwdn3=form.cwdn3.data, cwdlog=form.cwdlog.data, w_temp=form.water_temp.data, a_temp=form.ambient_temp.data, perc_dio=form.precentage_disolved_oxygen.data, sal=form.salinity.data, sp_condu=form.specific_conductance.data, condu=form.conductivity.data, comment=form.comment.data) new_survey.save() return redirect(url_for('surveys.view', id=new_survey.id)) if site_id: form.site.data = int(site_id) return render_template('surveys/new.jinja2', form=form)
def main(): engine = create_engine("sqlite:///{}.sqlite".format(DB_NAME)) session_factory = sessionmaker(bind=engine) session = session_factory() # select records based on a REGEXP WHERE clause query = session.query(Transaction).filter( Transaction.description.op("REGEXP")("^Withdrawal")) for record in query.all(): print("{r.transactionid}: {r.description}".format(r=record)) # alter the return of the column with a regexp_replace query = session.query( Transaction.transactionid, label('description', func.regexp_replace(Transaction.description, '^\D*: \d* ', ''))) for record in query.all(): print("{r.transactionid}: {r.description}".format(r=record))
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') degrees = request.args.get('degrees', '') affiliations = request.args.get('affiliations', '') page = int(request.args.get('page', 1)) start, end = pagination(page) verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) queryset = ( matching_profiles(query, degrees, affiliations).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' ', ), 1, # How many words in the name ), ), ).group_by(Profile.id)) return jsonify({ 'profileCount': queryset.count(), 'profiles': profiles_schema.dump(queryset[start:end]), })
def find_local_path(session, subq, oformat='file'): """Find the filesystem paths of ESGF matches Converts the results of :func:`match_query` to local filesystem paths, either to the file itself or to the containing dataset. Args: format ('file' or 'dataset'): Return the path to the file or the dataset directory subq: result of func:`esgf_query` Returns: Iterable of strings with the paths to either paths or datasets """ if oformat == 'file': return (session.query('esgf_paths.path').select_from(subq).filter( subq.c.esgf_paths_file_id != None).filter( sa.not_( sa.and_( subq.c.esgf_paths_path.like( '/g/data1/rr3/publications/CMIP5/%'), sa.not_( subq.c.esgf_paths_path.like( '/g/data1/rr3/publications/CMIP5/%/files/%')))) )) elif oformat == 'dataset': return (session.query( func.regexp_replace(subq.c.esgf_paths_path, '[^//]*$', '') ).select_from(subq).filter(subq.c.esgf_paths_file_id != None).filter( sa.not_( sa.and_( subq.c.esgf_paths_path.like( '/g/data1/rr3/publications/CMIP5/%'), sa.not_( subq.c.esgf_paths_path.like( '/g/data1/rr3/publications/CMIP5/%/files/%'))))). distinct()) else: raise NotImplementedError
def _list_dirpaths( session, print_queries=False, count=False, ensemble=None, multi_variable=None, multi_year_mean=None, mym_concatenated=None, depth=999, ): df_query = data_file_query( session, print_queries=print_queries, ensemble=ensemble, multi_variable=multi_variable, multi_year_mean=multi_year_mean, mym_concatenated=mym_concatenated, ) info_query = ( session.query( func.regexp_replace( DataFile.filename, r'^((/.[^/]+){{1,{}}}/).+$'.format(depth), r'\1' ).label('dir_path'), func.count().label('number') ) .filter(DataFile.id.in_(df_query)) .group_by('dir_path') .order_by('dir_path') ) if print_queries: print_query('Info query', info_query) template = '{row.dir_path} ({row.number})' list_information(info_query, template, count=count)
def queryset_(self, query, value): if value: full_name_query = func.jsonb_each_text( Participant.full_name_translations).lateral( 'full_name_translations') first_name_query = func.jsonb_each_text( Participant.first_name_translations).lateral( 'first_name_translations') other_names_query = func.jsonb_each_text( Participant.other_names_translations).lateral( 'other_names_translations') last_name_query = func.jsonb_each_text( Participant.last_name_translations).lateral( 'last_name_translations') subquery = Participant.query.outerjoin( full_name_query, true()).outerjoin(first_name_query, true()).outerjoin( other_names_query, true()).outerjoin(last_name_query, true()).filter( or_( text('full_name_translations.value ILIKE :name'), func.btrim( func.regexp_replace( func.concat_ws( ' ', text('first_name_translations.value'), text('other_names_translations.value'), text('last_name_translations.value'), ), r'\s+', ' ', 'g')).ilike(f'%{value}%'))).params( name=f'%{value}%').with_entities( Participant.id).subquery() return query.join(subquery, subquery.c.id == Participant.id) return query
def get(self): try: if not full_access_to_name_request(request): return {"message": "You do not have access to this NameRequest."}, 403 filters = [] nr_num_query_str = request.headers['Bcreg-Nr'] or request.headers['Bcreg-Nrl'] email_address_query_str = request.headers['Bcreg-User-Email'] phone_number_query_str = request.headers['Bcreg-User-Phone'] if not nr_num_query_str: raise InvalidInputError(message='An nrNum must be provided') else: if not email_address_query_str and not phone_number_query_str: raise InvalidInputError(message='Either an emailAddress or phoneNumber must be provided') # Continue nr_num = parse_nr_num(nr_num_query_str) email_address = email_address_query_str phone_number = phone_number_query_str # Filter on addresses # address_line = get_query_param_str('addrLine1') if nr_num: filters.append(func.lower(Request.nrNum) == nr_num.lower()) if phone_number: strip_phone_number_chars_regex = r"[^0-9]" filters.append( Request.applicants.any( func.regexp_replace(Applicant.phoneNumber, strip_phone_number_chars_regex, '', 'g').contains(re.sub(strip_phone_number_chars_regex, '', phone_number)) ) ) if email_address: filters.append( Request.applicants.any( func.lower(Applicant.emailAddress).startswith(email_address.lower()) ) ) ''' Filter on addresses if address_line: filters.append( Request.applicants.any( func.lower(Applicant.addrLine1).startswith(address_line.lower()) ) ) ''' criteria = RequestQueryCriteria( nr_num=nr_num, filters=filters ) results = Request.find_by_criteria(criteria) if not results: results = [] except InvalidInputError as err: return handle_exception(err, err.message, 400) except Exception as err: return handle_exception(err, 'Error retrieving the NR from the db.', 500) if nr_num and len(results) == 1: nr_model = results[0] if nr_model.requestTypeCd and (not nr_model.entity_type_cd or not nr_model.request_action_cd): # If requestTypeCd is set, but a request_entity (entity_type_cd) and a request_action (request_action_cd) # are not, use get_mapped_entity_and_action_code to map the values from the requestTypeCd entity_type, request_action = get_mapped_entity_and_action_code(nr_model.requestTypeCd) nr_model.entity_type_cd = entity_type nr_model.request_action_cd = request_action response_data = nr_model.json() # If draft, get the wait time and oldest queued request if nr_model.stateCd == 'DRAFT': service = WaitTimeStatsService() wait_time_response = service.get_waiting_time_dict() response_data.update(wait_time_response) # Add the list of valid Name Request actions for the given state to the response response_data['actions'] = get_nr_state_actions(results[0].stateCd, results[0]) return jsonify(response_data), 200 elif len(results) > 0: # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get # This method, NameRequests.get is for Existing NR Search return jsonify(list(map(lambda result: result.json(), results))), 200 # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get # This method, NameRequests.get is for Existing NR Search return jsonify(results), 200
def email_migrate(argv=sys.argv): if len(argv) < 2: usage(argv) config_uri = argv[1] options = parse_vars(argv[2:]) setup_logging(config_uri) settings = get_appsettings(config_uri, options=options) engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) from petl import fromcsv conn_data = fromcsv('import_connections.csv').dicts() # conn_data = [] users_changed = [] users_added = 0 state_to_abbr = dict([(x[1], x[0]) for x in USPS_CHOICES]) print 'processing connections' with transaction.manager: for v in conn_data: user = DBSession.query(User).filter( or_(func.lower(User.email) == v['_email'].lower(), func.lower(User.linkedin_email) == v['_email'].lower(), and_(func.lower(User.firstname) == v['firstname'].lower(), func.lower(User.lastname) == v['lastname'].lower())) ).first() if user: for attrn, val in v.iteritems(): if not val or not attrn or attrn in ('_email',): continue val = val.strip() if attrn == 'company' and not user.company_id: repl_val = val.lower().rstrip('.').replace('llc', '').replace('corporation', ''). \ replace('corp', '').replace('inc', '').rstrip(',').strip() print 'searching:', repl_val, val company = DBSession.query(BaseCompany).filter( or_(BaseCompany.name == val, func.lower(BaseCompany.name) == val.lower(), func.regexp_replace(func.lower(func.btrim(BaseCompany.name)), ',\s(inc|corporation|corp|llc)\.?$', '') == repl_val, func.regexp_replace(func.lower(func.btrim(BaseCompany.name)), '\s(inc|corporation|corp|llc)\.?$', '') == repl_val )).first() if not company and val: company = BaseCompany(name=val) DBSession.add(company) val = company if attrn == 'area of expertise' and val: attrn = 'tag' tag = DBSession.query(Tag).filter(Tag.text == val).first() if tag and len(filter(lambda t: t.text == val, user.tags)) == 0: user.tags.append(tag) if attrn == 'state': val = state_to_abbr.get(val) if val and hasattr(user, attrn) and not getattr(user, attrn): print 'setting connection.%s.%s = %s' % (user.id, attrn, val) setattr(user, attrn, val) users_changed.append(user.id) DBSession.flush() users_changed = list(set(users_changed)) print '%s new users added, %s users changed: %s' % (users_added, len(users_changed), users_changed) del conn_data comp_data = fromcsv('companies_data.csv').dicts() companys_changed = [] companys_added = 0 print 'starting to process companies' with transaction.manager: for v in comp_data: if not v['name'].strip(): continue company = DBSession.query(BaseCompany).filter( or_(BaseCompany.name == v['name'].strip()) ).first() if company: for attrn, val in v.iteritems(): if not val or not attrn or attrn in ('_email',): continue val = val.strip() attrn = attrn.replace('company_', '') if attrn == 'founded': try: val = int(val) except: continue if val and hasattr(company, attrn) and not getattr(company, attrn): print 'setting company.%s.%s = %s' % (company.id, attrn, val) setattr(company, attrn, val) companys_changed.append(company.id) DBSession.flush() companys_changed = list(set(companys_changed)) print '%s new companys added, %s companys changed: %s' % ( companys_added, len(companys_changed), companys_changed)
def edit(id): form = survey_form() form.site.choices = [(s.id, s.name) for s in site.query.order_by( cast(func.nullif(func.regexp_replace(site.name, "\\D", "", "g"), ""), DECIMAL)).all()] data = survey.query.filter(survey.id == id).first() if form.validate_on_submit(): data.site_id = form.site.data data.date = form.date.data data.crew = form.crew.data data.time_in = form.time_in.data data.time_out = form.time_out.data data.surveyed = form.precentage_surveyed.data data.method = form.method.data data.ac1 = form.ac1.data data.ac2 = form.ac2.data data.ac3 = form.ac3.data data.egg1 = form.egg1.data data.egg2 = form.egg2.data data.egg3 = form.egg3.data data.scrape = form.scrape.data data.chick02 = form.chick02.data data.chick39 = form.chick39.data data.chick1017 = form.chick1017.data data.fledgling = form.fledgling.data data.ef1 = form.ef1.data data.ef2 = form.ef2.data data.ef3 = form.ef3.data data.ef4 = form.ef4.data data.pveg = form.primary_vegitation.data data.perc_pveg = form.precentage_primary_vegitation.data data.size_type = form.size_type.data data.cwdn1 = form.cwdn1.data data.cwdn2 = form.cwdn2.data data.cwdn3 = form.cwdn3.data data.cwdlog = form.cwdlog.data data.w_temp = form.water_temp.data data.a_temp = form.ambient_temp.data data.perc_dio = form.precentage_disolved_oxygen.data data.sal = form.salinity.data data.sp_condu = form.specific_conductance.data data.condu = form.conductivity.data data.comment = form.comment.data data.save() return redirect(url_for('surveys.view', id=id)) form.site.data = data.site_id form.date.data = data.date form.crew.data = data.crew form.time_in.data = data.time_in form.time_out.data = data.time_out form.precentage_surveyed.data = data.surveyed form.method.data = data.method form.ac1.data = data.ac1 form.ac2.data = data.ac2 form.ac3.data = data.ac3 form.egg1.data = data.egg1 form.egg2.data = data.egg2 form.egg3.data = data.egg3 form.chick02.data = data.chick02 form.chick39.data = data.chick39 form.chick1017.data = data.chick1017 form.fledgling.data = data.fledgling form.ef1.data = data.ef1 form.ef2.data = data.ef2 form.ef3.data = data.ef3 form.ef4.data = data.ef4 form.scrape.data = data.scrape form.primary_vegitation.data = data.pveg form.precentage_primary_vegitation.data = data.perc_pveg form.size_type.data = data.size_type form.cwdn1.data = data.cwdn1 form.cwdn2.data = data.cwdn2 form.cwdn3.data = data.cwdn3 form.cwdlog.data = data.cwdlog form.water_temp.data = data.w_temp form.ambient_temp.data = data.a_temp form.precentage_disolved_oxygen.data = data.perc_dio form.salinity.data = data.sal form.specific_conductance.data = data.sp_condu form.conductivity.data = data.condu form.comment.data = data.comment return render_template('surveys/edit.jinja2', form=form)
def get(self): try: filters = [] # Validate the request if len(request.args) == 0: raise InvalidInputError( message='No query parameters were specified in the request' ) nr_num_query_str = get_query_param_str('nrNum') email_address_query_str = get_query_param_str('emailAddress') phone_number_query_str = get_query_param_str('phoneNumber') if not nr_num_query_str: raise InvalidInputError(message='An nrNum must be provided') else: if not email_address_query_str and not phone_number_query_str: raise InvalidInputError( message= 'Either an emailAddress or phoneNumber must be provided' ) # Continue nr_num = parse_nr_num(nr_num_query_str) email_address = email_address_query_str phone_number = get_query_param_str('phoneNumber') # Filter on addresses # address_line = get_query_param_str('addrLine1') if nr_num: filters.append(func.lower(Request.nrNum) == nr_num.lower()) if phone_number: strip_phone_number_chars_regex = r"[^0-9]" filters.append( Request.applicants.any( func.regexp_replace( Applicant.phoneNumber, strip_phone_number_chars_regex, '', 'g').contains( re.sub(strip_phone_number_chars_regex, '', phone_number)))) if email_address: filters.append( Request.applicants.any( func.lower(Applicant.emailAddress).startswith( email_address.lower()))) ''' Filter on addresses if address_line: filters.append( Request.applicants.any( func.lower(Applicant.addrLine1).startswith(address_line.lower()) ) ) ''' criteria = RequestQueryCriteria(nr_num=nr_num, filters=filters) results = Request.find_by_criteria(criteria) if not results: results = [] except InvalidInputError as err: return handle_exception(err, err.message, 400) except Exception as err: return handle_exception(err, 'Error retrieving the NR from the db.', 500) if nr_num and len(results) == 1: nr_model = results[0] if nr_model.requestTypeCd and (not nr_model.entity_type_cd or not nr_model.request_action_cd): # If requestTypeCd is set, but a request_entity (entity_type_cd) and a request_action (request_action_cd) # are not, use get_mapped_entity_and_action_code to map the values from the requestTypeCd entity_type, request_action = get_mapped_entity_and_action_code( nr_model.requestTypeCd) nr_model.entity_type_cd = entity_type nr_model.request_action_cd = request_action response_data = nr_model.json() # Add the list of valid Name Request actions for the given state to the response response_data['actions'] = get_nr_state_actions( results[0].stateCd, results[0]) return jsonify(response_data), 200 elif len(results) > 0: # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get # This method, NameRequests.get is for Existing NR Search return jsonify(list(map(lambda result: result.json(), results))), 200 # We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list # If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get # This method, NameRequests.get is for Existing NR Search return jsonify(results), 200