Python core.get_session函数代码示例

def n_available_tasks(app_id, user_id=None, user_ip=None):
    """Returns the number of tasks for a given app a user can contribute to,
    based on the completion of the app tasks, and previous task_runs submitted
    by the user"""
    try:
        if user_id and not user_ip:
            query = text('''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
                           (SELECT task_id FROM task_run WHERE
                           app_id=:app_id AND user_id=:user_id AND task_id=task.id)
                           AND app_id=:app_id AND state !='completed';''')
            session = get_session(db, bind='slave')
            result = session.execute(query, dict(app_id=app_id, user_id=user_id))
        else:
            if not user_ip:
                user_ip = '127.0.0.1'
            query = text('''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
                           (SELECT task_id FROM task_run WHERE
                           app_id=:app_id AND user_ip=:user_ip AND task_id=task.id)
                           AND app_id=:app_id AND state !='completed';''')

            session = get_session(db, bind='slave')
            result = session.execute(query, dict(app_id=app_id, user_ip=user_ip))
        n_tasks = 0
        for row in result:
            n_tasks = row.n_tasks
        return n_tasks
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def overall_progress(app_id):
    """Returns the percentage of submitted Tasks Runs done when a task is
    completed"""
    try:
        sql = text('''SELECT task.id, n_answers,
                   COUNT(task_run.task_id) AS n_task_runs
                   FROM task LEFT OUTER JOIN task_run ON task.id=task_run.task_id
                   WHERE task.app_id=:app_id GROUP BY task.id''')
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(app_id=app_id))
        n_expected_task_runs = 0
        n_task_runs = 0
        for row in results:
            tmp = row[2]
            if row[2] > row[1]:
                tmp = row[1]
            n_expected_task_runs += row[1]
            n_task_runs += tmp
        pct = float(0)
        if n_expected_task_runs != 0:
            pct = float(n_task_runs) / float(n_expected_task_runs)
        return (pct * 100)
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def hidden_apps(user_id):
    try:
        sql = text('''
                   SELECT app.id, app.name, app.short_name, app.description,
                   app.owner_id,
                   app.info
                   FROM app, task
                   WHERE app.id=task.app_id AND app.owner_id=:user_id AND
                   app.hidden=1 AND app.info LIKE('%task_presenter%')
                   GROUP BY app.id, app.name, app.short_name,
                   app.description,
                   app.info;''')
        apps_published = []
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(user_id=user_id))
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       owner_id=row.owner_id,
                       description=row.description,
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=json.loads(row.info))
            apps_published.append(app)
        return apps_published
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def apps_contributed(user_id):
    try:
        sql = text('''
                   WITH apps_contributed as 
                        (SELECT DISTINCT(app_id) FROM task_run 
                         WHERE user_id=:user_id)
                   SELECT app.id, app.name, app.short_name, app.owner_id,
                   app.description, app.info FROM app, apps_contributed
                   WHERE app.id=apps_contributed.app_id ORDER BY app.name DESC;
                   ''')
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(user_id=user_id))
        apps_contributed = []
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       owner_id=row.owner_id,
                       description=row.description,
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=json.loads(row.info))
            apps_contributed.append(app)
        return apps_contributed
    except: # pragma: no cover
        session.rollback()
    finally:
        session.close()

def get_featured(category, page=1, per_page=5):
    """Return a list of featured apps with a pagination"""
    try:
        count = n_featured()

        sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
                   app.description,
                   "user".fullname AS owner FROM app, featured, "user"
                   WHERE app.id=featured.app_id AND app.hidden=0
                   AND "user".id=app.owner_id GROUP BY app.id, "user".id
                   OFFSET(:offset) LIMIT(:limit);
                   ''')
        offset = (page - 1) * per_page

        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(limit=per_page, offset=offset))
        apps = []
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created, description=row.description,
                       overall_progress=overall_progress(row.id),
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       owner=row.owner,
                       featured=row.id,
                       info=dict(json.loads(row.info)))
            apps.append(app)
        return apps, count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_draft(category, page=1, per_page=5):
    """Return list of draft projects"""
    try:
        count = n_draft()

        sql = text('''SELECT app.id, app.name, app.short_name, app.created,
                   app.description, app.info, "user".fullname as owner
                   FROM "user", app LEFT JOIN task ON app.id=task.app_id
                   WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
                   AND app.hidden=0
                   AND app.owner_id="user".id
                   OFFSET :offset
                   LIMIT :limit;''')

        offset = (page - 1) * per_page
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(limit=per_page, offset=offset))
        apps = []
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created,
                       description=row.description,
                       owner=row.owner,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       info=dict(json.loads(row.info)))
            apps.append(app)
        return apps, count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def n_count(category):
    """Count the number of apps in a given category"""
    try:
        sql = text('''
                   WITH uniq AS (
                   SELECT COUNT(app.id) FROM task, app
                   LEFT OUTER JOIN category ON app.category_id=category.id
                   WHERE
                   category.short_name=:category
                   AND app.hidden=0
                   AND app.info LIKE('%task_presenter%')
                   AND task.app_id=app.id
                   GROUP BY app.id)
                   SELECT COUNT(*) FROM uniq
                   ''')

        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(category=category))
        count = 0
        for row in results:
            count = row[0]
        return count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def create(taskrun=None):
    authorized = False
    try:
        session = get_session(db, bind='slave')
        if taskrun.user_ip:
            sql = text('''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
                          WHERE task_run.app_id=:app_id AND
                          task_run.task_id=:task_id AND
                          task_run.user_ip=:user_ip;''')
            results = session.execute(sql, dict(app_id=taskrun.app_id,
                                                task_id=taskrun.task_id,
                                                user_ip=taskrun.user_ip))
        elif taskrun.user_id:
            sql = text('''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
                          WHERE task_run.app_id=:app_id AND
                          task_run.task_id=:task_id AND
                          task_run.user_id=:user_id;''')
            results = session.execute(sql, dict(app_id=taskrun.app_id,
                                                task_id=taskrun.task_id,
                                                user_id=taskrun.user_id))
        else:
            return False
        n_task_runs = 0
        for row in results:
            n_task_runs = row.n_task_runs
        authorized = (n_task_runs <= 0)
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()
    if not authorized:
        raise abort(403)
    return authorized

def get_incremental_task(app_id, user_id=None, user_ip=None, n_answers=30, offset=0):
    """
    Get a new task for a given project with its last given answer.
    It is an important strategy when dealing with large tasks, as
    transcriptions.
    """
    try:
        session = get_session(db, bind='slave')
        candidate_tasks = get_candidate_tasks(app_id, user_id, user_ip,
                                              n_answers, offset=0)
        total_remaining = len(candidate_tasks)
        if total_remaining == 0:
            return None
        rand = random.randrange(0, total_remaining)
        task = candidate_tasks[rand]
        #Find last answer for the task
        q = session.query(TaskRun)\
              .filter(TaskRun.task_id == task.id)\
              .order_by(TaskRun.finish_time.desc())
        last_task_run = q.first()
        if last_task_run:
            task.info['last_answer'] = last_task_run.info
            #TODO: As discussed in GitHub #53
            # it is necessary to create a lock in the task!
        return task
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_candidate_tasks(app_id, user_id=None, user_ip=None, n_answers=30, offset=0):
    """Gets all available tasks for a given project and user"""
    try:
        session = get_session(db, bind='slave')
        rows = None
        if user_id and not user_ip:
            query = text('''
                         SELECT id FROM task WHERE NOT EXISTS
                         (SELECT task_id FROM task_run WHERE
                         app_id=:app_id AND user_id=:user_id AND task_id=task.id)
                         AND app_id=:app_id AND state !='completed'
                         ORDER BY priority_0 DESC, id ASC LIMIT 10''')
            rows = session.execute(query, dict(app_id=app_id, user_id=user_id))
        else:
            if not user_ip:
                user_ip = '127.0.0.1'
            query = text('''
                         SELECT id FROM task WHERE NOT EXISTS
                         (SELECT task_id FROM task_run WHERE
                         app_id=:app_id AND user_ip=:user_ip AND task_id=task.id)
                         AND app_id=:app_id AND state !='completed'
                         ORDER BY priority_0 DESC, id ASC LIMIT 10''')
            rows = session.execute(query, dict(app_id=app_id, user_ip=user_ip))

        tasks = []
        for t in rows:
            tasks.append(session.query(Task).get(t.id))
        return tasks
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def new_task(app_id, user_id=None, user_ip=None, offset=0):
    '''Get a new task by calling the appropriate scheduler function.
    '''
    try:
        session = get_session(db, bind='slave')
        app = session.query(App).get(app_id)
        if not app.allow_anonymous_contributors and user_id is None:
            info = dict(
                error="This project does not allow anonymous contributors")
            error = Task(info=info)
            return error
        else:
            sched_map = {
                'default': get_depth_first_task,
                'breadth_first': get_breadth_first_task,
                'depth_first': get_depth_first_task,
                'random': get_random_task,
                'incremental': get_incremental_task,
			'filter_by_users': get_filtered_by_user_task}
            sched = sched_map.get(app.info.get('sched'), sched_map['default'])
            return sched(app_id, user_id, user_ip, offset=offset)
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_locs(): # pragma: no cover
    try:
        session = get_session(db, bind='slave')
        # All IP addresses from anonymous users to create a map
        locs = []
        if current_app.config['GEO']:
            sql = '''SELECT DISTINCT(user_ip) from task_run WHERE user_ip IS NOT NULL;'''
            results = session.execute(sql)

            geolite = current_app.root_path + '/../dat/GeoLiteCity.dat'
            gic = pygeoip.GeoIP(geolite)
            for row in results:
                loc = gic.record_by_addr(row.user_ip)
                if loc is None:
                    loc = {}
                if (len(loc.keys()) == 0):
                    loc['latitude'] = 0
                    loc['longitude'] = 0
                locs.append(dict(loc=loc))
        return locs
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_top5_users_24_hours():
    try:
        session = get_session(db, bind='slave')
        # Top 5 Most active users in last 24 hours
        sql = text('''SELECT "user".id, "user".fullname, "user".name,
                   COUNT(task_run.app_id) AS n_answers FROM "user", task_run
                   WHERE "user".id=task_run.user_id
                   AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
                   AND DATE(task_run.finish_time) <= NOW()
                   GROUP BY "user".id
                   ORDER BY n_answers DESC LIMIT 5;''')

        results = session.execute(sql, dict(limit=5))
        top5_users_24_hours = []
        for row in results:
            user = dict(id=row.id, fullname=row.fullname,
                        name=row.name,
                        n_answers=row.n_answers)
            top5_users_24_hours.append(user)
        return top5_users_24_hours
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_top5_apps_24_hours():
    try:
        session = get_session(db, bind='slave')
        # Top 5 Most active apps in last 24 hours
        sql = text('''SELECT app.id, app.name, app.short_name, app.info,
                   COUNT(task_run.app_id) AS n_answers FROM app, task_run
                   WHERE app.id=task_run.app_id
                   AND app.hidden=0
                   AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
                   AND DATE(task_run.finish_time) <= NOW()
                   GROUP BY app.id
                   ORDER BY n_answers DESC LIMIT 5;''')

        results = session.execute(sql, dict(limit=5))
        top5_apps_24_hours = []
        for row in results:
            tmp = dict(id=row.id, name=row.name, short_name=row.short_name,
                       info=dict(json.loads(row.info)), n_answers=row.n_answers)
            top5_apps_24_hours.append(tmp)
        return top5_apps_24_hours
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def draft_apps(user_id):
    try:
        sql = text('''
                   SELECT app.id, app.name, app.short_name, app.description,
                   owner_id,
                   app.info
                   FROM app
                   WHERE app.owner_id=:user_id
                   AND app.info NOT LIKE('%task_presenter%')
                   GROUP BY app.id, app.name, app.short_name,
                   app.description,
                   app.info;''')
        apps_draft = []
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(user_id=user_id))
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       owner_id=row.owner_id,
                       description=row.description,
                       info=json.loads(row.info))
            apps_draft.append(app)
        return apps_draft
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()