def createDeliveryForRequest(db, id): default_time = dt.datetime(2001, 1, 1) # get list of sidekicks that are currently online # online_sidekicks = db.session.query(Shifts.user_id)\ # .filter(Shifts.clock_in<func.now(), Shifts.clock_out==default_time)\ # .all() # # convert db query tuple format-- [(1,), (2,)] into an array of sidekick ids (1, 2) # print online_sidekicks # online_sidekicks = zip(*online_sidekicks)[0] deliveries = db.session.query(Deliveries.sidekick_id, func.count('*')\ .label('delivery_count'))\ .filter(Deliveries.completed==default_time)\ .group_by(Deliveries.sidekick_id).subquery() sidekick_deliveries_array = db.session.query(Users, deliveries.c.delivery_count)\ .outerjoin(deliveries, Users._id==deliveries.c.sidekick_id)\ .filter(Users.type=='sidekick')\ .filter(Users.online==True)\ .order_by(nullsfirst(asc(deliveries.c.delivery_count))) # if there is an online sidekick, assign delivery to new request if (sidekick_deliveries_array.count() > 0): [sk, count] = sidekick_deliveries_array[0] print "Assigning delivery for request", id, "to sidekick", sk._id delivery = Deliveries(id, sk._id) db.session.add(delivery) db.session.commit() else: print "No sidekicks currently online. No Delivery created for Request", id
def label_places(timeout): """Add labels to places that have no labels, or position has shifted significantly since labeling. Reverse geocoding api url and rate limit are read from configuration, for example: REVERSE_GEOCODING_URI_TEMPLATE = 'https://search.mapzen.com/v1/reverse?api_key=API_KEY&sources=osm&size=20&point.lat={lat}&point.lon={lon}' REVERSE_GEOCODING_QUERIES_PER_SECOND = 6""" print("label_places up to %ds" % timeout) url_template = app.config.get('REVERSE_GEOCODING_URI_TEMPLATE') qps = app.config.get('REVERSE_GEOCODING_QUERIES_PER_SECOND') if None in (url_template, qps): log.info("REVERSE_GEOCODING_URI_TEMPLATE or " + "REVERSE_GEOCODING_QUERIES_PER_SECOND unconfigured, places will " + "not be labeled") return places = db.metadata.tables["places"] labdist = func.ST_Distance(places.c.coordinate, places.c.label_coordinate) t0 = time.time() for p in db.engine.execute(select( [ places.c.id, func.ST_AsGeoJSON(places.c.coordinate).label("geojson")], or_(labdist == None, labdist > DEST_RADIUS_MAX), # = clust dist / 2 order_by=nullsfirst(desc(labdist)))): lon, lat = point_coordinates(p) url = url_template.format(lat=lat, lon=lon) # response = json.loads(urllib.request.urlopen(url, timeout=timeout).read()) response = requests.get(url, timeout=timeout).json() names, nameslower = [], set() for prop in ["street", "name"]: for feat in response["features"]: name = feat["properties"].get(prop) name = name and re.split(",", name)[0] if name and name.lower() not in nameslower: names.append(name) nameslower.add(name.lower()) label = " / ".join(names[:2]) coordstr = "{:.4f}/{:.4f}".format(lat, lon) label = label or coordstr # fallback # Show progress due to rate limiting. Force encoding in case of pipe print(coordstr, label.encode("utf-8")) db.engine.execute(places.update( places.c.id == p.id, {"label": label, "label_coordinate": "POINT(%f %f)" % (lon, lat)})) # Enforce configured API queries per second rate limit time.sleep(1./qps) if time.time() - t0 >= timeout: break
def obter_documentos_externos(): query = session().query( TB_DOCUMENTO.c.docu_nr_externo, TB_DOCUMENTO.c.docu_dk, TB_PROCESSO.c.prtj_dt_ultima_vista).outerjoin( TB_PROCESSO, TB_DOCUMENTO.c.docu_nr_externo == TB_PROCESSO.c.prtj_cd_numero_processo ).filter(TB_DOCUMENTO.c.docu_mate_dk == 4).filter( func.length(TB_DOCUMENTO.c.docu_nr_externo) == 20).order_by( nullsfirst(TB_PROCESSO.c.prtj_dt_ultima_vista), ) return [(doc[0], doc[1]) for doc in query]
def get_needing_update(self, quantity=10): """ Gets a number of capsuleers ordered by their last_checked value with nulls coming first. :param quantity: the number of capsuleers to be returned """ log.info('Finding {} capsuleers to update.'.format(quantity)) order_by = nullsfirst(self.__model__.last_checked.asc()) return self._find().order_by(order_by).limit(quantity)
def availability_for_advisor(cls, authorized_user_id, dept_code): results = cls.query.filter_by(authorized_user_id=authorized_user_id, dept_code=dept_code).order_by( cls.weekday, nullsfirst(cls.date_override), cls.start_time, ) availability = {} for weekday, group_by_weekday in groupby(results, lambda x: x.weekday): availability[weekday] = {} for date_key, group_by_date_override in groupby(group_by_weekday, lambda x: x.date_override): if date_key is None: date_key = 'recurring' else: date_key = str(date_key) availability[weekday][date_key] = [cls.to_api_json(a.id, a.start_time, a.end_time) for a in group_by_date_override] return availability
def build_sqlalchemy_ordering(order_params, visible_fields): ''' returns a scalar or list of ClauseElement objects which will comprise the ORDER BY clause of the resulting select. @param order_params passed as list in the request.GET hash ''' DEBUG_ORDERING = False or logger.isEnabledFor(logging.DEBUG) if DEBUG_ORDERING: logger.info('build sqlalchemy ordering: %s, visible fields: %s', order_params,visible_fields.keys()) if order_params and isinstance(order_params, basestring): # standard, convert single valued list params order_params = [order_params] order_clauses = [] for order_by in order_params: field_name = order_by order_clause = None if order_by.startswith('-'): field_name = order_by[1:] order_clause = nullslast(desc(column(field_name))) if ( field_name in visible_fields and visible_fields[field_name]['data_type'] == 'string'): # For string field ordering, double sort as numeric and text order_clause = text( "(substring({field_name}, '^[0-9]+'))::int desc " # cast to integer ",substring({field_name}, '[^0-9_].*$') " # works as text .format(field_name=field_name)) else: order_clause = nullsfirst(asc(column(field_name))) if ( field_name in visible_fields and visible_fields[field_name]['data_type'] == 'string'): order_clause = text( "(substring({field_name}, '^[0-9]+'))::int " ",substring({field_name}, '[^0-9_].*$') " .format(field_name=field_name)) if field_name in visible_fields: order_clauses.append(order_clause) else: logger.warn( 'order_by field %r not in visible fields, skipping: ', order_by) if DEBUG_ORDERING: logger.info('order_clauses %s',order_clauses) return order_clauses
def sort(self, keys,direction = None,explicit_nullsfirst = False): #we sort by a single argument if direction: keys = ((keys,direction),) order_bys = [] for key,direction in keys: if direction > 0: #when sorting in ascending direction, NULL values should come first if explicit_nullsfirst: direction = lambda *args,**kwargs: nullsfirst(asc(*args,**kwargs)) else: direction = asc else: #when sorting in descending direction, NULL values should come last if explicit_nullsfirst: direction = lambda *args,**kwargs: nullslast(desc(*args,**kwargs)) else: direction = desc order_bys.append((key,direction)) self.order_bys = order_bys self.objects = None return self
def label_places(timeout): """Add labels to places that have no labels, or position has shifted significantly since labeling. Reverse geocoding api url and rate limit are read from configuration, for example: REVERSE_GEOCODING_URI_TEMPLATE = 'https://search.mapzen.com/v1/reverse?api_key=API_KEY&sources=osm&size=20&point.lat={lat}&point.lon={lon}' REVERSE_GEOCODING_QUERIES_PER_SECOND = 6""" print "label_places up to %ds" % timeout url_template = app.config.get('REVERSE_GEOCODING_URI_TEMPLATE') qps = app.config.get('REVERSE_GEOCODING_QUERIES_PER_SECOND') if None in (url_template, qps): log.info( "REVERSE_GEOCODING_URI_TEMPLATE or " + "REVERSE_GEOCODING_QUERIES_PER_SECOND unconfigured, places will " + "not be labeled") return places = db.metadata.tables["places"] labdist = func.ST_Distance(places.c.coordinate, places.c.label_coordinate) t0 = time.time() for p in db.engine.execute( select( [ places.c.id, func.ST_AsGeoJSON(places.c.coordinate).label("geojson") ], or_(labdist == None, labdist > DEST_RADIUS_MAX), # = clust dist / 2 order_by=nullsfirst(desc(labdist)))): lon, lat = point_coordinates(p) url = url_template.format(lat=lat, lon=lon) response = json.loads(urllib2.urlopen(url, timeout=timeout).read()) names, nameslower = [], set() for prop in ["street", "name"]: for feat in response["features"]: name = feat["properties"].get(prop) name = name and re.split(",", name)[0] if name and name.lower() not in nameslower: names.append(name) nameslower.add(name.lower()) label = " / ".join(names[:2]) coordstr = "{:.4f}/{:.4f}".format(lat, lon) label = label or coordstr # fallback # Show progress due to rate limiting. Force encoding in case of pipe print coordstr, label.encode("utf-8") db.engine.execute( places.update(places.c.id == p.id, { "label": label, "label_coordinate": "POINT(%f %f)" % (lon, lat) })) # Enforce configured API queries per second rate limit time.sleep(1. / qps) if time.time() - t0 >= timeout: break
def direction(*args, **kwargs): return nullsfirst(asc(*args, **kwargs))