Here are the examples of the python api sqlalchemy.sql.func.count taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
29 Examples
3
Example 1
def __len__(self):
"""Returns number of rows selected by the statement."""
cnt = sqlalchemy.sql.func.count(1)
statement = sql.expression.select([cnt], from_obj=self.statement)
return self.store.connectable.scalar(statement)
3
Example 2
Project: sqlalchemy_mptt Source File: integrity.py
def test_greatest_right_is_always_double_number_of_nodes(self):
""" The greatest right key is always double the number of nodes.
The following example should match COUNT(id) * 2 equal MAX(right).
.. code-block:: sql
SELECT COUNT(id), MAX(right) FROM tree
"""
table = self.model
result = self.session.query(
func.count(table.get_pk_name()),
func.max(table.right)).group_by(table.tree_id).all()
for tree in result:
self.assertEqual(tree[0] * 2, tree[1])
3
Example 3
def append(self,obj):
with self.obj.backend.transaction(implicit = True):
relationship_table = self.params['relationship_table']
condition = and_(relationship_table.c['pk_%s' % self.params['collection']] == obj.pk,
relationship_table.c['pk_%s' % self.collection] == self.obj.pk)
s = select([func.count(text('*'))]).where(condition)
result = self.obj.backend.connection.execute(s)
cnt = result.first()[0]
if cnt:
return #the object is already inside
values = {
'pk_%s' % self.collection : self.obj.pk,
'pk_%s' % self.params['collection'] : obj.pk
}
insert = relationship_table.insert().values(**values)
self.obj.backend.connection.execute(insert)
self._queryset = None
3
Example 4
Project: grano Source File: facets.py
def make_facets(parent_alias, filter_func, parser_func):
""" Return a set of facets based on the current query string. This
will also consider filters set for the query, i.e. only show facets
that match the current set of filters. """
facets = {}
for facet in request.args.getlist('facet'):
parent_obj = parent_alias()
q = db.session.query()
facet_count = func.count(parent_obj.id)
q = q.add_columns(facet_count)
q = q.order_by(facet_count.desc())
q = filter_func(q, parent_obj)
q = parser_func(parent_obj, facet, facet, q)
facets[facet] = Pager(q, name='facet_%s' % facet,
results_converter=results_process)
return facets
3
Example 5
Project: changes Source File: testresult.py
def _record_test_counts(step):
create_or_update(ItemStat, where={
'item_id': step.id,
'name': 'test_count',
}, values={
'value': db.session.query(func.count(TestCase.id)).filter(
TestCase.step_id == step.id,
).as_scalar(),
})
db.session.commit()
3
Example 6
Project: changes Source File: testresult.py
def _record_test_failures(step):
create_or_update(ItemStat, where={
'item_id': step.id,
'name': 'test_failures',
}, values={
'value': db.session.query(func.count(TestCase.id)).filter(
TestCase.step_id == step.id,
TestCase.result == Result.failed,
).as_scalar(),
})
db.session.commit()
3
Example 7
Project: changes Source File: testresult.py
def _record_test_rerun_counts(step):
create_or_update(ItemStat, where={
'item_id': step.id,
'name': 'test_rerun_count',
}, values={
'value': db.session.query(func.count(TestCase.id)).filter(
TestCase.step_id == step.id,
TestCase.reruns > 0,
).as_scalar(),
})
3
Example 8
Project: coilmq Source File: __init__.py
def size(self, destination):
"""
Size of the queue for specified destination.
@param destination: The queue destination (e.g. /queue/foo)
@type destination: C{str}
@return: The number of frames in specified queue.
@rtype: C{int}
"""
session = meta.Session()
sel = select([func.count(model.frames_table.c.message_id)]).where(
model.frames_table.c.destination == destination)
result = session.execute(sel)
first = result.fetchone()
if not first:
return 0
else:
return int(first[0])
3
Example 9
Project: python-wechat Source File: conversation.py
def get_conversations(self):
master = self._tbl_master
stmt = select([master.c.name], and_(master.c.type == 'table',
master.c.name.like(TBL_CHAT_PATTERN)))
for row in self._db.conn.execute(stmt):
cid = row[0]
tbl = self._get_chat_tbl(cid)
stmt = select([select([func.count(tbl.c.MesLocalID)]).as_scalar(),
tbl.c.Message, tbl.c.CreateTime, tbl.c.Type, tbl.c.Des]).\
order_by(tbl.c.CreateTime.desc()).limit(1)
result = self._db.conn.execute(stmt).fetchone()
yield {'cid': cid,
'msg_count': result[0],
'last_msg_content': result[1],
'last_msg_ctime': result[2],
'last_msg_type': result[3],
'last_msg_dest': result[4]}
3
Example 10
def count(session, query):
"""Returns the count of the specified `query`.
This function employs an optimization that bypasses the
:meth:`sqlalchemy.orm.Query.count` method, which can be very slow
for large queries.
"""
counts = query.selectable.with_only_columns([func.count()])
num_results = session.execute(counts.order_by(None)).scalar()
if num_results is None or query._limit is not None:
return query.order_by(None).count()
return num_results
3
Example 11
Project: ostip Source File: models.py
@indicator_count.expression
def indicator_count(cls):
return (select([func.count(Indicator.id)]).
where(Indicator.event_id == cls.id).
label("indicator_count")
)
0
Example 12
Project: pytorctl Source File: SQLSupport.py
def _compute_stats_query(stats_clause):
tc_session.clear()
# http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_update
to_s = select([func.count(Extension.id)],
and_(stats_clause, Extension.table.c.to_node_idhex
== RouterStats.table.c.router_idhex)).as_scalar()
from_s = select([func.count(Extension.id)],
and_(stats_clause, Extension.table.c.from_node_idhex
== RouterStats.table.c.router_idhex)).as_scalar()
f_to_s = select([func.count(FailedExtension.id)],
and_(stats_clause, FailedExtension.table.c.to_node_idhex
== RouterStats.table.c.router_idhex,
FailedExtension.table.c.row_type=='failedextension')).as_scalar()
f_from_s = select([func.count(FailedExtension.id)],
and_(stats_clause, FailedExtension.table.c.from_node_idhex
== RouterStats.table.c.router_idhex,
FailedExtension.table.c.row_type=='failedextension')).as_scalar()
avg_ext = select([func.avg(Extension.delta)],
and_(stats_clause,
Extension.table.c.to_node_idhex==RouterStats.table.c.router_idhex,
Extension.table.c.hop==0,
Extension.table.c.row_type=='extension')).as_scalar()
RouterStats.table.update(stats_clause, values=
{RouterStats.table.c.circ_try_to:to_s,
RouterStats.table.c.circ_try_from:from_s,
RouterStats.table.c.circ_fail_to:f_to_s,
RouterStats.table.c.circ_fail_from:f_from_s,
RouterStats.table.c.avg_first_ext:avg_ext}).execute()
RouterStats.table.update(stats_clause, values=
{RouterStats.table.c.circ_from_rate:
RouterStats.table.c.circ_fail_from/RouterStats.table.c.circ_try_from,
RouterStats.table.c.circ_to_rate:
RouterStats.table.c.circ_fail_to/RouterStats.table.c.circ_try_to,
RouterStats.table.c.circ_bi_rate:
(RouterStats.table.c.circ_fail_to+RouterStats.table.c.circ_fail_from)
/
(RouterStats.table.c.circ_try_to+RouterStats.table.c.circ_try_from)}).execute()
# TODO: Give the streams relation table a sane name and reduce this too
for rs in RouterStats.query.filter(stats_clause).\
options(eagerload('router'),
eagerload('router.detached_streams'),
eagerload('router.streams')).all():
tot_bw = 0.0
s_cnt = 0
tot_bytes = 0.0
tot_duration = 0.0
for s in rs.router.streams:
if isinstance(s, ClosedStream):
tot_bytes += s.tot_bytes()
tot_duration += s.end_time - s.start_time
tot_bw += s.bandwidth()
s_cnt += 1
# FIXME: Hrmm.. do we want to do weighted avg or pure avg here?
# If files are all the same size, it shouldn't matter..
if s_cnt > 0:
rs.sbw = tot_bw/s_cnt
else: rs.sbw = None
rs.strm_closed = s_cnt
rs.strm_try = len(rs.router.streams)+len(rs.router.detached_streams)
if rs.sbw:
tot_var = 0.0
for s in rs.router.streams:
if isinstance(s, ClosedStream):
tot_var += (s.bandwidth()-rs.sbw)*(s.bandwidth()-rs.sbw)
tot_var /= s_cnt
rs.sbw_dev = math.sqrt(tot_var)
tc_session.add(rs)
tc_session.commit()
0
Example 13
def list_submissions(self, survey_id):
"""List all submissions for a survey."""
sub_resource = SubmissionResource()
sub_resource.ref_rh = self.ref_rh
sub_resource.request = self.request
sub_resource.application = self.application
where = Submission.survey_id == survey_id
result = sub_resource.list(where=where)
response = sub_resource.wrap_list_response(result)
if sub_resource.content_type == 'csv':
title = (
self.session
.query(Survey.title[Survey.default_language])
.filter_by(id=survey_id)
.scalar()
)
self._set_filename('survey_{}_submissions'.format(title), 'csv')
else:
response['total_entries'] = (
self.session
.query(func.count(Submission.id))
.filter_by(survey_id=survey_id)
.scalar()
)
response['survey_id'] = survey_id
return response
0
Example 14
Project: dokomoforms Source File: surveys.py
def _generate_activity_response(self,
days=30, user_id=None, survey_id=None):
"""Get the activity response.
Build and execute the query for activity, specifying the number of days
in the past from the current date to return.
If a survey_id is specified, only activity from that
survey will be returned.
"""
# number of days prior to return
today = datetime.date.today()
from_date = today - datetime.timedelta(days=days - 1)
# truncate the datetime to just the day
submission_date = (
cast(Submission.save_time, Date).label('submission_date')
)
query = self.session.query(submission_date, func.count())
if user_id is not None:
query = (
query
.join(Survey.submissions)
.outerjoin(_administrator_table)
.filter(administrator_filter(user_id))
)
query = query.filter(Submission.save_time >= from_date)
if survey_id is not None:
query = query.filter(Submission.survey_id == survey_id)
query = (
query
.group_by('submission_date')
.order_by(submission_date.desc())
)
# TODO: Figure out if this should use OrderedDict
return {'activity': [
{'date': date, 'num_submissions': num} for date, num in query
]}
0
Example 15
Project: bubbles Source File: ops.py
@added_rows.register("rows", "sql", name="added_rows")
def _(ctx, src, target, src_key, target_key=None):
src_key = prepare_key(src_key)
if target_key:
target_key = prepare_key(target_key)
else:
target_key = src_key
statement = target.sql_statement()
target_cols = target.columns(target_key)
field_filter = FieldFilter(keep=src_key).row_filter(src.fields)
def iterator():
for row in src.rows():
row_key = field_filter(row)
cond = zip_condition(target_cols, row_key)
select = sql.expression.select([sql.func.count(1)],
from_obj=statement,
whereclause=cond)
result = target.store.execute(select)
result = list(result)
if len(result) >= 1 and result[0][0] == 0:
yield row
return IterableDataSource(iterator(), fields=src.fields)
0
Example 16
Project: pokedex Source File: test_database_sanity.py
def test_default_forms(session):
"""Check that each pokemon has one default form and each species has one
default pokemon."""
q = session.query(tables.Pokemon)
q = q.join(tables.PokemonForm)
q = q.filter(tables.PokemonForm.is_default==True)
q = q.options(lazyload('*'))
q = q.group_by(tables.Pokemon)
q = q.add_columns(func.count(tables.PokemonForm.id))
for pokemon, num_default_forms in q:
if num_default_forms == 0:
pytest.fail("pokemon %s has no default forms" % pokemon.name)
elif num_default_forms > 1:
pytest.fail("pokemon %s has %d default forms" % (pokemon.name, num_default_forms))
q = session.query(tables.PokemonSpecies)
q = q.join(tables.Pokemon)
q = q.filter(tables.Pokemon.is_default==True)
q = q.options(lazyload('*'))
q = q.group_by(tables.PokemonSpecies)
q = q.add_columns(func.count(tables.Pokemon.id))
for species, num_default_pokemon in q:
if num_default_pokemon == 0:
pytest.fail("species %s has no default pokemon" % species.name)
elif num_default_pokemon > 1:
pytest.fail("species %s has %d default pokemon" % (species.name, num_default_pokemon))
0
Example 17
Project: Bookie Source File: __init__.py
@staticmethod
def find(limit=50, order_by=None, page=0, tags=None, username=None,
with_content=False, with_tags=True, requested_by=None):
"""Search for specific sets of bookmarks"""
qry = Bmark.query
qry = qry.join(Bmark.hashed).\
options(contains_eager(Bmark.hashed))
offset = limit * page
# If noqa is not used here the below error occurs with make lint.
# comparison to False should be 'if cond is False:'
# or 'if not cond:'
if not requested_by:
qry = qry.filter(Bmark.is_private == False) # noqa
elif requested_by != username:
qry = qry.filter(Bmark.is_private == False) # noqa
if username:
qry = qry.filter(Bmark.username == username)
if order_by is None:
order_by = Bmark.stored.desc()
if not tags:
qry = qry.order_by(order_by).\
limit(limit).\
offset(offset).\
from_self()
if tags:
tags = [tag.lower() for tag in tags] # For case matching
qry = qry.join(Bmark.tags).\
options(contains_eager(Bmark.tags))
if isinstance(tags, str):
qry = qry.filter(Tag.name == tags)
qry = qry.order_by(order_by).\
limit(limit).\
offset(offset).\
from_self()
else:
if username:
good_filter = and_(
Bmark.bid == bmarks_tags.c.bmark_id,
Bmark.username == username
)
else:
good_filter = (Bmark.bid == bmarks_tags.c.bmark_id)
bids_we_want = select(
[bmarks_tags.c.bmark_id.label('good_bmark_id')],
from_obj=[
bmarks_tags.join(
'tags',
and_(
Tag.name.in_(tags),
bmarks_tags.c.tag_id == Tag.tid
)
).
join('bmarks', good_filter)
]).\
group_by(bmarks_tags.c.bmark_id, Bmark.stored).\
having(
func.count(bmarks_tags.c.tag_id) >= len(tags)
).order_by(Bmark.stored.desc())
qry = qry.join(
(
bids_we_want.limit(limit).offset(offset).alias('bids'),
Bmark.bid == bids_we_want.c.good_bmark_id
)
)
# now outer join with the tags again so that we have the
# full list of tags for each bmark we filtered down to
if with_tags:
qry = qry.outerjoin(Bmark.tags).\
options(contains_eager(Bmark.tags))
if with_content:
qry = qry.outerjoin(Bmark.readable).\
options(contains_eager(Bmark.readable))
qry = qry.options(joinedload('hashed'))
return qry.order_by(order_by).all()
0
Example 18
Project: blitzdb Source File: queryset.py
def get_count_select(self):
s = self.get_bare_select(columns = [self.table.c.pk])
count_select = select([func.count()]).select_from(s.alias())
return count_select
0
Example 19
Project: Perspectives-Server Source File: notary_db.py
def count_services(self):
"""Return a count of the service records."""
with self._get_connection() as conn:
return conn.execute(select([func.count(Services.service_id)])).first()[0]
0
Example 20
Project: Perspectives-Server Source File: notary_db.py
def count_observations(self):
"""Return a count of the observation records."""
with self._get_connection() as conn:
return conn.execute(select([func.count(Observations.observation_id)])).first()[0]
0
Example 21
Project: changes Source File: jobphase_index.py
def get(self, job_id):
get_test_counts = request.args.get('test_counts', False)
job = Job.query.options(
subqueryload_all(Job.phases),
joinedload('project', innerjoin=True),
).get(job_id)
if job is None:
return '', 404
phase_list = list(JobPhase.query.options(
subqueryload_all(JobPhase.steps, JobStep.node),
subqueryload_all(JobPhase.steps, JobStep.logsources)
).filter(
JobPhase.job_id == job.id,
).order_by(JobPhase.date_started.asc(), JobPhase.date_created.asc()))
test_counts = {}
if get_test_counts:
rows = list(db.session.query(
TestCase.step_id,
func.count()
).filter(
TestCase.job_id == job.id,
TestCase.result == Result.failed,
).group_by(TestCase.step_id))
for row in rows:
test_counts[row[0]] = row[1]
logsource_registry = {LogSource: LogSourceWithoutStepCrumbler()}
context = []
for phase, phase_data in zip(phase_list, self.serialize(phase_list)):
phase_data['steps'] = []
for step, step_data in zip(phase.steps, self.serialize(list(phase.steps))):
step_data['logSources'] = self.serialize(list(step.logsources), extended_registry=logsource_registry)
if step.id in test_counts:
step_data['testFailures'] = test_counts[step.id]
step_data['commandTypeDurations'] = defaultdict(int)
for command in step.commands:
if command.duration is not None:
step_data['commandTypeDurations'][command.type.name] += command.duration
phase_data['steps'].append(step_data)
context.append(phase_data)
return self.respond(context, serialize=False)
0
Example 22
Project: changes Source File: jobstep_allocate.py
def find_next_jobsteps(self, limit=10, cluster=None):
cluster_filter = JobStep.cluster == cluster if cluster else JobStep.cluster.is_(None)
# find projects with pending allocations
project_list = [p for p, in db.session.query(
JobStep.project_id,
).filter(
JobStep.status == Status.pending_allocation,
cluster_filter,
).group_by(
JobStep.project_id
)]
if not project_list:
return []
# TODO(dcramer): this should be configurably and handle more cases
# than just 'active job' as that can be 1 step or 100 steps
# find the total number of job steps in progress per project
# hard limit of 10 active jobs per project
unavail_projects = [
p for p, c in
db.session.query(
Job.project_id, func.count(Job.project_id),
).filter(
Job.status.in_([Status.allocated, Status.in_progress]),
Job.project_id.in_(project_list),
).group_by(
Job.project_id,
)
if c >= 10
]
base_filters = [
JobStep.status == Status.pending_allocation,
cluster_filter,
]
filters = list(base_filters)
if unavail_projects:
filters.append(~JobStep.project_id.in_(unavail_projects))
base_queryset = JobStep.query.join(
Job, JobStep.job_id == Job.id,
).join(
Build, Job.build_id == Build.id,
).order_by(Build.priority.desc(), JobStep.date_created.asc())
# prioritize a job that's has already started
queryset = list(base_queryset.filter(
Job.status.in_([Status.allocated, Status.in_progress]),
*filters
)[:limit])
if len(queryset) == limit:
return queryset
results = queryset
# now allow any prioritized project, based on order
queryset = base_queryset.filter(
*filters
)
if results:
queryset = queryset.filter(~JobStep.id.in_(q.id for q in results))
results.extend(queryset[:limit - len(results)])
if len(results) >= limit:
return results[:limit]
# TODO(dcramer): we want to burst but not go too far. For now just
# let burst
queryset = base_queryset.filter(
*base_filters
)
if results:
queryset = queryset.filter(~JobStep.id.in_(q.id for q in results))
results.extend(queryset[:limit - len(results)])
return results[:limit]
0
Example 23
Project: changes Source File: project_details.py
def _get_green_percent(self, project, start_period, end_period):
build_counts = dict(db.session.query(
Build.result, func.count()
).join(
Source, Build.source_id == Source.id,
).filter(
Build.project_id == project.id,
Build.date_created >= start_period,
Build.date_created < end_period,
Build.status == Status.finished,
Build.result.in_([Result.passed, Result.failed]),
*build_type.get_any_commit_build_filters()
).group_by(
Build.result,
))
failed_builds = build_counts.get(Result.failed) or 0
passed_builds = build_counts.get(Result.passed) or 0
if passed_builds:
green_percent = int((passed_builds / (failed_builds + passed_builds)) * 100)
elif failed_builds:
green_percent = 0
else:
green_percent = None
return green_percent
0
Example 24
Project: changes Source File: system_stats.py
def _get_status_counts(self, cutoff):
excluded = [Status.finished, Status.collecting_results, Status.unknown]
build_stats = dict(db.session.query(
Build.status,
func.count(),
).filter(
Build.date_created >= cutoff,
~Build.status.in_(excluded),
).group_by(
Build.status,
))
job_stats = dict(db.session.query(
Job.status,
func.count(),
).filter(
Job.date_created >= cutoff,
~Job.status.in_(excluded),
).group_by(
Job.status,
))
jobstep_stats = dict(db.session.query(
JobStep.status,
func.count(),
).filter(
JobStep.date_created >= cutoff,
~JobStep.status.in_(excluded),
).group_by(
JobStep.status,
))
context = []
for status in Status.__members__.values():
if status in excluded:
continue
if status == Status.pending_allocation:
name = 'Pending Allocation'
else:
name = unicode(status)
context.append({
'name': name,
'numBuilds': build_stats.get(status, 0),
'numJobs': job_stats.get(status, 0),
'numJobSteps': jobstep_stats.get(status, 0),
})
return context
0
Example 25
Project: changes Source File: system_stats.py
def _get_result_counts(self, cutoff):
build_stats = dict(db.session.query(
Build.result,
func.count(),
).filter(
Build.date_created >= cutoff,
Build.status == Status.finished,
Build.result != Result.unknown,
).group_by(
Build.result,
))
job_stats = dict(db.session.query(
Job.result,
func.count(),
).filter(
Job.date_created >= cutoff,
Job.status == Status.finished,
Job.result != Result.unknown,
).group_by(
Job.result,
))
jobstep_stats = dict(db.session.query(
JobStep.result,
func.count(),
).filter(
JobStep.date_created >= cutoff,
JobStep.status == Status.finished,
JobStep.result != Result.unknown,
).group_by(
JobStep.result,
))
context = []
for result in Result.__members__.values():
if result in (Result.unknown, Result.skipped):
continue
context.append({
'name': unicode(result),
'numBuilds': build_stats.get(result, 0),
'numJobs': job_stats.get(result, 0),
'numJobSteps': jobstep_stats.get(result, 0),
})
return context
0
Example 26
Project: changes Source File: sync_job.py
def _find_and_retry_jobsteps(phase, implementation):
# phase.steps is ordered by date_started, so we retry the oldest jobsteps first
should_retry = [s for s in phase.steps if _should_retry_jobstep(s)]
if not should_retry:
return
already_retried = dict(db.session.query(JobStep.node_id, func.count(JobStep.node_id)).filter(
JobStep.job == phase.job,
JobStep.replacement_id.isnot(None)
).group_by(JobStep.node_id))
for step in should_retry:
# hard max on how many jobsteps we retry
if (sum(already_retried.itervalues()) >= current_app.config['JOBSTEP_RETRY_MAX']):
break
# max on how many different failing machines we'll retry jobsteps for.
if (step.node_id not in already_retried and len(already_retried) >= current_app.config['JOBSTEP_MACHINE_RETRY_MAX']):
break
newstep = implementation.create_replacement_jobstep(step)
if newstep:
statsreporter.stats().incr('jobstep_replaced')
# NB: node_id could be None if the jobstep failed before we got a node_id
already_retried[step.node_id] = already_retried.get(step.node_id, 0) + 1
0
Example 27
Project: changes Source File: build.py
def get_failure_stats_for_project(self, project, start_period, end_period):
base_query = db.session.query(
FailureReason.reason, FailureReason.build_id
).join(
Build, Build.id == FailureReason.build_id,
).join(
Source, Source.id == Build.source_id,
).join(
JobStep, JobStep.id == FailureReason.step_id,
).filter(
Build.project_id == project.id,
Build.date_created >= start_period,
Build.date_created < end_period,
JobStep.replacement_id.is_(None),
*build_type.get_any_commit_build_filters()
).group_by(
FailureReason.reason, FailureReason.build_id
).subquery()
return dict(
db.session.query(
base_query.c.reason,
func.count(),
).group_by(
base_query.c.reason,
)
)
0
Example 28
Project: snorkel Source File: annotations.py
def update(self, session, candidate_set, key_set, expand_key_set, f=None):
"""
Generates annotations for candidates in a candidate set and *adds* them to an existing annotation set,
also adding the respective keys to the key set; returns a sparse matrix representation of the full
candidate x annotation_key set.
:param session: SnorkelSession for the database
:param candidate_set: Can either be a CandidateSet instance or the name of one
:param key_set: Can either be an AnnotationKeySet instance or the name of one
:param expand_key_set: If True, annotations with keys not in the given key set will be added, and the
key set will be expanded; if False, these annotations will be considered out-of-domain (OOD) and discarded.
:param f: Can be either:
* A function which maps a candidate to a generator key_name, value pairs. Ex: A feature generator
* A list of functions, each of which maps from candidates to values; by default, the key_name
is the function.__name__. Ex: A list of labeling functions
"""
# Prepares arguments
candidate_set = get_ORM_instance(CandidateSet, session, candidate_set)
key_set = get_ORM_instance(AnnotationKeySet, session, key_set)
if f is None:
f = self.default_f
# Prepares helpers
annotation_generator = _to_annotation_generator(f) if hasattr(f, '__iter__') else f
pb = ProgressBar(len(candidate_set))
# Prepares queries
key_select_query = select([AnnotationKey.id]).where(AnnotationKey.name == bindparam('name'))
key_insert_query = AnnotationKey.__table__.insert()
assoc_select_query = select([func.count()]).select_from(assoc_table)
assoc_select_query = assoc_select_query.where(assoc_table.c.annotation_key_set_id == bindparam('ksid'))
assoc_select_query = assoc_select_query.where(assoc_table.c.annotation_key_id == bindparam('kid'))
assoc_insert_query = assoc_table.insert()
anno_update_query = self.annotation_cls.__table__.update()
anno_update_query = anno_update_query.where(self.annotation_cls.candidate_id == bindparam('cid'))
anno_update_query = anno_update_query.where(self.annotation_cls.key_id == bindparam('kid'))
anno_update_query = anno_update_query.values(value=bindparam('value'))
anno_insert_query = self.annotation_cls.__table__.insert()
# Generates annotations for CandidateSet
for i, candidate in enumerate(candidate_set):
pb.bar(i)
for key_name, value in annotation_generator(candidate):
# Check if the AnnotationKey already exists, and gets its id
key_id = session.execute(key_select_query, {'name': key_name}).first()
if key_id is not None:
key_id = key_id[0]
# If expand_key_set is True, then we will always insert or update the Annotation
if expand_key_set:
# If key_name does not exist in the database already, creates a new record
if key_id is None:
key_id = session.execute(key_insert_query, {'name': key_name}).inserted_primary_key[0]
# Adds the AnnotationKey to the AnnotationKeySet
if session.execute(assoc_select_query, {'ksid': key_set.id, 'kid': key_id}).scalar() == 0:
session.execute(assoc_insert_query, {'annotation_key_set_id': key_set.id, 'annotation_key_id': key_id})
# Updates the annotation value
res = session.execute(anno_update_query, {'cid': candidate.id, 'kid': key_id, 'value': value})
if res.rowcount == 0 and value != 0:
session.execute(anno_insert_query, {'candidate_id': candidate.id, 'key_id': key_id, 'value': value})
# Else, if the key already exists in the database, we just update the annotation value
elif key_id is not None:
res = session.execute(anno_update_query, {'cid': candidate.id, 'kid': key_id, 'value': value})
if res.rowcount == 0 and value != 0:
session.execute(anno_insert_query, {'candidate_id': candidate.id, 'key_id': key_id, 'value': value})
pb.close()
session.commit()
print "Loading sparse %s matrix..." % self.annotation_cls.__name__
return self.load(session, candidate_set, key_set)
0
Example 29
def get_top_participants(self, list_name, start, end, limit=None):
""" Return all the participants between two given dates.
:param list_name: The name of the mailing list in which this email
should be searched.
:param start: A datetime object representing the starting date of
the interval to query.
:param end: A datetime object representing the ending date of
the interval to query.
:param limit: Limit the number of participants to return. If None or
not supplied, return them all.
:returns: The list of thread-starting messages.
"""
part = self.db.query(Sender.name, Email.sender_email,
func.count(Email.sender_email)
).join(Email
).filter(and_(
Email.list_name == list_name,
Email.date >= start,
Email.date < end,
)).group_by(Email.sender_email, Sender.name
).order_by(desc(func.count(Email.sender_email)))
if limit is not None:
part = part.limit(limit)
return part.all()