示例#1
0
文件: board.py 项目: retooth/morse
def undiscovered_topic_stats (board_str):
    """ 
    returns a JSON undiscovered topic stats object for a board defined by board_str
    (structure {unreadCount, firstUnreadID}) this function is called via ajax in index.js
    """
    board_id = int(board_str.split("-")[0])

    board = Board.query.get(board_id)
    if not board:
        return "boardnotfound", 404

    if current_user.is_anonymous():
        return jsonify(undiscoveredCount = 0, firstUndiscoveredID = None)    

    topic_id_generator = Topic.query.filter(Topic.board_id == board_id).values(Topic.id)
    topic_ids = [oneple[0] for oneple in topic_id_generator] 

    discovered_ids_generator = DiscoveredTopic.query.filter(DiscoveredTopic.user_id == current_user.id, 
                                                            DiscoveredTopic.topic_id.in_(topic_ids)).values(DiscoveredTopic.topic_id)
    discovered_ids = [oneple[0] for oneple in discovered_ids_generator] 
    
    undiscovered_count = Topic.query.filter(Topic.board_id == board_id, not_(Topic.id.in_(discovered_ids))).count()
    first_undiscovered = Topic.query.filter(Topic.board_id == board_id, not_(Topic.id.in_(discovered_ids))).first()

    if not first_undiscovered:
        first_undiscovered_id = None
    else:
        first_undiscovered_id = first_undiscovered.id

    return jsonify(undiscoveredCount = undiscovered_count, firstUndiscoveredID = first_undiscovered_id)    
示例#2
0
文件: manager.py 项目: BtbN/esaupload
def post_process_video(session: Session, debug):
    vids = session.query(Video)
    vids = vids.filter(Video.processing_done)
    vids = vids.filter(not_(Video.post_processing_done))
    vids = vids.filter(or_(and_(Video.do_twitch, Video.done_twitch), not_(Video.do_twitch)))
    vids = vids.filter(or_(and_(Video.do_youtube, Video.done_youtube), not_(Video.do_youtube)))
    vids = vids.order_by(Video.id.asc())
    vid = vids.first()

    if not vid:
        print("No video in need of processing found")
        return 0

    out_dir = get_setting(session, "output_directory")
    final_dir = get_setting(session, "final_output_directory")
    out_prefix = get_setting(session, "output_video_prefix")
    out_ext = get_setting(session, "output_video_extension")

    out_fname = "%s%s%s" % (out_prefix, vid.id, out_ext)
    out_path = os.path.join(out_dir, out_fname)
    final_path = os.path.join(final_dir, out_fname)

    if out_path != final_path:
        shutil.move(out_path, final_path)
        vid.post_processing_status = "Moved %s to %s" % (out_path, final_path)
    else:
        vid.post_processing_status = "Nothing to do"

    vid.post_processing_done = True
    session.commit()

    return 0
示例#3
0
    def get_related(table, attr, title):
        if attr == "bra":
            data_attr = category
        else:
            data_attr = attr
        if data_attr == "hs" or data_attr == "wld":
            dataset = "secex"
        elif data_attr == "cnae" or data_attr == "cbo":
            dataset = "rais"
        elif data_attr == "university" or data_attr == "course_hedu":
            dataset = "hedu"
        elif data_attr == "course_sc":
            dataset = "sc"
        else:
            return
        q = table.query.filter(getattr(table, "{}_id".format(category)) == id) \
                 .filter(getattr(table, "year") == parse_year(__year_range__[dataset][1]))

        if category in ("bra"):
            q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("0xx")))
        elif category in ("cnae", "cbo", "wld"):
            q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("xx")))
        elif category in ("hs"):
            q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("22")))
        elif category in ("course_hedu", "course_sc"):
            q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("00")))

        q = q.order_by(getattr(table, "prox_{}".format(attr)).desc()).limit(40).all()
        if len(q) > 0:
            m = globals()[category.capitalize()]
            q = [(m.query.get(getattr(a, "{}_id_target".format(category))), getattr(a, "prox_{}".format(attr))) for a in q]
            related.append({"title": title, "pages": q})
示例#4
0
def randomProfile(category = None):

    ids = db.session.query(Search.id).filter(Search.kind == category)

    if category in ("bra"):
        ids = ids.filter(not_(Search.id.startswith("0xx")))
    elif category in ("cnae", "cbo", "wld"):
        ids = ids.filter(not_(Search.id.startswith("xx")))
    elif category in ("hs"):
        ids = ids.filter(not_(Search.id.startswith("22")))
    elif category in ("course_hedu", "course_sc"):
        ids = ids.filter(not_(Search.id.startswith("00")))

    if category == "bra":
        ids = ids.filter(func.length(Search.id) == 9)
    elif category in ("cnae", "hs", "course_hedu"):
        ids = ids.filter(func.length(Search.id) == 6)
    elif category == ("wld", "course_sc"):
        ids = ids.filter(func.length(Search.id) == 5)
    elif category == "cnae":
        ids = ids.filter(func.length(Search.id) == 4)

    ids = ids.order_by(Search.weight.desc()).limit(100).all()
    rand = random.randrange(0, len(ids))
    id = ids[rand][0]

    return redirect(url_for("profiles.profiles", category=category, id=id))
示例#5
0
    def get_metadata_counts(self, params, project, property):
        if "query_id" in params:
            values = db.session.query(
                Property.value.label("value"),
                func.count(Property.unit_id.distinct()).label("unit_count")
            ).\
            join(
                PropertyOfSentence,
                PropertyOfSentence.property_id == Property.id).\
            join(SentenceInQuery,
                PropertyOfSentence.sentence_id ==
                SentenceInQuery.sentence_id).\
            filter(SentenceInQuery.query_id == params["query_id"][0]).\
            filter(not_(Property.name.contains("_set"))).\
            filter(Property.name == property.property_name).\
            group_by(Property.value)
        else:
            values = db.session.query(
                Property.value.label('value'),
                PropertyCount.sentence_count.label("unit_count")
            ).filter(Property.project_id == project.id).\
            filter(not_(Property.name.contains("_set"))).\
            filter(Property.name == property.property_name).\
            filter(PropertyCount.property_value == Property.value).\
            order_by(desc("unit_count")).\
            group_by(Property.value).\
            limit(20)

        return values.all()
示例#6
0
文件: UserListAPI.py 项目: PageLib/ws
    def get(self):
        ensure_allowed('read', 'user')

        parser = reqparse.RequestParser()
        parser.add_argument('login', type=str, location='values')
        parser.add_argument('first_name', type=str, location='values')
        parser.add_argument('last_name', type=str, location='values')
        args = parser.parse_args()
        
        query = request.dbs.query(User).join(model.Entity)\
                                       .filter(not_(model.User.deleted))\
                                       .filter(not_(model.Entity.deleted))

        # Optional filters
        if args.get('login', None):
            query = query.filter(User.login == args['login'])

        if args.get('first_name', None):
            name_search = '%' + args['first_name'] + '%'
            query = query.filter(User.first_name.like(name_search))

        if args.get('last_name', None):
            name_search = '%' + args['last_name'] + '%'
            query = query.filter(User.first_name.like(name_search))

        return {'users': map(lambda u: marshal(u.to_dict(), user_fields), query.all())}
示例#7
0
文件: topic.py 项目: retooth/morse
def unread_post_stats (topic_str):
    """ 
    returns a JSON unread post stats object for a topic defined by topic_str 
    (structure {unreadCount, firstUnreadID}) this function is called via ajax 
    in board.js
    """
    topic_id = int(topic_str.split("-")[0])

    topic = Topic.query.get(topic_id)
    if not topic:
        return "topicnotfound", 404

    if current_user.is_anonymous():
        return jsonify(unreadCount = 0, firstUnreadID = None)    

    post_id_generator = Post.query.filter(Post.topic_id == topic_id).values(Post.id)
    post_ids = [oneple[0] for oneple in post_id_generator] 

    read_ids_generator = ReadPost.query.filter(ReadPost.user_id == current_user.id, ReadPost.post_id.in_(post_ids)).values(ReadPost.post_id)
    read_ids = [oneple[0] for oneple in read_ids_generator] 
    
    unread_count = Post.query.filter(Post.topic_id == topic_id, not_(Post.id.in_(read_ids))).count()
    first_unread = Post.query.filter(Post.topic_id == topic_id, not_(Post.id.in_(read_ids))).first()

    if not first_unread:
        first_unread_id = None
    else:
        first_unread_id = first_unread.id

    return jsonify(unreadCount = unread_count, firstUnreadID = first_unread_id)    
示例#8
0
    def __updateMachinesTable(self, connection, uuids = []):
        """
        Remove all rows in the Machines table that are no more needed

        if a list of uuids is given, only ghost for the given computers are
        looking for.
        """
        # Get all Machines id that are not a foreign key in Results
        if uuids:
            todelete = connection.execute(
                select([Machines.id], and_(
                    Machines.uuid.in_(uuids),
                    not_(
                        or_(
                            Machines.id.in_(select([Results.FK_machines])),
                            Machines.id.in_(select([ProfilesResults.FK_machines]))
                        )
                    )
                ))).fetchall()
        else:
            todelete = connection.execute(
                select([Machines.id], not_(
                    or_(
                        Machines.id.in_(select([Results.FK_machines])),
                        Machines.id.in_(select([ProfilesResults.FK_machines]))
                    )
                ))).fetchall()
        todelete = [{"id" : x[0]} for x in todelete]
        # Delete them if any
        if todelete:
            connection.execute(self.machines.delete(Machines.id == bindparam("id")), todelete)
示例#9
0
 def get_cv_count_by_loc(self, fold_count=10):
     '''
     Returns a list of pairs
     '''
     # Get list of dates and assign to folds of the same size
     all_dates = [date[0].strftime('%Y-%m-%d') for date in self.session.query(sqlalchemy.distinct(Rank.date))]
     random.shuffle(all_dates)
     fold_size = int(math.floor(len(all_dates) / fold_count))
     fold_dates = [all_dates[i:i+fold_size] for i in range(0, len(all_dates), fold_size)]
     folds = []
     for i in range(0, fold_count):
         # Query ranks for all videos
         ranks = self.session.query(Rank.video_id, Rank.loc, sqlalchemy.sql.func.count('*').label('entries')).\
             filter(sqlalchemy.not_(Rank.loc.like('%all_%'))).\
             filter_by(source='view').\
             group_by(Rank.video_id, Rank.loc).\
             filter(Rank.date.in_(sum([fold_dates[f] for f in range(0,fold_count) if f != i], [])))
         training = self._query_to_count_by_loc(ranks)
         ranks = self.session.query(Rank.video_id, Rank.loc, sqlalchemy.sql.func.count('*').label('entries')).\
             filter(sqlalchemy.not_(Rank.loc.like('%all_%'))).\
             filter_by(source='view').\
             group_by(Rank.video_id, Rank.loc).\
             filter(Rank.date.in_(fold_dates[i]))
         test = self._query_to_count_by_loc(ranks)
         folds.append((training, test))
     return folds
示例#10
0
文件: UserAPI.py 项目: PageLib/ws
    def delete(self, user_id):
        """
        Deletes a user.
        """
        # Check permission
        resource = 'own_user' if user_id == request.ws_session.user_id else 'user'
        ensure_allowed('delete', resource)

        try:
            user = request.dbs.query(model.User).join(model.Entity)\
                                                .filter(model.User.id == user_id)\
                                                .filter(not_(model.User.deleted))\
                                                .filter(not_(model.Entity.deleted)).one()

            user.deleted = True
            app.logger.info('User {} deleted'.format(user_id))
            return {'user_deleted': True}, 200

        except NoResultFound:
            app.logger.warning('DELETE Request on non existing user {}'.format(user_id))
            return 404

        except MultipleResultsFound:
            app.logger.error('Multiple results found for user {} on DELETE UserAPI'.format(user_id))
            return 500
示例#11
0
def getTestSeries(branches, start_date, test_names, last_run=None):
    # Find all the Branch/OS/Test combinations
    if len(test_names) > 0:
        test_clause = db.tests.pretty_name.in_(test_names)
    else:
        test_clause = True
    q = sa.select(
            [db.branches.id.label('branch_id'), db.branches.name.label('branch_name'), db.os_list.id.label('os_id'), db.os_list.name.label('os_name'), db.tests.id.label('test_id'), db.tests.pretty_name, db.tests.name.label('test_name')],
            sa.and_(
                db.test_runs.machine_id == db.machines.id,
                db.builds.id == db.test_runs.build_id,
                db.os_list.id == db.machines.os_id,
                db.tests.id == db.test_runs.test_id,
                db.test_runs.date_run > start_date,
                db.branches.name.in_(branches),
                goodNameClause,
                sa.not_(db.machines.name.like('%stage%')),
                sa.not_(db.tests.pretty_name.like("%NoChrome%")),
                sa.not_(db.tests.pretty_name.like("%Fast Cycle%")),
                test_clause,
            ))

    if last_run:
        q = q.where(db.test_runs.id > last_run)
    q = q.distinct()

    retval = []
    for row in q.execute():
        retval.append(TestSeries(*row))
    return retval
示例#12
0
    def test_in_filtering(self):
        """test the behavior of the in_() function."""

        users.insert().execute(user_id=7, user_name="jack")
        users.insert().execute(user_id=8, user_name="fred")
        users.insert().execute(user_id=9, user_name=None)

        s = users.select(users.c.user_name.in_([]))
        r = s.execute().fetchall()
        # No username is in empty set
        assert len(r) == 0

        s = users.select(not_(users.c.user_name.in_([])))
        r = s.execute().fetchall()
        # All usernames with a value are outside an empty set
        assert len(r) == 2

        s = users.select(users.c.user_name.in_(["jack", "fred"]))
        r = s.execute().fetchall()
        assert len(r) == 2

        s = users.select(not_(users.c.user_name.in_(["jack", "fred"])))
        r = s.execute().fetchall()
        # Null values are not outside any set
        assert len(r) == 0
示例#13
0
def db_fix_fields_attrs(session):
    """
    Ensures that the current store and the field_attrs.json file correspond.
    The content of the field_attrs dict is used to add and remove all of the
    excepted forms of field_attrs for FieldAttrs in the db.
    """
    field_attrs = read_json_file(Settings.field_attrs_file)

    std_lst = [u'inputbox', u'textarea', u'checkbox', u'tos', u'date']

    for field_type, attrs_dict in field_attrs.items():
        attrs_to_keep_for_type = attrs_dict.keys()
        if field_type in std_lst:
            # Ensure that the standard field attrs do not have extra attr rows
            _filter = not_(models.FieldAttr.name.in_(attrs_to_keep_for_type)), \
                      models.FieldAttr.field_id == models.Field.id, \
                      models.Field.type == field_type, \
                      models.Field.template_id == None
        else:
            # Look for dropped attrs in non-standard field_groups like whistleblower_identity
            _filter = not_(models.FieldAttr.name.in_(attrs_to_keep_for_type)), \
                      models.FieldAttr.field_id == models.Field.id, \
                      models.Field.template_id == field_type

        for res in session.query(models.FieldAttr).filter(*_filter):
            session.delete(res)

    # Add keys to the db that have been added to field_attrs
    for field in session.query(models.Field):
        type = field.type if field.template_id is None else field.template_id
        attrs = field_attrs.get(type, {})
        db_update_fieldattrs(session, field.id, attrs, None)
示例#14
0
def update_commutes(session, dests, modes, chunksize=50, delay=5, verbose=True, **kwargs):
    """
    Look up commute distances and times from Google Maps API for posts in the
    database that are missing commute information.
    """

    query = (
        session
        .query(ApartmentPost)
        .outerjoin(ApartmentPost.commutes)
        .filter(not_(ApartmentPost.commutes.any()))
        .filter(not_(ApartmentPost.latitude == None)))

    num_updates = query.count()
    num_processed = 0

    for posts in grouper(chunksize, query):
        _process_batch(session, posts, dests, modes, **kwargs)  
        num_processed += len(posts)
        print "{}/{} commutes processed".format(num_processed, num_updates)
        _random_pause(delay)

    try:
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()
示例#15
0
 def _query_citing_records(self, show_duplicates=False):
     """Returns records which cites this one."""
     index_ref = self._get_index_ref()
     if not index_ref:
         raise Exception("There is no index_ref for this object")
     citation_query = RecordMetadata.query.with_entities(RecordMetadata.id,
                                                         RecordMetadata.json['control_number'])
     citation_filter = referenced_records(RecordMetadata.json).contains([index_ref])
     filter_deleted_records = or_(not_(type_coerce(RecordMetadata.json, JSONB).has_key('deleted')),  # noqa: W601
                                  not_(RecordMetadata.json['deleted'] == cast(True, JSONB)))
     only_literature_collection = type_coerce(RecordMetadata.json, JSONB)['_collections'].contains(['Literature'])
     filter_superseded_records = or_(
         not_(type_coerce(RecordMetadata.json, JSONB).has_key('related_records')),  # noqa: W601
         not_(type_coerce(RecordMetadata.json, JSONB)['related_records'].contains([{'relation': 'successor'}]))
     )
     citations = citation_query.filter(citation_filter,
                                       filter_deleted_records,
                                       filter_superseded_records,
                                       only_literature_collection)
     if not show_duplicates:
         # It just hides duplicates, and still can show citations
         # which do not have proper PID in PID store
         # Duplicated data should be removed with the CLI command
         citations = citations.distinct(RecordMetadata.json['control_number'])
     return citations
示例#16
0
文件: app.py 项目: PageLib/ws
def check_permission_action_internal(session_id, action, resource, user_id):
    try:
        session = request.dbs.query(model.Session).join(model.User)\
                                                  .join(model.Entity)\
                                                  .filter(not_(model.User.deleted))\
                                                  .filter(not_(model.Entity.deleted))\
                                                  .filter(model.Session.id == session_id)\
                                                  .filter(model.Session.user_id == user_id).one()

        # Check that the session is still active
        if not session.is_active:
            app.logger.info('Session {} expired, unable to check permission'.format(session_id))
            return {'error': 'session_expired'}, 404

        # Refresh the session
        session.refreshed = datetime.datetime.now()

        # Check permission
        allowed = bool(acl.is_allowed(session.role, action, resource))
        app.logger.info('Permission {} for action {} on {} for user {} in session {}'.format(
            'granted' if allowed else 'denied', action, resource, user_id, session_id))
        return {'allowed': allowed}, 200

    except NoResultFound:
        app.logger.warning('No result found for user {} and session {}'.format(user_id, session_id))
        return {'error': 'invalid_session'}, 404

    except MultipleResultsFound:
        app.logger.error('Multiple results found for user {} and session {}'.format(user_id, session_id))
        return '', 500

    except AssertionError:
        app.logger.error('Request on non existing resource: {}'.format(resource))
        return {'error': 'invalid_resource'}, 404
示例#17
0
文件: app.py 项目: PageLib/ws
def session_info_action(session_id, user_id):
    try:
        # Find the session
        session = request.dbs.query(model.Session).join(model.User)\
                                                  .join(model.Entity)\
                                                  .filter(not_(model.User.deleted))\
                                                  .filter(not_(model.Entity.deleted))\
                                                  .filter(model.Session.id == session_id)\
                                                  .filter(model.Session.user_id == user_id).one()

        # Check that the session is still active
        if not session.is_active:
            app.logger.info('Session {} expired, unable to get session data'.format(session_id))
            return '', 404

        resp_data = {
            'session_id': session.id,
            'user_id': session.user_id,
            'opened': session.opened.isoformat(),
            'refreshed': session.refreshed.isoformat(),
            'expires': session.expires.isoformat()
        }
        app.logger.info('Informed about session {} for user {}'.format(session_id, user_id))
        return resp_data, 200

    except NoResultFound:
        app.logger.warning('Try to check NON existing session {} for user {}'.format(session_id, user_id))
        return '', 404

    except MultipleResultsFound:
        app.logger.error('Multiple results found on query for session with session:{} and user:{}in v1:logout'.format(session_id, user_id))
        return '', 500
示例#18
0
文件: api.py 项目: windskyer/k_cinder
def _ibm_volume_get_all_except_key_query(context,
                                         key=None,
                                         session=None):
    # Find all the ids in volumes that has is_boot_volume key in
    # volume_metadata table. Create a subquery.
    if not session:
        # it takes ~ 4s to get_session for the first time.
        session = cinder_db.get_session()
    with session.begin():
        boot_vols = model_query(context,
                                cinder_models.Volume.id,
                                session=session).\
            join("volume_metadata").\
            filter(cinder_models.VolumeMetadata.key == key).\
            subquery()
        # Filter out all the boot volumes
        if cinder_db.is_admin_context(context):
            return model_query(context,
                               cinder_models.Volume,
                               session=session).\
                options(joinedload('volume_metadata')).\
                options(joinedload('volume_admin_metadata')).\
                filter(not_(cinder_models.Volume.id.in_(boot_vols)))
        else:
            return model_query(context,
                               cinder_models.Volume,
                               session=session).\
                options(joinedload('volume_metadata')).\
                filter(not_(cinder_models.Volume.id.in_(boot_vols)))
示例#19
0
 def find_matching_subscriptions(s3_path):
     subscription_daos = SubscriptionDao.query\
         .join(DatasetDao, DatasetDao.id == SubscriptionDao.data['dataset_id'].astext)\
         .filter(SubscriptionDao.data['state'].astext == SubscriptionState.ACTIVE)\
         .filter(
             or_(
                 SubscriptionDao.data['s3_path_start_prefix_inclusive'].astext <= s3_path,
                 SubscriptionDao.data['s3_path_start_prefix_inclusive'] == 'null',
                 not_(SubscriptionDao.data.has_key('s3_path_start_prefix_inclusive')),
             ).self_group()
         )\
         .filter(
             or_(
                 SubscriptionDao.data['s3_path_end_prefix_exclusive'].astext > s3_path,
                 SubscriptionDao.data['s3_path_end_prefix_exclusive'] == 'null',
                 not_(SubscriptionDao.data.has_key('s3_path_end_prefix_exclusive')),
             ).self_group()
         )\
         .filter(
             or_(
                 literal(s3_path).op('~')(cast(SubscriptionDao.data['s3_path_regex_filter'].astext, String)),
                 SubscriptionDao.data['s3_path_regex_filter'] == 'null',
                 not_(SubscriptionDao.data.has_key('s3_path_regex_filter')),
             ).self_group()
         )\
         .filter(literal(s3_path).like(DatasetDao.data['location'].astext + '%'))\
         .all()
     return [s.to_model() for s in subscription_daos]
示例#20
0
文件: util.py 项目: kublaj/glottolog3
def match_obsolete_refs(args):
    with open(args.data_file(args.version, 'obsolete_refs.json')) as fp:
        refs = json.load(fp)
    matched = args.data_file(args.version, 'obsolete_refs_matched.json')
    if matched.exists():
        with open(matched) as fp:
            matched = json.load(fp)
    else:
        matched = {}

    #
    # TODO: optionally re-evaluate known-unmatched refs!
    #

    count = 0
    f, m = 0, 0
    for id_ in refs:
        if id_ in matched:
            continue
        count += 1
        if count > 1000:
            print '1000 obsolete refs processed!'
            break
        ref = Ref.get(id_)
        found = False
        if ref.description and len(ref.description) > 5:
            for match in DBSession.query(Ref)\
                    .filter(not_(Source.id.in_(refs)))\
                    .filter(Source.description.contains(ref.description))\
                    .filter(or_(Source.author == ref.author, Source.year == ref.year))\
                    .limit(10):
                print '++', ref.id, '->', match.id, '++', ref.author, '->', match.author, '++', ref.year, '->', match.year
                matched[ref.id] = match.id
                found = True
                break
            if not found and ref.name and len(ref.name) > 5:
                for match in DBSession.query(Ref)\
                        .filter(not_(Source.id.in_(refs)))\
                        .filter(Source.name == ref.name)\
                        .limit(10):
                    try:
                        if match.description and ref.description and slug(match.description) == slug(ref.description):
                            print '++', ref.id, '->', match.id, '++', ref.description, '->', match.description
                            matched[ref.id] = match.id
                            found = True
                            break
                    except AssertionError:
                        continue
        if not found:
            m += 1
            print '--', ref.id, ref.name, ref.description
            matched[ref.id] = None
        else:
            f += 1
    print f, 'found'
    print m, 'missed'

    with open(args.data_file(args.version, 'obsolete_refs_matched.json'), 'w') as fp:
        json.dump(matched, fp)
def main():
    ini_file = sys.argv[1]
    conf_parser = ConfigParser.ConfigParser( {'here': os.getcwd()} )
    conf_parser.read( ini_file )
    configuration = {}
    for key, value in conf_parser.items( "app:main" ):
        configuration[key] = value
    database_connection = configuration['database_connection']
    file_path = configuration['file_path']
    app = TestApplication( database_connection=database_connection, file_path=file_path )
    jobs = {}
    try:
        for job in app.model.Job.filter( sa.and_( app.model.Job.table.c.create_time.between( '2008-05-23', '2008-11-29' ),
                                                  app.model.Job.table.c.state == 'ok',
                                                  sa.or_( sa.and_( sa.or_( app.model.Job.table.c.tool_id == 'gops_intersect_1',
                                                                           app.model.Job.table.c.tool_id == 'gops_subtract_1',
                                                                           app.model.Job.table.c.tool_id == 'gops_coverage_1' ),
                                                                   sa.not_( app.model.Job.table.c.command_line.like( '%-2 1,2,3%' ) ) ),
                                                          sa.and_( sa.or_( app.model.Job.table.c.tool_id == 'gops_complement_1',
                                                                           app.model.Job.table.c.tool_id == 'gops_merge_1' ),
                                                                   sa.not_( app.model.Job.table.c.command_line.like( '%-1 1,2,3%' ) ) ) ) ) ).all():
            print "# processing job id %s" % str( job.id )
            for jtoda in job.output_datasets:
                print "# --> processing JobToOutputDatasetAssociation id %s" % str( jtoda.id )
                hda = app.model.HistoryDatasetAssociation.get( jtoda.dataset_id )
                print "# ----> processing HistoryDatasetAssociation id %s" % str( hda.id )
                if not hda.deleted:
                    # Probably don't need this check, since the job state should suffice, but...
                    if hda.dataset.state == 'ok':
                        history = app.model.History.get( hda.history_id )
                        print "# ------> processing history id %s" % str( history.id )
                        if history.user_id:
                            cmd_line = str( job.command_line )
                            new_output = tempfile.NamedTemporaryFile('w')
                            if job.tool_id in ['gops_intersect_1', 'gops_subtract_1', 'gops_coverage_1']:
                                new_cmd_line = " ".join(map(str, cmd_line.split()[:4])) + " " + new_output.name + " " + " ".join(map(str, cmd_line.split()[5:]))
                                job_output = cmd_line.split()[4]
                            else:
                                new_cmd_line = " ".join(map(str, cmd_line.split()[:3])) + " " + new_output.name + " " + " ".join(map(str, cmd_line.split()[4:]))
                                job_output = cmd_line.split()[3]
                            try:
                                os.system(new_cmd_line)
                            except:
                                pass
                            diff_status = os.system('diff %s %s >> /dev/null' % (new_output.name, job_output))
                            if diff_status == 0:
                                continue
                            print "# --------> Outputs differ"
                            user = app.model.User.get( history.user_id )
                            jobs[ job.id ] = {}
                            jobs[ job.id ][ 'hda_id' ] = hda.id
                            jobs[ job.id ][ 'hda_name' ] = hda.name
                            jobs[ job.id ][ 'hda_info' ] = hda.info
                            jobs[ job.id ][ 'history_id' ] = history.id
                            jobs[ job.id ][ 'history_name' ] = history.name
                            jobs[ job.id ][ 'history_update_time' ] = history.update_time
                            jobs[ job.id ][ 'user_email' ] = user.email
    except Exception, e:
        print "# caught exception: %s" % str( e )
def get_log_entries():
    # Limits and offsets
    if 'limit' in request.args:
        limit = int(request.args['limit'])
        if limit > 100:
            limit = 100
    else:
        limit = 10

    if 'offset' in request.args:
        offset = int(request.args['offset'])
    else:
        offset = 0

    if 'organization_id' in request.args:
        query = LogEntry.query\
            .filter(LogEntry.organization_id == request.args['organization_id'])\
                .filter(sa.not_(LogEntry.retired))

    else:
        query = LogEntry.query.filter(sa.not_(LogEntry.retired))

    if 'search' in request.args:
        if request.args["search"]:
            slist = []
            clist=[]
            for s in request.args["search"].split():
                slist.append(sa.or_(sa.func.upper(LogEntry.entry_title).like("%" + s.upper() + "%")))
                slist.append(sa.or_(sa.func.upper(LogEntry.entry_description).like("%" + s.upper() + "%")))
                clist.append(sa.or_(sa.func.upper(LogEntryComment.comment).like("%" + s.upper() + "%")))

            query=LogEntry.query\
                .filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\
                                                         .filter(sa.or_(*clist))\
                                                         .filter(LogEntryComment.retired == False)))\
                    .filter (LogEntry.user_id.in_(User.query.with_entities("id")\
                        .filter(sa.func.upper(User.first_name).in_(request.args["search"].upper().split()) | sa.func.upper(User.last_name).in_(request.args["search"].upper().split()))))\
                            .filter(LogEntry.organization_id == request.args['organization_id'])\
                                .filter(sa.not_(LogEntry.retired))

            if query.count()==0:
                query=LogEntry.query\
                    .filter(sa.or_(*slist) | LogEntry.id.in_(LogEntryComment.query.with_entities("log_entry_id")\
                                                             .filter(sa.or_(*clist))\
                                                             .filter(LogEntryComment.retired == False)))\
                        .filter(LogEntry.organization_id == request.args['organization_id'])\
                            .filter(LogEntry.retired == False)

    if 'daterange' in request.args and request.args["daterange"]:
        rdates=request.args["daterange"].split('_')
        query.whereclause.append(sa.between(LogEntry.entry_time,rdates[0] + " 00:00:00.000",rdates[1] + " 11:59:59.000"))

    log_entries = query.order_by(sa.desc(LogEntry.entry_time)).limit(limit).offset(offset).all()

    if not log_entries:
        return jsonify({}), 204

    log_entries = [l.to_json() for l in log_entries]
    return jsonify(log_entries=log_entries)
示例#23
0
def empty_category(session):
    q = session.query(Category)
    q = q.outerjoin(Snippet)
    q = q.filter(sql.not_(Category.children.any(Category.parent_id != None)))
    # q = q.group_by(Category.id)
    # q = q.having(sql.func.count(Snippet.id) == 0)
    q = q.filter(sql.not_(Category.snippets.any(Snippet.id != None)))
    return q
示例#24
0
def _find_addresses_to_be_unlocked(context, network_ids, addresses):
    addresses = [_to_int(address) for address in addresses]
    query = context.session.query(models.IPAddress)
    query = query.filter(models.IPAddress.network_id.in_(network_ids))
    if addresses:
        query = query.filter(not_(models.IPAddress.address.in_(addresses)))
    query = query.filter(not_(models.IPAddress.lock_id.is_(None)))
    return query.all()
示例#25
0
    def receive_list(self, session_key, page=1, page_length=20):
        '''
        @type  session_key: string
        @param session_key: 사용자 Login Session
        @type  page: int
        @param page: Page Number
        @type  page_length: int
        @param page_length: Number of Messages to get in one page
        @rtype: ttypes.MessageList
        @return:
            1. 리스트 읽어오기 성공: MessageList
            2. 리스트 읽어오기 실패:
                1. 로그인되지 않은 사용자: NotLoggedIn Exception
                2. 데이터베이스 오류: InternalError Exception
        '''
        # TODO: ArticleManager 참고하여 싹 뜯어고치기
        # TODO: page 관련 정보 처리하는거 ArticleManager 에서 아예 util 로 옮기기

        ret_dict = {}
        user_info = self.engine.login_manager.get_session(session_key)
        session = model.Session()
        to_user = self.engine.member_manager._get_user(session, user_info.username)
        blacklist_dict_list = self.engine.blacklist_manager.get_blacklist(session_key)
        blacklist_users = set()
        for blacklist_item in blacklist_dict_list:
            if blacklist_item.block_message:
                blacklist_users.add(blacklist_item.blacklisted_user_username)
        received_messages_count = session.query(model.Message).filter(
                and_(model.Message.to_id==to_user.id,
                    not_(model.Message.received_deleted==True)
                    )).count()
        received_new_messages_count = session.query(model.Message).filter(
                and_(model.Message.to_id==to_user.id,
                    model.Message.read_status==u'N',
                    not_(model.Message.received_deleted==True)
                    )).count()
        last_page = int(received_messages_count / page_length)
        page = int(page)
        if received_messages_count % page_length != 0:
            last_page += 1
        elif received_messages_count == 0:
            last_page += 1
        if page > last_page:
            session.close()
            raise InvalidOperation('wrong pagenum')
        offset = page_length * (page - 1)
        last = offset + page_length
        received_messages = session.query(model.Message).filter(
                and_(model.Message.to_id==to_user.id, 
                    not_(model.Message.received_deleted==True)
                    )).order_by(model.Message.id.desc())[offset:last]
        received_messages_dict_list = self._get_dict_list(received_messages, MESSAGE_WHITELIST, blacklist_users)
        ret_dict['hit'] = received_messages_dict_list
        ret_dict['last_page'] = last_page
        ret_dict['new_message_count'] = received_new_messages_count
        ret_dict['results'] = received_messages_count
        session.close()
        return MessageList(**ret_dict)
示例#26
0
def outstanding():
    # XXX: discounted /2
    outstanding = db.session.query(models.Attendance) \
                            .join(models.Course, models.Applicant) \
                            .filter(not_(models.Attendance.waiting),
                                    not_(models.Applicant.discounted),
                                    models.Attendance.has_to_pay,
                                    models.Attendance.amountpaid < models.Course.price)

    return dict(outstanding=outstanding)
示例#27
0
 def visible_in_request(self):
     if session.get('edit_mode'):
         return sa.text('1=1')
     else:
         return sa.and_(
             self.visible,
             self.photo.has(visible=True),
             sa.not_(self.trashed),
             sa.not_(self.photo.has(trashed=True)),
         )
    def _build_resource_map(self):
        import ckan.model as model

        # Find all non .csv/.xls links for gov.uk
        resources = model.Session.query(model.Resource).\
            filter(model.Resource.url.like("%/www.gov.uk/%")).\
            filter(not_(model.Resource.url.ilike("%.csv"))).\
            filter(not_(model.Resource.url.ilike("%.xls"))).\
            filter(not_(model.Resource.url.ilike("%.xlsx"))).\
            filter(not_(model.Resource.url.ilike("%.pdf"))).\
            filter(not_(model.Resource.url.ilike("%.rdf"))).\
            filter(not_(model.Resource.url.ilike("%.json"))).\
            filter(not_(model.Resource.url.ilike("%.doc"))).\
            filter(not_(model.Resource.resource_type=='documentation')).\
            filter(not_(model.Resource.resource_type=='timeseries'))
            #filter(model.Resource.state=='active')

        log.info("Found %d resources for www.gov.uk links" % resources.count())
        for r in resources:
            pkg = r.resource_group.package

            # If we only want one, then skip the others
            if self.options.single and not pkg.name == self.options.single:
                continue

            if pkg.state == 'active':
                self.local_resource_map[pkg].append(r)
示例#29
0
def _apply_extra_criteria(q_obj, parsed_query):
    if parsed_query.get('q', ''):
        query = parsed_query.get('q')
        q_obj = q_obj.filter(or_(Song.name.ilike('%{0}%'.format(query)), Song.artist.ilike('%{0}%'.format(query))))
    for exc in parsed_query.get('exclude', []):
        q_obj = q_obj.filter(and_(not_(Song.name.ilike('%{0}%'.format(exc))), not_(Song.artist.ilike('%{0}%'.format(exc)))))
    for ats in parsed_query.get('artist', []):
        q_obj = q_obj.filter(Song.artist.ilike('%{0}%'.format(ats)))
    for sng in parsed_query.get('song', []):
        q_obj = q_obj.filter(Song.name.ilike('%{0}%'.format(sng)))
    return q_obj
示例#30
0
文件: UserAPI.py 项目: PageLib/ws
    def put(self, user_id):
        """
        Updates a user.
        """
        # Check permission
        resource = 'own_user' if user_id == request.ws_session.user_id else 'user'
        ensure_allowed('update', resource)
        try:
            user = request.dbs.query(model.User).join(model.Entity)\
                                                .filter(model.User.id == user_id)\
                                                .filter(not_(model.User.deleted))\
                                                .filter(not_(model.Entity.deleted)).one()

            app.logger.info('Request on user {}'.format(user_id))

        except NoResultFound:
            app.logger.warning('PUT Request on non existing user {}'.format(user_id))
            return {}, 404

        except MultipleResultsFound:
            app.logger.error('Multiple results found for user {} on PUT UserAPI'.format(user_id))
            return {}, 500

        args = self.reqparse.parse_args()
        if args['login'] is not None:
            # We check if another non deleted user has the same login
            login = args['login']
            q = request.dbs.query(model.User).join(model.Entity)\
                                             .filter(not_(model.User.deleted))\
                                             .filter(not_(model.Entity.deleted))\
                                             .filter(model.User.login == login)

            if request.dbs.query(q.exists()).scalar():
                app.logger.warning('Tried to create an user with already existing name')
                return {'error': 'User with the same login exists.'}, 412

            user.login = login
        if args['entity_id'] is not None:
            user.entity_id = args['entity_id']
        if args['password_hash'] is not None:
            user.password_hash = args['password_hash']
        if args['role'] is not None:
            role = args['role']
            if role in roles:
                user.role = role
            else:
                return {'error': 'Role \'' + role + '\' is not allowed'}, 412
        if args['first_name'] is not None:
            user.first_name = args['first_name']
        if args['last_name'] is not None:
            user.last_name = args['last_name']
        app.logger.info('User {} successfully updated'.format(user_id))
        return marshal(user.to_dict(), user_fields)
示例#31
0
    def recipes(self, recipes, action='.', *args, **kw):
        recipes = recipes.join(Recipe.recipeset)\
            .join(RecipeSet.job)\
            .filter(not_(Job.is_deleted))
        recipes_return = self._recipes(recipes, **kw)
        searchvalue = None
        search_options = {}
        if recipes_return:
            if 'recipes_found' in recipes_return:
                recipes = recipes_return['recipes_found']
            if 'searchvalue' in recipes_return:
                searchvalue = recipes_return['searchvalue']
            if 'simplesearch' in recipes_return:
                search_options['simplesearch'] = recipes_return['simplesearch']
        PDC = widgets.PaginateDataGrid.Column
        recipes_grid = myPaginateDataGrid(fields=[
            PDC(name='id',
                getter=lambda x: make_link(url='./%s' % x.id, text=x.t_id),
                title='ID',
                options=dict(sortable=True)),
            PDC(name='whiteboard',
                getter=lambda x: x.whiteboard,
                title='Whiteboard',
                options=dict(sortable=True)),
            PDC(name='distro_tree.arch.arch',
                getter=lambda x: x.arch,
                title='Arch',
                options=dict(sortable=True)),
            PDC(name='resource.fqdn',
                getter=lambda x: x.resource and x.resource.link,
                title='System',
                options=dict(sortable=True)),
            PDC(name='distro_tree.distro.name',
                getter=lambda x: x.distro_tree and x.distro_tree.link,
                title='Distro Tree',
                options=dict(sortable=False)),
            PDC(name='progress',
                getter=lambda x: x.progress_bar,
                title='Progress',
                options=dict(sortable=False)),
            PDC(name='status',
                getter=_custom_status,
                title='Status',
                options=dict(sortable=True)),
            PDC(name='result',
                getter=_custom_result,
                title='Result',
                options=dict(sortable=True)),
            PDC(name='action',
                getter=lambda x: self.action_cell(x),
                title='Action',
                options=dict(sortable=False)),
        ])

        search_bar = SearchBar(
            name='recipesearch',
            label=_(u'Recipe Search'),
            simplesearch_label='Lookup ID',
            table=search_utility.Recipe.search.create_complete_search_table(),
            search_controller=url("/get_search_options_recipe"),
            quick_searches=[('Status-is-Queued', 'Queued'),
                            ('Status-is-Running', 'Running'),
                            ('Status-is-Completed', 'Completed')])
        return dict(title="Recipes",
                    grid=recipes_grid,
                    list=recipes,
                    search_bar=search_bar,
                    action=action,
                    options=search_options,
                    searchvalue=searchvalue)
示例#32
0
    def delete_not_in_view_types(cls, view_types):
        '''Delete the Resource Views not in the received view types list'''
        query = meta.Session.query(ResourceView) \
                    .filter(sa.not_(ResourceView.view_type.in_(view_types)))

        return query.delete(synchronize_session='fetch')
示例#33
0
def get_sqla_criterion_expr(column, criterion, negate=False):
    """Create a SQLAlchemy criterion expression
    
    **Parameters:**
    
    * **column** - (*SQLAlchemy column*) A SQLAlchemy column object to be used
    in the expression
    * **criterion** - (*3-item iterable*) A 3-item tuple or list of the format
    [field, operation, value(s)]. See `core.CRITERIA_OPERATIONS` for supported
    operations. The value item may take on different formats depending on the
    operation. In most cases passing an iterable will result in multiple
    criteria of that operation being formed. For example, ("my_field", "=",
    [1,2,3]) would logically or 3 conditions of equality to the 3 values in the
    list. The "between" operations expect each value to be a 2-item iterable
    representing the lower and upper bound of the criterion.
    * **negate** - (*bool, optional*) Negate the expression
    
    **Returns:**
    
    (*SQLAlchemy expression*) - A SQLALchemy expression representing the
    criterion
    
    **Notes:**
    
    Postgresql "like" is case sensitive, but mysql "like" is not. Postgresql
    also supports "ilike" to specify case insensitive, so one option is to look
    at the dialect to determine the function, but that is not supported yet.
    
    """
    field, op, values = criterion
    op = op.lower()
    if not isinstance(values, (list, tuple)):
        values = [values]

    use_or = True
    has_null = any([v is None for v in values])

    if op == "=":
        clauses = [column == v if v is not None else column.is_(None) for v in values]
    elif op == "!=":
        clauses = [column != v if v is not None else column.isnot(None) for v in values]
    elif op == ">":
        clauses = [column > v for v in values]
    elif op == "<":
        clauses = [column < v for v in values]
    elif op == ">=":
        clauses = [column >= v for v in values]
    elif op == "<=":
        clauses = [column <= v for v in values]
    elif op == "in":
        if has_null:
            clauses = [
                column == v if v is not None else column.is_(None) for v in values
            ]
        else:
            clauses = [column.in_(values)]
    elif op == "not in":
        use_or = False
        if has_null:
            clauses = [
                column != v if v is not None else column.isnot(None) for v in values
            ]
        else:
            clauses = [sa.not_(column.in_(values))]
    elif op == "between":
        raiseifnot(len(values) == 2, "Between clause value must have length of 2")
        clauses = [column.between(values[0], values[1])]
    elif op == "not between":
        raiseifnot(len(values) == 2, "Between clause value must have length of 2")
        clauses = [sa.not_(column.between(values[0], values[1]))]
    elif op == "like":
        clauses = [column.like(v) for v in values]
    elif op == "not like":
        use_or = False
        clauses = [sa.not_(column.like(v)) for v in values]
    else:
        raise ZillionException("Invalid criterion operand: %s" % op)

    if use_or:
        clause = sa.or_(*clauses)
    else:
        clause = sa.and_(*clauses)

    if negate:
        clause = sa.not_(clause)

    return clause
示例#34
0
    def delete_old_records(cls,
                           task_id: str,
                           dag_id: str,
                           num_to_keep=conf.getint(
                               "core",
                               "max_num_rendered_ti_fields_per_task",
                               fallback=0),
                           session: Session = None):
        """
        Keep only Last X (num_to_keep) number of records for a task by deleting others

        :param task_id: Task ID
        :param dag_id: Dag ID
        :param num_to_keep: Number of Records to keep
        :param session: SqlAlchemy Session
        """
        if num_to_keep <= 0:
            return

        tis_to_keep_query = session \
            .query(cls.dag_id, cls.task_id, cls.execution_date) \
            .filter(cls.dag_id == dag_id, cls.task_id == task_id) \
            .order_by(cls.execution_date.desc()) \
            .limit(num_to_keep)

        if session.bind.dialect.name in ["postgresql", "sqlite"]:
            # Fetch Top X records given dag_id & task_id ordered by Execution Date
            subq1 = tis_to_keep_query.subquery('subq1')

            session.query(cls) \
                .filter(
                    cls.dag_id == dag_id,
                    cls.task_id == task_id,
                    tuple_(cls.dag_id, cls.task_id, cls.execution_date).notin_(subq1)) \
                .delete(synchronize_session=False)
        elif session.bind.dialect.name in ["mysql"]:
            # Fetch Top X records given dag_id & task_id ordered by Execution Date
            subq1 = tis_to_keep_query.subquery('subq1')

            # Second Subquery
            # Workaround for MySQL Limitation (https://stackoverflow.com/a/19344141/5691525)
            # Limitation: This version of MySQL does not yet support
            # LIMIT & IN/ALL/ANY/SOME subquery
            subq2 = (session.query(subq1.c.dag_id, subq1.c.task_id,
                                   subq1.c.execution_date).subquery('subq2'))

            session.query(cls) \
                .filter(
                    cls.dag_id == dag_id,
                    cls.task_id == task_id,
                    tuple_(cls.dag_id, cls.task_id, cls.execution_date).notin_(subq2)) \
                .delete(synchronize_session=False)
        else:
            # Fetch Top X records given dag_id & task_id ordered by Execution Date
            tis_to_keep = tis_to_keep_query.all()

            filter_tis = [
                not_(
                    and_(cls.dag_id == ti.dag_id, cls.task_id == ti.task_id,
                         cls.execution_date == ti.execution_date))
                for ti in tis_to_keep
            ]

            session.query(cls) \
                .filter(and_(*filter_tis)) \
                .delete(synchronize_session=False)
示例#35
0
    repr(
        session.query(Customers).filter(
            or_(Customers.town == 'Peterbrugh',
                Customers.town == 'Norfolk')).all()))

print(
    repr(
        session.query(Customers).filter(
            and_(Customers.first_name == 'John',
                 Customers.town == 'Norfolk')).all()))

print(
    repr(
        session.query(Customers).filter(
            and_(Customers.first_name == 'John',
                 not_(Customers.town == 'Peterbrugh', ))).all()))

print(repr(session.query(Orders).filter(Orders.date_shipped == None).all()))
print(repr(session.query(Orders).filter(Orders.date_shipped != None).all()))
print(
    repr(
        session.query(Customers).filter(
            Customers.first_name.in_(['Toby', 'Sarah'])).all()))
print(
    repr(
        session.query(Customers).filter(
            Customers.first_name.notin_(['Toby', 'Sarah'])).all()))

print(repr(
    session.query(Items).filter(Items.cost_price.between(10, 50)).all()))
print(
示例#36
0
def _negate(t, expr):
    op = expr.op()
    arg, = map(t.translate, op.args)
    return sa.not_(arg) if isinstance(expr, ir.BooleanValue) else -arg
示例#37
0
    def _find_scheduled_tasks(
            self,
            dag_run: DagRun,
            session: Session,
            check_execution_date=False) -> Optional[List[TI]]:
        """
        Make scheduling decisions about an individual dag run

        ``currently_active_runs`` is passed in so that a batch query can be
        used to ask this for all dag runs in the batch, to avoid an n+1 query.

        :param dag_run: The DagRun to schedule
        :return: scheduled tasks
        """
        if not dag_run or dag_run.get_state() in State.finished:
            return
        try:
            dag = dag_run.dag = self.dagbag.get_dag(dag_run.dag_id,
                                                    session=session)
        except SerializedDagNotFound:
            self.log.exception("DAG '%s' not found in serialized_dag table",
                               dag_run.dag_id)
            return None

        if not dag:
            self.log.error("Couldn't find dag %s in DagBag/DB!",
                           dag_run.dag_id)
            return None

        currently_active_runs = session.query(TI.execution_date, ).filter(
            TI.dag_id == dag_run.dag_id,
            TI.state.notin_(list(State.finished)),
        ).all()

        if check_execution_date and dag_run.execution_date > timezone.utcnow(
        ) and not dag.allow_future_exec_dates:
            self.log.warning("Execution date is in future: %s",
                             dag_run.execution_date)
            return None

        if dag.max_active_runs:
            if (len(currently_active_runs) >= dag.max_active_runs
                    and dag_run.execution_date not in currently_active_runs):
                self.log.info(
                    "DAG %s already has %d active runs, not queuing any tasks for run %s",
                    dag.dag_id,
                    len(currently_active_runs),
                    dag_run.execution_date,
                )
                return None

        self._verify_integrity_if_dag_changed(dag_run=dag_run, session=session)

        schedulable_tis, callback_to_run = dag_run.update_state(
            session=session, execute_callbacks=False)
        dag_run.schedule_tis(schedulable_tis, session)
        session.commit()

        query = (session.query(TI).outerjoin(TI.dag_run).filter(
            or_(DR.run_id.is_(None),
                DR.run_type != DagRunType.BACKFILL_JOB)).join(
                    TI.dag_model).filter(not_(DM.is_paused)).filter(
                        TI.state == State.SCHEDULED).options(
                            selectinload('dag_model')))
        scheduled_tis: List[TI] = with_row_locks(
            query,
            of=TI,
            **skip_locked(session=session),
        ).all()
        return scheduled_tis
示例#38
0
def get_filtered_samples(response: Response,
                         dataset_id: int,
                         page: Optional[int] = None,
                         limit: Optional[int] = None,
                         labels: Optional[List[int]] = Query(None),
                         users: Optional[List[int]] = Query(None),
                         labeled: Optional[bool] = None,
                         free_text: Optional[Union[str, bytes]] = None,
                         divided_labels: Optional[bool] = None,
                         user: User = Depends(get_current_active_user)):
    """
    NOT for usage in connection with Active Learning!

    :param response:            gets Response Header Object from FastAPI, dont fill\\
    :param dataset_id:          dataset_id for dataset\\

    :param limit:               number of samples per page\\
    :param page:                number of page that should be fetched (beginning with 0) \\

    both limit and page need to be filled for paging, returns Total number of elements in the Header in X-Total \\

    :param labeled:             return only labeled samples (true) / unlabeled samples (false)\\
    :param labels:              list of label_ids to filter for add each label with label = label_id\\
    :param divided_labels:      search only for samples, which different users labeled differently\\

    :param users:               list of user_ids to filter for add each user with users = user_id\\

    :param free_text:           freetext search (only one word)\\

    :param user:                the currently active user -> needed for authentication-check\\
    :return:                    list of samples
    """

    # return only current associations, if changed code needs to be adapted
    only_current_associations = True

    dataset = db.query(Dataset).filter(Dataset.id == dataset_id)

    if not dataset:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Dataset not found for id: {}.".format(dataset_id))

    query = db.query(Sample).filter(Sample.dataset_id == dataset_id)

    # JOIN table association for later use
    if labels or users:
        query = query.join(Association, Sample.id == Association.sample_id)

    # filter for labels
    if labels:
        for label_id in labels:
            label = db.query(Label).get(label_id)
            if not label:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="Label not found for id: {}.".format(label_id),
                )
        query = query.filter(Association.label_id.in_(labels))

    # filter for users who labeled the sample
    if users:
        for user_id in users:
            user = db.query(User).get(user_id)
            if not user:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="User not found for id: {}.".format(user_id),
                )
        query = query.filter(Association.user_id.in_(users))

    # filter for only labeled or unlabeled datasets
    if labeled is not None:
        if labeled:
            if not (labels or users):
                query = query.join(Association,
                                   Sample.id == Association.sample_id)
        else:
            if users or labels or divided_labels:
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail=
                    "Cannot process unlabeled Samples if filters for Labels or Users are set.",
                )
            query = query.filter(Sample.dataset_id == dataset_id,
                                 ~Sample.associations.any())

    # text search
    if free_text:
        # prepare text
        free_text = free_text.replace(" ", " & ")

        sample = db.query(Sample).filter(
            Sample.dataset_id == dataset_id).first()
        content_type = sample.type

        # text search only for content type 'text' and 'table'
        if content_type == "text":
            matched_tables = select([Text.id]).where(
                Text.content.match('{}'.format(free_text)))
            query = query.join(Text).filter(Text.id.in_(matched_tables))
        elif content_type == "table":
            matched_tables = select([Table.id]).where(
                Table.content.match('{}'.format(free_text)))
            query = query.join(Table).filter(Table.id.in_(matched_tables))
        else:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=
                "The Dataset with id {} does not have text to search as content."
                .format(dataset_id),
            )

    # filter for divided labels (sample has more than 1 label)
    if divided_labels:
        # rebuild base query, join association 2x with alias
        association1 = aliased(Association)
        association2 = aliased(Association)

        base_query = db.query(Sample) \
            .filter(Sample.dataset_id == dataset_id) \
            .join(association1, Sample.id == association1.sample_id) \
            .join(association2, Sample.id == association2.sample_id) \
            .filter(association1.is_current == only_current_associations) \
            .filter(association1.is_current == only_current_associations)

        # use query as subquery to apply other filters (eg. for labels or users)
        sub_query = query.with_entities(Sample.id).subquery()

        # build new query
        query = base_query \
            .filter(not_(association1.label_id == association2.label_id)) \
            .filter(Sample.id.in_(sub_query)) \
            .group_by(Sample.id).having(func.count(association1.label_id) > 1) \
            .order_by(func.count(association1.label_id).desc())

    # only return samples with no label or a current label
    # All Samples with a current label
    with_current_association = db.query(Sample.id)\
        .join(Association, Sample.id == Association.sample_id)\
        .filter(Association.is_current == only_current_associations)
    # All Samples with a label
    with_association = db.query(Sample.id)\
        .join(Association, Sample.id == Association.sample_id)\
        .subquery()
    # All Samples without any labels
    without_association = db.query(Sample.id)\
        .filter(Sample.id.notin_(with_association))

    valid_samples = with_current_association.union(without_association)

    query = query.filter(Sample.id.in_(valid_samples))

    # limit number of returned elements and paging, return total_elements in header
    if page is not None and limit:
        if page < 0:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=
                "Page number needs to be 0 or greater. Page number was: {}.".
                format(page),
            )

        total_elements = query.count()
        response.headers["X-Total"] = "{}".format(total_elements)
        lower_limit = page * limit
        upper_limit = page * limit + limit
        query = query.order_by(Sample.id).slice(lower_limit, upper_limit)

    samples = query.all()
    return samples
示例#39
0
def main():
    ini_file = sys.argv[1]
    conf_parser = ConfigParser.ConfigParser({'here': os.getcwd()})
    conf_parser.read(ini_file)
    configuration = {}
    for key, value in conf_parser.items("app:main"):
        configuration[key] = value
    database_connection = configuration['database_connection']
    file_path = configuration['file_path']
    app = TestApplication(database_connection=database_connection,
                          file_path=file_path)
    jobs = {}
    try:
        for job in app.model.Job.filter(
                sa.and_(
                    app.model.Job.table.c.create_time.between(
                        '2008-05-23', '2008-11-29'),
                    app.model.Job.table.c.state == 'ok',
                    sa.or_(
                        sa.and_(
                            sa.or_(
                                app.model.Job.table.c.tool_id ==
                                'gops_intersect_1',
                                app.model.Job.table.c.tool_id ==
                                'gops_subtract_1',
                                app.model.Job.table.c.tool_id ==
                                'gops_coverage_1'),
                            sa.not_(
                                app.model.Job.table.c.command_line.like(
                                    '%-2 1,2,3%'))),
                        sa.and_(
                            sa.or_(
                                app.model.Job.table.c.tool_id ==
                                'gops_complement_1',
                                app.model.Job.table.c.tool_id ==
                                'gops_merge_1'),
                            sa.not_(
                                app.model.Job.table.c.command_line.like(
                                    '%-1 1,2,3%')))))).all():
            print("# processing job id %s" % str(job.id))
            for jtoda in job.output_datasets:
                print("# --> processing JobToOutputDatasetAssociation id %s" %
                      str(jtoda.id))
                hda = app.model.HistoryDatasetAssociation.get(jtoda.dataset_id)
                print("# ----> processing HistoryDatasetAssociation id %s" %
                      str(hda.id))
                if not hda.deleted:
                    # Probably don't need this check, since the job state should suffice, but...
                    if hda.dataset.state == 'ok':
                        history = app.model.History.get(hda.history_id)
                        print("# ------> processing history id %s" %
                              str(history.id))
                        if history.user_id:
                            cmd_line = str(job.command_line)
                            new_output = tempfile.NamedTemporaryFile('w')
                            if job.tool_id in [
                                    'gops_intersect_1', 'gops_subtract_1',
                                    'gops_coverage_1'
                            ]:
                                new_cmd_line = " ".join(
                                    map(str,
                                        cmd_line.split()[:4])
                                ) + " " + new_output.name + " " + " ".join(
                                    map(str,
                                        cmd_line.split()[5:]))
                                job_output = cmd_line.split()[4]
                            else:
                                new_cmd_line = " ".join(
                                    map(str,
                                        cmd_line.split()[:3])
                                ) + " " + new_output.name + " " + " ".join(
                                    map(str,
                                        cmd_line.split()[4:]))
                                job_output = cmd_line.split()[3]
                            try:
                                os.system(new_cmd_line)
                            except Exception:
                                pass
                            diff_status = os.system(
                                'diff %s %s >> /dev/null' %
                                (new_output.name, job_output))
                            if diff_status == 0:
                                continue
                            print("# --------> Outputs differ")
                            user = app.model.User.get(history.user_id)
                            jobs[job.id] = {}
                            jobs[job.id]['hda_id'] = hda.id
                            jobs[job.id]['hda_name'] = hda.name
                            jobs[job.id]['hda_info'] = hda.info
                            jobs[job.id]['history_id'] = history.id
                            jobs[job.id]['history_name'] = history.name
                            jobs[job.id][
                                'history_update_time'] = history.update_time
                            jobs[job.id]['user_email'] = user.email
    except Exception as e:
        print("# caught exception: %s" % e)

    print("\n\n# Number of incorrect Jobs: %d\n\n" % (len(jobs)))
    print(
        "#job_id\thda_id\thda_name\thda_info\thistory_id\thistory_name\thistory_update_time\tuser_email"
    )
    for jid in jobs:
        print('%s\t%s\t"%s"\t"%s"\t%s\t"%s"\t"%s"\t%s' %
              (str(jid), str(jobs[jid]['hda_id']), jobs[jid]['hda_name'],
               jobs[jid]['hda_info'], str(
                   jobs[jid]['history_id']), jobs[jid]['history_name'],
               jobs[jid]['history_update_time'], jobs[jid]['user_email']))
    sys.exit(0)
示例#40
0
    def token_per_realm_count(self, realm, status=None):
        """
        Give the number of tokens per realm

        :return a dict with the keys: active, inactive,
            assigned, unassigned, total
        """
        result = {}

        # if no realm or empty realm is specified
        if realm.strip() == '' or realm.strip() == '/:no realm:/':
            #  get all tokenrealm ids
            token_id_tuples = Session.query(TokenRealm.token_id).all()
            token_ids = set()
            for token_tuple in token_id_tuples:
                token_ids.add(token_tuple[0])
            # all tokens, which are not references in TokenRealm
            r_condition = and_(not_(Token.LinOtpTokenId.in_(token_ids)))
        else:
            # otherwise query all items with realm references
            r_condition = and_(TokenRealm.realm_id == Realm.id,
                               Realm.name == u'' + realm,
                               TokenRealm.token_id == Token.LinOtpTokenId)

        result['total'] = Session.query(Token).\
            filter(r_condition).distinct().count()

        if not status:
            return result

        for stat in status:
            conditions = (and_(r_condition), )
            # handle combinations like:
            # status=unassigned&active,unassigned&inactive
            if '&' in stat:
                stati = stat.split('&')
                if 'assigned' in stati:
                    conditions += (and_(Token.LinOtpUserid != u''), )
                else:
                    conditions += (and_(Token.LinOtpUserid == u''), )
                if 'active' in stati:
                    conditions += (and_(Token.LinOtpIsactive == True), )
                else:
                    conditions += (and_(Token.LinOtpIsactive == False), )
            else:
                # handle single expressions like
                # status=unassigned,active
                if 'assigned' == stat:
                    conditions += (and_(Token.LinOtpUserid != u''), )
                elif 'unassigned' == stat:
                    conditions += (and_(Token.LinOtpUserid == u''), )
                elif 'active' == stat:
                    conditions += (and_(Token.LinOtpIsactive == True), )
                elif 'inactive' == stat:
                    conditions += (and_(Token.LinOtpIsactive == False), )

            #  create the final condition as AND of all conditions
            condition = and_(*conditions)
            result[stat] = Session.query(TokenRealm, Realm, Token).\
                            filter(condition).count()

        return result
示例#41
0
文件: fund_.py 项目: dxcv/paramecium
    def get_instruments(self, month_end):
        issue_dt = [t for t in get_dates(const.FreqEnum.D)
                    if t < month_end][-self.issue]
        with get_session() as ss:
            filters = [
                # date
                fund.Description.setup_date <= issue_dt,
                fund.Description.redemption_start_dt <= month_end,
                fund.Description.maturity_date >= month_end,
                # not connect fund
                fund.Description.wind_code.notin_(
                    ss.query(fund.Connections.child_code)),
                # issue reset after convert happened.
                sa.not_(
                    ss.query(fund.Converted.wind_code).filter(
                        fund.Converted.chg_date > issue_dt,
                        fund.Converted.chg_date <= month_end,
                        fund.Converted.wind_code ==
                        fund.Description.wind_code).exists())
            ]
            if self.open_only:
                filters.append(fund.Description.fund_type == '契约型开放式')
            if self.initial_only:
                filters.append(fund.Description.is_initial == 1)
            if self.size > 0:
                filters.append(
                    ss.query(fund.PortfolioAsset.wind_code).filter(
                        fund.PortfolioAsset.net_asset >= self.size * 1e8,
                        fund.PortfolioAsset.end_date == (
                            month_end - pd.Timedelta(days=22) - QuarterEnd()),
                        fund.PortfolioAsset.wind_code ==
                        fund.Description.wind_code).exists())
            # if self.manager:
            #
            fund_list = {
                code
                for (code, ) in ss.query(fund.Description.wind_code).filter(
                    *filters).all()
            }

        if self.include or self.exclude:
            sector_type = pd.concat((
                get_sector(const.AssetEnum.CMF,
                           valid_dt=month_end,
                           sector_prefix='2001'),
                get_sector(
                    const.AssetEnum.CMF,
                    sector_prefix='1000',
                    valid_dt=max((t for t in get_dates(const.FreqEnum.Q)
                                  if t < month_end)),
                ),
            ))
            if self.include:
                in_fund = sector_type.loc[
                    lambda df: df['sector_code'].isin(self.include),
                    'wind_code']
                fund_list = fund_list & {*in_fund}
            if self.exclude:
                ex_fund = sector_type.loc[
                    lambda df: df['sector_code'].isin(self.exclude),
                    'wind_code']
                fund_list = fund_list - {*ex_fund}

        return fund_list
示例#42
0
query = session.query(Employee).filter(
    and_(Employee.emp_no > 12, Employee.emp_no < 40))
show(query)
print('=' * 100)

print('---------------or------------------')
query = session.query(Employee).filter((Employee.emp_no < 20)
                                       | (Employee.emp_no >= 40))
show(query)
print('*' * 30)
query = session.query(Employee).filter(
    or_(Employee.emp_no < 20, Employee.emp_no >= 40))
show(query)
print('*' * 30)
query = session.query(Employee).filter(not_(Employee.emp_no < 20))
show(query)
print('*' * 30)
query = session.query(Employee).filter(~(Employee.emp_no < 20))
show(query)

print('---------------in------------------')
query = session.query(Employee).filter(Employee.emp_no.in_([30, 40]))
show(query)
print('*' * 100)
print('---------------not in------------------')
query = session.query(Employee).filter(~Employee.emp_no.in_([30, 40]))
show(query)
print('*' * 100)

print('--------------like------------------')
示例#43
0
def plans_to_teams(plan_results,
                   debug=True,
                   group=None,
                   include=None,
                   exclude=None):
    guestentry = """
%(time)s Uhr - %(roundname)s bei "%(host)s"
%(address)s
Klingeln bei: %(bell)s
Bei Problemen erreicht ihr dieses Team unter: %(phone)s
Routenlink: %(link)s"""

    hostentry = """
%(time)s Uhr - %(roundname)s wird von Euch - "%(teamname)s" - zubereitet.

Tipp: Es lohnt sich, für jeden Namen in der Runde etwas Kreppband bereit zu halten!

Zu Gast sind bei Euch:
%(guests)s"""

    hostguests = """
  Team "%(guestname)s"
  (Allergien: %(allergies)s)
  (Vegetarier dabei: %(vegetarians)s)
  Telefon: %(guestphone)s
"""
    subject = "meet&eat - Abendplanung Team %s"
    round_datas = ({
        "time": "18:00",
        "name": "Vorspeise"
    }, {
        "time": "20:00",
        "name": "Hauptgericht"
    }, {
        "time": "21:30",
        "name": "Dessert"
    })

    print "Preprocess plan..."
    guestmap = defaultdict(list)
    for team in plan_results:
        for host in plan_results[team]:
            if team != host:
                guestmap[host].append(team)

    template = get_template("plan")
    sender = "meet&eat Orga <%s>" % config.MAIL_DEFAULT_SENDER
    envelope = config.MAIL_DEFAULT_SENDER

    print "Fetch data..."
    teams = {}
    qry = db.session.query(Team).filter_by(deleted=False,
                                           confirmed=True,
                                           backup=False)
    if include is not None:
        qry = qry.filter(Team.id.in_(include))
    if exclude is not None:
        qry = qry.filter(not_(Team.id.in_(exclude)))
    if group is not None:
        qry = qry.filter_by(groups=group)
    for team in qry:
        teams[str(team.id)] = team

    event_date = pretty_date(parse_cfg_date(config.EVENT_DATE), show_year=True)

    i = 0
    print "Connect to smtp..."
    with smtp_session() as session:
        print "Send mails..."
        for team in plan_results:
            plan_detail = []
            start_point = MapPoint.from_team(teams[team])
            for (round_idx, host) in enumerate(plan_results[team]):
                round_data = round_datas[round_idx]
                end_point = MapPoint.from_team(teams[host])
                # route = openroute_link([start_point, end_point])
                route = gmaps_link([start_point, end_point])
                start_point = end_point
                if team != host:
                    detail = guestentry % {
                        "time": round_data["time"],
                        "roundname": round_data["name"],
                        "host": teams[host].name,
                        "address": teams[host].location.street,
                        "bell": teams[host].location.extra,
                        "phone": teams[host].phone,
                        "link": route
                    }
                    plan_detail.append(detail)
                else:
                    guest_details = []
                    for guest in guestmap[team]:
                        guest_detail = hostguests % {
                            "guestname": teams[guest].name,
                            "allergies": teams[guest].allergies,
                            "vegetarians": teams[guest].vegetarians,
                            "guestphone": teams[guest].phone
                        }
                        guest_details.append(guest_detail)
                    detail = hostentry % {
                        "time": round_data["time"],
                        "roundname": round_data["name"],
                        "teamname": teams[team].name,
                        "guests": "\n".join(guest_details)
                    }
                    plan_detail.append(detail)
            plan = "\n\n".join(plan_detail)
            text = template.render(eventdate=event_date,
                                   teamname=teams[team].name,
                                   volume=config.VOLUME,
                                   contact_email=config.CONTACT_EMAIL,
                                   contact_phone=config.CONTACT_PHONE,
                                   plan=plan)
            msg = MIMEText(text, "plain", "utf8")

            rcpt = teams[team].email
            if debug:
                rcpt = config.MAIL_DEFAULT_SENDER

            msg['Subject'] = subject % teams[team].name
            msg['From'] = sender
            msg['To'] = rcpt
            msg['Date'] = formatdate(localtime=True)
            msg['Message-ID'] = make_msgid()

            session.sendmail(envelope, [rcpt] + [config.CONTACT_EMAIL],
                             msg.as_string())
            i += 1
        print "Mails sent: %d" % i
示例#44
0
# 定义自定义操作返回类型为bool
# somecolumn.bool_op('-->')('some value')
"""
where 条件使用连接符
目前没有实现 order by,group by,having操作
"""
from sqlalchemy import and_, or_, not_

# and, or, not 连接符
print(
    and_(
        users.c.name.like('j%'), users.c.id == addresses.c.user_id,
        or_(
            addresses.c.email_address == '*****@*****.**',
            addresses.c.email_address == '*****@*****.**',
        ), not_(users.c.id > 5)))
# 使用位运算符表示and,or,not
print(
    users.c.name.like('j%') & (users.c.id == addresses.c.user_id)
    & ((addresses.c.email_address == '*****@*****.**')
       | (addresses.c.email_address == '*****@*****.**'))
    & ~(users.c.id > 5))
# as,between的使用
s = select([(users.c.fullname +
             ", " + addresses.c.email_address).
           label('title')]). \
    where(
    and_(
        users.c.id == addresses.c.user_id,
        users.c.name.between('m', 'z'),
        or_(
示例#45
0
def _not_contains(t, expr):
    return sa.not_(_contains(t, expr))
示例#46
0
 def decorated(*args, **kwargs):
   return sqlalchemy.not_(operation(*args, **kwargs))
示例#47
0
def add_comment(json):
    if (not json or not ('postId' in json) or not ('userId' in json)
            or not ('commentText' in json)):
        abort(400, 'Not all fields were received.')

    post_query = db.session.query(MoveDetails).filter(
        and_(MoveDetails.id == json['postId'],
             not_(MoveDetails.deleted))).first()
    if not post_query:
        abort(400, 'Post with given id does not exist.')

    user_query = db.session.query(User).filter(
        and_(User.id == json['userId'], not_(User.deleted))).first()
    if not user_query:
        abort(400, 'User with given id does not exist.')

    if 'parentCommentId' in json:
        comment_query = db.session.query(Comment).filter(
            Comment.id == json['parentCommentId']).first()
        if not comment_query:
            abort(400, 'Comment with given parentCommentId does not exist.')
        comment = Comment(poster=user_query.id,
                          parent_post=post_query.id,
                          creation_datetime=datetime.now(),
                          text=json['commentText'],
                          parent_comment=json['parentCommentId'],
                          is_offer=False)
    else:
        comment = Comment(poster=user_query.id,
                          parent_post=post_query.id,
                          creation_datetime=datetime.now(),
                          text=json['commentText'],
                          is_offer=False)

    post_query.comments.append(comment)

    db.session.commit()

    if 'parentCommentId' in json and comment_query.poster != user_query.id:
        update = Update(update_type='comment_reply',
                        updated_movee_id=comment_query.poster,
                        concerning_movee_id=user_query.id,
                        description=json['commentText'],
                        move_id=post_query.id,
                        update_time=datetime.now())

        db.session.add(update)
        db.session.commit()

    if post_query.movee_id != user_query.id and not 'parentCommentId' in json:
        update = Update(update_type='comment',
                        updated_movee_id=post_query.movee_id,
                        concerning_movee_id=user_query.id,
                        description=json['commentText'],
                        move_id=post_query.id,
                        update_time=datetime.now())

        db.session.add(update)
        db.session.commit()

    if user_query.user_type == 'Removalist':
        post_record = db.session.query(PostRecord).filter(
            and_(PostRecord.move_id == post_query.id,
                 PostRecord.user_id == user_query.id)).first()
        if not post_record:
            post_record = PostRecord(move_id=post_query.id,
                                     user_id=user_query.id,
                                     last_updated=datetime.now())
            user_query.post_records.append(post_record)
        else:
            post_record.last_updated = datetime.now()
        db.session.commit()

    resp = jsonify({
        'comment': comment.to_dict(),
        'commenter': user_query.to_dict()
    })
    resp.status_code = 200
    return resp
        def inner_wrapper(self,
                          column,
                          mostly=None,
                          result_format=None,
                          *args,
                          **kwargs):
            if result_format is None:
                result_format = self.default_expectation_args["result_format"]

            result_format = parse_result_format(result_format)

            if result_format['result_format'] == 'COMPLETE':
                warnings.warn(
                    "Setting result format to COMPLETE for a SqlAlchemyDataset can be dangerous because it will not limit the number of returned results."
                )
                unexpected_count_limit = None
            else:
                unexpected_count_limit = result_format[
                    'partial_unexpected_count']

            expected_condition = func(self, column, *args, **kwargs)

            # FIXME Temporary Fix for counting missing values
            # Added to compensate when an ignore_values argument is added to the expectation
            ignore_values = [None]
            if func.__name__ in [
                    'expect_column_values_to_not_be_null',
                    'expect_column_values_to_be_null'
            ]:
                ignore_values = []
                # Counting the number of unexpected values can be expensive when there is a large
                # number of np.nan values.
                # This only happens on expect_column_values_to_not_be_null expectations.
                # Since there is no reason to look for most common unexpected values in this case,
                # we will instruct the result formatting method to skip this step.
                result_format['partial_unexpected_count'] = 0

            count_query = sa.select([
                sa.func.count().label('element_count'),
                sa.func.sum(
                    sa.case(
                        [(
                            sa.or_(
                                sa.column(column).in_(ignore_values),
                                # Below is necessary b/c sa.in_() uses `==` but None != None
                                # But we only consider this if None is actually in the list of ignore values
                                sa.column(column).is_(None)
                                if None in ignore_values else False),
                            1)],
                        else_=0)).label('null_count'),
                sa.func.sum(
                    sa.case([(sa.and_(
                        sa.not_(expected_condition),
                        sa.case([(sa.column(column).is_(None), False)],
                                else_=True)
                        if None in ignore_values else True), 1)],
                            else_=0)).label('unexpected_count')
            ]).select_from(self._table)

            count_results = dict(self.engine.execute(count_query).fetchone())

            # Handle case of empty table gracefully:
            if "element_count" not in count_results or count_results[
                    "element_count"] is None:
                count_results["element_count"] = 0
            if "null_count" not in count_results or count_results[
                    "null_count"] is None:
                count_results["null_count"] = 0
            if "unexpected_count" not in count_results or count_results[
                    "unexpected_count"] is None:
                count_results["unexpected_count"] = 0

            # Retrieve unexpected values
            unexpected_query_results = self.engine.execute(
                sa.select([sa.column(column)]).select_from(self._table).where(
                    sa.and_(
                        sa.not_(expected_condition),
                        sa.or_(
                            # SA normally evaluates `== None` as `IS NONE`. However `sa.in_()`
                            # replaces `None` as `NULL` in the list and incorrectly uses `== NULL`
                            sa.case([(sa.column(column).is_(None), False)],
                                    else_=True)
                            if None in ignore_values else False,
                            # Ignore any other values that are in the ignore list
                            sa.column(column).in_(ignore_values) ==
                            False))).limit(unexpected_count_limit))

            nonnull_count = count_results['element_count'] - \
                count_results['null_count']

            if "output_strftime_format" in kwargs:
                output_strftime_format = kwargs["output_strftime_format"]
                maybe_limited_unexpected_list = []
                for x in unexpected_query_results.fetchall():
                    if isinstance(x[column], string_types):
                        col = parse(x[column])
                    else:
                        col = x[column]
                    maybe_limited_unexpected_list.append(
                        datetime.strftime(col, output_strftime_format))
            else:
                maybe_limited_unexpected_list = [
                    x[column] for x in unexpected_query_results.fetchall()
                ]

            success_count = nonnull_count - count_results['unexpected_count']
            success, percent_success = self._calc_map_expectation_success(
                success_count, nonnull_count, mostly)

            return_obj = self._format_map_output(
                result_format,
                success,
                count_results['element_count'],
                nonnull_count,
                count_results['unexpected_count'],
                maybe_limited_unexpected_list,
                None,
            )

            if func.__name__ in [
                    'expect_column_values_to_not_be_null',
                    'expect_column_values_to_be_null'
            ]:
                # These results are unnecessary for the above expectations
                del return_obj['result']['unexpected_percent_nonmissing']
                try:
                    del return_obj['result']['partial_unexpected_counts']
                    del return_obj['result']['partial_unexpected_list']
                except KeyError:
                    pass

            return return_obj
示例#49
0
文件: auth.py 项目: xyzlat/palette
    def load(self, agent, check_odbc_state=True):
        # pylint: disable=too-many-locals
        # pylint: disable=too-many-statements
        envid = self.server.environment.envid

        if check_odbc_state and not self.server.odbc_ok():
            return {"error": "Cannot run command while in state: %s" % \
                        self.server.state_manager.get_state()}

        stmt = \
            'SELECT system_users.name, system_users.email, ' +\
            ' system_users.hashed_password, system_users.salt, ' +\
            ' system_users.friendly_name, system_users.admin_level, ' +\
            ' system_users.created_at, system_users.id ' +\
            'FROM system_users'

        excludes = ['guest', '_system']

        data = agent.odbc.execute(stmt)

        # Send tableau readonly password-related events if appropriate.
        self._eventit(agent, data)

        if failed(data):
            return data

        session = meta.Session()

        names = ['palette']
        cache = self.load_users(agent)

        system_key = SystemKeys.ALERTS_NEW_USER_ENABLED
        alerts_new_user_enabled = self.system[system_key]
        if alerts_new_user_enabled == 'yes':
            default_email_level = 1
        else:
            default_email_level = 0

        user_count = UserProfile.user_count(envid)
        if user_count <= 1:
            first_load = True
        else:
            first_load = False

        for row in data['']:
            name = row[0]
            if name.lower() in excludes:
                continue

            sysid = row[7]
            names.append(name)

            entry = UserProfile.get_by_name(envid, name)
            if not entry:
                entry = UserProfile(envid=envid, name=name)
                entry.email_level = default_email_level
                session.add(entry)

            entry.email = row[1]
            entry.hashed_password = row[2]
            entry.salt = row[3]
            entry.friendly_name = row[4]
            entry.system_admin_level = row[5]
            entry.system_created_at = row[6]
            entry.system_user_id = sysid

            if sysid in cache:
                obj = cache[sysid]
                entry.login_at = obj.login_at
                entry.user_admin_level = obj.admin_level
                entry.licensing_role_id = obj.licensing_role_id
                entry.publisher = obj.publisher

            # On first user table import, Tableau Server Administrators
            # are set to Palette Super Admins.
            if first_load and entry.system_admin_level == 10:
                entry.roleid = Role.SUPER_ADMIN

        session.commit()

        # deleted entries no longer found in Tableau are marked inactive.
        session.query(UserProfile).\
            filter(not_(UserProfile.name.in_(names))).\
            update({'active': False}, synchronize_session='fetch')

        timestamp = datetime.now().strftime(DATEFMT)
        self.system.save(SystemKeys.AUTH_TIMESTAMP, timestamp)

        d = {u'status': 'OK', u'count': len(data[''])}
        logger.debug("auth load returning: %s", str(d))
        return d
示例#50
0
    def prepare_filter(self, req, path, relation, values):

        table = req.get_table(path)
        # assoc = req.get_table(path, self.assoc_table, include_tail=True)
        # req.join(assoc, table.c.id == assoc.c.parent_id)

        # TODO: Make these all assert that the entities on the other side are _active.
        # Right now it is likely that this will be okay, since the events/scanner
        # should keep the association up to date.

        if relation in ('is', 'is_not', 'in', 'not_in', 'type_is',
                        'type_is_not'):

            # We could easily do this, but lets match Shotgun!
            if 'is' in relation and len(values) > 1:
                raise ClientFault('more than one value for %s' % relation)

            check_empty = False
            by_type = dict()

            if 'type' in relation:
                if values[0] is None:
                    check_empty = True
                else:
                    by_type = {values[0]: None}
            else:
                for e in values:
                    if e is None:
                        check_empty = True
                        continue
                    try:
                        by_type.setdefault(e['type'], []).append(e['id'])
                    except (TypeError, KeyError, IndexError):
                        raise ClientFault(
                            'multi_entity %s value must be an entity or null' %
                            relation)

            clauses = []
            for type_, ids in by_type.iteritems():

                assoc = self.assoc_table.alias()  # Must be clean.

                type_path = FieldPath(list(path) + [(type_, 'id')])
                type_table = req.get_table(type_path)

                query = sa.select([sa.literal(1)]).select_from(
                    assoc.join(
                        type_table,
                        sa.and_(
                            assoc.c.child_id == type_table.c.id,
                            # Ugly shortcut here.
                            True
                            if type_ is None else assoc.c.child_type == type_,
                        ))).where(
                            sa.and_(
                                table.c.id == assoc.c.parent_id,
                                # Ugly shortcut here.
                                True
                                if ids is None else type_table.c.id.in_(ids),
                            ))
                clauses.append(sa.exists(query))

            if check_empty:
                assoc = self.assoc_table.alias()  # Must be clean.
                query = sa.select([
                    sa.literal(1)
                ]).select_from(assoc).where(table.c.id == assoc.c.parent_id)
                clauses.append(sa.not_(sa.exists(query)))

            clause = clauses[0] if len(clauses) == 1 else sa.or_(*clauses)
            clause = sa.not_(clause) if 'not' in relation else clause
            return clause

        raise FilterNotImplemented('%s on %s' % (relation, self.type_name))
示例#51
0
    def make_query(search, option, order, genre=None, server_id_mod=None):
        query = db.session.query(ModelBotDownloaderKtvItem)
        if search is not None and search != '':
            if search.find('|') != -1:
                tmp = search.split('|')
                conditions = []
                for tt in tmp:
                    if tt != '':
                        conditions.append(
                            ModelBotDownloaderKtvItem.filename.like(
                                '%' + tt.strip() + '%'))
                query = query.filter(or_(*conditions))
            elif search.find(',') != -1:
                tmp = search.split(',')
                for tt in tmp:
                    if tt != '':
                        query = query.filter(
                            ModelBotDownloaderKtvItem.filename.like(
                                '%' + tt.strip() + '%'))
            else:
                query = query.filter(
                    or_(
                        ModelBotDownloaderKtvItem.filename.like('%' + search +
                                                                '%'),
                        ModelBotDownloaderKtvItem.daum_title == search))

        if genre is not None and genre != '':
            if genre.find('|') != -1:
                tmp = genre.split('|')
                conditions = []
                for tt in tmp:
                    if tt != '':
                        conditions.append(
                            ModelBotDownloaderKtvItem.daum_genre.like(
                                '%' + tt.strip() + '%'))
                query = query.filter(or_(*conditions))
            elif genre.find(',') != -1:
                tmp = genre.split(',')
                for tt in tmp:
                    if tt != '':
                        query = query.filter(
                            ModelBotDownloaderKtvItem.daum_genre.like(
                                '%' + tt.strip() + '%'))
            else:
                query = query.filter(
                    or_(
                        ModelBotDownloaderKtvItem.daum_genre.like('%' + genre +
                                                                  '%'),
                        ModelBotDownloaderKtvItem.daum_genre == genre))

        if option == 'request_True':
            query = query.filter(
                ModelBotDownloaderKtvItem.download_status.like('True%'))
        elif option == 'request_False':
            query = query.filter(
                ModelBotDownloaderKtvItem.download_status.like('False%'))
        elif option == 'by_plex_on':
            query = query.filter(ModelBotDownloaderKtvItem.plex_key != None)
        elif option == 'by_plex_off':
            query = query.filter(ModelBotDownloaderKtvItem.plex_key == None)
        elif option == 'by_plex_episode_off':
            query = query.filter(ModelBotDownloaderKtvItem.plex_key != None)
            query = query.filter(
                not_(ModelBotDownloaderKtvItem.plex_key.like('E%')))
        #실패. 아래 동작 안함.
        #elif option == 'torrent_incomplted':
        #    query = query.filter(ModelBotDownloaderKtvItem.downloader_item_id != None)
        #elif option == 'torrent_completed':
        #    from downloader.model import ModelDownloaderItem
        #    query = query.filter(ModelBotDownloaderKtvItem.downloader_item_id != None).filter(ModelBotDownloaderKtvItem.downloader_item_id == ModelDownloaderItem.id).filter(ModelDownloaderItem.completed_time != None)
        elif option == 'share_received':
            query = query.filter(ModelBotDownloaderKtvItem.folderid != None)
        elif option == 'share_no_received':
            query = query.filter(ModelBotDownloaderKtvItem.folderid == None)
        elif option == 'share_request_incompleted':
            query = query.filter(
                ModelBotDownloaderKtvItem.share_copy_time != None).filter(
                    ModelBotDownloaderKtvItem.share_copy_completed_time ==
                    None)
        elif option == 'share_request_completed':
            query = query.filter(
                ModelBotDownloaderKtvItem.share_copy_time != None).filter(
                    ModelBotDownloaderKtvItem.share_copy_completed_time != None
                )

        if order == 'desc':
            query = query.order_by(desc(ModelBotDownloaderKtvItem.id))
        else:
            query = query.order_by(ModelBotDownloaderKtvItem.id)

        if server_id_mod is not None and server_id_mod != '':
            tmp = server_id_mod.split('_')
            if len(tmp) == 2:
                query = query.filter(ModelBotDownloaderKtvItem.server_id %
                                     int(tmp[0]) == int(tmp[1]))

        return query
示例#52
0
def make_query(data_table, url_args, lang, **kwargs):

    from dataviva import db
    from dataviva.attrs.models import Bra, Cnae, Cbo, Hs, Wld, University, Course_hedu, Course_sc

    ops = {">": operator.gt,
           ">=": operator.ge,
           "<": operator.lt,
           "<=": operator.le}

    check_keys = ["bra_id", "cnae_id", "cbo_id", "hs_id", "wld_id", "university_id", "course_hedu_id", "course_sc_id"]
    unique_keys = []

    download = url_args.get("download", None)
    raw = True if "raw" in kwargs else None
    order = url_args.get("order", None)
    offset = url_args.get("offset", None)
    limit = url_args.get("limit", None)
    cols = url_args.get("cols", None)
    if type(cols) == str or type(cols) == unicode:
        cols = cols.split(".")
    excluding = url_args.get("excluding", None)
    if offset:
        offset = float(offset)
        limit = limit or 50
    filter = url_args.get("filter", None)
    if filter:
        filter = re.split("(>=|>|<=|<)", filter)
    join = kwargs["join"] if "join" in kwargs else False
    show_id = None
    cache_id = request.path
    ret = {}
    # first lets test if this query is cached (be sure we are not paginating
    # results) as these should not get cached
    if limit is None and download is None and raw is None and cols is None:
        cached_q = cached_query(cache_id)
        if cached_q:
            return cached_q

    query = db.session.query(data_table)
    if join:
        for j in join:
            query = query.add_entity(j["table"])
            for col in j["on"]:
                query = query.filter(getattr(data_table, col) == getattr(j["table"], col))

    query = query.group_by(data_table.year)

    # handle year (if specified)
    if "year" in kwargs:
        ret["year"] = parse_years(kwargs["year"])
        query = query \
            .filter(data_table.year.in_(ret["year"]))

    # parse all filters
    for key in check_keys:
        if key in kwargs:
            if key != "bra_id":
                unique_keys.append(key)
            if "show" in kwargs[key]:
                show_id = key
            parse_results = parse_filter(kwargs,key,query,data_table,ret)
            query = parse_results["query"]
            ret = parse_results["ret"]

    if filter:
        query = query.filter(ops[filter[1]](getattr(data_table, filter[0]), float(filter[2])))

    if excluding:
        for e in excluding:
            query = query.filter(not_(getattr(data_table, e).startswith(excluding[e])))

    # lastly we want to get the actual data held in the table requested
    if "aggregate" not in ret:
        # handle ordering
        if order:
            direction = "asc"

            if "." in order:
                o, direction = order.split(".")
            else:
                o = order

            order_table = None
            if join:
                for j in join:
                    if o in j["columns"]:
                        order_table = j["table"]

            if order_table == None:
                order_table = data_table

            all_col_names = data_table.__table__.columns.keys() + order_table.__table__.columns.keys()
            if o in all_col_names:
                if direction == "asc":
                    query = query.order_by(asc(getattr(order_table,o)))
                elif direction == "desc":
                    query = query.order_by(desc(getattr(order_table,o)))

        if limit:
            query = query.limit(limit).offset(offset)

    # raise Exception(compile_query(query))
    if join:
        ret["data"] = []
        items = query.all()
        for row in items:
            datum = row[0].serialize()
            join_data = []
            for i, r in enumerate(row):
                if i != 0:
                    serialized = r.serialize()
                    for k in serialized:
                        if k in join[i-1]["columns"]:
                            datum[k] = serialized[k]
            ret["data"].append(datum)
    elif raw:
        return query.all()
    else:
        ret["data"] = [d.serialize() for d in query.all()]

    if "aggregate" in ret:

        agg_data = []
        ret["data"] = sorted(ret["data"],key=lambda x: x["year"])

        if "bra" not in ret:
            ret["bra"] = {}

        for bra in ret["bra"]:

            if "aggregates" in bra:
                filtered_objs = []
                for key, group in groupby(ret["data"],lambda x: x["year"]):
                    year_data = []
                    for obj in group:
                        if obj["bra_id"] in bra["aggregates"]:
                            year_data.append(obj)

                    if len(unique_keys) > 0:

                        def check_filter(d,keys,i):
                            if i == len(keys):
                                merged_data = merge_objects(d)
                                merged_data["year"] = key
                                merged_data["bra_id"] = bra["id"]
                                agg_data.append(merged_data)
                            else:
                                d = sorted(d,key=lambda x: x[keys[i]])
                                for x, g in groupby(d,lambda x: x[keys[i]]):
                                    new_array = []
                                    for o in g:
                                        new_array.append(o)
                                    check_filter(new_array,keys,i+1)

                        check_filter(year_data,unique_keys,0)
                    else:
                        merged_data = merge_objects(year_data)
                        merged_data["year"] = key
                        merged_data["bra_id"] = bra["id"]
                        agg_data.append(merged_data)
            else:
                bra_data = [obj for obj in ret["data"] if obj["bra_id"] == bra["id"]]
                agg_data = agg_data + bra_data
        ret["data"] = agg_data

        # handle ordering
        if order:
            direction = "asc"
            if "." in order:
                o, direction = order.split(".")
            else:
                o = order
            if direction == "asc":
                ret["data"].sort(key=lambda x: x[o] if o in x else None)
            elif direction == "desc":
                ret["data"].sort(key=lambda x: x[o] if o in x else None, reverse=True)

        if limit:
            ret["data"] = ret["data"][int(offset):int(offset)+int(limit)]

    if cols:
        cols = ["year","bra_id"]+unique_keys+cols
        new_return = []
        attrs = None
        if ("name" or "id_ibge" or "id_mdic" in cols) and show_id:
            attr_table = locals()[show_id[:-3].capitalize()]
            attrs = [x.serialize() for x in attr_table.query.all()]
            attrs = {x["id"]:x or None for x in attrs}
        for d in ret["data"]:
            new_obj = {}
            for k in d:
                if k in cols:
                    new_obj[k] = d[k]
            if attrs:
                if "name" in cols and "name_{0}".format(lang) in attrs[d[show_id]]:
                    new_obj["name"] = attrs[d[show_id]]["name_{0}".format(lang)]
                if "id_ibge" in cols and "id_ibge" in attrs[d[show_id]]:
                    new_obj["id_ibge"] = attrs[d[show_id]]["id_ibge"]
                if "id_mdic" in cols and "id_mdic" in attrs[d[show_id]]:
                    new_obj["id_mdic"] = attrs[d[show_id]]["id_mdic"]
            new_return.append(new_obj)
        ret["data"] = new_return

    if order:
        for i, d in enumerate(ret["data"]):
            r = i+1
            if offset:
                r = r+offset
            d["rank"] = int(r)

    if download is not None:
        header = [str(c).split(".")[1] for c in data_table.__table__.columns]
        if cols:
            stickies = [c for c in header if c in unique_keys]
            header = stickies+cols

        def generate():
            for i, data_dict in enumerate(ret["data"]):
                row = [str(data_dict[c]) if c in data_dict else '' for c in header]
                if i == 0:
                    yield ';'.join(header) + '\n' + ';'.join(row) + '\n'
                yield ';'.join(row) + '\n'

        content_disposition = "attachment;filename=%s.csv" % (cache_id[1:-1].replace('/', "_"))

        if sys.getsizeof(ret["data"]) > 10485760:
            resp = Response(['Unable to download, request is larger than 10mb'],
                            mimetype="text/csv;charset=UTF-8",
                            headers={"Content-Disposition": content_disposition})
        else:
            resp = Response(generate(), mimetype="text/csv;charset=UTF-8",
                            headers={"Content-Disposition": content_disposition})
        return resp

    # gzip and jsonify result
    ret = gzip_data(jsonify(ret).data)

    if limit is None and download is None and raw is None and cols is None:
        cached_query(cache_id, ret)

    return ret
示例#53
0
    def _save_tags(self, tags, post_id, conn):

        tags = self.normalize_tags(tags)
        tag_ids = []

        for tag in tags:  # iterate over given tags
            try:
                # check if the tag exists
                statement = self._tag_table.select().where(
                    self._tag_table.c.text == tag)
                tag_result = conn.execute(statement).fetchone()
                if tag_result is None:
                    # insert if it is a new tag
                    tag_insert_statement = self._tag_table.insert().\
                        values(text=tag)
                    result = conn.execute(tag_insert_statement)
                    tag_id = result.inserted_primary_key[0]
                else:
                    # tag already exists
                    tag_id = tag_result[0]

            except sqla.exc.IntegrityError as e:
                # some database error occurred;
                tag_id = None
                self._logger.exception(str(e))

            except Exception as e:
                # unknown exception occurred
                tag_id = None
                self._logger.exception(str(e))

            if tag_id is not None:
                # for a valid tag_id
                tag_ids.append(tag_id)

                try:
                    # check if given post has tag given by tag_id
                    statement = self._tag_posts_table.select().where(
                        sqla.and_(self._tag_posts_table.c.tag_id == tag_id,
                                  self._tag_posts_table.c.post_id == post_id))
                    tag_post_result = conn.execute(statement).fetchone()

                    if tag_post_result is None:
                        # if tag_id not present for the post given by post_id
                        tag_post_statement = self._tag_posts_table.insert().\
                            values(tag_id=tag_id, post_id=post_id)
                        conn.execute(tag_post_statement)

                except sqla.exc.IntegrityError as e:
                    self._logger.exception(str(e))
                except Exception as e:
                    self._logger.exception(str(e))
        try:
            # remove tags that have been deleted
            statement = self._tag_posts_table.delete().where(
                sqla.and_(
                    sqla.not_(self._tag_posts_table.c.tag_id.in_(tag_ids)),
                    self._tag_posts_table.c.post_id == post_id))
            conn.execute(statement)
        except Exception as e:
            self._logger.exception(str(e))
示例#54
0
def parse_sqla_operator(raw_key, *operands):
    key = raw_key.lower().strip()
    if not operands:
        raise APIError("Missing arguments for '%s'." % (key))
    if key in ["and"]:
        query = and_(*operands)
        return query
    elif key in ["or"]:
        query = or_(*operands)
        return query
    elif key in ["not"]:
        x = operands[0]
        return not_(parse_condition(x))
    else:
        if len(operands) != 2:
            raise APIError(
                "Wrong number of arguments for '%s'. Expected: 2 Got: %s"
                % (key, len(operands))
            )
        x, y = operands
        if x is None:
            x = null()

        if key in ["equals", "="]:
            return x == y
        if key in ["greater", ">"]:
            return x > y
        if key in ["lower", "<"]:
            return x < y
        if key in ["notequal", "<>", "!="]:
            return x != y
        if key in ["notgreater", "<="]:
            return x <= y
        if key in ["notlower", ">="]:
            return x >= y
        if key in ["add", "+"]:
            return x + y
        if key in ["substract", "-"]:
            return x - y
        if key in ["multiply", "*"]:
            return x * y
        if key in ["divide", "/"]:
            return x / y
        if key in ["concatenate", "||"]:
            return fun.concat(x, y)
        if key in ["is"]:
            return x is y
        if key in ["is not"]:
            return x.isnot(y)
        if key in ["like"]:
            return x.like(y)
        if key in ["<->"]:
            return x.distance_centroid(y)
        if key in ["getitem"]:
            if isinstance(y, Slice):
                return x[parse_single(y.start, int) : parse_single(y.stop, int)]
            else:
                return x[read_pgid(y)]
        if key in ["in"]:
            return x.in_(y)

    raise APIError("Operator '%s' not supported" % key)
示例#55
0
def _generate_simple_operator_clause(
        ctx, clause: 'FilterClause',
        taxon_model_info_map: Dict[str, TaxonModelInfo]) -> ClauseElement:
    """
    :param ctx: Husky query context
    :param clause: Filter clause
    :param taxon_model_info_map: map of taxons and model infos
    :return: SQL clause element
    """
    taxon_model_info: TaxonModelInfo = taxon_model_info_map[clause.taxon]

    if isinstance(clause, TaxonTaxonFilterClause):
        # Always referring to the full column name, never relying on alchemy.column reference to not be ambiguous.
        right_taxon_info = taxon_model_info_map[clause.right_taxon]
        right_operand = literal_column(right_taxon_info.taxon_sql_accessor)
    else:
        right_operand = literal(
            clause.value) if clause.value is not None else None

    # Always referring to the full column name, never relying on alchemy.column reference to not be ambiguous.
    left_operand = literal_column(taxon_model_info.taxon_sql_accessor)

    if clause.operator == SimpleFilterOperator.EQ and taxon_model_info.is_array:
        # Otherwise in SF, if taxon is an array, instead of using equal sign(=),
        # we check if the value is obtained in the taxon value (list)
        # First argument to ARRAY_CONTAINS needs to be of type VARIANT
        return func.array_contains(
            right_operand.op('::')(text('VARIANT')), left_operand)
    if clause.operator in SIMPLE_OPERATORS_FUNCTIONS:
        if right_operand is None:  # support "IS NULL" and " IS NOT NULL"
            if clause.operator is SimpleFilterOperator.EQ:
                return left_operand.is_(None)
            elif clause.operator is SimpleFilterOperator.NEQ:
                return not_(left_operand.is_(None))
            else:
                raise UnknownOperator(clause)

        return SIMPLE_OPERATORS_FUNCTIONS[clause.operator](left_operand,
                                                           right_operand)
    else:
        # when using LIKE/NOT LIKE operators, we need right operand
        if right_operand is None:
            raise UnknownOperator(clause)

        # When using *like, always cast operand to string.
        left_operand = sql_cast(left_operand, String)

        # LIKE operator is handled differently
        # We should not call escape_special_character_in_like_pattern, coz Fe already does that.
        if clause.operator is SimpleFilterOperator.LIKE:
            return left_operand.like(right_operand,
                                     escape=LIKE_PATTERN_ESCAPE_CHAR)
        elif clause.operator is SimpleFilterOperator.NOT_LIKE:
            return not_(
                left_operand.like(right_operand,
                                  escape=LIKE_PATTERN_ESCAPE_CHAR))
        elif clause.operator is SimpleFilterOperator.ILIKE:
            return left_operand.ilike(right_operand,
                                      escape=LIKE_PATTERN_ESCAPE_CHAR)
        elif clause.operator is SimpleFilterOperator.NOT_ILIKE:
            return not_(
                left_operand.ilike(right_operand,
                                   escape=LIKE_PATTERN_ESCAPE_CHAR))

    raise UnknownOperator(clause)
示例#56
0
    def token_count(self, realm_list, status=None):
        """
        Give the number of tokens (with status) per realm
        if multiple tokens are given, give summary for all tokens
        tokens which are in multiple realms are only counted once!

        :param realm_list: list of realms which must be queried
        :param status: string which contains requested token status
        :return: dict with the keys: active, inactive,
            assigned, unassigned, total
        """

        if not isinstance(realm_list, (list, tuple)):
            realms = [realm_list]
        else:
            # copy realms so that we can delete items safely
            realms = realm_list[:]

        if len(realms) < 1:
            realms = ['/:no realm:/']

        result = {}
        cond = tuple()

        for realm in realms:
            realm = realm.strip()
            if '/:no realm:/' in realm or realm == '':
                #  get all tokenrealm ids
                token_id_tuples = db.session.query(TokenRealm.token_id).all()
                token_ids = set()
                for token_tuple in token_id_tuples:
                    token_ids.add(token_tuple[0])
                # all tokens, which are not references in TokenRealm
                cond += (and_(not_(Token.LinOtpTokenId.in_(token_ids))),)
                if '/:no realm:/' in realm:
                    realms.remove('/:no realm:/')

            else:
                cond += (and_(TokenRealm.realm_id == Realm.id,
                              Realm.name == realm,
                              TokenRealm.token_id == Token.LinOtpTokenId),)

        # realm condition:
        r_condition = or_(*cond)

        if 'total' in status:

            # count all tokens in an realm

            token_query = db.session.query(TokenRealm, Realm, Token)
            token_query = token_query.filter(r_condition)
            token_query = token_query.distinct(Token.LinOtpTokenId)

            result['total'] = token_query.count()

        if 'total users' in status:

            # according to the token users license spec, we count only
            # the distinct users of all assigned and active tokens of an realm

            user_query = db.session.query(TokenRealm, Realm, Token)
            user_query = user_query.filter(r_condition)
            user_query = user_query.filter(Token.LinOtpUserid != '')
            user_query = user_query.filter(Token.LinOtpIsactive == True)
            user_query = user_query.distinct(
                    Token.LinOtpUserid, Token.LinOtpIdResClass)

            result['total users'] = user_query.count()

        for stat in status:

            if stat in ['total users', 'total']:
                continue

            conditions = (and_(r_condition),)
            # handle combinations like:
            # status=unassigned & active, unassigned & inactive

            if '&' in stat:
                stati = stat.split('&')
                if 'assigned' in stati:
                    conditions += (and_(Token.LinOtpUserid != ''),)
                else:
                    conditions += (and_(Token.LinOtpUserid == ''),)
                if 'active' in stati:
                    conditions += (and_(Token.LinOtpIsactive == True),)
                else:
                    conditions += (and_(Token.LinOtpIsactive == False),)
            else:
                # handle single expressions like
                # status=unassigned,active
                if 'assigned' == stat:
                    conditions += (and_(Token.LinOtpUserid != ''),)
                elif 'unassigned' == stat:
                    conditions += (and_(Token.LinOtpUserid == ''),)
                elif 'active' == stat:
                    conditions += (and_(Token.LinOtpIsactive == True),)
                elif 'inactive' == stat:
                    conditions += (and_(Token.LinOtpIsactive == False),)

            #  create the final condition as AND of all conditions
            condition = and_(*conditions)
            result[stat] = db.session.query(TokenRealm, Realm, Token).\
                filter(condition).count()

        return result
示例#57
0
def _create(wlk, root, session):
    query = session.query(DatabaseEntry)
    for key, value in root.attrs.iteritems():
        typ = key[0]
        if typ == 'tag':
            criterion = TableTag.name.in_([value])
            # `key[1]` is here the `inverted` attribute of the tag. That means
            # that if it is True, the given tag must not be included in the
            # resulting entries.
            if key[1]:
                query = query.filter(~DatabaseEntry.tags.any(criterion))
            else:
                query = query.filter(DatabaseEntry.tags.any(criterion))
        elif typ == 'fitsheaderentry':
            key, val, inverted = value
            key_criterion = TableFitsHeaderEntry.key == key
            value_criterion = TableFitsHeaderEntry.value == val
            if inverted:
                query = query.filter(
                    not_(
                        and_(
                            DatabaseEntry.fits_header_entries.any(
                                key_criterion),
                            DatabaseEntry.fits_header_entries.any(
                                value_criterion))))
            else:
                query = query.filter(
                    and_(
                        DatabaseEntry.fits_header_entries.any(key_criterion),
                        DatabaseEntry.fits_header_entries.any(
                            value_criterion)))
        elif typ == 'download time':
            start, end, inverted = value
            if inverted:
                query = query.filter(
                    ~DatabaseEntry.download_time.between(start, end))
            else:
                query = query.filter(
                    DatabaseEntry.download_time.between(start, end))
        elif typ == 'path':
            path, inverted = value
            if inverted:
                query = query.filter(
                    or_(DatabaseEntry.path != path,
                        DatabaseEntry.path == None))
            else:
                query = query.filter(DatabaseEntry.path == path)
        elif typ == 'wave':
            wavemin, wavemax, waveunit = value
            query = query.filter(
                and_(DatabaseEntry.wavemin >= wavemin,
                     DatabaseEntry.wavemax <= wavemax))
        elif typ == 'time':
            start, end, near = value
            query = query.filter(
                and_(DatabaseEntry.observation_time_start < end,
                     DatabaseEntry.observation_time_end > start))
        else:
            if typ.lower() not in SUPPORTED_SIMPLE_VSO_ATTRS.union(
                    SUPPORTED_NONVSO_ATTRS):
                raise NotImplementedError(
                    "The attribute {0!r} is not yet supported to query a database."
                    .format(typ))
            query = query.filter_by(**{typ: value})
    return query.all()
    def build_facts_query(self,
                          db: Session,
                          *,
                          user: models.User,
                          filters: schemas.FactSearch = schemas.FactSearch()):
        visible_decks = (db.query(models.Deck.id).join(
            models.User_Deck).filter(
                models.User_Deck.owner_id == user.id).subquery())

        user_facts = (db.query(models.Fact).join(
            visible_decks, models.Fact.deck_id == visible_decks.c.id).filter(
                models.Fact.user_id == user.id))

        deck_owners = (db.query(
            models.User_Deck.deck_id,
            models.User_Deck.owner_id).outerjoin(visible_decks).filter(
                models.User_Deck.permissions ==
                schemas.Permission.owner).subquery())
        filtered_facts = (db.query(models.Fact).join(
            visible_decks, models.Fact.deck_id == visible_decks.c.id).join(
                deck_owners,
                and_(models.Fact.deck_id == deck_owners.c.deck_id,
                     models.Fact.user_id == deck_owners.c.owner_id)))
        facts_query = (user_facts.union(filtered_facts))
        # Don't allow Jeopardy facts
        facts_query = facts_query.filter(models.Fact.deck_id != 2)
        if filters.studyable:
            facts_query = (facts_query.outerjoin(
                models.Deleted,
                and_(models.Fact.fact_id == models.Deleted.fact_id,
                     models.Deleted.user_id == user.id)
            ).filter(models.Deleted.user_id == None).outerjoin(
                models.Reported,
                and_(models.Fact.fact_id == models.Reported.fact_id,
                     models.Reported.user_id == user.id)).filter(
                         models.Reported.user_id == None).outerjoin(
                             models.Suspended,
                             and_(
                                 models.Fact.fact_id ==
                                 models.Suspended.fact_id,
                                 models.Suspended.user_id == user.id)).filter(
                                     models.Suspended.user_id == None))
        else:
            facts_query = (facts_query.outerjoin(
                models.Deleted,
                and_(models.Fact.fact_id == models.Deleted.fact_id,
                     models.Deleted.user_id == user.id)).filter(
                         models.Deleted.user_id == None))
            if filters.suspended is not None:
                if filters.suspended:
                    facts_query = facts_query.join(models.Suspended).filter(
                        models.Suspended.user_id == user.id)
                else:
                    facts_query = (facts_query.outerjoin(
                        models.Suspended,
                        and_(models.Fact.fact_id == models.Suspended.fact_id,
                             models.Suspended.user_id == user.id)).filter(
                                 models.Suspended.user_id == None))

            if filters.reported is not None:
                if filters.reported:
                    facts_query = facts_query.join(models.Reported)
                    if not user.is_superuser:
                        facts_query = facts_query.filter(
                            models.Reported.user_id == user.id)
                else:
                    facts_query = (facts_query.outerjoin(
                        models.Reported,
                        and_(models.Fact.fact_id == models.Reported.fact_id,
                             models.Reported.user_id == user.id)).filter(
                                 models.Reported.user_id == None))
        if filters.all:
            facts_query = facts_query.filter(
                models.Fact.__ts_vector__.op('@@')(func.plainto_tsquery(
                    'english', filters.all)))
        if filters.text:
            facts_query = facts_query.filter(
                models.Fact.text.ilike(filters.text))
        if filters.answer:
            facts_query = facts_query.filter(
                models.Fact.answer.ilike(filters.answer))
        if filters.category:
            facts_query = facts_query.filter(
                models.Fact.category.ilike(filters.category))
        if filters.identifier:
            facts_query = facts_query.filter(
                models.Fact.identifier.ilike(filters.identifier))
        if filters.deck_ids:
            facts_query = facts_query.filter(
                models.Fact.deck_id.in_(filters.deck_ids))
        if filters.deck_id:
            facts_query = facts_query.filter(
                models.Fact.deck_id == filters.deck_id)
        if filters.marked is not None:
            if filters.marked:
                facts_query = facts_query.filter(
                    models.Fact.markers.any(id=user.id))
            else:
                facts_query = facts_query.filter(
                    not_(models.Fact.markers.any(id=user.id)))
        if filters.randomize:
            facts_query = facts_query.order_by(func.random())
        return facts_query
示例#59
0
文件: views.py 项目: lpcqq/luo
def sel_stu():
    # 查询所有操作
    students = Student.query.all()
    # 第一种方式:查询id=1的学生信息
    students = Student.query.filter(Student.id == 1)
    # 第二种方式:查询id=1的学生信息
    students = Student.query.filter_by(id=1)
    # 第三种方式:查询id=1的学生信息
    sql = 'select * from student where id=1;'
    students = db.session.execute(sql)
    # 查询所有学生数据
    sql = 'select * from student;'
    students = db.session.execute(sql)

    # 模糊查询, 查询姓名中包含妲己的学生信息
    # 在django中 filter(s_name__contains='妲己')
    # 语法:filter(模型名.属性.运算符('xx'))
    students = Student.query.filter(Student.s_name.contains('妲己'))
    # 以什么开始,startswith
    students = Student.query.filter(Student.s_name.startswith('校'))
    # 以什么结束,endswith
    students = Student.query.filter(Student.s_name.endswith('9'))
    # 查询年龄大于等于16的学生信息
    students = Student.query.filter(Student.s_age.__ge__(16))
    # 查询id在10到12之间的学生信息
    students = Student.query.filter(Student.id.in_([10, 11, 12]))
    # 查询年龄小于15的学生信息
    students = Student.query.filter(Student.s_age.__lt__(15))
    # 模糊查询,使用like,查询姓名中第二位为花的学生信息
    # like '_花%'
    students = Student.query.filter(Student.s_name.like('_花%'))
    # 按照id降序,升序
    students = Student.query.order_by('id')
    students = Student.query.order_by('-id')

    students = Student.query.order_by(desc('id'))
    students = Student.query.order_by(asc('id'))

    students = Student.query.order_by('id desc')
    students = Student.query.order_by('id asc')

    # 查询数据,获取第五个到第十个的数据
    students = Student.query.all()[4:10]
    page = 3
    students = Student.query.offset((page - 1) * 2).limit(2)
    # 使用get,获取id=1的学生信息
    # get拿不到值,不会报错,只会返回一个空
    students = Student.query.get(1)

    # and_, not_, or_
    students = Student.query.filter(Student.s_age == 16,
                                    Student.s_name.contains('花'))

    students = Student.query.filter(
        and_(Student.s_age == 16, Student.s_name.contains('花')))
    students = Student.query.filter(not_(Student.s_age == 16),
                                    not_(Student.s_name.contains('花')))

    students = Student.query.filter(
        or_(Student.s_age == 16, Student.s_name.contains('花')))

    return render_template('stus.html', stus=students)
示例#60
0
def packages_json_generate():
    yield '{{"name":"{}","packages":['.format(REPO_NAME)

    cached_packages = db_session.query(Package) \
        .filter(Package.last_updated.isnot(None),
                Package.last_update_successful,
                Package.last_updated >= datetime.utcnow() - timedelta(hours=24)) \
        .options(load_only(Package.owner,
                           Package.name,
                           Package.description,
                           Package.filename,
                           Package.date,
                           Package.version,
                           Package.download_url,
                           Package.homepage))
    iter_cached_packages = iter(cached_packages)
    package = next(iter_cached_packages, None)
    if package:
        yield json_dump_package(package)
    for package in iter_cached_packages:
        yield "," + json_dump_package(package)

    update_packages = db_session.query(Package) \
        .filter(or_(Package.last_updated.is_(None),
                    and_(Package.last_update_successful,
                         Package.last_updated < datetime.utcnow() - timedelta(hours=24)),
                    and_(not_(Package.last_update_successful),
                         Package.last_updated < datetime.utcnow() - timedelta(hours=4)))) \
        .options(load_only(Package.owner,
                           Package.repo,
                           Package.path,
                           Package.ptype,
                           Package.date))
    loop = asyncio.new_event_loop()
    asyncio.set_event_loop(loop)
    update_tasks = [
        asyncio.ensure_future(update_package(package))
        for package in update_packages
    ]
    iter_update_tasks = asyncio.as_completed(update_tasks)
    if not package:
        update_task = next(iter_update_tasks, None)
        if update_task:
            updated_package = None
            try:
                updated_package = loop.run_until_complete(update_task)
            except Exception as ex:
                LOGGER.error(ex)
                LOGGER.debug(traceback.format_exc())
            if updated_package:
                yield json_dump_package(updated_package)
    for update_task in iter_update_tasks:
        try:
            updated_package = loop.run_until_complete(update_task)
        except Exception as ex:
            LOGGER.error(ex)
            LOGGER.debug(traceback.format_exc())
            continue
        if updated_package:
            yield "," + json_dump_package(updated_package)
    loop.close()

    if update_tasks:
        last_updated_prop = Property("last_updated",
                                     date_val=datetime.utcnow())
        last_updated_prop = db_session.merge(last_updated_prop)
        db_session.commit()
        last_updated = last_updated_prop.date_val
    else:
        last_updated = db_session.query(Property.date_val).filter(
            Property.identifier == "last_updated").scalar()

    yield '],"last_updated":"{}"}}'.format(
        last_updated.isoformat() if last_updated else "")