Python sqlalchemy.not_() Examples

The following are 30 code examples of sqlalchemy.not_(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy , or try the search function .
Example #1
Source File: quote.py    From CloudBot with GNU General Public License v3.0 6 votes vote down vote up
def get_quote_by_chan(db, chan, num=False):
    """Returns a formatted quote from a channel, random or selected by number"""
    count_query = select([qtable]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.chan == chan) \
        .alias("count") \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, chan)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.chan == chan) \
        .order_by(qtable.c.time) \
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count) 
Example #2
Source File: alchemy.py    From flask-rest-jsonapi with MIT License 6 votes vote down vote up
def resolve(self):
        """Create filter for a particular node of the filter tree"""
        if 'or' not in self.filter_ and 'and' not in self.filter_ and 'not' not in self.filter_:
            value = self.value

            if isinstance(value, dict):
                value = Node(self.related_model, value, self.resource, self.related_schema).resolve()

            if '__' in self.filter_.get('name', ''):
                value = {self.filter_['name'].split('__')[1]: value}

            if isinstance(value, dict):
                return getattr(self.column, self.operator)(**value)
            else:
                return getattr(self.column, self.operator)(value)

        if 'or' in self.filter_:
            return or_(Node(self.model, filt, self.resource, self.schema).resolve() for filt in self.filter_['or'])
        if 'and' in self.filter_:
            return and_(Node(self.model, filt, self.resource, self.schema).resolve() for filt in self.filter_['and'])
        if 'not' in self.filter_:
            return not_(Node(self.model, self.filter_['not'], self.resource, self.schema).resolve()) 
Example #3
Source File: constraint.py    From choochoo with GNU General Public License v2.0 6 votes vote down vote up
def get_source_ids(s, owner, name, op, value, group, type):
    op_attr = get_op_attr(op, value)
    statistic_journal = STATISTIC_JOURNAL_CLASSES[type]
    q = s.query(Source.id). \
        join(statistic_journal). \
        join(StatisticName). \
        filter(StatisticName.name.like(name))
    if owner:
        q = q.filter(StatisticName.owner.like(owner))
    if group is not UNDEF:
        if group:
            q = q.join(ActivityGroup).filter(ActivityGroup.name.ilike(group))
        else:
            q = q.filter(Source.activity_group_id == None)
    if op_attr == 'nlike':  # no way to negate like in a single attribute
        q = q.filter(not_(statistic_journal.value.ilike(value)))
    else:
        q = q.filter(getattr(statistic_journal.value, op_attr)(value))
    return q 
Example #4
Source File: constraint.py    From choochoo with GNU General Public License v2.0 6 votes vote down vote up
def get_source_ids_for_null(s, owner, name, group, with_conversion):
    q = s.query(StatisticJournal.source_id). \
        join(StatisticName). \
        filter(StatisticName.name.like(name))
    if owner:
        q = q.filter(StatisticName.owner.like(owner))
    if group is not UNDEF:
        if group:
            q = q.join(ActivityGroup).filter(ActivityGroup.name.ilike(group))
        else:
            q = q.join(Source).filter(Source.activity_group_id == None)
    if with_conversion:
        # will invert later (in conversion)
        return q
    else:
        return s.query(Source.id).filter(not_(Source.id.in_(q))) 
Example #5
Source File: constraint.py    From choochoo with GNU General Public License v2.0 6 votes vote down vote up
def activity_conversion(s, source_ids, null):
    # convert the query that gives any source id and select either those that are activities directly,
    # or activities associated with a topic (eg user entered activity notes)

    # for most queries, we have some source IDs and we want to know if they are activityjournal ids
    # (which we pass through) or activitytopicjournal ids (in which case we convert to activityjournal).
    source_ids = source_ids.cte()
    q_direct = s.query(ActivityJournal.id). \
        filter(ActivityJournal.id.in_(source_ids))
    q_via_topic = s.query(ActivityJournal.id). \
        join(FileHash). \
        join(ActivityTopicJournal). \
        filter(ActivityTopicJournal.id.in_(source_ids))
    q = aliased(union(q_direct, q_via_topic)).select()

    if null:
        # for 'is null' queries we are really asking if the data are missing (since values are not null constrained)
        # so we find what does exist and then invert it.  that inversion has to happen avter conversion
        return s.query(ActivityJournal.id).filter(not_(ActivityJournal.id.in_(q)))
    else:
        return q 
Example #6
Source File: service.py    From lemur with Apache License 2.0 6 votes vote down vote up
def get_all_pending_cleaning_issued_since_days(source, days_since_issuance):
    """
    Retrieves all certificates that are available for cleaning: not attached to endpoint, and X days since issuance.

    :param days_since_issuance: defines how many days since the certificate is issued
    :param source: the source to search for certificates
    :return: list of pending certificates
    """
    not_in_use_window = (
        arrow.now().shift(days=-days_since_issuance).format("YYYY-MM-DD")
    )
    return (
        Certificate.query.filter(Certificate.sources.any(id=source.id))
        .filter(not_(Certificate.endpoints.any()))
        .filter(Certificate.date_created > not_in_use_window)
        .all()
    ) 
Example #7
Source File: service.py    From lemur with Apache License 2.0 6 votes vote down vote up
def get_all_pending_cleaning_expiring_in_days(source, days_to_expire):
    """
    Retrieves all certificates that are available for cleaning, not attached to endpoint,
    and within X days from expiration.

    :param days_to_expire: defines how many days till the certificate is expired
    :param source: the source to search for certificates
    :return: list of pending certificates
    """
    expiration_window = arrow.now().shift(days=+days_to_expire).format("YYYY-MM-DD")
    return (
        Certificate.query.filter(Certificate.sources.any(id=source.id))
        .filter(not_(Certificate.endpoints.any()))
        .filter(Certificate.not_after < expiration_window)
        .all()
    ) 
Example #8
Source File: quote.py    From CloudBot with GNU General Public License v3.0 6 votes vote down vote up
def get_quote_by_nick_chan(db, chan, nick, num=False):
    """Returns a formatted quote from a nick in a channel, random or selected by number"""
    count_query = select([qtable]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.chan == chan) \
        .where(qtable.c.nick == nick.lower()) \
        .alias("count") \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, nick)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.chan == chan) \
        .where(qtable.c.nick == nick.lower()) \
        .order_by(qtable.c.time) \
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count) 
Example #9
Source File: quote.py    From CloudBot with GNU General Public License v3.0 6 votes vote down vote up
def get_quote_by_nick(db, nick, num=False):
    """Returns a formatted quote from a nick, random or selected by number"""

    count_query = select([qtable]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.nick == nick.lower()) \
        .alias("count") \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, nick)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(not_(qtable.c.deleted)) \
        .where(qtable.c.nick == nick.lower()) \
        .order_by(qtable.c.time) \
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count) 
Example #10
Source File: test_query.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_bind_in(self, connection):
        """test calling IN against a bind parameter.

        this isn't allowed on several platforms since we
        generate ? = ?.

        """

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

        u = bindparam("search_key", type_=String)

        s = users.select(not_(u.in_([])))
        r = connection.execute(s, search_key="john").fetchall()
        assert len(r) == 3
        r = connection.execute(s, search_key=None).fetchall()
        assert len(r) == 3 
Example #11
Source File: test_query.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_in_filtering(self, connection):
        """test the behavior of the in_() function."""

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

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

        s = users.select(not_(users.c.user_name.in_([])))
        r = connection.execute(s).fetchall()
        assert len(r) == 3

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

        s = users.select(not_(users.c.user_name.in_(["jack", "fred"])))
        r = connection.execute(s).fetchall()
        # Null values are not outside any set
        assert len(r) == 0 
Example #12
Source File: offline_filters.py    From ReadableWebProxy with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
def exposed_delete_old_nu_root_outbound():
	'''
	Delete NU outbound links that use the homepage as their referrer.

	Apparently NU was validating the referrer to see if the referring page actually had
	the referring link on it, or /something/.

	Anyways, it's easier to generate a permanent referrer by just pointing it at
	the series page.
	'''


	with db.session_context() as sess:

		for row in sess.query(db.NuReleaseItem) \
			.filter(not_(db.NuReleaseItem.referrer.like("%novelupdates.com/series%"))) \
			.yield_per(50).all():
			if not len(list(row.resolved)):
				print(row.id, row.referrer)
				sess.delete(row)
				sess.commit() 
Example #13
Source File: test_query.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_or_and_as_columns(self, connection):
        true, false = literal(True), literal(False)

        eq_(connection.execute(select([and_(true, false)])).scalar(), False)
        eq_(connection.execute(select([and_(true, true)])).scalar(), True)
        eq_(connection.execute(select([or_(true, false)])).scalar(), True)
        eq_(connection.execute(select([or_(false, false)])).scalar(), False)
        eq_(
            connection.execute(select([not_(or_(false, false))])).scalar(),
            True,
        )

        row = connection.execute(
            select(
                [or_(false, false).label("x"), and_(true, false).label("y")]
            )
        ).first()
        assert row.x == False  # noqa
        assert row.y == False  # noqa

        row = connection.execute(
            select([or_(true, false).label("x"), and_(true, false).label("y")])
        ).first()
        assert row.x == True  # noqa
        assert row.y == False  # noqa 
Example #14
Source File: test_operators.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_operator_precedence_9(self):
        self.assert_compile(
            self.table2.select(not_(self.table2.c.field.between(5, 6))),
            "SELECT op.field FROM op WHERE "
            "op.field NOT BETWEEN :field_1 AND :field_2",
        ) 
Example #15
Source File: test_operators.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_implicitly_boolean(self):
        # test for expressions that the database always considers as boolean
        # even if there is no boolean datatype.
        assert not self.table1.c.myid._is_implicitly_boolean
        assert (self.table1.c.myid == 5)._is_implicitly_boolean
        assert (self.table1.c.myid == 5).self_group()._is_implicitly_boolean
        assert (self.table1.c.myid == 5).label("x")._is_implicitly_boolean
        assert not_(self.table1.c.myid == 5)._is_implicitly_boolean
        assert or_(
            self.table1.c.myid == 5, self.table1.c.myid == 7
        )._is_implicitly_boolean
        assert not column("x", Boolean)._is_implicitly_boolean
        assert not (self.table1.c.myid + 5)._is_implicitly_boolean
        assert not not_(column("x", Boolean))._is_implicitly_boolean
        assert (
            not select([self.table1.c.myid])
            .scalar_subquery()
            ._is_implicitly_boolean
        )
        assert not text("x = y")._is_implicitly_boolean
        assert not literal_column("x = y")._is_implicitly_boolean 
Example #16
Source File: nearby.py    From choochoo with GNU General Public License v2.0 5 votes vote down vote up
def _missing(self, s):
        prev = Timestamp.get(s, self.owner_out)
        if not prev:
            return [True]
        prev_ids = s.query(Timestamp.source_id). \
            filter(Timestamp.owner == self.owner_in,
                   Timestamp.constraint == None,
                   Timestamp.time < prev.time).cte()
        later = s.query(count(ActivityJournal.id)). \
            join(ActivityGroup). \
            filter(not_(ActivityJournal.id.in_(prev_ids))).scalar()
        if later:
            return [True]
        else:
            return [] 
Example #17
Source File: test_joins.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_contains(self):
        Node = self.classes.Node

        sess = create_session()
        n4 = sess.query(Node).filter_by(data="n4").one()

        eq_(
            sess.query(Node)
            .filter(Node.children.contains(n4))
            .order_by(Node.data)
            .all(),
            [Node(data="n1"), Node(data="n3")],
        )
        eq_(
            sess.query(Node)
            .filter(not_(Node.children.contains(n4)))
            .order_by(Node.data)
            .all(),
            [
                Node(data="n2"),
                Node(data="n4"),
                Node(data="n5"),
                Node(data="n6"),
                Node(data="n7"),
            ],
        ) 
Example #18
Source File: viewsUser.py    From muesli with GNU General Public License v3.0 5 votes vote down vote up
def listSubjectsByTerm(request):
    settings = {
            'starting_term': '20121',          # SS 2012
            'minimal_count': 20,               # minimal count overall terms
            'exclude_lecture_name': 'Vorkurs'  # lecture name to exclude
    }
    subject_term_dict = collections.defaultdict(lambda: collections.defaultdict(int))
    terms = [str(x[0]) for x in request.db.query(models.Lecture.term)
            .filter(models.Lecture.term >= settings['starting_term'])
            .group_by(models.Lecture.term).order_by(models.Lecture.term.desc())]
    subjects_by_term = []
    table = request.db.query(models.Lecture.term, models.User.subject, func.count(models.User.id))\
            .join(models.LectureStudent)\
            .join(models.User)\
            .filter(models.Lecture.term >= settings['starting_term'])\
            .filter(not_(models.Lecture.name.contains(settings['exclude_lecture_name'])))\
            .group_by(models.User.subject, models.Lecture.term)\
            .order_by(models.Lecture.term, models.User.subject)
    for (term, subject, count) in table:
        subject = re.sub(r'\(.*\)', '', str(subject))
        subject = re.sub(r'\s$', '', str(subject))
        if subject == 'None' or subject == '':
            subject = '<ohne Studiengang>'
        subject_term_dict[subject][str(term)] += count
    for subject in sorted(subject_term_dict.keys()):
        counts = [((term), subject_term_dict[subject][term]) for term in terms]
        if sum([x[1] for x in counts]) > settings['minimal_count']:
            subjects_by_term.append((subject, counts))
    readable_terms = [Term(x) for x in terms]
    settings['starting_term'] = Term(settings['starting_term'])
    return {'subjects_by_term': subjects_by_term, 'terms': readable_terms, 'settings': settings} 
Example #19
Source File: constraint.py    From choochoo with GNU General Public License v2.0 5 votes vote down vote up
def build_property(s, ast):
    qname, op, value = ast
    cls, attr = parse_property(qname)
    op_attr = get_op_attr(op, value)
    q = s.query(cls.id)
    if attr == 'nlike':
        q = q.filter(not_(getattr(cls, attr).ilike(value)))
    else:
        q = q.filter(getattr(getattr(cls, attr), op_attr)(value))
    return q 
Example #20
Source File: utils.py    From choochoo with GNU General Public License v2.0 5 votes vote down vote up
def _missing(self, s):
        existing_ids = s.query(Timestamp.source_id).filter(Timestamp.owner == self.owner_out)
        q = s.query(self._journal_type.start). \
            filter(not_(self._journal_type.id.in_(existing_ids.cte()))). \
            order_by(self._journal_type.start)
        return [row[0] for row in self._delimit_query(q)] 
Example #21
Source File: nearby.py    From choochoo with GNU General Public License v2.0 5 votes vote down vote up
def _aj_lon_lat(self, s, new=True):
        from ..owners import SegmentReader
        lat = s.query(StatisticName.id). \
            filter(StatisticName.name == Names.LATITUDE,
                   StatisticName.owner == SegmentReader).scalar()
        lon = s.query(StatisticName.id). \
            filter(StatisticName.name == Names.LONGITUDE,
                   StatisticName.owner == SegmentReader).scalar()
        if not lat or not lon:
            log.warning(f'No {Names.LATITUDE} or {Names.LONGITUDE} in database')
            return

        # todo - use tables() instead
        sj_lat = inspect(StatisticJournal).local_table
        sj_lon = alias(inspect(StatisticJournal).local_table)
        sjf_lat = inspect(StatisticJournalFloat).local_table
        sjf_lon = alias(inspect(StatisticJournalFloat).local_table)
        aj = inspect(ActivityJournal).local_table
        ns = inspect(ActivitySimilarity).local_table

        existing_lo = select([ns.c.activity_journal_lo_id])
        existing_hi = select([ns.c.activity_journal_hi_id])
        existing = existing_lo.union(existing_hi).cte()

        # todo - has not been tuned for latest schema
        stmt = select([sj_lat.c.source_id, sjf_lon.c.value, sjf_lat.c.value]). \
            select_from(sj_lat).select_from(sj_lon).select_from(sjf_lat).select_from(sjf_lat).select_from(aj). \
            where(and_(sj_lat.c.source_id == sj_lon.c.source_id,  # same source
                       sj_lat.c.time == sj_lon.c.time,            # same time
                       sj_lat.c.source_id == aj.c.id,             # and associated with an activity
                       sj_lat.c.id == sjf_lat.c.id,               # lat sub-class
                       sj_lon.c.id == sjf_lon.c.id,               # lon sub-class
                       sj_lat.c.statistic_name_id == lat,
                       sj_lon.c.statistic_name_id == lon))

        if new:
            stmt = stmt.where(func.not_(sj_lat.c.source_id.in_(existing)))
        else:
            stmt = stmt.where(sj_lat.c.source_id.in_(existing))
        stmt = stmt.order_by(sj_lat.c.source_id)  # needed for seen logic
        yield from s.connection().execute(stmt) 
Example #22
Source File: pipeline.py    From choochoo with GNU General Public License v2.0 5 votes vote down vote up
def _query(cls, s, type=None, like=tuple(), unlike=tuple(), id=None):
        q = s.query(Pipeline)
        if type is not None:  # enum can be 0
            q = q.filter(Pipeline.type == type)
        if like:
            q = q.filter(or_(*[Pipeline.cls.like(pattern) for pattern in like]))
        if unlike:
            q = q.filter(not_(or_(*[Pipeline.cls.like(pattern) for pattern in unlike])))
        if id:
            q = q.filter(Pipeline.id == id)
        return q 
Example #23
Source File: test_selectable.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_unary_boolean(self):

        s1 = select([not_(True)], use_labels=True)
        eq_(
            [type(entry[-1]) for entry in s1.compile()._result_columns],
            [Boolean],
        ) 
Example #24
Source File: resourceview.py    From radremedy with Mozilla Public License 2.0 5 votes vote down vote up
def prepare_population_query(self, query):
        """
        Prepares the provided query by ensuring that
        filtering out resources with populations has been applied.

        Args:
            query: The query to update.

        Returns:
            The updated query.
        """
        # Ensure there are no populations defined
        query = query.filter(not_(self.model.populations.any()))

        return query 
Example #25
Source File: test_operators.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_negate_operator_self_group(self):
        orig_expr = or_(
            self.table1.c.myid == 1, self.table1.c.myid == 2
        ).self_group()
        expr = not_(orig_expr)
        is_not_(expr, orig_expr)

        self.assert_compile(
            expr,
            "NOT (mytable.myid = :myid_1 OR mytable.myid = :myid_2)",
            dialect=default.DefaultDialect(supports_native_boolean=False),
        ) 
Example #26
Source File: test_operators.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_operator_precedence_10(self):
        self.assert_compile(
            self.table2.select(not_(self.table2.c.field) == 5),
            "SELECT op.field FROM op WHERE (NOT op.field) = :param_1",
        ) 
Example #27
Source File: test_operators.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_operator_precedence_7(self):
        self.assert_compile(
            self.table2.select(
                not_(and_(self.table2.c.field == 5, self.table2.c.field == 7))
            ),
            "SELECT op.field FROM op WHERE NOT "
            "(op.field = :field_1 AND op.field = :field_2)",
        ) 
Example #28
Source File: test_operators.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_operator_precedence_8(self):
        self.assert_compile(
            self.table2.select(not_(self.table2.c.field == 5)),
            "SELECT op.field FROM op WHERE op.field != :field_1",
        ) 
Example #29
Source File: db_auth.py    From aiohttp-security with Apache License 2.0 5 votes vote down vote up
def permits(self, identity, permission, context=None):
        if identity is None:
            return False

        async with self.dbengine.acquire() as conn:
            where = sa.and_(db.users.c.login == identity,
                            sa.not_(db.users.c.disabled))
            query = db.users.select().where(where)
            ret = await conn.execute(query)
            user = await ret.fetchone()
            if user is not None:
                user_id = user[0]
                is_superuser = user[3]
                if is_superuser:
                    return True

                where = db.permissions.c.user_id == user_id
                query = db.permissions.select().where(where)
                ret = await conn.execute(query)
                result = await ret.fetchall()
                if ret is not None:
                    for record in result:
                        if record.perm_name == permission:
                            return True

            return False 
Example #30
Source File: __init__.py    From build-relengapi with Mozilla Public License 2.0 5 votes vote down vote up
def branches():
    "Return a list of all the branches clobberer knows about."
    session = g.db.session(DB_DECLARATIVE_BASE)
    branches = session.query(Build.branch).distinct()
    # Users shouldn't see any branch associated with a release builddir
    branches = branches.filter(not_(Build.builddir.startswith(BUILDDIR_REL_PREFIX)))
    branches = branches.order_by(Build.branch)
    return [branch[0] for branch in branches]