def get_by_unique_key(self, unique_key, name, default=None): pkey = (unique_key, name) session = DBSession() # Eager load related resources here. key = session.query(Key).options( orm.joinedload_all( Key.resource, Resource.data, CurrentPropertySheet.propsheet, innerjoin=True, ), orm.joinedload_all( Key.resource, Resource.rels, Link.target, Resource.data, CurrentPropertySheet.propsheet, ), ).get(pkey) if key is None: return default model = key.resource if model.item_type != self.item_type: return default return self.Item(self, model)
def show_ballot_room_editor(ballot_id, ballot_type_name, db): ballot_type = db.query(m.BallotType).filter(func.lower(m.BallotType.name) == ballot_type_name.lower()).one() ballot_eventsq = (db .query(m.BallotEvent) .join(m.BallotSeason) .filter(m.BallotEvent.type == ballot_type) .filter(m.BallotSeason.year <= ballot_id) .order_by(m.BallotSeason.year.desc()) .limit(2) ) ballot_events = ballot_eventsq.all() if ballot_events[0].season.year != ballot_id: raise HTTPError(404, "No {} ballot for the {} season {} {}".format(ballot_type.name, ballot_id, ballot_eventsq, ballot_events)) else: ballot = ballot_events[0] if len(ballot_events) == 2: last_ballot = ballot_events[1] else: last_ballot = None root = db.query(m.Place).options( joinedload_all('children.rooms.listing_for'), joinedload_all('children.children.rooms.listing_for'), joinedload_all('children.children.children.rooms.listing_for'), joinedload_all('children.children.children.children.rooms.listing_for'), ).filter(m.Place.parent == None).one() return template('ballot-event-edit-rooms', ballot_event=ballot, last_ballot_event=last_ballot, root=root)
def __call__(self, model, req): query = req.db.query(model).filter(model.id == req.matchdict['id']) custom_query = self.refined_query(query, model, req) if query == custom_query: # no customizations done, apply the defaults if model == common.Contribution: query = query.options( joinedload_all( common.Contribution.valuesets, common.ValueSet.parameter, ), joinedload_all( common.Contribution.valuesets, common.ValueSet.values, common.Value.domainelement), joinedload_all( common.Contribution.references, common.ContributionReference.source), joinedload(common.Contribution.data), ) if model == common.ValueSet: query = query.options( joinedload(common.ValueSet.values), joinedload(common.ValueSet.parameter), joinedload(common.ValueSet.language), ) else: query = custom_query # pragma: no cover return query.one()
def get_attribute_data(attr_ids, node_ids, **kwargs): """ For a given attribute or set of attributes, return all the resources and resource scenarios in the network """ node_attrs = db.DBSession.query(ResourceAttr).\ options(joinedload_all('attr')).\ filter(ResourceAttr.node_id.in_(node_ids), ResourceAttr.attr_id.in_(attr_ids)).all() ra_ids = [] for ra in node_attrs: ra_ids.append(ra.resource_attr_id) resource_scenarios = db.DBSession.query(ResourceScenario).filter( ResourceScenario.resource_attr_id.in_(ra_ids)).options( joinedload('resourceattr')).options( joinedload_all('dataset.metadata')).order_by( ResourceScenario.scenario_id).all() for rs in resource_scenarios: if rs.dataset.hidden == 'Y': try: rs.dataset.check_read_permission(kwargs.get('user_id')) except: rs.dataset.value = None rs.dataset.frequency = None rs.dataset.start_time = None db.DBSession.expunge(rs) return node_attrs, resource_scenarios
def __call__(self, model, req): query = req.db.query(model).filter(model.id == req.matchdict['id']) custom_query = self.refined_query(query, model, req) if query == custom_query: # no customizations done, apply the defaults if model == common.Contribution: query = query.options( joinedload_all( common.Contribution.valuesets, common.ValueSet.parameter, ), joinedload_all(common.Contribution.valuesets, common.ValueSet.values, common.Value.domainelement), joinedload_all(common.Contribution.references, common.ContributionReference.source), joinedload(common.Contribution.data), ) if model == common.ValueSet: query = query.options( joinedload(common.ValueSet.values), joinedload(common.ValueSet.parameter), joinedload(common.ValueSet.language), ) else: query = custom_query # pragma: no cover return query.one()
def query(self, req): self._domainelements = DBSession.query(DomainElement).all() return DBSession.query(Language)\ .order_by(Language.id)\ .options( joinedload_all(Language.valuesets, ValueSet.values), joinedload_all(WalsLanguage.genus, Genus.family))
def by_well_tag(self): well_tag_field = fl.well_tag_field(str(self.form_result['well_tag'])) c.group_by_plate = self.form_result['group_by_plate'] c.tag_id = self.form_result['well_tag'] c.tag_name = Session.query(WellTag).get(c.tag_id).name c.form = h.LiteralFormSelectPatch( value = {'well_tag': well_tag_field['value'], 'group_by_plate': [u'1' if c.group_by_plate else u'0']}, option = {'well_tag': [('--','--')]+well_tag_field['options'], 'group_by_plate': [(u'1', '')]} ) well_tags = Session.query(WellTag).\ filter_by(id=c.tag_id).\ options(joinedload_all(WellTag.tag_wells, QLBWell.plate, QLBPlate.file, innerjoin=True), joinedload_all(WellTag.tag_wells, QLBWell.plate, QLBPlate.plate, innerjoin=True)).\ all() c.label_names = [] if not len(well_tags): c.wells = [] c.well_groups = [] elif c.group_by_plate: wells = sorted(well_tags[0].wells, key=lambda well: (well.plate_id, well.well_name)) well_groups = [(plate, list(wells)) for plate, wells in itertools.groupby(wells, lambda well: well.plate)] c.well_groups = sorted(well_groups, key=lambda tup: tup[0].host_datetime) c.well_groups.reverse() else: c.wells = sorted(well_tags[0].wells, key=lambda well: well.host_datetime) c.wells.reverse() return render('/box2/by_well_tag.html')
def user(request, info, session, userid): u = session\ .query(User)\ .get(int(userid)) if not u: raise Http404 sales = session\ .query(Transline)\ .filter(Transline.user == u)\ .options(joinedload('transaction'), joinedload_all('stockref.stockitem.stocktype.unit'))\ .order_by(desc(Transline.time))[:50] payments = session\ .query(Payment)\ .filter(Payment.user == u)\ .options(joinedload('transaction'), joinedload('paytype'))\ .order_by(desc(Payment.time))[:50] annotations = session\ .query(StockAnnotation)\ .options(joinedload_all('stockitem.stocktype'), joinedload('type'))\ .filter(StockAnnotation.user == u)\ .order_by(desc(StockAnnotation.time))[:50] return ('user.html', { 'tillobject': u, 'tuser': u, 'sales': sales, 'payments': payments, 'annotations': annotations, })
def index(self, *args, **kw): query = Watchdog.by_status(status=u'active')\ .join(Watchdog.recipe).join(Recipe.recipeset).join(RecipeSet.job)\ .order_by(Job.id)\ .options( joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.job), joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.lab_controller), joinedload_all(Watchdog.recipetask, RecipeTask.task)) col = myPaginateDataGrid.Column fields = [ col(name='job_id', getter=lambda x: x.recipe.recipeset.job.link, title="Job ID"), col(name='system_name', getter=lambda x: x.recipe.resource.link, title="System"), col(name='lab_controller', getter=lambda x: x.recipe.recipeset.lab_controller, title="Lab Controller"), col(name='task_name', getter=lambda x: x.recipetask.name_markup if x.recipetask is not None else None, title="Task Name"), col(name='kill_time', getter=lambda x: x.kill_time, title="Kill Time", options=dict(datetime=True)) ] watchdog_grid = myPaginateDataGrid(fields=fields) return dict(title="Watchdogs", grid=watchdog_grid, search_bar=None, list=query)
def show_ballot_room_editor(ballot_id, ballot_type_name, db): ballot_type = db.query(m.BallotType).filter( func.lower(m.BallotType.name) == ballot_type_name.lower()).one() ballot_eventsq = (db.query(m.BallotEvent).join( m.BallotSeason).filter(m.BallotEvent.type == ballot_type).filter( m.BallotSeason.year <= ballot_id).order_by( m.BallotSeason.year.desc()).limit(2)) ballot_events = ballot_eventsq.all() if ballot_events[0].season.year != ballot_id: raise HTTPError( 404, "No {} ballot for the {} season {} {}".format( ballot_type.name, ballot_id, ballot_eventsq, ballot_events)) else: ballot = ballot_events[0] if len(ballot_events) == 2: last_ballot = ballot_events[1] else: last_ballot = None root = db.query(m.Place).options( joinedload_all('children.rooms.listing_for'), joinedload_all('children.children.rooms.listing_for'), joinedload_all('children.children.children.rooms.listing_for'), joinedload_all( 'children.children.children.children.rooms.listing_for'), ).filter(m.Place.parent == None).one() return template('ballot-event-edit-rooms', ballot_event=ballot, last_ballot_event=last_ballot, root=root)
def user(request, info, session, userid): u = session\ .query(User)\ .get(int(userid)) if not u: raise Http404 sales = session\ .query(Transline)\ .filter(Transline.user == u)\ .options(joinedload('transaction'), joinedload_all('stockref.stockitem.stocktype.unit'))\ .order_by(desc(Transline.time))[:50] payments = session\ .query(Payment)\ .filter(Payment.user == u)\ .options(joinedload('transaction'), joinedload('paytype'))\ .order_by(desc(Payment.time))[:50] annotations = session\ .query(StockAnnotation)\ .options(joinedload_all('stockitem.stocktype'), joinedload('type'))\ .filter(StockAnnotation.user == u)\ .order_by(desc(StockAnnotation.time))[:50] return ('user.html', {'tillobject': u, 'tuser': u, 'sales': sales, 'payments': payments, 'annotations': annotations, })
def admin_search_candidates(self): params = self.request.params status = params.get('status') order = params.get('order') q = params.get('q') tags = split_strip(params.get('tags')) basequery = DBSession.query(SearchResultCandidate) \ .options(joinedload_all('languages.language'), joinedload_all('languages.proficiency'), joinedload_all('skills.skill'), joinedload_all('skills.level'), joinedload('preferred_locations'), joinedload('target_position')) if status: status = get_by_name_or_raise(CandidateStatus, status) basequery = basequery.filter(Candidate.status == status) if q: q = q.lower() basequery = basequery.filter( or_(func.lower(Candidate.first_name).startswith(q), func.lower(Candidate.last_name).startswith(q), func.lower(func.concat(Candidate.first_name, " ", Candidate.last_name)).startswith(q), func.lower(Candidate.email).startswith(q))) if tags: basequery = basequery.outerjoin(CandidateSkill).join(Skill).filter(Skill.name.in_(tags)) if order: basequery = add_sorting(basequery, order, CANDIDATE_SORTABLES) return run_paginated_query(self.request, basequery, counter=distinct_counter(SearchResultCandidate.id))
def refined_query(self, query, model, req): if model == common.Contribution: query = query.options( joinedload_all( common.Contribution.valuesets, common.ValueSet.parameter, ), joinedload_all( common.Contribution.valuesets, common.ValueSet.values, common.Value.domainelement), joinedload_all( common.Contribution.valuesets, common.ValueSet.values, common.Value.sentence_assocs, common.ValueSentence.sentence), joinedload(ApicsContribution.language), ) if model == common.Parameter: query = query.options( joinedload_all( common.Parameter.valuesets, common.ValueSet.values, ), joinedload_all( common.Parameter.valuesets, common.ValueSet.language, ), ) return query
def user(request, info, session, userid): try: u = session.query(User).options(joinedload("permissions")).options(joinedload("tokens")).get(int(userid)) except NoResultFound: raise Http404 sales = ( session.query(Transline) .filter(Transline.user == u) .options(joinedload("transaction")) .options(joinedload_all("stockref.stockitem.stocktype.unit")) .order_by(desc(Transline.time))[:50] ) payments = ( session.query(Payment) .filter(Payment.user == u) .options(joinedload("transaction")) .options(joinedload("paytype")) .order_by(desc(Payment.time))[:50] ) annotations = ( session.query(StockAnnotation) .options(joinedload_all("stockitem.stocktype")) .options(joinedload("type")) .filter(StockAnnotation.user == u) .order_by(desc(StockAnnotation.time))[:50] ) return ("user.html", {"tuser": u, "sales": sales, "payments": payments, "annotations": annotations})
def base_query(self, query): query = super(Phonemes, self).base_query(query) if self.parameter: query = query.join(ValueSet.contribution).options( joinedload_all(Value.valueset, ValueSet.language), joinedload_all(Value.valueset, ValueSet.contribution)) return query
def get_one(cls, id_): query = cls.query.options(joinedload(cls.pool), joinedload(cls.storage), joinedload_all('jobs.status'), joinedload_all('jobs.client'), ).get(int(id_)) return super(Media, cls).get_one(query=query)
def list(self): offers = DBSession.query(EmployerOffer).filter(EmployerOffer.employer_id == self.employer.id) \ .options(joinedload_all('candidate.skills.skill'), joinedload_all('candidate.skills.level')) if self.request.params.get('status') == 'active': offers = offers.filter(Offer.by_active(False)) return offers.all()
def reader_history(self, id=None, admin=True): box2 = self.__setup_box2_context_by_code(id) c.admin = admin != 'False' logs = Session.query(Box2Log).filter_by(box2_id=box2.id)\ .order_by('time_effective desc')\ .options(joinedload_all(Box2Log.circuit))\ .all() statuses = Session.query(DRStatusLog).filter_by(box2_id=box2.id)\ .order_by('time_effective desc')\ .options(joinedload_all(DRStatusLog.reporter))\ .all() fixes = Session.query(DRFixLog).filter_by(box2_id=box2.id)\ .order_by('time_effective desc')\ .all() log_pairs = [(logs[i].time_effective, [logs[i],(logs[i+1] if i < len(logs)-1 else None)]) for i in range(len(logs))] for pair in log_pairs: pair[1].append((sorted(box2log_mv.labeleditems(pair[1][0]).items()), sorted(box2log_mv.labeleditems(pair[1][1]).items()))) status_pairs = [(status.time_effective, status) for status in statuses] fix_pairs = [(fix.time_effective, fix) for fix in fixes] changes = log_pairs + status_pairs + fix_pairs c.changes = sorted(changes, key=operator.itemgetter(0)) c.changes.reverse() return render('/admin/reader_history.html')
def _get_ideas_real(discussion, view_def=None, ids=None, user_id=None): user_id = user_id or Everyone # optimization: Recursive widget links. from assembl.models import ( Widget, IdeaWidgetLink, IdeaDescendantsShowingWidgetLink) universal_widget_links = [] by_idea_widget_links = defaultdict(list) widget_links = discussion.db.query(IdeaWidgetLink ).join(Widget).join(Discussion).filter( Widget.test_active(), Discussion.id == discussion.id, IdeaDescendantsShowingWidgetLink.polymorphic_filter() ).options(joinedload_all(IdeaWidgetLink.idea)).all() for wlink in widget_links: if isinstance(wlink.idea, RootIdea): universal_widget_links.append({ '@type': wlink.external_typename(), 'widget': Widget.uri_generic(wlink.widget_id)}) else: for id in wlink.idea.get_all_descendants(True): by_idea_widget_links[Idea.uri_generic(id)].append({ '@type': wlink.external_typename(), 'widget': Widget.uri_generic(wlink.widget_id)}) next_synthesis = discussion.get_next_synthesis() ideas = discussion.db.query(Idea).filter_by( discussion_id=discussion.id ) ideas = ideas.outerjoin(SubGraphIdeaAssociation, and_(SubGraphIdeaAssociation.sub_graph_id==next_synthesis.id, SubGraphIdeaAssociation.idea_id==Idea.id) ) ideas = ideas.outerjoin(IdeaLink, and_(IdeaLink.target_id==Idea.id) ) ideas = ideas.order_by(IdeaLink.order, Idea.creation_date) if ids: ids = [get_database_id("Idea", id) for id in ids] ideas = ideas.filter(Idea.id.in_(ids)) # remove tombstones ideas = ideas.filter(and_(*Idea.base_conditions())) ideas = ideas.options( joinedload_all(Idea.source_links), joinedload_all(Idea.has_showing_widget_links), undefer(Idea.num_children)) permissions = get_permissions(user_id, discussion.id) Idea.prepare_counters(discussion.id, True) retval = [idea.generic_json(view_def, user_id, permissions) for idea in ideas] retval = [x for x in retval if x is not None] for r in retval: if r.get('widget_links', None) is not None: links = r['widget_links'][:] links.extend(universal_widget_links) links.extend(by_idea_widget_links[r['@id']]) r['active_widget_links'] = links return retval
def home(fmt=None, page=1): flags = ( g.db.query(SpamFlag) .order_by(SpamFlag.id.desc()) .options( joinedload_all(SpamFlag.message, Message.chat), joinedload_all(SpamFlag.message, Message.chat_user), joinedload_all(SpamFlag.message, Message.user), ) .offset((page - 1) * 50).limit(50).all() ) if not flags and page != 1: abort(404) flag_count = g.db.query(func.count("*")).select_from(SpamFlag).scalar() if fmt == "json": return jsonify({ "flags": [_.to_dict() for _ in flags], }) paginator = paginate.Page( [], page=page, items_per_page=50, item_count=flag_count, url_maker=lambda page: url_for("spamless2_home", page=page, **request.args), ) return render_template( "admin/spamless2/home.html", flags=flags, paginator=paginator, )
def base_query(self, query): query = query.join(ValueSet).options( joinedload(Value.valueset), joinedload_all(Counterpart.references, CounterpartReference.source) ) if self.language: query = ( query.join(ValueSet.parameter) .join(ValueSet.contribution) .options( joinedload(Value.valueset, ValueSet.contribution), joinedload(Value.valueset, ValueSet.parameter) ) ) return query.filter(ValueSet.language_pk == self.language.pk) if self.parameter: query = ( query.join(ValueSet.language) .outerjoin(LexibankLanguage.family) .options(joinedload_all(Value.valueset, ValueSet.language, LexibankLanguage.family)) ) return query.filter(ValueSet.parameter_pk == self.parameter.pk) if self.contribution: query = query.join(ValueSet.parameter) return query.filter(ValueSet.contribution_pk == self.contribution.pk) return ( query.join(ValueSet.parameter) .join(ValueSet.language) .options(joinedload(Value.valueset, ValueSet.parameter), joinedload(Value.valueset, ValueSet.language)) )
def _floating_ip_get_by_address(context, address, session, load_instances=True, use_first=True, use_baked=False): """This is a port of nova.db.sqlalchemy.api._floating_ip_get_by_address. It includes conditionals which select for the behaviors that are currently within the function vs. alternate behaviors that feature better optimization. """ if use_baked: result = model_query_baked(context, models.FloatingIp, session=session) result.bake(lambda query: query.filter_by( address=bindparam('address'))).params(address=address) else: result = model_query_ordinary( context, models.FloatingIp, session=session).\ filter_by(address=address) if load_instances: # the current source for _floating_ip_get_by_address includes # an unconditional joinedload two levels deep. In the case # of floating_ip_update and most likely floating_ip_fixed_ip_associate, # the rows returned and processed by these joins are not used. # # The overhead of this joinedload is by far the biggest hindrance # to the performance of these two API functions - it multiplies the # measured function call count / time spent by a factor of twelve. # So getting rid of eager loads that aren't needed is a very easy # and effective way to regain significant speed. In this case, # _floating_ip_get_by_address should accept a flag as to whether # the extended load of ->fixed_ip->instance is needed or not. if use_baked: result.bake(lambda query: query.options(joinedload_all('fixed_ip.instance'))) else: result = result.options(joinedload_all('fixed_ip.instance')) if use_baked: result = result.all() if not result: raise Exception("floating ip not found: %s" % address) else: result = result[0] elif use_first: result = result.first() if not result: raise Exception("floating ip not found: %s" % address) else: try: result = result.one() except orm_exc.NoResultFound: raise Exception("floating ip not found: %s" % address) return result
def query(self, req): self._domainelements = DBSession.query(DomainElement).all() return DBSession.query(Language)\ .order_by(Language.id)\ .options( subqueryload_all('languageidentifier', 'identifier'), subqueryload_all('countries'), joinedload_all(Language.valuesets, ValueSet.values), joinedload_all(WalsLanguage.genus, Genus.family))
def changes(request): """ select vs.id, v.updated, h.domainelement_pk, v.domainelement_pk from value_history \ as h, value as v, valueset as vs where h.pk = v.pk and v.valueset_pk = vs.pk; """ # changes in the 2011 edition: check values with an updated date after 2011 and # before 2013 E2009 = utc.localize(datetime(2009, 1, 1)) E2012 = utc.localize(datetime(2012, 1, 1)) E2014 = utc.localize(datetime(2014, 6, 30)) E2015 = utc.localize(datetime(2015, 6, 30)) history = inspect(Value.__history_mapper__).class_ query = DBSession.query(Value)\ .outerjoin(history, Value.pk == history.pk)\ .join(ValueSet)\ .order_by(ValueSet.parameter_pk, ValueSet.language_pk)\ .options(joinedload_all(Value.valueset, ValueSet.language), joinedload_all(Value.valueset, ValueSet.parameter)) changes2011 = query.join(ValueSet.parameter)\ .filter(Parameter.id.contains('A'))\ .filter(Parameter.id != '143A')\ .filter(Parameter.id != '144A')\ .filter(or_( and_(E2009 < Value.updated, Value.updated < E2012), and_(history.updated != None, E2009 < history.updated, history.updated < E2012))) changes2013 = query.filter( or_(and_(E2012 < Value.updated, Value.updated < E2014), and_(E2012 < history.updated, history.updated < E2014))) changes2014 = query.filter( or_(and_(E2014 < Value.updated, Value.updated < E2015), and_(E2014 < history.updated, history.updated < E2015))) # # TODO: # # history = inspect(ValueSet.__history_mapper__).class_ # current = DBSession.query(ValueSet.pk).subquery() # removals2013 = DBSession.query(Parameter.id, Parameter.name, count(history.pk))\ # .filter(Parameter.pk == history.parameter_pk)\ # .filter(not_(history.pk.in_(current)))\ # .group_by(Parameter.pk, Parameter.id, Parameter.name)\ # .order_by(Parameter.pk) grouped = lambda changes: groupby([v.valueset for v in changes2011], lambda vs: vs.parameter) return { 'changes2011': grouped(changes2011), 'changes2013': grouped(changes2013), 'changes2014': grouped(changes2014), 'removals2013': [] }
def stockline(request, info, session, stocklineid): s = session\ .query(StockLine)\ .options(joinedload_all('stockonsale.stocktype.unit'), joinedload_all('stockonsale.delivery'), undefer_qtys('stockonsale'))\ .get(int(stocklineid)) if not s: raise Http404 return ('stockline.html', {'stockline': s})
def delivery(request, info, session, deliveryid): d = session\ .query(Delivery)\ .options(joinedload_all('items.stocktype.unit'), joinedload_all('items.stockline'), undefer_qtys('items'))\ .get(int(deliveryid)) if not d: raise Http404 return ('delivery.html', {'delivery': d})
def get_test_template_by_id(self, id_): # type: (int) -> TestTemplate res = self._db.query(TestTemplate).options( joinedload_all(TestTemplate.questions, TestQuestionTemplate.solution_code_snippet, CodeSnippet.function, Function.arguments), joinedload_all(TestTemplate.questions, TestQuestionTemplate.solution_code_snippet, CodeSnippet.function, Function.testing_input)) \ .filter(TestTemplate.id == id_) \ .first() return res
def pubroot(request, info, session): date = datetime.date.today() # If it's the early hours of the morning, it's more useful for us # to consider it still to be yesterday. if datetime.datetime.now().hour < 4: date = date - datetime.timedelta(1) thisweek_start = date - datetime.timedelta(date.weekday()) thisweek_end = thisweek_start + datetime.timedelta(6) lastweek_start = thisweek_start - datetime.timedelta(7) lastweek_end = thisweek_end - datetime.timedelta(7) weekbefore_start = lastweek_start - datetime.timedelta(7) weekbefore_end = lastweek_end - datetime.timedelta(7) weeks = [ ("Current week", thisweek_start, thisweek_end, business_totals(session, thisweek_start, thisweek_end)), ("Last week", lastweek_start, lastweek_end, business_totals(session, lastweek_start, lastweek_end)), ( "The week before last", weekbefore_start, weekbefore_end, business_totals(session, weekbefore_start, weekbefore_end), ), ] currentsession = Session.current(session) barsummary = ( session.query(StockLine) .filter(StockLine.location == "Bar") .order_by(StockLine.dept_id, StockLine.name) .options(joinedload_all("stockonsale.stocktype.unit")) .options(undefer_group("qtys")) .all() ) stillage = ( session.query(StockAnnotation) .join(StockItem) .outerjoin(StockLine) .filter( tuple_(StockAnnotation.text, StockAnnotation.time).in_( select( [StockAnnotation.text, func.max(StockAnnotation.time)], StockAnnotation.atype == "location" ).group_by(StockAnnotation.text) ) ) .filter(StockItem.finished == None) .order_by(StockLine.name != null(), StockAnnotation.time) .options(joinedload_all("stockitem.stocktype.unit")) .options(joinedload_all("stockitem.stockline")) .options(undefer_group("qtys")) .all() ) return ( "index.html", {"currentsession": currentsession, "barsummary": barsummary, "stillage": stillage, "weeks": weeks}, )
def changes(request): """ select vs.id, v.updated, h.domainelement_pk, v.domainelement_pk from value_history \ as h, value as v, valueset as vs where h.pk = v.pk and v.valueset_pk = vs.pk; """ # changes in the 2011 edition: check values with an updated date after 2011 and # before 2013 E2009 = utc.localize(datetime(2009, 1, 1)) E2012 = utc.localize(datetime(2012, 1, 1)) E2014 = utc.localize(datetime(2014, 6, 30)) E2015 = utc.localize(datetime(2015, 6, 30)) history = inspect(Value.__history_mapper__).class_ query = DBSession.query(Value)\ .outerjoin(history, Value.pk == history.pk)\ .join(ValueSet)\ .order_by(ValueSet.parameter_pk, ValueSet.language_pk)\ .options(joinedload_all(Value.valueset, ValueSet.language), joinedload_all(Value.valueset, ValueSet.parameter)) changes2011 = query.join(ValueSet.parameter)\ .filter(Parameter.id.contains('A'))\ .filter(Parameter.id != '143A')\ .filter(Parameter.id != '144A')\ .filter(or_( and_(E2009 < Value.updated, Value.updated < E2012), and_(history.updated != None, E2009 < history.updated, history.updated < E2012))) changes2013 = query.filter(or_( and_(E2012 < Value.updated, Value.updated < E2014), and_(E2012 < history.updated, history.updated < E2014))) changes2014 = query.filter(or_( and_(E2014 < Value.updated, Value.updated < E2015), and_(E2014 < history.updated, history.updated < E2015))) # # TODO: # # history = inspect(ValueSet.__history_mapper__).class_ # current = DBSession.query(ValueSet.pk).subquery() # removals2013 = DBSession.query(Parameter.id, Parameter.name, count(history.pk))\ # .filter(Parameter.pk == history.parameter_pk)\ # .filter(not_(history.pk.in_(current)))\ # .group_by(Parameter.pk, Parameter.id, Parameter.name)\ # .order_by(Parameter.pk) grouped = lambda changes: groupby([v.valueset for v in changes2011], lambda vs: vs.parameter) return { 'changes2011': grouped(changes2011), 'changes2013': grouped(changes2013), 'changes2014': grouped(changes2014), 'removals2013': []}
def battles(clan): """ Table of all battles of a clan. :param clan: :return: """ if not clan in config.CLAN_NAMES: abort(404) battles = Battle.query.options(joinedload_all('battle_group.battles')).options( joinedload_all('attendances.player')).filter_by(clan=clan).all() return render_template('battles/battles.html', clan=clan, battles=battles)
def _get_scenario(scenario_id, include_data=True, include_items=True): try: scenario_qry = DBSession.query(Scenario).filter(Scenario.scenario_id==scenario_id) if include_data is True: scenario_qry = scenario_qry.options(joinedload_all('resourcescenarios')) if include_items is True: scenario_qry = scenario_qry.options(joinedload_all('resourcegroupitems')) scenario = scenario_qry.one() return scenario except NoResultFound: raise ResourceNotFoundError("Scenario %s does not exist."%(scenario_id))
def get_quality_wells(weeks_ago=0, channels=False): exclude_quality_names = ('3nM FAM, 3nM VIC','3 nM FAM, 0 nM VIC','Stealth','0 nM FAM, 3 nM VIC') # exclude beta apps plates (not sure what they are, not labeled systematically) beta_app_setup = Session.query(PlateType).filter_by(code='bapp').first() wells = analyzed_well_query( setup_filter(weeks_ago_filter(weeks_ago)).all(), channels=channels ).filter(Plate.plate_type_id != beta_app_setup.id).\ options(joinedload_all(QLBWell.plate, QLBPlate.plate, Plate.plate_type, innerjoin=True), joinedload_all(QLBWell.plate, QLBPlate.plate, Plate.box2, innerjoin=True)).all() return [w for w in wells if w.sample_name not in exclude_quality_names]
def _modify_details_query(self, cursor, params): fields = self._get_show_fields(params) if not fields: return cursor if fields and 'data_fields' in fields: cursor = cursor.options(joinedload_all(Model.datasets)) if fields and 'features' in fields: cursor = cursor.options(joinedload_all(Model.features_set)) cursor = cursor.options(undefer(Model.classifier)) return cursor
def _execute(self, uow): self._session = uow.session item = self._session.query(db.Item)\ .options(joinedload_all('data_ref'))\ .options(joinedload_all('item_tags.tag'))\ .options(joinedload_all('item_fields.field'))\ .get(self.__itemId) if item is None: raise err.NotFoundError() self._session.expunge(item) return item
def delivery(request, info, session, deliveryid): try: d = ( session.query(Delivery) .filter_by(id=int(deliveryid)) .options(joinedload_all("items.stocktype.unit")) .options(joinedload_all("items.stockline")) .options(undefer_group("qtys")) .one() ) except NoResultFound: raise Http404 return ("delivery.html", {"delivery": d})
def by_number(cls, number, eager=True): q = cls.query.filter_by(number=number) if eager: q = q.options( orm.joinedload_all('items.image'), orm.joinedload_all('templates.images'), orm.joinedload_all('items.category.image'), orm.joinedload_all('items.category.subcategories'), ) try: return q.one() except NoResultFound: return None
def prime_cache(args): """If data needs to be denormalized for lookup, do that here. This procedure should be separate from the db initialization, because it will have to be run periodiucally whenever data has been updated. """ q = DBSession.query(models.Doculect)\ .order_by(models.Doculect.pk)\ .options( joinedload_all(common.Language.valuesets, common.ValueSet.values), joinedload_all(common.Language.valuesets, common.ValueSet.parameter) ) for doculect in q: doculect.txt = doculect.to_txt()
def delivery(request, info, session, deliveryid): d = session\ .query(Delivery)\ .options(joinedload_all('items.stocktype.unit'), joinedload_all('items.stockline'), undefer_qtys('items'))\ .get(int(deliveryid)) if not d: raise Http404 return ('delivery.html', { 'tillobject': d, 'delivery': d, })
def __batch(self, id=None): if id is None: return None return ( Session.query(ConsumableBatch) .filter(ConsumableBatch.id == int(id)) .options( joinedload_all(ConsumableBatch.consumable_molding_style), joinedload_all(ConsumableBatch.consumable_bonding_style), ) .first() )
def stockline(request, info, session, stocklineid): s = session\ .query(StockLine)\ .options(joinedload_all('stockonsale.stocktype.unit'), joinedload_all('stockonsale.delivery'), undefer_qtys('stockonsale'))\ .get(int(stocklineid)) if not s: raise Http404 return ('stockline.html', { 'tillobject': s, 'stockline': s, })
def forMovie(self, id = None): db = get_session() releases_raw = db.query(Relea) \ .options(joinedload_all('info')) \ .options(joinedload_all('files')) \ .filter(Relea.movie_id == id) \ .all() releases = [r.to_dict({'info':{}, 'files':{}}) for r in releases_raw] releases = sorted(releases, key = lambda k: k['info'].get('score', 0), reverse = True) return releases
def stock(request, info, session, stockid): s = session\ .query(StockItem)\ .options(joinedload_all('stocktype.department'), joinedload_all('stocktype.stockline_log.stockline'), joinedload_all('delivery.supplier'), joinedload_all('stockunit.unit'), joinedload_all('annotations.type'), subqueryload_all('out.transline.transaction'), undefer_group('qtys'))\ .get(int(stockid)) if not s: raise Http404 return ('stock.html', {'stock': s})
def _get_domain(self, domainid): "utility to return domain" try: cachekey = 'domain-%s' % domainid qry = Session.query(Domain).filter(Domain.id == domainid)\ .options(joinedload_all(Domain.servers), joinedload_all(Domain.aliases), joinedload_all(Domain.authservers))\ .options(FromCache('sql_cache_med', cachekey)) if self.invalidate: qry.invalidate() domain = qry.one() except NoResultFound: domain = None return domain
def _get_scenario(scenario_id, include_data=True, include_items=True): try: scenario_qry = DBSession.query(Scenario).filter( Scenario.scenario_id == scenario_id) if include_data is True: scenario_qry = scenario_qry.options( joinedload_all('resourcescenarios')) if include_items is True: scenario_qry = scenario_qry.options( joinedload_all('resourcegroupitems')) scenario = scenario_qry.one() return scenario except NoResultFound: raise ResourceNotFoundError("Scenario %s does not exist." % (scenario_id))
def department(request, info, session, departmentid, as_spreadsheet=False): d = session\ .query(Department)\ .get(int(departmentid)) if d is None: raise Http404 include_finished = request.GET.get("show_finished", "off") == "on" items = session\ .query(StockItem)\ .join(StockType)\ .filter(StockType.department == d)\ .order_by(desc(StockItem.id))\ .options(joinedload_all('stocktype.unit'), undefer_group('qtys'), joinedload('stockline'), joinedload('delivery'), joinedload('finishcode')) if not include_finished: items = items.filter(StockItem.finished == None) if as_spreadsheet: return spreadsheets.stock(session, items.all(), tillname=info['tillname'], filename="{}-dept{}-stock.ods".format( info['tillname'], departmentid)) pager = Pager(request, items) return ('department.html', { 'department': d, 'pager': pager, 'include_finished': include_finished })
def get_scenario_data(scenario_id, **kwargs): """ Get all the datasets from the group with the specified name @returns a list of dictionaries """ user_id = kwargs.get('user_id') scenario_data = DBSession.query(Dataset).filter( Dataset.dataset_id == ResourceScenario.dataset_id, ResourceScenario.scenario_id == scenario_id).options( joinedload_all('metadata')).distinct().all() for sd in scenario_data: if sd.hidden == 'Y': try: sd.check_read_permission(user_id) except: sd.value = None sd.frequency = None sd.start_time = None sd.metadata = [] DBSession.expunge_all() log.info("Retrieved %s datasets", len(scenario_data)) return scenario_data
def test_option_against_multi_nonexistent_basestring(self): Item = self.classes.Item Keyword = self.classes.Keyword self._assert_eager_with_entity_exception( [Keyword, Item], (joinedload_all("description"), ), r"Can't find property named 'description' on the mapped " r"entity Mapper\|Keyword\|keywords in this Query.")
def test_option_against_multi_non_relation_twolevel_basestring(self): Item = self.classes.Item Keyword = self.classes.Keyword self._assert_eager_with_entity_exception( [Keyword, Item], (joinedload_all("id", "keywords"), ), r"Attribute 'id' of entity 'Mapper\|Keyword\|keywords' " "does not refer to a mapped entity")
def geo(args): with_session(args) fname = args.pkg_dir.joinpath('static', 'download', 'languages-and-dialects-geo.csv') with transaction.manager, UnicodeWriter(fname) as writer: writer.writerow([ 'glottocode', 'name', 'isocodes', 'level', 'macroarea', 'latitude', 'longitude' ]) for l in DBSession.query(models.Languoid)\ .filter(or_( models.Languoid.level == models.LanguoidLevel.dialect, models.Languoid.level == models.LanguoidLevel.language))\ .options( joinedload(models.Languoid.macroareas), joinedload_all( common.Language.languageidentifier, common.LanguageIdentifier.identifier))\ .order_by(common.Language.name): writer.writerow([ l.id, l.name, ' '.join( i.name for i in l.get_identifier_objs(common.IdentifierType.iso)), l.level, l.macroareas[0].name if l.macroareas else '', l.latitude if l.latitude is not None else '', l.longitude if l.longitude is not None else '' ]) args.log.info('{0} written'.format(fname))
def test_option_against_multi_no_entities_basestring(self): Item = self.classes.Item Keyword = self.classes.Keyword self._assert_eager_with_entity_exception( [Keyword.id, Item.id], (joinedload_all("keywords"), ), r"Query has only expression-based entities - can't find property " "named 'keywords'.")
def base_query(self, query): query = DBSession.query(self.model)\ .join(ValueSet)\ .options(joinedload_all( Value.valueset, ValueSet.references, ValueSetReference.source )).distinct() if not self.parameter: query = query.join(ValueSet.parameter).filter(Parameter.id != '0') if self.ftype: query = query.filter(Feature.feature_type == self.ftype) if self.language: return query\ .options(joinedload(Value.domainelement))\ .filter(ValueSet.language_pk.in_( [l.pk for l in [self.language] + self.language.lects])) if self.parameter: query = query.join(ValueSet.contribution)\ .join(self.vs_lang, ValueSet.language_pk == self.vs_lang.pk)\ .join(self.vs_lect, ValueSet.language_pk == self.vs_lect.pk)\ .join(DomainElement)\ .options(joinedload(Value.domainelement)) return query.filter(ValueSet.parameter_pk == self.parameter.pk) return query
def test_option_against_wrong_multi_entity_type_attr_one(self): Item = self.classes.Item Keyword = self.classes.Keyword self._assert_eager_with_entity_exception( [Keyword, Item], (joinedload_all(Keyword.id, Item.keywords), ), r"Attribute 'id' of entity 'Mapper\|Keyword\|keywords' " "does not refer to a mapped entity")
def get_departure_schedule(cls, session, stop_id, date=None, route_id=None, limit=None): ''' helper routine which returns the stop schedule for a give date ''' from gtfsdb.model.trip import Trip # step 0: make sure we have a valid date if date is None: date = datetime.date.today() # step 1: get stop times based on date log.info("QUERY StopTime") q = session.query(StopTime) q = q.filter_by(stop_id=stop_id) q = q.filter(StopTime.departure_time != None) q = q.filter(StopTime.trip.has(Trip.universal_calendar.any(date=date))) # step 2: apply an optional route filter if route_id: q = q.filter(StopTime.trip.has(Trip.route_id == route_id)) # step 3: order the stop times q = q.order_by(StopTime.departure_time) # step 4: options to speed up /q q = q.options(joinedload_all('trip')) if limit: q = q.limit(limit) ret_val = q.all() return ret_val
def base_query(self, query): return query\ .join(ContributionContributor)\ .join(Contributor)\ .distinct()\ .options(joinedload_all( Contribution.contributor_assocs, ContributionContributor.contributor))
def _build_list_query(self, params, **kwargs): # TODO: What about joins? cursor = self._set_list_query_opts(self.Model.query, params) filter_params = self._prepare_filter_params(params) filter_params.update(kwargs) fields = self._get_show_fields(params) if fields: opts = [] for field in self.Model.__table__.columns.keys(): if field in fields or field in ('id', ): opts.append(undefer(getattr(self.Model, field))) else: opts.append(defer(getattr(self.Model, field))) relation_properties = filter( lambda p: isinstance(p, properties.RelationshipProperty), self.Model.__mapper__.iterate_properties) for field in relation_properties: if field.key in fields: cursor = cursor.options( joinedload_all(getattr(self.Model, field.key))) if opts: cursor = cursor.options(*opts) for name, val in filter_params.iteritems(): fltr = self._build_query_item(name, val) if fltr is not None: cursor = cursor.filter(fltr) return cursor