def by_filter(cls, session, opts, **kwargs): """ Get packages from given filters. :param session: SQLAlchemy session :type session: :class:`sqlalchemy.Session` :param opts: filtering options :type opts: `dict :return: package instances :rtype: generator of :class:`pyshop.models.Package` """ where = [] if opts.get('local_only'): where.append(cls.local == True) if opts.get('names'): where.append(cls.name.in_(opts['names'])) if opts.get('classifiers'): ids = [c.id for c in opts.get('classifiers')] cls_pkg = classifier__package qry = session.query(cls_pkg.c.package_id, func.count('*')) qry = qry.filter(cls_pkg.c.classifier_id.in_(ids)) qry = qry.group_by(cls_pkg.c.package_id) qry = qry.having(func.count('*') >= len(ids)) where.append(cls.id.in_([r[0] for r in qry.all()])) return cls.find(session, where=where, **kwargs)
def clientstats(db): startdatestring = request.json['startdate'] enddatestring = request.json['enddate'] dateformat = '%d-%m-%Y' startdate = datetime.strptime(startdatestring, dateformat) enddate = datetime.strptime(enddatestring, dateformat) selectedRequestsBrowser = db.query(Request.browsername, func.count(Request.browsername)).filter_by(isPageview=True).filter(Request.datetime.between(startdate, enddate)).group_by(Request.browsername) selectedRequestsPlatform = db.query(Request.platformname, func.count(Request.platformname)).filter_by(isPageview=True).filter(Request.datetime.between(startdate, enddate)).group_by(Request.platformname) returnDict = {'postdata' : request.json, 'returndata' : {'browserstats' : [], 'platformstats' : []}} for selectedRequestBrowser in selectedRequestsBrowser: tempDict = {'browser' : selectedRequestBrowser[0], 'pageviews' : selectedRequestBrowser[1]} returnDict['returndata']['browserstats'].append(tempDict) for selectedRequestPlatform in selectedRequestsPlatform: tempDict = {'platform' : selectedRequestPlatform[0], 'pageviews' : selectedRequestPlatform[1]} returnDict['returndata']['platformstats'].append(tempDict) return returnDict
def dataset_counts(cls, datasets_q): sq = datasets_q.subquery() q = select([cls.code, func.count(cls.dataset_id)], group_by=cls.code, order_by=func.count(cls.dataset_id).desc()) q = q.where(cls.dataset_id == sq.c.id) return db.session.bind.execute(q).fetchall()
def test_migrate_batch_stureg(self): batch_guid = '2bb942b9-75cf-4055-a67a-8b9ab53a9dfc' batch = {UdlStatsConstants.REC_ID: '6', UdlStatsConstants.BATCH_GUID: batch_guid, UdlStatsConstants.TENANT: self.__tenant, UdlStatsConstants.SCHEMA_NAME: None, Constants.DEACTIVATE: False, UdlStatsConstants.LOAD_TYPE: LoadType.STUDENT_REGISTRATION, UdlStatsConstants.BATCH_OPERATION: 's', UdlStatsConstants.SNAPSHOT_CRITERIA: '{"reg_system_id": "015247bd-058c-48cd-bb4d-f6cffe5b40c1", "academic_year": 2015}'} self.insert_into_udl_stats(batch[UdlStatsConstants.REC_ID], batch_guid, self.__tenant, batch[UdlStatsConstants.LOAD_TYPE]) preprod_conn = EdMigrateSourceConnection(tenant=get_unittest_preprod_tenant_name()) count_to_source_query = select([func.count()]).select_from(preprod_conn.get_table(Constants.STUDENT_REG)) count_to_be_inserted = preprod_conn.execute(count_to_source_query).fetchall()[0][0] self.assertEqual(10, count_to_be_inserted) prod_conn = EdMigrateDestConnection(tenant=get_unittest_preprod_tenant_name()) student_reg_table = prod_conn.get_table(Constants.STUDENT_REG) count_query = select([func.count()]).select_from(student_reg_table) count_before = prod_conn.execute(count_query).fetchall()[0][0] self.assertEqual(2581, count_before) count_snapshot_query = select([func.count()], student_reg_table.c.academic_year == 2015).select_from(student_reg_table) count_to_be_deleted = prod_conn.execute(count_snapshot_query).fetchall()[0][0] self.assertEqual(1217, count_to_be_deleted) rtn = migrate_batch(batch) self.assertTrue(rtn) expected_count_after = count_before - count_to_be_deleted + count_to_be_inserted count_after = prod_conn.execute(count_query).fetchall()[0][0] self.assertEqual(expected_count_after, count_after)
def get_community_tags(self, item=None, limit=None): """Returns community tags for an item.""" # Get item-tag association class. item_class = item.__class__ item_tag_assoc_class = self.get_tag_assoc_class(item_class) if not item_tag_assoc_class: return [] # Build select statement. cols_to_select = [item_tag_assoc_class.table.c.tag_id, func.count('*')] from_obj = item_tag_assoc_class.table.join(item_class.table).join(galaxy.model.Tag.table) where_clause = (self.get_id_col_in_item_tag_assoc_table(item_class) == item.id) order_by = [func.count("*").desc()] group_by = item_tag_assoc_class.table.c.tag_id # Do query and get result set. query = select(columns=cols_to_select, from_obj=from_obj, whereclause=where_clause, group_by=group_by, order_by=order_by, limit=limit) result_set = self.sa_session.execute(query) # Return community tags. community_tags = [] for row in result_set: tag_id = row[0] community_tags.append(self.get_tag_by_id(tag_id)) return community_tags
def dataset_counts(cls, datasets): ds_ids = [d.id for d in datasets] if not len(ds_ids): return [] q = select([cls.code, func.count(cls.dataset_id)], cls.dataset_id.in_(ds_ids), group_by=cls.code, order_by=func.count(cls.dataset_id).desc()) return db.session.bind.execute(q).fetchall()
def top_workouts(self): this_client_id = self.id counts_time_length_workouts = db.session.query(func.count(Time_Length_Workout.id),Time_Length_Workout.name).group_by(Time_Length_Workout.name).all() counts_time_length_workouts = [ (w[0],w[1],'Time_Length_Workout') for w in counts_time_length_workouts] counts_rep_set_workouts = db.session.query(func.count(Rep_Set_Workout.id),Rep_Set_Workout.name).group_by(Rep_Set_Workout.name).all() counts_rep_set_workouts = [ (w[0],w[1],'Rep_Set_Workout') for w in counts_time_length_workouts] counts_workouts = counts_time_length_workouts + counts_rep_set_workouts top_workouts = sorted(counts_workouts, key=lambda x: x[0]) return top_workouts[0:3]
def calculate_score(self, educatives, seminar): total_score = 0 score = self.score ken_count = session.query(Educative.hug_id, Educative.ken_id, func.count(Educative.ken_id)).group_by(Educative.hug_id, Educative.ken_id).all() for ken in ken_count: total_score += (ken[2] ** 2) * score second_ken_count = session.query(Educative.hug_id, Educative.second_ken_id, func.count(Educative.second_ken_id)).group_by(Educative.hug_id, Educative.second_ken_id).all() for second_ken in second_ken_count: total_score += (second_ken[2] ** 2) * score return total_score
def PageContent(self): results = dict() storySubselect = self.session.query(func.count(Story.idstory).label('storycount')).group_by(Story.categoryid).add_column(Story.categoryid).subquery() charSubselect = self.session.query(func.count(Character.idcharacter).label('charactercount')).group_by(Character.categoryid).add_column(Character.categoryid).subquery() query = self.session.query(Category, storySubselect.c.storycount, charSubselect.c.charactercount).order_by(Category.name) query = query.join((storySubselect, Category.idcategory == storySubselect.c.categoryid)) query = query.join((charSubselect, Category.idcategory == charSubselect.c.categoryid)) results['categories'] = [(r[0].name, r[1], r[2], self.request.route_url("search", category=r[0].idcategory)) for r in query] numPerRow = 3 results['numPerRow'] = numPerRow results['rows'] = (len(results['categories'])/numPerRow) + 1 return results
def quotas(connection, target): allowed = target.owner.org.tier.cron_jobs current = connection.scalar( select([func.count(distinct(Job.id))]).where(and_( Job.owner_id == target.owner_id, Job.enabled == True))) # noqa return allowed, current
def test_migrate_fact_asmt_outcome_vw(self): preprod_conn = EdMigrateSourceConnection(tenant=get_unittest_preprod_tenant_name()) prod_conn = EdMigrateDestConnection(tenant=get_unittest_prod_tenant_name()) batch_guid = "288220EB-3876-41EB-B3A7-F0E6C8BD013B" fact_asmt_outcome_table = prod_conn.get_table(Constants.FACT_ASMT_OUTCOME) query = select([func.count().label('asmt_outcome_vw_rec_ids')], fact_asmt_outcome_table.c.asmt_outcome_vw_rec_id.in_([1000000776, 1000001034, 1000001112])) query_c = query.where(fact_asmt_outcome_table.c.rec_status == 'C') query_d = query.where(fact_asmt_outcome_table.c.rec_status == 'D') query_I = query.where(fact_asmt_outcome_table.c.rec_status == 'I') rset = prod_conn.execute(query_c) row = rset.fetchone() self.assertEqual(3, row['asmt_outcome_vw_rec_ids']) rset.close() delete_count, insert_count = migrate_table(batch_guid, None, preprod_conn, prod_conn, 'fact_asmt_outcome_vw', False) self.assertEqual(3, delete_count) self.assertEqual(3, insert_count) rset = prod_conn.execute(query_c) row = rset.fetchone() self.assertEqual(0, row['asmt_outcome_vw_rec_ids']) rset.close() rset = prod_conn.execute(query_d) row = rset.fetchone() self.assertEqual(3, row['asmt_outcome_vw_rec_ids']) rset.close() # The deactivation count will be always zero in unit test rset = prod_conn.execute(query_I) row = rset.fetchone() self.assertEqual(0, row['asmt_outcome_vw_rec_ids']) rset.close()
def test_migrate_student_reg(self): Unittest_with_edcore_sqlite.setUpClass(EdMigrateDestConnection.get_datasource_name(TestMigrate.test_tenant), use_metadata_from_db=False) preprod_conn = EdMigrateSourceConnection(tenant=get_unittest_preprod_tenant_name()) prod_conn = EdMigrateDestConnection(tenant=get_unittest_prod_tenant_name()) batch_guid = "0aa942b9-75cf-4055-a67a-8b9ab53a9dfc" student_reg_table = preprod_conn.get_table(Constants.STUDENT_REG) get_query = select([student_reg_table.c.student_reg_rec_id]).order_by(student_reg_table.c.student_reg_rec_id) count_query = select([func.count().label('student_reg_rec_ids')], student_reg_table.c.student_reg_rec_id.in_(range(15541, 15551))) rset = preprod_conn.execute(get_query) row = rset.fetchall() self.assertEqual(10, len(row)) self.assertListEqual([(15541,), (15542,), (15543,), (15544,), (15545,), (15546,), (15547,), (15548,), (15549,), (15550,)], row) rset.close() rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(0, row['student_reg_rec_ids']) rset.close() delete_count, insert_count = migrate_table(batch_guid, None, preprod_conn, prod_conn, 'student_reg', False) self.assertEqual(0, delete_count) self.assertEqual(10, insert_count) rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(10, row['student_reg_rec_ids']) rset.close()
def calculate_score(self, educatives, seminar): total_score = 0 gender_count = session.query(Educative.hug_id, Educative.gender, func.count(Educative.gender)).group_by(Educative.hug_id, Educative.gender).all() for hug_gender in gender_count: count = hug_gender[2] total_score += (count ** 2) * self.score return total_score
def bioconcept_interaction_starter(): nex_session = nex_session_maker() id_to_bioentity = dict([(x.id, x) for x in nex_session.query(Locus).all()]) id_to_bioconcept = dict([(x.id, x) for x in nex_session.query(Bioconcept).all()]) bad_interactors = set([x.id for x in nex_session.query(Bioconcept).filter(Bioconcept.format_name.in_({'vegetative_growth', 'haploinsufficient', 'viable', 'heat_sensitivity', 'toxin_resistance', 'chronological_lifespan', 'competitive_fitness', 'desiccation_resistance', 'resistance_to_cycloheximide', 'resistance_to_methyl_methanesulfonate', 'resistance_to_sirolimus', 'vacuolar_morphology', 'inviable'})).all()]) #Go for row in nex_session.query(Goevidence.locus_id, Goevidence.go_id, func.count(Goevidence.id)).filter(Goevidence.annotation_type != 'computational').group_by(Goevidence.locus_id, Goevidence.go_id).all(): go = id_to_bioconcept[row[1]] locus = id_to_bioentity[row[0]] if go.go_aspect == 'biological process' and go.id not in bad_interactors: yield {'interaction_type': 'GO', 'evidence_count': row[2], 'bioentity': locus, 'interactor': go} #Phenotype for row in nex_session.query(Phenotypeevidence.locus_id, Phenotypeevidence.phenotype_id, func.count(Phenotypeevidence.id)).group_by(Phenotypeevidence.locus_id, Phenotypeevidence.phenotype_id).all(): observable = id_to_bioconcept[row[1]].observable locus = id_to_bioentity[row[0]] if observable.id not in bad_interactors: yield {'interaction_type': 'PHENOTYPE', 'evidence_count': row[2], 'bioentity': locus, 'interactor': observable} nex_session.close()
def build_query(cls, session, join=None, where=None, order_by=None, limit=None, offset=None, count=None): if count is not None: query = session.query(func.count(count)).select_from(cls) else: query = session.query(cls) if join: if isinstance(join, (list, tuple)): for j in join: query = query.join(j) else: query = query.join(join) if where: for filter in where: query = query.filter(filter) if order_by is not None: if isinstance(order_by, (list, tuple)): query = query.order_by(*order_by) else: query = query.order_by(order_by) if limit: query = query.limit(limit) if offset: query = query.offset(offset) return query
def get_sub_entries_count(self, identity = None, exclude_identity = None): """ Returns the number of child entries of this instance. :param offset: SQLAlchemy query offset :param limit: SQLAlchemy query limit :param identity: Count only DataLinker children of the given identity :param exclude_identity: Count only DataLinker children not be of the given identity :return: (int) Number of child entries :since: v0.2.00 """ if (self.log_handler is not None): self.log_handler.debug("#echo(__FILEPATH__)# -{0!r}.get_sub_entries_count()- (#echo(__LINE__)#)", self, context = "pas_datalinker") if (identity is None and exclude_identity is None): _return = self.get_data_attributes("sub_entries")['sub_entries'] elif (identity is not None and exclude_identity is not None): raise ValueException("Defining both an identity and to exclude an identity is not supported") else: with self: db_query = self.local.db_instance.rel_children.with_entities(sql.count(_DbDataLinker.id)) if (identity is not None): db_query = db_query.filter(_DbDataLinker.identity == identity) elif (exclude_identity is not None): db_query = db_query.filter(_DbDataLinker.identity != exclude_identity) db_query = DataLinker._db_apply_id_site_condition(db_query) _return = db_query.scalar() # # return _return
def get_searches_ordered_by_label_matches( labels, searcher_role, searching_for_role, offset_number=0, max_number=10): if offset_number > 0: offset_number *= max_number labelnames = [label.name.lower() for label in labels] query = store.session.query(Search, func.count(Label.id).label('matches')) query = query.join(Search.labels) query = query.filter(func.lower(Label.name).in_(labelnames)) query = query.filter(Search.active==True) query = query.filter(Search.searcher_role==searcher_role) query = query.filter(Search.searching_for_role==searching_for_role) query = query.join(Search.searcher_user) query = query.filter(User.email_confirmed==True) query = query.filter(User.active==True) query = query.group_by(Search.id) query = query.order_by('matches DESC') searches_and_count = query.offset(offset_number).limit(max_number) searches = [sc[0] for sc in searches_and_count] return searches
def get_number_solved_subquery(): """ Get a subquery that returns how many teams have solved a challenge. Example usage: .. code-block:: python number_of_solved_subquery = get_number_solved_subquery() challenge_query = (DBSession.query(Challenge, number_of_solved_subquery) Here we query for a list of all challenges and additionally fetch the number of times it has been solved. This subquery will use the outer challenge to correlate on, so make sure to provide one or this query makes no sense. """ from fluxscoreboard.models import dynamic_challenges query = (DBSession.query(func.count('*')). filter(Challenge.id == Submission.challenge_id). correlate(Challenge).as_scalar()) for name, module in dynamic_challenges.registry.items(): dyn_cnt = module.solved_count_query().filter(Challenge.module == name) query = query + dyn_cnt.as_scalar() return query.label("solved_count")
def get_staging_demographic_counts(self): demographics = ['hispanicorlatinoethnicity', 'americanindianoralaskanative', 'asian', 'blackorafricanamerican', 'nativehawaiianorotherpacificislander', 'white', 'demographicracetwoormoreraces', 'ideaindicator', 'lepstatus', 'section504status', 'economicdisadvantagestatus', 'migrantstatus'] results_dict = {} with get_udl_connection() as conn: stg_outcome = conn.get_table('stg_sbac_asmt_outcome') for entry in demographics: query = select([func.count(stg_outcome.c[entry])], from_obj=stg_outcome).where(stg_outcome.c[entry].in_(['Y', 'y', 'yes'])) result = conn.execute(query) for row in result: demo_count = row[0] results_dict[entry] = demo_count corrleated_results = { 'dmg_eth_hsp': results_dict['hispanicorlatinoethnicity'], 'dmg_eth_ami': results_dict['americanindianoralaskanative'], 'dmg_eth_asn': results_dict['asian'], 'dmg_eth_blk': results_dict['blackorafricanamerican'], 'dmg_eth_pcf': results_dict['nativehawaiianorotherpacificislander'], 'dmg_eth_wht': results_dict['white'], 'dmg_eth_2om': results_dict['demographicracetwoormoreraces'], 'dmg_prg_iep': results_dict['ideaindicator'], 'dmg_prg_lep': results_dict['lepstatus'], 'dmg_prg_504': results_dict['section504status'], 'dmg_sts_ecd': results_dict['economicdisadvantagestatus'], 'dmg_sts_mig': results_dict['migrantstatus'], } return corrleated_results
def rank(self): inner_team = aliased(Team) return (DBSession.query(func.count('*') + 1). select_from(inner_team). filter(inner_team.score > Team.score). correlate(Team). label('rank'))
def get_stats(cls): return dict( DBSession.query(Provider.pk, func.count(cls.ref_pk).label('c')) .filter(Provider.pk == cls.provider_pk) .group_by(Provider.pk) .order_by(desc('c')) .all())
def is_activated_by_callfilter_id(session, callfilter_id): return (session.query(func.count(Callfiltermember.active)) .join((Callfilter, Callfilter.id == Callfiltermember.callfilterid)) .filter(and_(Callfiltermember.callfilterid == callfilter_id, Callfiltermember.bstype == 'secretary', Callfiltermember.active == 1)) .first()[0])
def get_extract_assessment_item_and_raw_count_query(params, extract_type): """ private method to generate SQLAlchemy object or sql code for extraction of students for item level/raw data :param params: for query parameters asmt_year, asmt_type, asmt_subject, asmt_grade :param extract_type: Type of extraction: Item Level, Raw Data Level, Student Assessment """ state_code = params.get(Constants.STATECODE) with EdCoreDBConnection(state_code=state_code) as connector: fact_asmt_outcome_vw = connector.get_table( Constants.FACT_ASMT_OUTCOME_VW) query = select_with_context([ func.count().label(Constants.COUNT)], from_obj=[fact_asmt_outcome_vw], permission=get_required_permission(extract_type), state_code=state_code) query = _assessment_item_and_raw_where_clause_builder( query, fact_asmt_outcome_vw, params) return query
def quotas(connection, target): allowed = target.org.tier.nodes current = connection.scalar( select([func.count(distinct(Node.id))]).where( Node.org_id == target.org.id).where(Node.enabled == True)) # noqa return allowed, current
def count(self): """ Get a count of the number of distinct objects. """ q = select(from_obj=self.join(self.alias)) q = self.filter(q, partial=True) q = q.column(func.count(func.distinct(self.alias.c.id)).label('num')) rp = db.session.execute(q) return rp.fetchone().num
def store_item(self, item, spider): # {{{ item_table = self.table dbobj = {} for k,v in item.iteritems(): if isinstance(v, list) or isinstance(v, dict): dbobj[k] = json.dumps(v) else: dbobj[k] = v conn = self.engine.connect() page_url = item['page_url'] where = item_table.c.page_url == page_url sel = select([func.count(item_table.c.id)]).where(where) cnt = conn.execute(sel).scalar() if cnt: assert cnt==1, 'More than one item with page_url %s' % page_url upd = item_table.update().where(where) conn.execute(upd, dbobj) status = 'updated' else: ins = item_table.insert() conn.execute(ins, dbobj) status = 'inserted' log.msg('Item %s into %s: %s' % (status, item_table.name, page_url), level=log.DEBUG, spider=spider) conn.close()
def get_number_comments(self, status=None): if not status: return Session.query(sa.func.count(Comment.id)).filter_by(change_id=self.id).first()[0] date = Session.query(func.max(CommentStatus.created_date).label('date'), Comment.id) date = date.filter(CommentStatus.comment_id==Comment.id).filter(Comment.change_id==self.id) date = date.group_by(CommentStatus.comment_id, Comment.id) subq = date.subquery() q = Session.query(func.count(Comment.id)).outerjoin((subq, subq.c.id==Comment.id)) q = q.outerjoin((CommentStatus, CommentStatus.comment_id==Comment.id)) q = q.filter(Comment.change_id==self.id).filter(Comment.status!=STATUS_REMOVED) q = q.filter(Comment.in_reply_to_id==None) if status == STATUS_OPEN: q = q.filter(sa.or_( CommentStatus.id==None, sa.and_(CommentStatus.created_date==subq.columns.date, CommentStatus.status==status) )) return q.scalar() else: q = q.filter( sa.and_(CommentStatus.created_date==subq.columns.date, CommentStatus.status==status) ) return q.scalar()
def pivot(query, column_expressions, column_labels): query = query.group_by(query.statement) columns = [] for i in range(0, len(column_expressions)): column_expressions[i] = func.count(case([(column_expressions[i], 1)])) columns.append(column_expressions[i].label(column_labels[i])) return query.add_columns(*columns)
def num_entries(self, conditions="1=1"): """ Return the count of entries on the dataset fact table having the dimension set to a value matching the filter given by ``conditions``. """ query = select([func.count(func.distinct(self.column_alias))], conditions) rp = self.dataset.bind.execute(query) return rp.fetchone()[0]
def by_filter(cls, session, opts, **kwargs): where = [] if opts.get('local_only'): where.append(cls.local==True) if opts.get('classifiers'): ids = [c.id for c in opts.get('classifiers')] cls_pkg = classifier__package qry = session.query(cls_pkg.c.package_id, func.count('*')) qry = qry.filter(cls_pkg.c.classifier_id.in_(ids)) qry = qry.group_by(cls_pkg.c.package_id) qry = qry.having(func.count('*') >= len(ids)) where.append(cls.id.in_([r[0] for r in qry.all()])) return cls.find(session, where=where, **kwargs)
def childnodes(request): if request.params.get('t') == 'select2': query = DBSession.query(Languoid.id, Languoid.name, Languoid.level)\ .filter(icontains(Languoid.name, request.params.get('q'))) total = query.count() ms = LanguoidsMultiSelect(request, None, None, url='x') return dict( results=[ms.format_result(l) for l in query.limit(100)], context={}, more=total > 500) query = DBSession.query( Languoid.pk, Languoid.id, Languoid.name, Languoid.level, func.count(TreeClosureTable.child_pk).label('children'))\ .filter(Language.pk == TreeClosureTable.parent_pk)\ .filter(Language.active == true()) if request.params.get('node'): query = query.filter(Languoid.father_pk == int(request.params['node'])) else: # narrow down selection of top-level nodes in the tree: query = query.filter(Languoid.father_pk == null()) if request.params.get('q'): query = query.filter(Language.name.contains(request.params.get('q'))) query = query.group_by( Languoid.pk, Languoid.id, Languoid.name, Languoid.level).order_by(Language.name) return [{ 'label': ('%s (%s)' % (l.name, l.children - 1)) if l.children > 1 else l.name, 'glottocode': l.id, 'lname': l.name, 'id': l.pk, 'level': l.level.value, #'children': l.children 'load_on_demand': l.children > 1} for l in query]
def do_collector(self, input: str): try: arguments = self._process_input(KisConsoleConsoleCommand.collector, input) if len(arguments) == 0: with self._engine.session_scope() as session: workspace_id = session.query(Workspace.id).filter_by( name=self._workspace).scalar_subquery() query = session.query(CollectorName.name.label("collector"), CollectorName.type.label("type"), func.coalesce(Command.status, CommandStatus.pending.name).label("status"), CollectorName.priority, func.count(Command.status).label("count")) \ .outerjoin((Command, CollectorName.commands)) \ .filter(Command.workspace_id == workspace_id) \ .group_by(CollectorName.name, CollectorName.type, func.coalesce(Command.status, CommandStatus.pending.name), CollectorName.priority) df = pandas.read_sql(query.statement, query.session.bind) df["status"] = df["status"].apply( lambda x: CommandStatus(x).name) df["type"] = df["type"].apply( lambda x: CollectorType(x).name) results = pandas.pivot_table( df, index=["collector", "type", "priority"], columns=["status"], values="count", aggfunc=numpy.sum, fill_value=0).sort_values(by="priority") print(results) elif arguments[0] == CollectorArgumentEnum.current: if self._producer_thread.current_collector: print(self._producer_thread.current_collector.name) else: print("none") elif arguments[0] == CollectorArgumentEnum.remaining: for item in self._producer_thread.remaining_collectors: print(item) except Exception: traceback.print_exc(file=sys.stderr)
def index(): if current_user.is_authenticated: page = request.args.get('page', 1, type=int) per_page = current_app.config['ALBUMY_PHOTO_PER_PAGE'] pagination = Photo.query \ .join(Follow, Follow.followed_id == Photo.author_id) \ .filter(Follow.follower_id == current_user.id) \ .order_by(Photo.timestamp.desc()) \ .paginate(page, per_page) photos = pagination.items else: pagination = None photos = None tags = Tag.query.join(Tag.photos).group_by(Tag.id).order_by( func.count(Photo.id).desc()).limit(10) return render_template('main/index.html', pagination=pagination, photos=photos, tags=tags, Collect=Collect)
def feed_ratingindex(): off = request.args.get("offset") or 0 entries = calibre_db.session.query(db.Ratings, func.count('books_ratings_link.book').label('count'), (db.Ratings.rating / 2).label('name')) \ .join(db.books_ratings_link)\ .join(db.Books)\ .filter(calibre_db.common_filters()) \ .group_by(text('books_ratings_link.rating'))\ .order_by(db.Ratings.rating).all() pagination = Pagination( (int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page, len(entries)) element = list() for entry in entries: element.append(FeedObject(entry[0].id, "{} Stars".format(entry.name))) return render_xml_template('feed.xml', listelements=element, folder='opds.feed_ratings', pagination=pagination)
def get_skills(min_frequency, max_frequency): count = func.count() filters = [] if min_frequency: filters.append(count >= min_frequency) if max_frequency: filters.append(count <= max_frequency) skill_counts = db.session.query(Skill.name, count)\ .group_by(Skill.name)\ .having(and_(*filters))\ .all() res = [] for skill in skill_counts: skill_json = {"name": skill[0], "frequency": skill[1]} res.append(skill_json) return res
def seed_subscriptions(): subscriptions = db.session.query(func.count( UserSubscription.following_id)).scalar() subscriptions_to_seed = 30 user_ids = [user[0] for user in db.session.query(User.id).all()] for i in range(subscriptions, subscriptions_to_seed): follower_id = random.choice(user_ids) already_following_ids = [ u[0] for u in UserSubscription.query.filter( UserSubscription.follower_id == follower_id).with_entities( UserSubscription.following_id) ] already_following_ids.append(follower_id) user_ids_not_following = set(user_ids).difference( set(already_following_ids)) following_id = random.sample(user_ids_not_following, 1)[0] db.session.add( UserSubscription(following_id=following_id, follower_id=follower_id)) db.session.commit()
def build_sub_query(self, extra_columns=[], where_guid=None): ''' build select columns based on request ''' query = select(extra_columns + [self._fact_asmt_outcome_vw.c.asmt_subject.label(Constants.ASMT_SUBJECT), self._fact_asmt_outcome_vw.c.inst_hier_rec_id, func.count().label(Constants.TOTAL), self._fact_asmt_outcome_vw.c.asmt_perf_lvl.label(Constants.LEVEL)])\ .where(and_(self._fact_asmt_outcome_vw.c.state_code == self._state_code, self._fact_asmt_outcome_vw.c.asmt_type == self._asmt_type, self._fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT, or_(self._fact_asmt_outcome_vw.c.administration_condition == Constants.ADMINISTRATION_CONDITION_STANDARDIZED, self._fact_asmt_outcome_vw.c.administration_condition == null()), self._fact_asmt_outcome_vw.c.asmt_year == self._asmt_year))\ .group_by(self._fact_asmt_outcome_vw.c.asmt_subject, self._fact_asmt_outcome_vw.c.inst_hier_rec_id, self._fact_asmt_outcome_vw.c.asmt_perf_lvl) if where_guid is not None: query = query.where(and_(where_guid)) return apply_filter_to_query(query, self._fact_asmt_outcome_vw, self._dim_student, self._filters)
def dashboard_def(): names = set() for numes in db.sqlalchemy_session.query(Message.MessageSender).distinct(): names.add(numes.MessageSender) print("type names", numes.MessageSender) names_converted = tuple(names) values = [] for i in names: q = db.sqlalchemy_session.query(func.count( Message.MessageSender)).filter(Message.MessageSender == i).one() list_of_max = list(q) new_index = list_of_max[0] values.append(new_index) bar = go.Bar( x=names_converted, y=values, ) scatter = go.Scatter( x=names_converted, y=values, ) # messages = db.sqlalchemy_session.query(Message).order_by(Message.MessageContent) # calories = [dish.calories_amount for dish in messages] # bar = go.Bar( # x=calories, # y=[dish.dishname for dish in messages] # ) # scatter = go.Scatter( # x=calories, # y=[dish.dishname for dish in messages], # ) ids = [0, 1] data = [scatter, bar] graphJSON = json.dumps(data, cls=plotly.utils.PlotlyJSONEncoder) return render_template('dashboard.html', graphJSON=graphJSON, ids=ids)
def seed_users(): role, created = get_or_create( db.session, Role, defaults={'description': 'for standard users'}, name='ROLE_USER') db.session.commit() non_standard_user_ids = db.session.query(User.id) \ .filter(~User.roles.any(id=role.id)).all() all_users_count = db.session.query(func.count(User.id)).all()[0][0] all_users_count = db.session.query(User.id).count() # User.query.filter(User.roles.any(UserRole.role_id.in_([1,2]))).count() standard_users_count = db.session.query(User).filter( User.roles.any(UserRole.role_id.in_([role.id]))).count() standard_users_count = db.session.query(User.id).filter( User.roles.any(id=role.id)).count() users_to_seed = 23 users_to_seed -= standard_users_count sys.stdout.write('[+] Seeding %d users\n' % users_to_seed) for i in range(0, users_to_seed): profile = fake.profile(fields='username,mail,name') username = profile['username'] # fake.first_name() fake.first_name_male() fake.first_name_female(), same for last_name() first_name = profile['name'].split()[0] last_name = profile['name'].split()[1] email = profile['mail'] password = bcrypt.generate_password_hash('password') user = User(username=username, first_name=first_name, last_name=last_name, email=email, password=password) user.roles.append(role) db.session.add(user) db.session.commit() db.session.commit()
def search(self, query, **kwargs): """ Searches in the configured media directories given a query. It uses the built-in SQLite index if available. If any directory has changed since the last scan then it will be indexed again and the up-to-date results will be returned. """ session = self._get_db_session() results = {} for media_dir in self.dirs: self.logger.info('Searching {} for "{}"'.format(media_dir, query)) dir_record = self._get_or_create_dir_entry(session, media_dir) if self._has_directory_changed_since_last_indexing(dir_record): self.logger.info( '{} has changed since last indexing, '.format(media_dir) + 're-indexing') self.scan(media_dir, session=session, dir_record=dir_record) query_tokens = [ _.lower() for _ in re.split(self._filename_separators, query.strip()) ] for file_record in session.query(MediaFile.path). \ join(MediaFileToken). \ join(MediaToken). \ filter(MediaToken.token.in_(query_tokens)). \ group_by(MediaFile.path). \ having(func.count(MediaFileToken.token_id) >= len(query_tokens)): if os.path.isfile(file_record.path): results[file_record.path] = { 'url': 'file://' + file_record.path, 'title': os.path.basename(file_record.path), 'size': os.path.getsize(file_record.path) } return results.values()
def seed_orders(): orders_count = db.session.query(func.count(Order.id)).scalar() orders_to_seed = 31 addresses = db.session.query(Address).options(load_only('id', 'user_id')).all() products = db.session.query(Product).options( load_only('id', 'name', 'slug', 'price')).all() for i in range(orders_count, orders_to_seed): address = random.choice(addresses) tracking_number = fake.uuid4() order_status = fake.random_int(min=0, max=2) user_id = address.user_id order = Order(tracking_number=tracking_number, order_status=order_status, address_id=address.id, user_id=user_id) db.session.add(order) ''' this is to save the order now, so I can have the id to be used in order items or the other way is to comment flush(), order_id=order.id, and session.add(oi). Instead use order.order_items.append(oi); See below. Both ways lead to the same result ''' db.session.flush() for i in range(fake.random_int(min=1, max=6)): product = random.choice(products) oi = OrderItem(name=product.name, slug=product.slug, price=product.price, order_id=order.id, product_id=product.id, user_id=user_id, quantity=fake.random_int(min=1, max=5)) db.session.add(oi) # order.order_items.append(oi) db.session.commit()
def store_fits_header(connection, galaxy_id, group): """ Store the fits header data for a galaxy in the HDF5 file """ LOG.info('Storing the fits headers') count = connection.execute( select([func.count(FITS_HEADER.c.fitsheader_id) ]).where(FITS_HEADER.c.galaxy_id == galaxy_id)).first()[0] data = numpy.zeros(count, dtype=data_type_fits_header1_01) count = 0 for fits_header in connection.execute( select([FITS_HEADER ]).where(FITS_HEADER.c.galaxy_id == galaxy_id).order_by( FITS_HEADER.c.fitsheader_id)): data[count] = ( fits_header[FITS_HEADER.c.keyword], fits_header[FITS_HEADER.c.value], fits_header[FITS_HEADER.c.comment], ) count += 1 group.create_dataset('fits_header', data=data, compression='gzip')
def count_star(session: Union[Session, Engine, Connection], tablename: str, *criteria: Any) -> int: """ Returns the result of ``COUNT(*)`` from the specified table (with additional ``WHERE`` criteria if desired). Args: session: SQLAlchemy :class:`Session`, :class:`Engine`, or :class:`Connection` object tablename: name of the table criteria: optional SQLAlchemy "where" criteria Returns: a scalar """ # works if you pass a connection or a session or an engine; all have # the execute() method query = select([func.count()]).select_from(table(tablename)) for criterion in criteria: query = query.where(criterion) return session.execute(query).scalar()
def store_area(connection, galaxy_id, group): """ Store the areas associated with a galaxy """ LOG.info('Storing the areas') count = connection.execute(select([func.count(AREA.c.area_id)]).where(AREA.c.galaxy_id == galaxy_id)).first()[0] data = numpy.zeros(count, dtype=data_type_area) count = 0 for area in connection.execute(select([AREA]).where(AREA.c.galaxy_id == galaxy_id).order_by(AREA.c.area_id)): data[count] = ( area[AREA.c.area_id], area[AREA.c.top_x], area[AREA.c.top_y], area[AREA.c.bottom_x], area[AREA.c.bottom_y], area[AREA.c.workunit_id] if area[AREA.c.workunit_id] is not None else -1, str(area[AREA.c.update_time]), ) count += 1 group.create_dataset('area', data=data, compression='gzip') return count
def index(): """ 主页 """ logger.info('url = ' + str(request.url)) logger.info('当前用户是否登录: ' + str(current_user.is_authenticated)) # 在主页页面,登录用户和未登录用户显示的是不一致的 # 如果登录了,就会按时间顺序显示自己和关注的用户的发布的图片 # 而如果没有登录,则显示主页图片以及注册功能 if current_user.is_authenticated: # 页码 page = request.args.get("page", 1, type=int) # 每一页的图片多少 per_page = current_app.config["ALBUMY_PHOTO_PER_PAGE"] pagination = ( # 关联Follow表,关联条件是Follow的followed_id和Photo的authid_id一致 Photo.query.join(Follow, Follow.followed_id == Photo.author_id) # 过滤出自己关注的对象 .filter(Follow.follower_id == current_user.id) # 按时间顺序排序 .order_by(Photo.timestamp.desc()).paginate(page, per_page) ) # 得到photo集合 photos = pagination.items else: pagination = None photos = None tags = ( Tag.query.join(Tag.photos) .group_by(Tag.id) .order_by(func.count(Photo.id).desc()) .limit(10) ) return render_template( "main/index.html", pagination=pagination, photos=photos, tags=tags, Collect=Collect, )
def test_migrate_student_reg(self): Unittest_with_edcore_sqlite.setUpClass( EdMigrateDestConnection.get_datasource_name( TestMigrate.test_tenant), use_metadata_from_db=False) preprod_conn = EdMigrateSourceConnection( tenant=get_unittest_preprod_tenant_name()) prod_conn = EdMigrateDestConnection( tenant=get_unittest_prod_tenant_name()) batch_guid = "0aa942b9-75cf-4055-a67a-8b9ab53a9dfc" student_reg_table = preprod_conn.get_table(Constants.STUDENT_REG) get_query = select([student_reg_table.c.student_reg_rec_id ]).order_by(student_reg_table.c.student_reg_rec_id) count_query = select([func.count().label('student_reg_rec_ids')], student_reg_table.c.student_reg_rec_id.in_( range(15541, 15551))) rset = preprod_conn.execute(get_query) row = rset.fetchall() self.assertEqual(10, len(row)) self.assertListEqual([(15541, ), (15542, ), (15543, ), (15544, ), (15545, ), (15546, ), (15547, ), (15548, ), (15549, ), (15550, )], row) rset.close() rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(0, row['student_reg_rec_ids']) rset.close() delete_count, insert_count = migrate_table(batch_guid, None, preprod_conn, prod_conn, 'student_reg', False) self.assertEqual(0, delete_count) self.assertEqual(10, insert_count) rset = prod_conn.execute(count_query) row = rset.fetchone() self.assertEqual(10, row['student_reg_rec_ids']) rset.close()
def store_area_user(connection, galaxy_id, group): """ Store the areas associated with a galaxy """ LOG.info('Storing the area_users') count = connection.execute( select([func.count(AREA_USER.c.areauser_id)], from_obj=AREA_USER.join(AREA)).where( AREA.c.galaxy_id == galaxy_id)).first()[0] data = numpy.zeros(count, dtype=data_type_area_user) count = 0 for area_user in connection.execute( select([AREA_USER], from_obj=AREA_USER.join(AREA)).where( AREA.c.galaxy_id == galaxy_id).order_by( AREA_USER.c.areauser_id)): data[count] = ( area_user[AREA_USER.c.area_id], area_user[AREA_USER.c.userid], str(area_user[AREA_USER.c.create_time]), ) count += 1 group.create_dataset('area_user', data=data, compression='gzip')
def insert_feed(cls, source_id, feed_articles): insert = Article.__table__.insert().prefix_with('IGNORE') article_list = [] for position in feed_articles: distress = filter_dir.binary_filter.classify_bert([position['title']]) article_list.append({ 'title': position['title'], 'body': position['summary'], 'link': position['link'], 'guid': position['id'], 'distress': int(distress), 'source_id': source_id, 'date_published': position['published'], 'img_link': position['img_link'], 'img_credit': position['img_credit'], 'tags': position['tags'] }) db.engine.execute(insert, article_list) count = db.session.query(func.count(Article.title)).scalar() if count>100: db.session.query(func.min(Article.date_added)).one().delete() db.session.commit()
def post(self): request_data = request.data normal_user_id = request_data["userId"] try: purchased_gifts = db.session.query(User_Gift.gift_id,func.count(User_Gift.gift_id)).filter(and_(User_Gift.user_id==normal_user_id,User_Gift.status=="active")).group_by(User_Gift.gift_id).all() print(purchased_gifts) giftList=[] for purchased_gift in purchased_gifts: giftInfo={} giftInfo["giftId"]=purchased_gift[0] gift=Merchant_Gift.query.get(purchased_gift[0]) giftInfo["giftName"]=gift.gift_name giftInfo["amount"]=gift.amount giftInfo["count"]=purchased_gift[1] giftList.append(giftInfo) print(giftList) message = "Success" return self.response("200","false",giftList,message) except Exception as err: message = str(err) return self.response("503", "true",{}, message)
def stats_prometheus_route(): """returns internal stats; prometheus""" stats = {} stats['sner_storage_hosts_total'] = Host.query.count() stats['sner_storage_services_total'] = Service.query.count() stats['sner_storage_vulns_total'] = Vuln.query.count() stats['sner_storage_notes_total'] = Note.query.count() stale_horizont = datetime.utcnow() - timedelta(days=5) stats['sner_scheduler_jobs_total{{state="running"}}'] = Job.query.filter(Job.retval == None, Job.time_start > stale_horizont).count() # noqa: E501,E711 pylint: disable=singleton-comparison stats['sner_scheduler_jobs_total{{state="stale"}}'] = Job.query.filter(Job.retval == None, Job.time_start < stale_horizont).count() # noqa: E501,E711 pylint: disable=singleton-comparison stats['sner_scheduler_jobs_total{{state="finished"}}'] = Job.query.filter(Job.retval == 0).count() stats['sner_scheduler_jobs_total{{state="failed"}}'] = Job.query.filter(Job.retval != 0).count() queue_targets = db.session.query(Queue.name, func.count(Target.id).label('cnt')).select_from(Queue).outerjoin(Target).group_by(Queue.name).all() for queue, targets in queue_targets: stats[f'sner_scheduler_queue_targets_total{{name="{queue}"}}'] = targets output = '\n'.join(f'{key} {val}' for key, val in stats.items()) return Response(output, mimetype='text/plain')
def get_fqdn_hash_range(db): hash_counts = (db.query( db_models.Frontier.fqdn_hash_fetcher_index, func.count(db_models.Frontier.fqdn), ).group_by(db_models.Frontier.fqdn_hash_fetcher_index).order_by( db_models.Frontier.fqdn_hash_fetcher_index).all()) hash_values = [x[1] for x in hash_counts] if hash_values: count = len(hash_values) min_value = min(hash_values) max_value = max(hash_values) avg_value = sum(hash_values) / count hash_range = max_value - min_value perc_range = (hash_range / 2) / avg_value else: perc_range = 0.0 return round(perc_range, 2)
def list(): now_time = datetime.datetime.now() day_now = now_time.strftime("%Y-%m-%d") day_before = (now_time - datetime.timedelta(days=1)).strftime("%Y-%m-%d") username_set = set() username_set.add('11') username_set.add('22') user_list = User.query.filter(func.find_in_set(User.username, username_set)).all() User.session.query(func.count('*').label('count'))\ .group_by(func.DATE_FORMAT(User.update_time, '%Y/%m')) current_page = 1 page_size = 10 ids = [1, 2, 3] User.query.filter(and_(User.id.in_(ids)), User.name.in_(ids))\ .outerjoin(WxInfo, User.id == WxInfo.id).add_entity(WxInfo)\ .group_by(User.id) \ .add_column(func.group_concat(User.name)) \ .paginate(current_page, page_size)
def main() -> None: BATCH_SIZE = 50000 total_count = 0 with ScopedSession() as session: offset = 0 db_comments_count = session.query(func.count( Comment.comment_id)).scalar() parsed_comments = set() while True: print(f'Offset: {offset}') q = session.query(Comment).order_by( Comment.comment_id).limit(BATCH_SIZE).offset(offset) for comment in q: comment.text = normalize_text(comment.text) parsed_comments.add(comment.comment_id) total_count += 1 offset += BATCH_SIZE if offset > db_comments_count: break print( f'Total_count: {total_count}, db_count: {db_comments_count}, parsed_comments_len: {len(parsed_comments)}' )
def home(): def join(query): return query.options( \ joinedload(Package.license), \ joinedload(Package.media_license)) query = Package.query.filter_by(state=PackageState.APPROVED) count = query.count() new = join(query.order_by(db.desc(Package.approved_at))).limit(8).all() pop_mod = join( query.filter_by(type=PackageType.MOD).order_by(db.desc( Package.score))).limit(8).all() pop_gam = join( query.filter_by(type=PackageType.GAME).order_by(db.desc( Package.score))).limit(4).all() pop_txp = join( query.filter_by(type=PackageType.TXP).order_by(db.desc( Package.score))).limit(4).all() updated = db.session.query(Package).select_from(PackageRelease).join(Package) \ .filter_by(state=PackageState.APPROVED) \ .order_by(db.desc(PackageRelease.releaseDate)) \ .limit(20).all() updated = updated[:8] reviews = PackageReview.query.filter_by(recommends=True).order_by( db.desc(PackageReview.created_at)).limit(5).all() downloads_result = db.session.query(func.sum( Package.downloads)).one_or_none() downloads = 0 if not downloads_result or not downloads_result[ 0] else downloads_result[0] tags = db.session.query(func.count(Tags.c.tag_id), Tag) \ .select_from(Tag).outerjoin(Tags).group_by(Tag.id).order_by(db.asc(Tag.title)).all() return render_template("index.html", count=count, downloads=downloads, tags=tags, \ new=new, updated=updated, pop_mod=pop_mod, pop_txp=pop_txp, pop_gam=pop_gam, reviews=reviews)
def speaking_language(self, languages=None, return_all_languages=False, with_count=False, reverse_order=False): from . import get_locale if with_count: if not languages: languages = self.session.query(Languages, func.count('books_languages_link.book'))\ .join(books_languages_link).join(Books)\ .filter(self.common_filters(return_all_languages=return_all_languages)) \ .group_by(text('books_languages_link.lang_code')).all() for lang in languages: lang[0].name = isoLanguages.get_language_name(get_locale(), lang[0].lang_code) return sorted(languages, key=lambda x: x[0].name, reverse=reverse_order) else: if not languages: languages = self.session.query(Languages) \ .join(books_languages_link) \ .join(Books) \ .filter(self.common_filters(return_all_languages=return_all_languages)) \ .group_by(text('books_languages_link.lang_code')).all() for lang in languages: lang.name = isoLanguages.get_language_name(get_locale(), lang.lang_code) return sorted(languages, key=lambda x: x.name, reverse=reverse_order)
def count_star_and_max(session: Union[Session, Engine, Connection], tablename: str, maxfield: str, *criteria: Any) -> Tuple[int, Optional[int]]: """ Args: session: SQLAlchemy :class:`Session`, :class:`Engine`, or :class:`Connection` object tablename: name of the table maxfield: name of column (field) to take the ``MAX()`` of criteria: optional SQLAlchemy "where" criteria Returns: a tuple: ``(count, maximum)`` """ query = select([func.count(), func.max(column(maxfield))]).select_from(table(tablename)) for criterion in criteria: query = query.where(criterion) result = session.execute(query) return result.fetchone() # count, maximum
def get_staging_demographic_counts(self): demographics = [ 'hispanicorlatinoethnicity', 'americanindianoralaskanative', 'asian', 'blackorafricanamerican', 'nativehawaiianorotherpacificislander', 'white', 'demographicracetwoormoreraces', 'ideaindicator', 'lepstatus', 'section504status', 'economicdisadvantagestatus', 'migrantstatus' ] results_dict = {} with get_udl_connection() as conn: stg_outcome = conn.get_table('stg_sbac_asmt_outcome') for entry in demographics: query = select([func.count(stg_outcome.c[entry])], from_obj=stg_outcome).where( stg_outcome.c[entry].in_(['Y', 'y', 'yes'])) result = conn.execute(query) for row in result: demo_count = row[0] results_dict[entry] = demo_count corrleated_results = { 'dmg_eth_hsp': results_dict['hispanicorlatinoethnicity'], 'dmg_eth_ami': results_dict['americanindianoralaskanative'], 'dmg_eth_asn': results_dict['asian'], 'dmg_eth_blk': results_dict['blackorafricanamerican'], 'dmg_eth_pcf': results_dict['nativehawaiianorotherpacificislander'], 'dmg_eth_wht': results_dict['white'], 'dmg_eth_2om': results_dict['demographicracetwoormoreraces'], 'dmg_prg_iep': results_dict['ideaindicator'], 'dmg_prg_lep': results_dict['lepstatus'], 'dmg_prg_504': results_dict['section504status'], 'dmg_sts_ecd': results_dict['economicdisadvantagestatus'], 'dmg_sts_mig': results_dict['migrantstatus'], } return corrleated_results
def get_data_range(session, device_id, date_from, date_to, resolution): row_count = session.query(func.count(DefaultDeviceData.id)).filter( DefaultDeviceData.date > date_from, DefaultDeviceData.date < date_to).filter_by( device_id=device_id).scalar() data_list = [] if (row_count > resolution): all_data = session.query(DefaultDeviceData).filter( DefaultDeviceData.date > date_from, DefaultDeviceData.date < date_to).filter_by( device_id=device_id).all() skip_rows = floor(row_count / resolution) counter = 0 data_sum = 0 for item in all_data: counter += 1 data_sum += item.temp if (counter == skip_rows): avg_item = item avg_item.temp = round(data_sum / counter, 2) data_list.append(avg_item) counter = 0 data_sum = 0 else: data_list = session.query(DefaultDeviceData).filter( DefaultDeviceData.date > date_from, DefaultDeviceData.date < date_to).filter_by( device_id=device_id).all() data_object_list = [] for data in data_list: data_object = DeviceDataView(data.id, 1 if data.led_state else 0, str(data.temp), str(data.date)) data_object_list.append(data_object) return jsonify({'data': [result.serialize for result in data_object_list]})
def show(self, fullname, crew): self.view['fullname'] = fullname sql = """ select id from movie where meta is not null and btrim(json_select(meta, '{"%s"}')::text, '"') like :fullname """ if crew == 'director': sql = sql % 'Director' elif crew == 'screenwriter': sql = sql % 'Writer' elif crew == 'actor': sql = sql % 'Actors' else: raise RuntimeError, 'routing for crew:%s not provided' % crew fullname_ = fullname.encode('ascii', 'replace').replace('?','%') movie_ids = self.session.execute(sql, {'fullname': '%'+fullname_+'%'}).fetchall() movie_ids = map(itemgetter0, movie_ids) sq = self.session.query(Rating.movie_id, func.avg(Rating.rating).label('avg_rating'), func.count(Rating.user_id).label('rev_cnt'))\ .group_by(Rating.movie_id).subquery() movies = self.session.query(Movie, sq.c.avg_rating, sq.c.rev_cnt)\ .outerjoin((sq, sq.c.movie_id==Movie.id))\ .filter(Movie.id.in_(movie_ids))\ .order_by(Movie.year.desc()).all() self.view.update({'crew': crew, 'movies': movies}) self.template = 'cast.phtml'
def leaderboard_json(request): """ Render a top-users JSON dump. """ user = _get_user(request, request.matchdict.get('id')) leaderboard = request.db.session.query( m.Person, func.count(m.Person.assertions)).join( m.Assertion).order_by('count_1 desc').filter( m.Person.opt_out == False).group_by(m.Person.id).all() # Hackishly, but relatively cheaply get the rank of all users. # This is: # { <person object>: # { # 'badges': <number of badges they have>, # 'rank': <their global rank> # } # } user_to_rank = dict([[data[0], { 'badges': data[1], 'rank': idx + 1 }] for idx, data in enumerate(leaderboard)]) if user: idx = [i[0] for i in leaderboard].index(user) # Handle the case of leaderboard[-2:2] which will be [] always. if idx < 2: idx = 2 leaderboard = leaderboard[(idx - 2):(idx + 3)] else: leaderboard = leaderboard[:25] ret = [ dict(user_to_rank[p[0]].items() + {'nickname': p[0].nickname}.items()) for p in leaderboard ] return {'leaderboard': ret}