def subreddit_stats(config, ranges): def get_id(*args, **kwargs): kwargs.setdefault('limit', 1) results = list(kind._query(*args, **kwargs)) if not results: return None else: return results[0]._id sr_counts = defaultdict(int) for kind in (Link, Comment): thing_table, data_table = get_thing_table(kind._type_id) first_id = get_id(kind.c._date > ranges['yesterday'][0], sort=asc('_date')) last_id = get_id(kind.c._date < ranges['yesterday'][1], sort=desc('_date')) if not first_id or not last_id: continue q = sa.select([data_table.c.value, sa.func.count(data_table.c.value)], (data_table.c.thing_id > first_id) & (data_table.c.thing_id < last_id) & (data_table.c.key == 'sr_id') & (thing_table.c.thing_id == data_table.c.thing_id) & (thing_table.c.spam == False), group_by=data_table.c.value) for sr_id, count in q.execute(): sr_counts[sr_id] += count return { 'subreddits_active_yesterday': len(list(count for count in sr_counts.itervalues() if count > 5)) }
def subreddit_stats(config, ranges): def get_id(*args, **kwargs): kwargs.setdefault('limit', 1) results = list(kind._query(*args, **kwargs)) if not results: return None else: return results[0]._id sr_counts = defaultdict(int) for kind in (Link, Comment): thing_table, data_table = get_thing_table(kind._type_id) first_id = get_id(kind.c._date > ranges['yesterday'][0], sort=asc('_date')) last_id = get_id(kind.c._date < ranges['yesterday'][1], sort=desc('_date')) if not first_id or not last_id: continue q = sa.select([data_table.c.value, sa.func.count(data_table.c.value)], (data_table.c.thing_id > first_id) & (data_table.c.thing_id < last_id) & (data_table.c.key == 'sr_id') & (thing_table.c.thing_id == data_table.c.thing_id) & (thing_table.c.spam == False), group_by=data_table.c.value) for sr_id, count in q.execute(): sr_counts[sr_id] += count return {'subreddits_active_yesterday': len(list(count for count in sr_counts.itervalues() if count > 5))}
def total_things(self, table_name, spam=None, all_time=None): """Return totals based on items in the thing tables.""" t = tdb_sql.get_thing_table(table_name)[0] s = sa.select([sa.func.count(t.c.thing_id)]) if spam: s.append_whereclause(t.c.spam == spam) s.append_whereclause(t.c.deleted == 'f') s = self.append_date_clause(t, s, all_time=all_time) return s.execute().fetchone()[0]
def total_things(self, table_name, spam=None, all_time=None): """Return totals based on items in the thing tables.""" t = tdb_sql.get_thing_table(table_name)[0] s = sa.select([sa.func.count(t.c.thing_id)]) if spam: s.append_whereclause(t.c.spam==spam) s.append_whereclause(t.c.deleted=='f') s = self.append_date_clause(t, s, all_time=all_time) return s.execute().fetchone()[0]
def top_users(): tt, dt = tdb.get_thing_table(Account._type_id) karma = dt.alias() s = sa.select([tt.c.thing_id], sa.and_(tt.c.spam == False, tt.c.deleted == False, karma.c.thing_id == tt.c.thing_id, karma.c.key == 'link_karma'), order_by = sa.desc(sa.cast(karma.c.value, sa.Integer)), limit = 10) rows = s.execute().fetchall() return [r.thing_id for r in rows]
def find_tz(): q = Link._query(sort=desc('_hot'), limit=1) link = list(q)[0] t = tdb_sql.get_thing_table(Link._type_id)[0] s = sa.select([sa.func.hot(t.c.ups, t.c.downs, t.c.date), t.c.thing_id], t.c.thing_id == link._id) db_hot = s.execute().fetchall()[0].hot.__float__() db_hot == round(db_hot, 7) for tz_name in pytz.common_timezones: tz = pytz.timezone(tz_name) sorts.epoch = datetime(1970, 1, 1, tzinfo=tz) if db_hot == link._hot: print tz_name
def find_tz(): q = Link._query(sort = desc('_hot'), limit = 1) link = list(q)[0] t = tdb_sql.get_thing_table(Link._type_id)[0] s = sa.select([sa.func.hot(t.c.ups, t.c.downs, t.c.date), t.c.thing_id], t.c.thing_id == link._id) db_hot = s.execute().fetchall()[0].hot.__float__() db_hot == round(db_hot, 7) for tz_name in pytz.common_timezones: tz = pytz.timezone(tz_name) sorts.epoch = datetime(1970, 1, 1, tzinfo = tz) if db_hot == link._hot: print tz_name
def subreddit_stats(config): sr_counts = defaultdict(int) for kind in (Link, Comment): thing_table, data_table = get_thing_table(kind._type_id) first_id = list(kind._query(kind.c._date > timeago('1 day'), sort=asc('_date'), limit=1)) if not first_id: continue else: first_id = first_id[0]._id q = sa.select([data_table.c.value, sa.func.count(data_table.c.value)], (data_table.c.thing_id > first_id) & (data_table.c.key == 'sr_id') & (thing_table.c.thing_id == data_table.c.thing_id) & (thing_table.c.spam == False), group_by=data_table.c.value) for sr_id, count in q.execute(): sr_counts[sr_id] += count return {'subreddits_active_past_day': len(list(count for count in sr_counts.itervalues() if count > 5))}
def css_stats(self, val, all_time=None): """Create stats related to custom css and headers.""" t = tdb_sql.get_thing_table('subreddit')[1] s = sa.select([sa.func.count(t.c.key)], t.c.key == val) return s.execute().fetchone()[0]
def css_stats(self, val, all_time=None): """Create stats related to custom css and headers.""" t = tdb_sql.get_thing_table('subsciteit')[1] s = sa.select([sa.func.count(t.c.key)], t.c.key == val) return s.execute().fetchone()[0]