def load_in_extent_from_db(cls, session: Session, min_easting: float, max_easting: float, min_northing: float, max_northing: float) -> List["AbstractGeoObject"]: """ Returns all objects inside the given extent in the database connected to the SQLAlchemy Session session. :param session: represents the database connection as SQLAlchemy Session :param min_easting: minimal easting of extent :param max_easting: maximal easting of extent :param min_northing: minimal northing of extent :param max_northing: maximal northing of extent :return: a list of objects representing the result of the database query :raises ValueError: if one of the extension values is not compatible to type float :raises TypeError: if session is not of type SQLAlchemy Session """ min_easting = float(min_easting) max_easting = float(max_easting) min_northing = float(min_northing) max_northing = float(max_northing) if not isinstance(session, Session): raise TypeError("'session' is not of type SQLAlchemy Session!") result = session.query(cls).filter(sq.between(cls.east, min_easting, max_easting)). \ filter(sq.between(cls.north, min_northing, max_northing)) result = result.order_by(cls.id).all() for obj in result: obj.session = session return result
def load_in_extent_without_lines_from_db(cls, session: Session, min_easting: float, max_easting: float, min_northing: float, max_northing: float) -> List["GeoPoint"]: """ Returns all points inside the given extent in the database connected to the SQLAlchemy Session session, which are not part of a line. This function is similar the :meth:`AbstractGeoObject.load_in_extent_from_db()<geological_toolbox.geo_object.AbstractGeoObject.load_in_extent_from_db>` function. :param min_easting: minimal easting of extent :param max_easting: maximal easting of extent :param min_northing: minimal northing of extent :param max_northing: maximal northing of extent :param session: represents the database connection as SQLAlchemy Session :return: a list of points representing the result of the database query :raises ValueError: if one of the extension values is not compatible to type float :raises TypeError: if session is not of type SQLAlchemy Session """ min_easting = float(min_easting) max_easting = float(max_easting) min_northing = float(min_northing) max_northing = float(max_northing) if not isinstance(session, Session): raise TypeError("'session' is not of type SQLAlchemy Session!") result = session.query(GeoPoint).filter(sq.between(GeoPoint.east, min_easting, max_easting)). \ filter(sq.between(GeoPoint.north, min_northing, max_northing)). \ filter(sq.or_(GeoPoint.line_id == None, GeoPoint.line_id == -1)). \ order_by(cls.id).all() for point in result: point.session = session return result
def get_data(): json_data = request.json print("/data", json_data, "\n") q_stime = datetime.datetime.fromtimestamp( json_data['s_time'] / 1e3) # conversions from database date string to timestamp number q_etime = datetime.datetime.fromtimestamp(json_data['e_time'] / 1e3) try: statement = select([Measurements]).where( between(Measurements.s_time, q_stime, q_etime) | between(Measurements.e_time, q_stime, q_etime)) # select statement on the database results = db_session.execute(statement).fetchall() print(results) result = [] for row in results: # formats the results into a JSON list tmp = Measurements(id=row['id'], s_time=row['s_time'], e_time=row['e_time'], data=row['data']) result.append(tmp.jsonize()) except Exception as e: print("Error occured trying to upload data:", e) result = {'result': 'failure'} finally: db_session.close() return jsonify(result)
def create_doc_annotations(): url = server_address + 'basicviz/get_all_doc_data/{}'.format( args.experiment_id) response = requests.get(url) doc_features = response.json() # json.dump(doc_features, open('tmp.json', 'w')) # doc_features = list(json.load(open('tmp.json', 'r'))) for doc, feature_list, motifs in doc_features: if doc not in scan: continue results = db_session.query(Fragment.mz, Molecule.mol).\ filter(Fragment.scanid == scan[doc]).\ join(Molecule, Fragment.molid==Molecule.molid).all() sys.stderr.write('doc: ' + doc + '; scan: ' + str(scan[doc]) + '\n') if len(results) != 1: sys.stderr.write('---> No molecule !\n') continue parent_mz, parent_mol = results[0] ddoc = { 'name': doc, 'scan': scan[doc], 'mol': parent_mol, 'features': [] } # read fragments and losses frags = db_session.query(Fragment).\ filter(Fragment.scanid == scan[doc] + 1) for f, i in feature_list: feature_type, massbin = f.split('_') feature = { 'name': f, 'intensity': i, 'type': feature_type, 'matches': [] } massbin = float(massbin) if feature_type == 'fragment': matching_frags = frags.filter( between(Fragment.mz, massbin - args.binsize / 2, massbin + args.binsize / 2)).all() else: matching_frags = frags.filter( between(parent_mz - Fragment.mz, massbin - args.binsize / 2, massbin + args.binsize / 2)).all() for frag in matching_frags: if frag.atoms in [m['fragatoms'] for m in feature['matches']]: continue # avoid redundant matches smiles = frag.smiles if feature_type == 'fragment' else loss2smiles( parent_mol, frag.atoms) match = { 'smiles': smiles, 'mz': frag.mz, 'fragatoms': frag.atoms } feature['matches'].append(match) ddoc['features'].append(feature) doc_annotations.append(ddoc)
def select_data(begin, end, person=None): if person: expression = sqlselect([Log]).where(and_(between(Log.timestamp, begin, end), Log.userid == person)) data = pd.read_sql(expression, engine) return data else: expression = sqlselect([Log]).where(between(Log.timestamp, begin, end)) return pd.read_sql(expression, engine)
def create_thickness_point( sorted_dict: dict, well_id: int, marker_1: int, marker_2: int, session: Session, use_faulted: bool = False, fault_name: str = "", add_properties: Tuple = tuple()) -> GeoPoint: """ Generate a new GeoPoint with thickness property from 2 well marker :param sorted_dict: dictionary containing well_id / WellMarker data :param well_id: current well_id :param marker_1: id of marker 1 :param marker_2: id of marker 2 :param session: current SQLAlchemy session :param use_faulted: should faulted sequence be included? :param fault_name: name of fault stratigraphic unit (default: "Fault") :param add_properties: Adds the properties to the GeoPoint. Format for each property: (value, type, name, unit) :return: new GeoPoint Object :raises FaultException: if a fault is inside the section and use_faulted is False :raises ValueError: if a property in the add_property tuple has less than 3 entries """ min_depth = sorted_dict[well_id][marker_1].depth max_depth = sorted_dict[well_id][marker_2].depth faults = session.query(WellMarker).join(StratigraphicObject). \ filter(WellMarker.horizon_id == StratigraphicObject.id). \ filter(StratigraphicObject.unit_name == fault_name). \ filter(WellMarker.well_id == well_id) if min_depth > max_depth: faults = faults.filter( sq.between(WellMarker.drill_depth, max_depth, min_depth)) else: faults = faults.filter( sq.between(WellMarker.drill_depth, min_depth, max_depth)) if (faults.count() > 0) and (use_faulted is False): raise FaultException("Fault inside section") point = sorted_dict[well_id][marker_1].to_geopoint() thickness = Property(max_depth - min_depth, PropertyTypes.FLOAT, "thickness", "m", session) point.add_property(thickness) if use_faulted: faulted = Property(faults.count(), PropertyTypes.INT, "faulted", "count", session) point.add_property(faulted) for prop in add_properties: if len(prop) < 4: raise ValueError("property tuple has less than 4 entries!") new_property = Property(prop[0], PropertyTypes[prop[1]], prop[2], prop[3], session) point.add_property(new_property) return point
def orders_from_to_query(is_history, from_, to, business_id): if is_history is None: print('here') query = get_orders_query(is_history, business_id).filter( between(Orders.order_date, from_, to)) else: query = get_orders_query(is_history, business_id).filter( between(Orders.completion_date, from_, to)) return query
def immediate_children(self): """ Get immediate children of the organization Reference: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ http://www.sitepoint.com/hierarchical-data-database/ Generated SQL Sample: SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth <= 1 ORDER BY node.lft; """ s_node = aliased(Organization, name='s_node') s_parent = aliased(Organization, name='s_parent') sub_tree = db.session.query(s_node.id, (func.count(s_parent.name) - 1).label('depth')). \ filter(and_(between(s_node.lft, s_parent.lft, s_parent.rgt), s_node.id == self.id)) \ .group_by(s_node.id, s_node.lft).order_by(s_node.lft).subquery() t_node = aliased(Organization, name='t_node') t_parent = aliased(Organization, name='t_parent') t_sub_parent = aliased(Organization, name='t_sub_parent') # Postgres does not support label as (func.count(t_parent.name) - (sub_tree.c.depth + 1)).label('xxx') # And have the field in having clause will cause issue. query = ( db.session.query( t_node.id, t_node.name, (func.count(t_parent.name) - (sub_tree.c.depth + 1))).filter( and_( between(t_node.lft, t_parent.lft, t_parent.rgt), between(t_node.lft, t_sub_parent.lft, t_sub_parent.rgt), t_node.id != self.id, # Exclude current node --> itself t_sub_parent.id == sub_tree.c.id)).group_by( t_node.id, t_node.name, t_node.lft, 'depth').having( (func.count(t_parent.name) - (sub_tree.c.depth + 1)) <= 1).order_by( t_node.lft)) return id_query_to_obj(Organization, query)
def listIdentifiers(self, metadataPrefix, set=None, cursor=None, from_=None, until=None, batch_size=None): '''List all identifiers for this repository. ''' data = [] packages = [] group = None if not set: if not from_ and not until: packages = Session.query(Package).filter(Package.type=='dataset').\ filter(Package.private!=True).filter(Package.state=='active').all() else: if from_ and not until: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() if until and not from_: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() if from_ and until: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() else: group = Group.get(set) if group: packages = group.packages(return_query=True).filter(Package.type=='dataset').\ filter(Package.private!=True).filter(Package.state=='active') if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active') if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active') if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active') packages = packages.all() if cursor: packages = packages[cursor:] for package in packages: spec = package.name if group: spec = group.name else: if package.owner_org: group = Group.get(package.owner_org) if group and group.name: spec = group.name group = None data.append(common.Header('', package.id, package.metadata_created, [spec], False)) return data
def nodes(): max_viewport_area = 15000 * 15000 # 15km * 15km node_types = request.args.getlist('features') radius = dict([map(int, value.split(',')) for value in request.args.getlist('radius')]) if not node_types: # nothing to look for... return jsonify({}) try: bounds = request.args.get('bounds').split(',') except TypeError: abort(400) # Convert bounds to utm bounds = ( # upper left bound lat/lon utm.from_latlon(float(bounds[1]), float(bounds[0])), # lower right bound lat/lon utm.from_latlon(float(bounds[3]), float(bounds[2])) ) if nodetools.bounds_area(bounds) > max_viewport_area: return jsonify({'error': 'Zoom in to view the results'}) zone_number, zone_letter = bounds[0][2:4] # Select nodes. I'm going to assume all nodes within these bounds belong to # the same utm zone number/letter for now. nodes = db.session.query(Node.x, Node.y, Node.node_type_id) \ .filter( Node.node_type_id.in_(node_types), between(Node.x, bounds[0][0], bounds[1][0]), between(Node.y, bounds[0][1], bounds[1][1]), Node.zone_number == zone_number, Node.zone_letter == zone_letter ) \ .order_by(Node.node_type_id) \ .all() try: intersections = node_intersections(nodes, min_layers=len(node_types), type_radius=radius) except TooFewNodeTypesException: intersections = [] # Convert back to WGS84. latlon_intersections = [list(to_latlon(polygon.exterior.coords, zone_number, zone_letter)) for polygon in intersections] data = { 'polygons': latlon_intersections } return jsonify(data)
def listRecords(self, metadataPrefix, set=None, cursor=None, from_=None, until=None, batch_size=None): '''Show a selection of records, basically lists all datasets. ''' data = [] packages = [] group = None if not set: if not from_ and not until: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(Package.state=='active').all() if from_ and not until: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(PackageRevision.revision_timestamp > from_).filter(Package.name==PackageRevision.name).\ filter(Package.state=='active').all() if until and not from_: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(PackageRevision.revision_timestamp < until).filter(Package.name==PackageRevision.name).\ filter(Package.state=='active').all() if from_ and until: packages = Session.query(Package).filter(Package.type=='dataset').filter(Package.private!=True).\ filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() else: group = Group.get(set) if group: packages = group.packages(return_query=True) if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.type=='dataset').filter(Package.private!=True).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.type=='dataset').filter(Package.private!=True).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.type=='dataset').filter(Package.private!=True).\ filter(Package.name==PackageRevision.name).filter(Package.state=='active').all() if cursor: packages = packages[cursor:] for res in packages: spec = res.name if group: spec = group.name else: if res.owner_org: group = Group.get(res.owner_org) if group and group.name: spec = group.name group = None data.append(self._record_for_dataset(res, spec)) return data
def SelectByDateRange(self, query, date_type, from_date_string, to_date_string): print from_date_string[6:10], from_date_string[0:2], from_date_string[3:5] from_date=datetime.date(int(from_date_string[6:10]), int(from_date_string[0:2]), int(from_date_string[3:5])) to_date=datetime.date(int(to_date_string[6:10]), int(to_date_string[0:2]), int(to_date_string[3:5])) if date_type == 'CreatedDate': return query.filter(between(FileIndex.ctime, from_date, to_date)) elif date_type == 'ModifiedDate': return query.filter(between(FileIndex.mtime, from_date, to_date)) elif date_type == 'AddedDate': return query.filter(between(FileIndex.added_time, from_date, to_date)) return query
def _filter_packages(set, cursor, from_, until, batch_size): '''Get a part of datasets for "listNN" verbs. ''' packages = [] setspc = None if not set: packages = Session.query(Package).filter(Package.type=='dataset'). \ filter(Package.state == 'active').filter(Package.private!=True) if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name) if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name) if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name) if batch_size: packages = packages.limit(batch_size) if cursor: packages = packages.offset(cursor) packages = packages.all() elif set == 'openaire_data': oa_tag = Session.query(Tag).filter( Tag.name == 'openaire_data').first() if oa_tag: packages = oa_tag.packages setspc = set else: group = Group.get(set) if group: # Note that group.packages never returns private datasets regardless of 'with_private' parameter. packages = group.packages(return_query=True, with_private=False).filter(Package.type=='dataset'). \ filter(Package.state == 'active') if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name) if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name) if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name) if batch_size: packages = packages.limit(batch_size) if cursor: packages = packages.offset(cursor) packages = packages.all() # if cursor is not None: # cursor_end = cursor + batch_size if cursor + batch_size < len(packages) else len(packages) # packages = packages[cursor:cursor_end] return packages, setspc
def __init__( self, hid=None, hdiameter=None, hheight=None ): """ Instatiate the Calibration Chamber class. Parameters ---------- hid: str | calibration chamber ID hdiameter: float | chamber diameter (cm) hheight: float | chamber height (cm) """ # Get the connection and soils table objects. connection, htable = get_table() # Filter the Soils Database. stmt = select([htable]) ## Filter by chamber ID. if hid: stmt = stmt.where(htable.columns.hid.in_((hid))) ## Filter by chamber diameter. if hdiameter: stmt = stmt.where( between( htable.columns.hdiameter, hdiameter[0], hdiameter[1] ) ) ## Filter by chamber height. if hheight: stmt = stmt.where( between( htable.columns.hheight, hheight[0], hheight[1] ) ) ## Make the dataframe. self.__results = pd.read_sql( stmt, connection ).set_index('hid')
def search_matching_transaction(res): def format_result(data): for row in data: item = row.UserTransaction yield { 'user_transaction_id': item.user_transaction_id, 'amount': float(str(item.amount)), 'currency': item.currency, 'exchange_currency': item.exchange_currency, 'depart_date': item.depart_date, 'arrive_date': item.arrive_date, 'depart_from': item.depart_from, 'arrive_to': item.arrive_to, 'name': row.name, 'email': row.email, 'user_id': item.user_id } params = utils.get_data(['transaction_id'], [], request.args) base = db.session.query(UserTransaction)\ .filter(UserTransaction.user_transaction_id == params['transaction_id']).first() base_arrive = base.arrive_to base_depart = base.depart_from # Set 12 hours as grace period/waiting time base_arrive_date_start = base.arrive_date + datetime.timedelta(hours=3) base_arrive_date_end = base.arrive_date + datetime.timedelta(hours=15) base_depart_date_start = base.depart_date - datetime.timedelta(hours=15) base_depart_date_end = base.depart_date - datetime.timedelta(hours=3) if not base: raise FailedRequest('Cannot find transaction!', status_code=404) transactions = db.session.query(UserTransaction, User.name, User.email)\ .join(User, User.user_id == UserTransaction.user_id)\ .filter(UserTransaction.closed == False)\ .filter(UserTransaction.user_id != g.current_user['user_id'])\ .filter(UserTransaction.exchange_currency == base.currency)\ .filter( or_( and_(UserTransaction.depart_from == base_arrive, between(UserTransaction.depart_date, base_arrive_date_start, base_arrive_date_end)), and_(UserTransaction.arrive_to == base_depart, between(UserTransaction.arrive_date, base_depart_date_start, base_depart_date_end)) ) ).all() return res.send(list(format_result(transactions)))
def date_ap(item: QueryableAttribute, expected: datetime): """ Date is approximately equal. Approximately means at discretion of the implementation. Here for dates we set approximation between one day before and one day after the required value :param item: :param expected: :return: """ delta = timedelta(days=1) if isinstance(item.property.columns[0].type, sqltypes.Date): return between(func.DATE(item), func.DATE(expected - delta), func.DATE(expected + delta)) else: return between(func.DATETIME(item), func.DATETIME(expected - delta), func.DATETIME(expected + delta))
def list_all(country_code=None): """Show a list of all airports.""" or_args = [] if country_code is None: or_args = [between(Airport.style, 2, 5)] else: or_args = [and_(between(Airport.style, 2, 5), Airport.country_code == country_code)] query = session.query(Airport) \ .order_by(Airport.name) \ .filter(*or_args) print('--- Airports ---') for airport in query.all(): print(airport.name)
def listIdentifiers(self, metadataPrefix, set=None, cursor=None, from_=None, until=None, batch_size=None): '''List all identifiers for this repository. ''' data = [] packages = [] if not set: if not from_ and not until: packages = Session.query(Package).all() else: if from_: packages = Session.query(Package).\ filter(PackageRevision.revision_timestamp > from_).\ all() if until: packages = Session.query(Package).\ filter(PackageRevision.revision_timestamp < until).\ all() if from_ and until: packages = Session.query(Package).\ filter(between(PackageRevision.revision_timestamp, from_, until)\ ).all() else: group = Group.get(set) if group: packages = group.active_packages() if from_ and not until: packages = packages.\ filter(PackageRevision.revision_timestamp > from_) if until and not from_: packages = packages.\ filter(PackageRevision.revision_timestamp < until) if from_ and until: packages = packages.filter( between(PackageRevision.revision_timestamp, from_, until)) packages = packages.all() if cursor: packages = packages[:cursor] for package in packages: data.append(common.Header(package.id, package.metadata_created, [package.name], False)) return data
def index(self): from gviz_data_table import Table from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent if request.args.get('activity') == 'activity': activity_model, activity_date = UserActivity, UserActivity.date_actioned else: activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date try: interval_count = int(request.args['interval_count']) except Exception: interval_count = 10 interval = request.args.get('interval') if interval not in ('week', 'month'): interval = 'week' cohort = func.date_part(interval, User.date_joined) cohort_label = func.min(func.date(User.date_joined)) active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval') q = readonly_session.query(User).filter( User.date_joined > LAUNCHDATE, User.refresh_token != '') if request.args.get('gender') in ('m', 'f'): q = q.filter(User.gender == request.args['gender']) if request.args.get('locale') in app.config['ENABLED_LOCALES']: q = q.filter(User.locale == request.args['locale']) if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'): age1, age2 = map(int, request.args['age'].split('-')) q = q.filter(between( func.age(User.date_of_birth), text("interval '%d years'" % age1), text("interval '%d years'" % age2) )) active_users = dict( ((c, int(w)), u) for c, w, u in q.join( activity_model, (activity_model.user == User.id) & (activity_date >= User.date_joined) ).group_by(cohort, active_interval).values( cohort, active_interval, func.count(func.distinct(activity_model.user)) ) ) table = Table( [dict(id='cohort', type=date)] + [dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)] ) totals = q.group_by(cohort).order_by(cohort) for c, l, t in totals.values(cohort, cohort_label, func.count()): data = [] for i in range(interval_count): a = active_users.get((c, i), '') data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a)) table.append([l] + data) return self.render('admin/retention_stats.html', data=table.encode())
def post_list(self, fromDate, toDate): return PostId.query.filter( between( PostId.publish_date, fromDate, toDate)).order_by( desc(PostId.publish_date), PostId.publish_id).all()
def calls_for_pricing(self, area_code, phone_number, start_date, end_date): call_start_t = aliased(self.t) call_end_t = aliased(self.t) query_join = join(call_start_t, call_end_t, call_start_t.c.call_id == call_end_t.c.call_id) columns = [ call_start_t.c.id.label('call_start_id'), call_start_t.c.call_id, call_start_t.c.destination_area_code.label('area_code'), call_start_t.c.destination.label('phone'), call_start_t.c.timestamp.label('start_at'), call_end_t.c.timestamp.label('end_at') ] query = select(columns).select_from(query_join) query = query.where(call_start_t.c.id != call_end_t.c.id) query = query.where(call_start_t.c.source_area_code == area_code) query = query.where(call_start_t.c.source == phone_number) query = query.where( between(call_end_t.c.timestamp, start_date, end_date)) result = self.connection.execute(query).fetchall() calls = self.to_dict(result) for call in calls: configs = [] where = [self.db.applied_config.call_id == call['call_start_id']] for config in self.db.applied_config.select(where=where): configs.append(config) call['configs'] = configs return calls
def search_around(timestamp: T.number, ): get_logger().info('/search_around %s', timestamp) utc_timestamp = timestamp # old 'timestamp' name is legacy # TODO meh. use count/pagination instead? delta_back = timedelta(hours=3).total_seconds() delta_front = timedelta(minutes=2).total_seconds() # TODO not sure about delta_front.. but it also serves as quick hack to accomodate for all the truncations etc return search_common( url= 'http://dummy.org', # NOTE: not used in the where query (below).. perhaps need to get rid of this where=lambda table, url: between( func.strftime( '%s', # NOTE: it's tz aware, e.g. would distinguish +05:00 vs -03:00 # this is a bit fragile, relies on cachew internal timestamp format, e.g. # 2020-11-10T06:13:03.196376+00:00 Europe/London func.substr( table.c.dt, 1, # substr is 1-indexed # instr finds the first match, but if not found it defaults to 0.. which we hack by concatting with ' ' func.instr( func.cast(table.c.dt, types.Unicode).op('||') (' '), ' ') - 1, # for f***s sake.. seems that cast is necessary otherwise it tries to treat ' ' as datetime??? )) - literal(utc_timestamp), literal(-delta_back), literal(delta_front), ), )
def run_test(args): start_date = parser.parse(args.start_date) end_date = parser.parse(args.end_date) all_games = (db.session.query(Game) .filter(between(Game.game_day, start_date, end_date))).all() numb_success = 0 numb_failed = 0 for game in all_games: computed_spread = compute_spread( host=game.home_team, visitor=game.visitor, date=str(game.game_day), ) if abs(computed_spread - game.spread) <= 3: continue actual_spread = game.actual_spread success = False if computed_spread < game.spread and actual_spread < game.spread: success = True elif computed_spread > game.spread and actual_spread > game.spread: success = True if success: numb_success += 1 else: numb_failed += 1 print game print computed_spread, game.spread, actual_spread, success print 'total games ', len(all_games) print numb_success, numb_failed
def prune_keep_record(job_id, keep, storage): event = storage.session.query(Event).filter_by(job_id=job_id).\ order_by(asc(Event.id)).limit(1).first() min_id = None if event is not None: min_id = event.id events = storage.session.query(Event).filter_by(job_id=job_id).\ order_by(desc(Event.id)).limit(keep) event_ids = [e.id for e in events] if len(event_ids) == 0: logger.info('No events for {0}'.format(job_id)) return max_id = min(event_ids) if min_id == max_id: logger.info('Min and max event ids for {0} are the same: {1} - {2}'.format( # noqa job_id, min_id, max_id)) return if min_id > max_id: logger.info('Min event id for {0} is larger than max event id: {1} - {2}'.format( # noqa job_id, min_id, max_id)) return logger.info('Job ID {0}, Pruning events {1} - {2}'.format( job_id, min_id, max_id)) stmt = Event.__table__.delete()\ .where(between(Event.id, min_id, max_id))\ .where(Event.job_id == job_id) storage._engine.execute(stmt) storage.session.commit()
def get_current_configs(self, source_area_code, dest_area_code, timestamp): cfg_con_join = join(self.t, self.db.tariff_condition.t, self.conditions_id == self.db.tariff_condition.id) columns = [ self.id, self.standard_charge, self.call_time_charge, self.db.tariff_condition.id, self.db.tariff_condition.start_at, self.db.tariff_condition.end_at ] query = select(columns).select_from(cfg_con_join) query = query.where( between(timestamp, self.config_start_date, self.config_end_date)) query = query.where( self.db.tariff_condition.source_area_code == source_area_code) query = query.where( self.db.tariff_condition.destination_area_code == dest_area_code) configs = self.connection.execute(query).fetchall() configs = [{ 'config_id': config_id, 'standard_charge': standard_charge, 'call_time_charge': call_time_charge, 'condition_id': condition_id, 'start_at': start_at, 'end_at': end_at } for config_id, standard_charge, call_time_charge, condition_id, start_at, end_at in configs] return configs
def active_on(cls, date, transient=None): """Constructs a check to filter the table 'transient' (with columns 'effective_from' and 'effective_to') down to only those rows active on 'date'. Args: date: The datetime on which the transient must be active. transient: If given, the table, column set or model whose transient columns will be checked; if None, the class from which this method was called will be the transient. (Default: None.) Returns: A SQLAlchemy expression implementing the transient activity check. """ null = None # stop static analysis checkers from moaning about == None if transient is None: transient = cls return sqlalchemy.between( date, transient.effective_from, sqlalchemy.case( [ # NULL effective_to => always on past effective_from (transient.effective_to == null, date), (transient.effective_to != null, transient.effective_to) ] ) )
def weight_query(bin_id: int, start_timestamp: datetime, end_timestamp: datetime, db: Session): return db.query(weight_model.BinWeight).filter( and_( weight_model.BinWeight.bin_id == bin_id, between(weight_model.BinWeight.datetimestamp, start_timestamp, end_timestamp))).all()
def getMaxCount(self, session=None, numerator_table=None, cur_year=None): """ Determine the maximum value of the numbering counter. The maximum value of the counter must be produced taking into account the current program year. :param session: SQLAlchemy session. :param numerator_table: Numerator table. :param cur_year: Current year. If not defined then get actual year. :return: Maximum numerator counter value. """ if numerator_table is None: numerator_table = self.createTable() if session is None: session = sqlalchemy.orm.session.sessionmaker( bind=self._connection) if cur_year is None: cur_year = self.getActualYear() min_date = datetime.date(cur_year, 1, 1) max_date = datetime.date(cur_year, 12, 31) session_query = sqlalchemy.orm.scoping.scoped_session(session) max_count = session_query.query( sqlalchemy.sql.functions.max( numerator_table.c.cur_count).label('max_count')).filter( sqlalchemy.between(numerator_table.c.dt_num, min_date, max_date)).one()[0] return max_count
def filter_ancestors(self, and_self=False): "The same as :meth:`filter_descendants` but filters ancestor nodes." options = self._tree_options obj = self._get_obj() #self._get_session_and_assert_flushed(obj) # Restrict ourselves to just those nodes within the same tree: tree_id = getattr(obj, self.tree_id_field.name) filter_ = self.tree_id_field == tree_id alias = sqlalchemy.alias(options.table) left_field = self.left_field filter_ &= sqlalchemy.between(getattr(alias.c, self.left_field.name), self.left_field, self.right_field) filter_ &= getattr(alias.c, self.pk_field.name) == \ getattr(obj, self.pk_field.name) if not and_self: filter_ &= self.pk_field != getattr(obj, self.pk_field.name) # WHERE tree_id = <node.tree_id> AND <node.path> LIKE path || '%' #filter_ = (self.tree_id_field == tree_id) \ # & sqlalchemy.sql.expression.literal( # path, sqlalchemy.String # ).like(options.path_field + '%') #if and_self: # filter_ &= self.depth_field <= depth #else: # filter_ &= self.depth_field < depth return filter_
def filter_ancestors(self, and_self=False): "The same as :meth:`filter_descendants` but filters ancestor nodes." options = self._tree_options obj = self._get_obj() #self._get_session_and_assert_flushed(obj) # Restrict ourselves to just those nodes within the same tree: tree_id = getattr(obj, self.tree_id_field.name) filter_ = self.tree_id_field == tree_id alias = sqlalchemy.alias(options.table) left_field = self.left_field filter_ &= sqlalchemy.between( getattr(alias.c, self.left_field.name), self.left_field, self.right_field) filter_ &= getattr(alias.c, self.pk_field.name) == \ getattr(obj, self.pk_field.name) if not and_self: filter_ &= self.pk_field != getattr(obj, self.pk_field.name) # WHERE tree_id = <node.tree_id> AND <node.path> LIKE path || '%' #filter_ = (self.tree_id_field == tree_id) \ # & sqlalchemy.sql.expression.literal( # path, sqlalchemy.String # ).like(options.path_field + '%') #if and_self: # filter_ &= self.depth_field <= depth #else: # filter_ &= self.depth_field < depth return filter_
def _filter_by_column_value(cls, query, column, value): def return_(e): return query.filter(e) import_value = getattr(cls, 'import_value') if not isinstance(value, str): raise HttpBadRequest() in_operator_match = FILTERING_IN_OPERATOR_REGEX.match(value) if in_operator_match: not_ = value.startswith('!') items = in_operator_match.groupdict()['items'].split(',') items = [i for i in items if i.strip() != ''] if not len(items): raise HttpBadRequest('Invalid query string: %s' % value) expression = column.in_([import_value(column, j) for j in items]) if not_: expression = ~expression return return_(expression) between_operator_match = FILTERING_BETWEEN_OPERATOR_REGEX.match(value) if between_operator_match: not_ = value.startswith('!') groups = between_operator_match.groupdict() start, end = groups['min'].strip(), groups['max'].strip() if not (start or end): raise HttpBadRequest('Invalid query string: %s' % value) expression = between(column, start, end) if not_: expression = ~expression return return_(expression) if value == 'null': expression = column.is_(None) elif value == '!null': expression = column.isnot(None) elif value.startswith('!'): expression = column != import_value(column, value[1:]) elif value.startswith('>='): expression = column >= import_value(column, value[2:]) elif value.startswith('>'): expression = column > import_value(column, value[1:]) elif value.startswith('<='): expression = column <= import_value(column, value[2:]) elif value.startswith('<'): expression = column < import_value(column, value[1:]) # LIKE elif '%' in value: func, actual_value = (column.ilike, value[1:]) if value.startswith('~') else (column.like, value) expression = func(import_value(column, actual_value)) # EQUAL else: expression = column == import_value(column, value) return return_(expression)
def get_event(self, statistic): if statistic.start_date is None and statistic.finish_date is None and statistic.ready_date is None: raise IncorrectData() HOURS = 3600 DAY = 86400 if statistic.ready_date == 'this_day': start_date = int(time.time()) - DAY finish_date = int(time.time()) elif statistic.ready_date == 'this_hours': start_date = int(time.time()) - HOURS finish_date = int(time.time()) else: start_date = int( time.mktime(time.strptime(statistic.start_date, '%Y-%m-%d'))) finish_date = int( time.mktime(time.strptime(statistic.finish_date, '%Y-%m-%d'))) query = (self.session.query(StatisticsModel).filter( between(StatisticsModel.datetime, start_date, finish_date)).all()) return_event = [] for event in query: return_event.append(event.as_dict()) for item in return_event: item.update({ 'datetime': time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(item.get('datetime'))) }) return return_event
def create_country_stats(session, date, logger=None): if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(CountryStats).filter( CountryStats.date == date).delete() country_stats = (session.query( literal(date), Country.gid, func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"), func.count(func.distinct( AircraftBeacon.receiver_id)).label("device_count")).filter( between(AircraftBeacon.timestamp, start, end)).filter( func.st_contains(Country.geom, AircraftBeacon.location)).group_by( Country.gid).subquery()) # And insert them ins = insert(CountryStats).from_select([ CountryStats.date, CountryStats.country_id, CountryStats.aircraft_beacon_count, CountryStats.device_count ], country_stats) res = session.execute(ins) insert_counter = res.rowcount session.commit()
def _get_event_with_enterqueue(session, start, end, match, event): start = start.strftime(_STR_TIME_FMT) end = end.strftime(_STR_TIME_FMT) enter_queues = (session .query(QueueLog.callid, cast(QueueLog.time, TIMESTAMP).label('time')) .filter(and_(QueueLog.event == 'ENTERQUEUE', between(QueueLog.time, start, end)))) enter_map = {} for enter_queue in enter_queues.all(): enter_map[enter_queue.callid] = enter_queue.time if enter_map: res = (session .query(QueueLog.event, QueueLog.queuename, cast(QueueLog.time, TIMESTAMP).label('time'), QueueLog.callid, QueueLog.data3) .filter(and_(QueueLog.event == match, QueueLog.callid.in_(enter_map)))) for r in res.all(): yield { 'callid': r.callid, 'queue_name': r.queuename, 'time': enter_map[r.callid], 'event': event, 'talktime': 0, 'waittime': int(r.data3) if r.data3 else 0 }
def listener_peak(): """Return the global listener peak Keyword arguments: - None """ peak = StatsistcsData.query.join(Statistic).filter(Statistic.identifier == 'lst-total').order_by(StatsistcsData.value.desc()).first() if peak: data = {'listener_peak': {'peak_value': peak.value, 'peak_time': peak.timestamp.isoformat()}} show = Show.query.join(UserShow).filter(between(peak.timestamp, Show.begin, Show.end)).first() if show: dj = [] for usershow in show.users: dj.append({'dj_name': usershow.user.username, 'dj_id': usershow.user.user, 'status': usershow.status}) data['listener_peak']['peak_show'] = { 'show_id': show.show, 'show_name': show.name, 'show_description': show.description, 'show_flags': show.flags, 'show_begin': show.begin.isoformat(), 'show_end': show.end.isoformat(), 'dj': dj } else: data['listener_peak']['peak_show'] = None else: data = {'listener_peak': None} return jsonify(wrapper(data))
def traffic_report_filter(cls, contributor_id): query = cls.query.filter(cls.status == 'published') query = query.filter(cls.contributor_id == contributor_id) query = query.filter( between(get_current_time(), cls.start_publication_date, cls.end_publication_date)) return query.all()
def __iter__(self, *, chunksize: int = 100): """Yield pairs of ``(Entry.id, Entry.hash)`` and unmerged field values.""" with self.connect() as conn: assert Entry.allid(conn=conn) assert Entry.onetoone(conn=conn) select_values = (sa.select( Entry.id, Entry.hash, Value.field, Value.value, File.name.label('filename'), Entry.bibkey).join_from(Entry, File).join(Value).where( sa.between(Entry.id, sa.bindparam('first'), sa.bindparam('last'))).order_by( 'id', 'field', File.priority.desc(), 'filename', 'bibkey')) groupby_id_hash = functools.partial(itertools.groupby, key=operator.attrgetter( 'id', 'hash')) groupby_field = functools.partial(itertools.groupby, key=operator.attrgetter('field')) with self.connect() as conn: for first, last in Entry.windowed(conn, key_column='id', size=chunksize): result = conn.execute(select_values, { 'first': first, 'last': last }) for id_hash, grp in groupby_id_hash(result): fields = [(field, [(r.value, r.filename, r.bibkey) for r in g]) for field, g in groupby_field(grp)] yield id_hash, fields
def get_grade_timings(problem_id, grader_id): query_per_problem = Solution.query.filter(Solution.grader_id == grader_id, Solution.problem_id == problem_id)\ .order_by(Solution.graded_at) first_grade, last_grade = query_per_problem[0].graded_at, query_per_problem[-1].graded_at if query_per_problem.count() == 1: # only one solution graded, then first and last grade are the same # look for some other graded solution before first_grade previous_grade = Solution.query.filter(Solution.grader_id == grader_id, Solution.graded_at < first_grade)\ .order_by(desc(Solution.graded_at)).first() if previous_grade is None: return None return np.array([[previous_grade.problem_id, previous_grade.graded_at.timestamp()], [problem_id, first_grade.timestamp()]]) # get the datetime data for all Solutions graded by the same grader ordered in ascending order graded_timings = np.array([ [it.problem_id, it.graded_at.timestamp()] for it in Solution.query .filter(Solution.grader_id == grader_id, between(Solution.graded_at, first_grade, last_grade)) .order_by(Solution.graded_at) if it.graded_at ]) return graded_timings
def query_criteria(query, table, criteria): for column, value in criteria.items(): if column in table.c: if re.search(',',value): sp = value.split(',') if sp[0] == 'IN': if re.search('|',sp[1]): data = sp[1].split('|') else: data = sp[1] query = query.where(table.c[column].in_(data)) elif sp[0] == 'LIKE': query = query.where(table.c[column].like('%'+sp[1]+'%')) elif sp[0] == '<': query = query.where(table.c[column] < sp[1]) elif sp[0] == '>': query = query.where(table.c[column] > sp[1]) elif sp[0] == '<>': query = query.where(table.c[column] != sp[1]) elif sp[0] == '=': query = query.where(table.c[column] == sp[1]) else: if value == 'IS NULL': query = query.where(table.c[column].is_(None)) elif value == 'IS NOT NULL': query = query.where(table.c[column].isnot(None)) else: if column == 'bbox': sp = value.split(',') query = query.where(and_(between(table.c['LAT'], float(sp[1]), float(sp[3])), between(table.c['LON'], float(sp[0]), float(sp[2])))) return query
def get_acquirers_count(self, start_date: datetime, end_date: datetime): df = [] # const data = [ # { name: 'Adyen 25%', value: 25 }, # { name: 'Group B', value: 25 }, # { name: 'Group C', value: 25 }, # { name: 'Group D', value: 25 }, # ]; try: queue_files = self.session.query(func.count(QueueDetail.detailId).label("quantity"), QueueProcesses.acquirer)\ .join(QueueProcesses, QueueDetail.processId==QueueProcesses.process_id)\ .group_by(QueueProcesses.acquirer)\ .filter(between(QueueDetail.createdAt, start_date, end_date), QueueDetail.statusCode == '200') df = pd.read_sql(queue_files.statement, self.session.bind) # pivot table df['quantity'] = df['quantity'].fillna(0).astype(float) total = df['quantity'].sum() df['name'] = [f"{acquirer} {int((quantity/total*100))}%" for quantity, acquirer in df.itertuples(index=False)] df = df.drop('acquirer', axis=1).to_dict(orient="records") except Exception as error: print(str(error)) finally: self.session.close() return df, []
def run(long, lat): lat_min = lat - SEARCH_R lat_max = lat + SEARCH_R long_min = long - SEARCH_R long_max = long + SEARCH_R geokey_min = CombineGeo(long_min, lat_min) geokey_max = CombineGeo(long_max, lat_max) with dbconfig.Session() as session: users = session.query(UserGeoPosition).filter( and_(between(UserGeoPosition.geokey, geokey_min, geokey_max), between(UserGeoPosition.lat, lat_min, lat_max), between(UserGeoPosition.long, long_min, long_max))).all() ulist = [] for user in users: ulist.append(user.toJson()) return Res({"users": ulist})
def printf(cls): return sa.case([ (cls.lang == '', cls.name), (sa.between(sa.func.length(cls.lang), 2, 3), sa.func.printf('%s [%s]', cls.name, cls.lang)), ], else_=cls.name)
def get_log_entries(): # Limits and offsets if 'limit' in request.args: limit = int(request.args['limit']) if limit > 100: limit = 100 else: limit = 10 if 'offset' in request.args: offset = int(request.args['offset']) else: offset = 0 if 'organization_id' in request.args: query = LogEntry.query\ .filter(LogEntry.organization_id == request.args['organization_id'])\ .filter(sa.not_(LogEntry.retired)) else: query = LogEntry.query.filter(sa.not_(LogEntry.retired)) if 'search' in request.args: if request.args["search"]: slist = [] clist=[] for s in request.args["search"].split(): slist.append(sa.or_(sa.func.upper(LogEntry.entry_title).like("%" + s.upper() + "%"))) slist.append(sa.or_(sa.func.upper(LogEntry.entry_description).like("%" + s.upper() + "%"))) clist.append(sa.or_(sa.func.upper(LogEntryComment.comment).like("%" + s.upper() + "%"))) query=LogEntry.query\ .filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\ .filter(sa.or_(*clist))\ .filter(LogEntryComment.retired == False)))\ .filter (LogEntry.user_id.in_(User.query.with_entities("id")\ .filter(sa.func.upper(User.first_name).in_(request.args["search"].upper().split()) | sa.func.upper(User.last_name).in_(request.args["search"].upper().split()))))\ .filter(LogEntry.organization_id == request.args['organization_id'])\ .filter(sa.not_(LogEntry.retired)) if query.count()==0: query=LogEntry.query\ .filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\ .filter(sa.or_(*clist))\ .filter(LogEntryComment.retired == False)))\ .filter(LogEntry.organization_id == request.args['organization_id'])\ .filter(LogEntry.retired == False) if 'daterange' in request.args and request.args["daterange"]: rdates=request.args["daterange"].split('_') query.whereclause.append(sa.between(LogEntry.entry_time,rdates[0] + " 00:00:00.000",rdates[1] + " 11:59:59.000")) log_entries = query.order_by(sa.desc(LogEntry.entry_time)).limit(limit).offset(offset).all() if not log_entries: return jsonify({}), 204 log_entries = [l.to_json() for l in log_entries] return jsonify(log_entries=log_entries)
def traffic_report_filter(cls, contributor_id): query = cls.query.filter(cls.status == "published") query = query.join(Disruption) query = query.filter(Disruption.contributor_id == contributor_id) query = query.filter( between(get_current_time(), Disruption.start_publication_date, Disruption.end_publication_date) ) return query.all()
def get_last_24_hour_post_count(self): qry = Posts.query.filter( and_( Posts.thread.in_(Threads.query.with_entities(Threads.id).filter_by(fk_forum=self.id)), between(Posts.timestamp, datetime.utcnow() - timedelta(days=1), datetime.utcnow()) ), ) return qry.count()
def processXML(self): # records represents whatever element you're tacking more onto, like entry_exits or clients if settings.DEBUG: print "processXML: Appending XML to Base Record" self.root_element = self.createDoc() #makes root element with XML header attributes #print '==== root created' clients = self.createClients(self.root_element) # JCS - tag is <clientRecords> Only node under clients is <Client> print '==== clientRecords created' if self.options.reported == True: Persons = self.session.query(dbobjects.Person).filter(dbobjects.Person.reported == True) elif self.options.unreported == True: Persons = self.session.query(dbobjects.Person).filter(or_(dbobjects.Person.reported == False, dbobjects.Person.reported == None)) elif self.options.reported == None: Persons = self.session.query(dbobjects.Person) # Now apply the dates to the result set. if self.options.alldates == None: Persons = Persons.filter(between(dbobjects.Person.person_id_date_collected, self.options.startDate, self.options.endDate)) pulledConfigID = 0 # JCS Only pull it if it has changed for self.person in Persons: #print "person is: ", self.person export = self.person.fk_person_to_export # this is a single record because: # person has: export_index_id = Column(Integer, ForeignKey('export.id')) # export has: fk_export_to_person = relationship('Person', backref='fk_person_to_export') # Therefore there are multiple persons to one export - but only one export to a person #print "==== export before pullconfig:", export.id, export # JCS if pulledConfigID != export.id: self.pullConfiguration(export.export_id) pulledConfigID = export.id self.ph = self.person.fk_person_to_person_historical # JCS This is a list of records self.race = self.person.fk_person_to_races self.site_service_part = self.person.site_service_participations # JCS #information_releases = self.person.fk_person_to_release_of_information # JCS a set #self.service_event = self.person.fk_person_to_service_event # Instead of generating a number (above), use the client number that is already provided in the legacy system # or # self.iDG.initializeSystemID(self.person.id) self.sysID = self.person.id # JCS beware set self.sysID #if settings.DEBUG: #print "self.person is:", self.person if self.person: # and not self.person.person_legal_first_name_unhashed+self.person.person_legal_last_name_unhashed == None: self.client = self.createClient(clients) # JCS - no clients in svc5? yes as clientRecords # Sub can be: active, anonymous, firstName, suffix, unnamedClient, alias, middleName, childEntryExit, # childReleaseOfInfo, childGoal self.customizeClient(self.client) self.customizeClientPersonalIdentifiers(self.client, self.person) self.assessment_data = self.createAssessmentData(self.client) # JCS New - self? self.customizeAssessmentData(self.assessment_data) if self.site_service_part: # JCS 21 Dec 2012 self.child_entry_exit = self.createChildEntryExit(self.client) for ssp in self.site_service_part: self.createEntryExit(self.child_entry_exit, ssp) # update the reported flag for person (This needs to be applied to all objects that we are getting data from) self.updateReported(self.person)
def prune_record(event_id, max_event_id, event_range, storage): last = event_id + event_range if last > max_event_id: last = max_event_id stmt = Event.__table__.delete().where(between(Event.id, event_id, last)) storage._engine.execute(stmt) logger.info('Pruning {0} - {1}'.format(event_id, last)) storage.session.commit() return last
def run(long,lat): lat_min=lat-SEARCH_R lat_max=lat+SEARCH_R long_min=long-SEARCH_R long_max=long+SEARCH_R geokey_min=CombineGeo(long_min,lat_min) geokey_max=CombineGeo(long_max,lat_max) with dbconfig.Session() as session: users=session.query(UserGeoPosition).filter( and_( between(UserGeoPosition.geokey,geokey_min,geokey_max), between(UserGeoPosition.lat,lat_min,lat_max), between(UserGeoPosition.long,long_min,long_max) ) ).all() ulist=[] for user in users: ulist.append(user.toJson()) return Res({"users":ulist})
def hours_with_calls(session, start, end): start = start.strftime(_STR_TIME_FMT) end = end.strftime(_STR_TIME_FMT) hours = (session .query(distinct(func.date_trunc('hour', cast(QueueLog.time, TIMESTAMP))).label('time')) .filter(between(QueueLog.time, start, end))) for hour in hours.all(): yield hour.time
def listRecords(self, metadataPrefix, set=None, cursor=None, from_=None, until=None, batch_size=None): '''Show a selection of records, basically lists all datasets. ''' data = [] packages = [] if not set: if not from_ and not until: packages = Session.query(Package).all() if from_: packages = Session.query(Package).\ filter(PackageRevision.revision_timestamp > from_).all() if until: packages = Session.query(Package).\ filter(PackageRevision.revision_timestamp < until).all() if from_ and until: packages = Session.query(Package).filter( between(PackageRevision.revision_timestamp,from_,until)).\ all() else: group = Group.get(set) if group: packages = group.active_packages() if from_ and not until: packages = packages.\ filter(PackageRevision.revision_timestamp > from_).\ all() if until and not from_: packages = packages.\ filter(PackageRevision.revision_timestamp < until).\ all() if from_ and until: packages = packages.filter( between(PackageRevision.revision_timestamp, from_, until))\ .all() if cursor: packages = packages[:cursor] for res in packages: data.append(self._record_for_dataset(res)) return data
def _build(self): query = db.session.\ query(Project, Task, TimeEntry).\ join(Project.tasks).\ join(Task.time_entries).\ filter(Project.client_id == self.client.id).\ filter(between(TimeEntry.added_at, *self.date_range)).\ order_by(Project.name.asc()) dates = [day.date() for day in arrow.Arrow.range('day', *self.date_range)] daily_totals = OrderedDict() for date in dates: daily_totals[date] = datetime.timedelta() projects = [] for project, group in itertools.groupby(query, lambda row: row[0]): project_total = datetime.timedelta() daily_data = OrderedDict() for date in dates: daily_data[date] = { 'total': datetime.timedelta(), 'tasks': set(), } for row in group: task_title = row[1].title date = localize(row[2].added_at).date() duration = row[2].duration daily_data[date]['tasks'].add(task_title) daily_data[date]['total'] += duration project_total += duration daily_totals[date] += duration projects.append({ 'project': project, 'time': list(daily_data.values()), 'total': project_total, }) timesheet = { 'client': self.client, 'date_range': { 'beg': self.date_range[0], 'end': self.date_range[1], }, 'projects': projects, 'totals': { 'time': list(daily_totals.values()), 'total': sum(daily_totals.values(), datetime.timedelta()), }, } return timesheet
def get_q_unique(date): return session.query(GroupMemberTitle).filter( rdb.and_( schema.user_group_memberships.c.group_id == group_id, schema.title_types.c.user_unique == True, schema.member_titles.c.title_type_id == title_type_id, rdb.or_( rdb.between(date, schema.member_titles.c.start_date, schema.member_titles.c.end_date), schema.member_titles.c.end_date == None, ), ) )
def get_semester_for_date(target_date): """ Get the semester which contains a given target date. :param date target_date: The date for which a corresponding semester should be found. :rtype: Semester :raises sqlalchemy.orm.exc.NoResultFound if no semester was found :raises sqlalchemy.orm.exc.MultipleResultsFound if multiple semester were found. """ return Semester.q.filter( between(target_date, Semester.begin_date, Semester.end_date) ).one()
def get_q_user(date): return session.query(GroupMemberTitle).filter( rdb.and_( GroupMemberTitle.group_id == group_id, GroupMemberTitle.membership_id == membership_id, GroupMemberTitle.title_type_id == title_type_id, rdb.or_( rdb.between( date, schema.member_title.c.start_date, schema.member_title.c.end_date), schema.member_title.c.end_date == None )))
def create_clause(min_val, max_val, column): """returns correct ==, between, <=, >= filter clause based on min_val and max_val""" if min_val: if min_val == max_val: return column == int(min_val) elif max_val: return between(column, int(min_val), int(max_val), True) else: return column >= int(min_val) elif max_val: return column <= int(max_val) else: return None
def get_q_unique(date): return session.query(GroupMemberTitle).filter( sa.and_( GroupMemberTitle.group_id == group_id, schema.title_type.c.user_unique == True, GroupMemberTitle.title_type_id == title_type_id, sa.or_( sa.between( date, schema.member_title.c.start_date, schema.member_title.c.end_date), schema.member_title.c.end_date == None )))
def _filter_packages(set, cursor, from_, until, batch_size): '''Get a part of datasets for "listNN" verbs. ''' packages = [] group = None if not set: packages = Session.query(Package).filter(Package.type=='dataset'). \ filter(Package.state == 'active').filter(Package.private!=True) if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name) if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name) if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name) packages = packages.all() else: group = Group.get(set) if group: # Note that group.packages never returns private datasets regardless of 'with_private' parameter. packages = group.packages(return_query=True, with_private=False).filter(Package.type=='dataset'). \ filter(Package.state == 'active') if from_ and not until: packages = packages.filter(PackageRevision.revision_timestamp > from_).\ filter(Package.name==PackageRevision.name) if until and not from_: packages = packages.filter(PackageRevision.revision_timestamp < until).\ filter(Package.name==PackageRevision.name) if from_ and until: packages = packages.filter(between(PackageRevision.revision_timestamp, from_, until)).\ filter(Package.name==PackageRevision.name) packages = packages.all() if cursor is not None: cursor_end = cursor + batch_size if cursor + batch_size < len(packages) else len(packages) packages = packages[cursor:cursor_end] return packages, group
def target_get_entity(self): session = sqlalchemy.orm.object_session(self) assert session is not None, "Instance has no session: Must have session to read relations" query = session.query(target_entity).filter( and_( target_column == getattr(self,local_column_name), target_table.c.changeset_id >= self._changeset_view, between(self._changeset_view, target_table.c.changeset_id, target_table.c.changeset_invalid_id) ) ) e = query.first() e._changeset_view = self._changeset_view return e
def get_at_changeset(self, changeset): sess = sqlalchemy.orm.object_session(self) q = sess.query(self.Revision) q = q.filter(and_( self.Revision.head == self, self.Revision.changeset_id >= changeset, between(changeset, self.Revision.changeset_id, self.Revision.changeset_invalid_id) )).limit(1) r = q.first() if r: r._changeset_view = changeset return r
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None): tbl = dbcon.getTable('argo2', 'oceanobs') #first create a subquery to quickly discard argo profiles subqry = select([tbl]) if tspan: subqry = subqry.where( func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1])) # Apply initial geospatial constraints if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box subqry = subqry.where( func.ST_DWithin( literal_column('ST_Envelope(geom::geometry)::geography'), func.ST_GeogFromText(geoWKT), withinDmeter)) else: subqry = subqry.where( func.ST_Intersects(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT, 4326))) #we need to assign an alias to this subquery in order to work with it subqry = subqry.alias("ar") #expand the arrays and points int he subquery qry = select([ subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter, func.unnest(subqry.c.mode).label('mode'), func.unnest(subqry.c.ascend).label('ascend'), func.unnest(subqry.c.tlocation).label('tlocation'), func.unnest(subqry.c.cycle).label('cycle'), func.unnest(subqry.c.iprof).label('iprof'), ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom') ]) #additional spatial constraints finalqry = qry qry = qry.alias("arex") if tspan: finalqry = select([qry]).where( between(qry.c.tlocation, tspan[0], tspan[1])) if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box finalqry = finalqry.where( func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT), withinDmeter)) else: finalqry = finalqry.where( func.ST_Within(literal_column("arex.geom"), func.ST_GeomFromText(geoWKT, 4326))) if tsort: finalqry = finalqry.order_by(qry.c.tlocation) return dbcon.dbeng.execute(finalqry)