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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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]