sqlalchemy.between

Here are the examples of the python api sqlalchemy.between taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

2 Examples 7

Example 1

Project: datanommer Source File: __init__.py
    @classmethod
    def grep(cls, start=None, end=None,
             page=1, rows_per_page=100,
             order="asc", msg_id=None,
             users=None, not_users=None,
             packages=None, not_packages=None,
             categories=None, not_categories=None,
             topics=None, not_topics=None,
             contains=None,
             defer=False):
        """ Flexible query interface for messages.

        Arguments are filters.  start and end should be :mod:`datetime` objs.

        Other filters should be lists of strings.  They are applied in a
        conjunctive-normal-form (CNF) kind of way

        for example, the following::

          users = ['ralph', 'lmacken']
          categories = ['bodhi', 'wiki']

        should return messages where

          (user=='ralph' OR user=='lmacken') AND
          (category=='bodhi' OR category=='wiki')

        Furthermore, you can use a negative version of each argument.

            users = ['ralph']
            not_categories = ['bodhi', 'wiki']

        should return messages where

            (user == 'ralph') AND
            NOT (category == 'bodhi' OR category == 'wiki')

        ----

        If the `defer` argument evaluates to True, the query won't actually
        be executed, but a SQLAlchemy query object returned instead.
        """

        users = users or []
        not_users = not_users or []
        packages = packages or []
        not_packs = not_packages or []
        categories = categories or []
        not_cats = not_categories or []
        topics = topics or []
        not_topics = not_topics or []
        contains = contains or []

        query = Message.query

        # A little argument validation.  We could provide some defaults in
        # these mixed cases.. but instead we'll just leave it up to our caller.
        if (start != None and end == None) or (end != None and start == None):
            raise ValueError("Either both start and end must be specified "
                             "or neither must be specified")

        if start and end:
            query = query.filter(between(Message.timestamp, start, end))

        if msg_id:
            query = query.filter(Message.msg_id == msg_id)

        # Add the four positive filters as necessary
        if users:
            query = query.filter(or_(
                *[Message.users.any(User.name == u) for u in users]
            ))

        if packages:
            query = query.filter(or_(
                *[Message.packages.any(Package.name == p) for p in packages]
            ))

        if categories:
            query = query.filter(or_(
                *[Message.category == category for category in categories]
            ))

        if topics:
            query = query.filter(or_(
                *[Message.topic == topic for topic in topics]
            ))

        if contains:
            query = query.filter(or_(
                *[Message._msg.like('%%%s%%' % contain)
                  for contain in contains]
            ))

        # And then the four negative filters as necessary
        if not_users:
            query = query.filter(not_(or_(
                *[Message.users.any(User.name == u) for u in not_users]
            )))

        if not_packs:
            query = query.filter(not_(or_(
                *[Message.packages.any(Package.name == p) for p in not_packs]
            )))

        if not_cats:
            query = query.filter(not_(or_(
                *[Message.category == category for category in not_cats]
            )))

        if not_topics:
            query = query.filter(not_(or_(
                *[Message.topic == topic for topic in not_topics]
            )))

        # Finally, tag on our pagination arguments
        total = query.count()
        query = query.order_by(getattr(Message.timestamp, order)())

        if rows_per_page is None:
            pages = 1
        else:
            pages = int(math.ceil(total / float(rows_per_page)))
            query = query.offset(rows_per_page * (page - 1)).limit(rows_per_page)

        if defer:
            return total, page, query
        else:
            # Execute!
            messages = query.all()
            return total, pages, messages

Example 2

Project: designate Source File: base.py
    @staticmethod
    def _apply_criterion(table, query, criterion):
        if criterion is not None:
            for name, value in criterion.items():
                column = getattr(table.c, name)

                # Wildcard value: '%'
                if isinstance(value, six.string_types) and '%' in value:
                    query = query.where(column.like(value))

                elif (isinstance(value, six.string_types) and
                        value.startswith('!')):
                    queryval = value[1:]
                    query = query.where(column != queryval)

                elif (isinstance(value, six.string_types) and
                        value.startswith('<=')):
                    queryval = value[2:]
                    query = query.where(column <= queryval)

                elif (isinstance(value, six.string_types) and
                        value.startswith('<')):
                    queryval = value[1:]
                    query = query.where(column < queryval)

                elif (isinstance(value, six.string_types) and
                        value.startswith('>=')):
                    queryval = value[2:]
                    query = query.where(column >= queryval)

                elif (isinstance(value, six.string_types) and
                        value.startswith('>')):
                    queryval = value[1:]
                    query = query.where(column > queryval)

                elif (isinstance(value, six.string_types) and
                        value.startswith('BETWEEN')):
                    elements = [i.strip(" ") for i in
                                value.split(" ", 1)[1].strip(" ").split(",")]
                    query = query.where(between(
                        column, elements[0], elements[1]))

                elif isinstance(value, list):
                    query = query.where(column.in_(value))

                else:
                    query = query.where(column == value)

        return query