def clear_data_for_current_system(self): p = alias(FileStorage) ps = alias(FileStorage) pl = alias(RpmDetailPatchStorageLink) pls = alias(RpmDetailPatchStorageLink) rd = alias(RpmDetail) s = alias(System) delete_links_sql = delete(pl).where( exists( select([1]).select_from( pls.join( rd, pls.c.rpm_detail_id == rd.c.rpm_detail_id).join( s, rd.c.system_id == s.c.system_id)).where( s.c.system_id == self.system.system_id).where( pl.c.id == pls.c.id))) delete_patches_sql = delete(p).where( not_( exists( select([1]).select_from( pl.join(ps, pl.c.file_storage_id == ps.c.id)).where( p.c.id == ps.c.id)))) result_links = self._session.execute(delete_links_sql) if result_links.rowcount: log.info(f"Removed {result_links.rowcount} previous patch links") result_patches = self._session.execute(delete_patches_sql) if result_patches.rowcount: log.info(f"Removed {result_patches.rowcount} previous patches")
def purge(self, days): thresh = datetime_to_unix(datetime.now() - timedelta(days=days)) rel = self._db.query(Release) torr = self._db.query(Torrent) r_orphans = exists().where(ReleaseMonitor.release_id == Release.id) l_orphans = exists().where(Torrent.monitor_id == ReleaseMonitor.id) return (self.all_query.filter_by(downloaded=True).filter( or_(ReleaseMonitor.download_date_stamp.is_(None), ReleaseMonitor.download_date_stamp < thresh)).delete(), rel.filter(~r_orphans).delete(False), torr.filter(~l_orphans).delete(False))
async def add_contact_method(session: AsyncSession, user_profile_id: UUID, email_address: str = None, phone_number: str = None) -> UUID: if email_address is not None: email_exists = exists().where( email_contact_method.c.email_address == email_address) q = select(literal(True)).\ select_from(user_profile.join(contact_method).join(email_contact_method)).\ where(email_exists) result = await session.execute(q) ex = result.scalar() if ex: raise ValueError( f"user profile with email {email_address} already exists") r = await session.execute(contact_method.insert().values( user_profile_id=user_profile_id, contact_method_type=ContactMethodType.Email, verified=False).returning(contact_method.c.contact_method_id)) email_id = r.scalar() await session.execute(email_contact_method.insert().values( user_profile_id=user_profile_id, contact_method_id=email_id, email_address=email_address)) return email_id elif phone_number is not None: pn_exists = exists().where( phone_contact_method.c.phone_number == phone_number) q = select(literal(True)).\ select_from(user_profile.join(contact_method).join(phone_contact_method)).\ where(pn_exists) result = await session.execute(q) ex = result.scalar() if ex: raise ValueError( f"user profile with phone number {phone_number} already exists" ) r = await session.execute(contact_method.insert().values( user_profile_id=user_profile_id, contact_method_type=ContactMethodType.Phone, verified=False).returning(contact_method.c.contact_method_id)) pn_id = r.scalar() await session.execute(phone_contact_method.insert().values( user_profile_id=user_profile_id, contact_method_id=pn_id, phone_number=phone_number)) return pn_id raise ValueError("one of email_address or phone_number is required")
def validate_version(request, **kwargs): document_id = request.validated['document_id'] lang = request.validated['lang'] version_id = request.validated['version_id'] # check the version to revert to actually exists version_exists = DBSession.query( exists().where( and_(DocumentVersion.id == version_id, DocumentVersion.document_id == document_id, DocumentVersion.lang == lang)) ).scalar() if not version_exists: raise HTTPBadRequest('Unknown version {}/{}/{}'.format( document_id, lang, version_id)) # check the version to revert to is not the latest one last_version_id, = DBSession.query(DocumentVersion.id). \ filter(and_( DocumentVersion.document_id == document_id, DocumentVersion.lang == lang)). \ order_by(DocumentVersion.id.desc()).first() if version_id == last_version_id: raise HTTPBadRequest( 'Version {}/{}/{} is already the latest one'.format( document_id, lang, version_id))
def query_multiple( self, data, filter_col="piece.id", table="clefs_ins_piece"): if self.validate_table(table): _table = self.tables[table] _filter_col = getattr(_table.columns, filter_col) q = select([_filter_col]) for elem in data: query = _table.select() nxtalias = alias(_table) for key in elem: col = getattr(nxtalias.columns, key) expr = self.mk_or_expr(elem[key], col) query = query.where(expr) alias_filter = getattr(nxtalias.columns, filter_col) query = query.where(alias_filter == _filter_col) q = q.where(exists(query)) result_prox = self.execute(q) return set([elem[0] for elem in result_prox]) else: raise BadTableException( "table {} not in {}".format( table, self.tables.keys()))
def _get_stalled_work_requests(self, session, work_requests, results): """ :param session: :type session: :param work_requests: :type work_requests: dict :param results: :type results: list :return: :rtype: """ action_alias = aliased(ActionInstance) inner_query = ~exists()\ .where(action_alias.workflow_instance_id == WorkflowInstance.id) \ .where(action_alias.order < ActionInstance.order)\ .where(action_alias.end_date == None)\ .correlate(ActionInstance) \ .correlate(WorkflowInstance) for action_instance, pipeline_parameters, action_instance_config in session.query(ActionInstance, PipelineParameters, ActionInstanceConfig) \ .join(PipelineInstance, PipelineInstance.id == ActionInstance.pipeline_instance_id) \ .join(WorkflowInstance, WorkflowInstance.id == ActionInstance.workflow_instance_id) \ .outerjoin(PipelineParameters, PipelineParameters.pipeline_instance_id == ActionInstance.pipeline_instance_id) \ .outerjoin(ActionInstanceConfig, ActionInstanceConfig.action_instance_id == ActionInstance.id) \ .filter(ActionInstance.status_id == StatusConstants.NEW) \ .filter(ActionInstance.manual == 0) \ .filter(PipelineInstance.status_id == StatusConstants.INPROGRESS) \ .filter(inner_query) \ .order_by(PipelineInstance.priority.desc(), PipelineInstance.created_date.asc(), PipelineInstance.id.asc(), ActionInstance.order.asc(), ActionInstance.slice.asc()).all(): action_instance.configuration = action_instance_config self.configure_work_request(action_instance, pipeline_parameters, work_requests, results)
def get_user_pending_org_address(orm, user, org_id): Address_v_all = aliased(Address_v) Address_v_new = aliased(Address_v) query = orm.query(Address_v_all) \ .outerjoin(( Address, Address.address_id == Address_v_all.address_id )) \ .join(( org_address_v, and_( org_address_v.c.address_id == Address_v_all.address_id, org_address_v.c.org_id == org_id, org_address_v.c.existence == 1, ) )) \ .filter( Address_v_all.moderation_user_id == user.user_id, ~exists().where(and_( Address_v_new.address_id == Address_v_all.address_id, Address_v_new.a_time > Address_v_all.a_time, )), or_( Address.a_time == None, Address_v_all.a_time > Address.a_time, ) ) \ .order_by(Address_v_all.a_time.desc()) return query.all()
def project_exists(*filter_params): from sqlalchemy.sql.expression import select, exists session = DBSession() project_query = session.query(Project).filter_by(*filter_params) project_exists_select = select((exists(project_query.statement),)) engine = Base.metadata.bind return engine.execute(project_exists_select).scalar()
def get_user_pending_event_contact(orm, user, event_id): Contact_v_all = aliased(Contact_v) Contact_v_new = aliased(Contact_v) query = orm.query(Contact_v_all) \ .outerjoin(( Contact, Contact.contact_id == Contact_v_all.contact_id )) \ .join(( event_contact_v, and_( event_contact_v.c.contact_id == Contact_v_all.contact_id, event_contact_v.c.event_id == event_id, event_contact_v.c.existence == 1, ) )) \ .filter( Contact_v_all.moderation_user_id == user.user_id, ~exists().where(and_( Contact_v_new.contact_id == Contact_v_all.contact_id, Contact_v_new.a_time > Contact_v_all.a_time, )), or_( Contact.a_time == None, Contact_v_all.a_time > Contact.a_time, ) ) \ .order_by(Contact_v_all.a_time.desc()) return query.all()
def __nonzero__(self): session = Session.object_session(self.instance) if session is None: return bool(self.count()) for v, in session.query(exists(self.as_scalar())): return bool(v) return False
def handle(self, *args, **options): # set up config = get_config() if config is None: raise CommandError('Unable to process configuration file p_to_p.yml') connection = get_connection(config) pedsnet_session = init_pedsnet(connection) init_pcornet(connection) observation_period = pedsnet_session.query(ObservationPeriod.person_id, ObservationPeriod.observation_period_start_date, ObservationPeriod.observation_period_end_date, ObservationPeriod.site, bindparam("chart", 'Y'), bindparam("enr_basis", 'E') ).filter( exists().where(ObservationPeriod.person_id == PersonVisit.person_id)).all() odo(observation_period, Enrollment.__table__, dshape='var * {patid: string, enr_start_date: date, enr_end_date: date, site: string, chart: String, ' 'enr_basis: String} ' ) # close session pedsnet_session.close() # ouutput result self.stdout.ending = '' print('Enrollment ETL completed successfully', end='', file=self.stdout)
def is_coordinator(self): ## circular import :/ from intranet3.models import Project, Client is_coordinator = DBSession.query(exists().where( or_(Client.coordinator_id == self.id, Project.coordinator_id == self.id))).scalar() return is_coordinator
def get_user_pending_contact_event(orm, user, contact_id): Event_v_all = aliased(Event_v) Event_v_new = aliased(Event_v) query = orm.query(Event_v_all) \ .outerjoin(( Event, Event.event_id == Event_v_all.event_id )) \ .join(( event_contact_v, and_( event_contact_v.c.event_id == Event_v_all.event_id, event_contact_v.c.contact_id == contact_id, event_contact_v.c.existence == 1, ) )) \ .filter( Event_v_all.moderation_user_id == user.user_id, ~exists().where(and_( Event_v_new.event_id == Event_v_all.event_id, Event_v_new.a_time > Event_v_all.a_time, )), or_( Event.a_time == None, Event_v_all.a_time > Event.a_time, ) ) \ .order_by(Event_v_all.a_time.desc()) \ return query.all()
def query_multiple(self, data, filter_col="piece.id", table="clefs_ins_piece"): if self.validate_table(table): _table = self.tables[table] _filter_col = getattr(_table.columns, filter_col) q = select([_filter_col]) for elem in data: query = _table.select() nxtalias = alias(_table) for key in elem: col = getattr(nxtalias.columns, key) expr = self.mk_or_expr(elem[key], col) query = query.where(expr) alias_filter = getattr(nxtalias.columns, filter_col) query = query.where(alias_filter == _filter_col) q = q.where(exists(query)) result_prox = self.execute(q) return set([elem[0] for elem in result_prox]) else: raise BadTableException("table {} not in {}".format( table, self.tables.keys()))
def get_user_pending_contact_org(orm, user, contact_id): Org_v_all = aliased(Org_v) Org_v_new = aliased(Org_v) query = orm.query(Org_v_all) \ .outerjoin(( Org, Org.org_id == Org_v_all.org_id )) \ .join(( org_contact_v, and_( org_contact_v.c.org_id == Org_v_all.org_id, org_contact_v.c.contact_id == contact_id, org_contact_v.c.existence == 1, ) )) \ .filter( Org_v_all.moderation_user_id == user.user_id, ~exists().where(and_( Org_v_new.org_id == Org_v_all.org_id, Org_v_new.a_time > Org_v_all.a_time, )), or_( Org.a_time == None, Org_v_all.a_time > Org.a_time, ) ) \ .order_by(Org_v_all.a_time.desc()) \ return query.all()
def operate(self, op, *other, **kwargs): q = select([self.remote_attr]) q = q.where(self.target_class.foreign_id == self.owning_class.id) q = q.where(self.target_class.local_language_id == bindparam( '_default_language_id')) q = q.where(op(self.remote_attr, *other)) return exists(q)
def _buildQuery(self, languageId=None, userId=None, q=None): ''' Builds the general query for blogs. ''' sql = self.session().query(BlogMapped) #Hide deleted blogs sql = sql.filter(BlogMapped.DeletedOn == None) if languageId: sql = sql.filter(BlogMapped.Language == languageId) if userId: #TODO: change it for the new version of Ally-Py, where it is a complete implementation of security qRole = QRole() qRole.name = self.admin_role isAdmin = len(self.roleService.getRoles(userId, q=qRole)) if not isAdmin: userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \ & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \ & (BlogCollaboratorMapped.Blog == BlogMapped.Id)) sql = sql.filter(userFilter) if q: assert isinstance(q, QBlog), 'Invalid query %s' % q sql = buildQuery(sql, q, BlogMapped) if (QBlog.isOpen in q) and (AsBoolean.value in q.isOpen): if q.isOpen.value: sql = sql.filter(BlogMapped.ClosedOn == None) else: sql = sql.filter(BlogMapped.ClosedOn != None) return sql
def _has_permission(self, user_id, outing_id): """Check if the user with the given id has permission to change an outing. That is only users that are currently assigned to the outing can modify it. """ return DBSession.query(exists().where( and_(Association.parent_document_id == user_id, Association.child_document_id == outing_id))).scalar()
def contains(name): with get_conn() as conn: # This is: # SELECT EXISTS (SELECT 1 FROM sent_invitation WHERE entry = ?) return conn.execute( select([ exists(select([1]).where(sent_invitation.c.entry == name)) ])).scalar()
def delete_orphaned(cls): with new_session() as session: subquery = select([ErrorReport.id])\ .where(ErrorReport.item_id == Item.id)\ .limit(1) query = delete(ErrorReport).where(~exists(subquery)) session.execute(query)
def is_coordinator(self): ## circular import :/ from intranet3.models import Project, Client is_coordinator = DBSession.query(exists().where(or_( Client.coordinator_id==self.id, Project.coordinator_id==self.id ))).scalar() return is_coordinator
def where_clause_fn(id) : beam_me = beam.alias(name='beam_me') beam_other = beam.alias(name='beam_other') stmt = select([beam_other.c.id]).\ where(and_(stem_direction.c.id == beam_other.c.id, beam_other.c.val == beam_me.c.val, beam_me.c.id == id)) return exists(stmt)
def containsDuplicate(transaction): return client.session.query(exists()\ .where(Transaction.amount==transaction.amount)\ .where(Transaction.entities_account_id==transaction.entities_account_id)\ .where(Transaction.date==transaction.date.date())\ .where(Transaction.imported_payee==transaction.imported_payee)\ .where(Transaction.source==transaction.source)\ ).scalar()
def contains(self, url_uuid: UUID, source: DiscussionSource) -> bool: """Return true if the given url and source are currently in the frontier.""" query = self._build_frontier_query().where( and_( self.b.c.url_uuid == url_uuid, self.ds.c.discussion_source_id == source.value, )) return self.session.execute(select([exists(query)])).scalar()
def where_clause_fn(id) : stmt = select([literal(id).label('id')]).cte(name="anchors", recursive=True) stmt_prev = stmt.alias(name='stmt_prev') stmt = stmt.union_all( select([ anchor.c.id ]).where(anchor.c.val == stmt_prev.c.id) ) return exists(select([stmt.c.id]).where(anchored_table.c.id == stmt.c.id))
def checkHaveHistory(self, url): # Only do version fiddling if versioning is enabled. if not settings.DO_VERSIONING: return 99 ctbl = version_table(db.WebPages.__table__) query = self.db_sess.query(exists().where(ctbl.c.url == url)) return query.scalar()
def _has_permission(self, user_id, outing_id): """Check if the user with the given id has permission to change an outing. That is only users that are currently assigned to the outing can modify it. """ return DBSession.query(exists().where( and_( Association.parent_document_id == user_id, Association.child_document_id == outing_id ))).scalar()
def pushDB(self, db, domain_id, rr, value): rr = filter_rrtype[rr] for ttl, v in self.ttls.items(): oid = None try: q = db.query(Entry) q = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)) q.update({Entry.first_seen: case([(Entry.first_seen > v[1], v[1])], else_=Entry.first_seen), Entry.last_seen: case([(Entry.last_seen < v[2], v[2])], else_=Entry.last_seen), Entry.count: Entry.count + v[0]}, synchronize_session=False) q = db.query(Entry.entry_id) o = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)).one() oid = o[0] except NoResultFound: entry = Entry() entry.domain_id = domain_id entry.type = rr entry.ttl = ttl entry.value = value entry.first_seen = v[1] entry.last_seen = v[2] entry.count = v[0] db.add(entry) db.flush() oid = entry.entry_id for s, sv in v[3].items(): try: q = db.query(exists().where((and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)))).scalar() if not q: raise NoResultFound('') q = db.query(DNS_Server) q = q.filter(and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)) q.update({DNS_Server.first_seen: case([(DNS_Server.first_seen > sv[1], sv[1])], else_=DNS_Server.first_seen), DNS_Server.last_seen: case([(DNS_Server.last_seen < sv[2], sv[2])], else_=DNS_Server.last_seen), DNS_Server.count: DNS_Server.count + sv[0]}, synchronize_session=False) except NoResultFound: dns_server = DNS_Server() dns_server.entry_id = oid dns_server.ip = s dns_server.first_seen = sv[1] dns_server.last_seen = sv[2] dns_server.count = sv[0] db.add(dns_server) db.flush() db.flush()
def check_if_database_has(self, name: str) -> Query: """ Checks if specified name in database :param name: string name :return: boolean result of query """ session = self._sessionmaker() found_pokemon_bool = session.query( exists().where(Pokemon.name == name)) session.close() return found_pokemon_bool
def createBlogMediaType(key): creator = alchemySessionCreator() session = creator() assert isinstance(session, Session) if not session.query(exists().where(BlogMediaTypeMapped.Key == key)).scalar(): blogMediaTypeDb = BlogMediaTypeMapped() blogMediaTypeDb.Key = key session.add(blogMediaTypeDb) session.commit() session.close()
def has_been_created_by(document_id, user_id): """Check if passed user_id is the id of the user that has created the initial version of this document, whatever the language. """ return DBSession.query( exists().where(and_( ArchiveDocument.document_id == document_id, ArchiveDocument.version == 1, DocumentVersion.document_id == document_id, HistoryMetaData.user_id == user_id )) ).scalar()
def __verify_record__(user_id): try: (user_record, ), = db.session.query( exists().where(User.id==user_id).where(User.last_run.isnot(None)) ) if user_record == False: # @TODO: Build page for general error & log custom exception raise Exception # Persists change to database db.session.commit() except Exception as e: raise
async def checkRowsExists(rowSpecs: List[RowSpec]): conditions = [] for tableCondition in rowSpecs: # build a EXISTS() query query = tableCondition.table.select() for condition in tableCondition.conditions: # combine conditions into the WHERE clause query = query.where(condition) conditions.append(exists(query)) # combine the exists queries together query = select([and_(*conditions)]) return await db.execute(query)
def createPostType(key): creator = alchemySessionCreator() session = creator() assert isinstance(session, Session) if not session.query(exists().where(PostTypeMapped.Key == key)).scalar(): postTypeDb = PostTypeMapped() postTypeDb.Key = key session.add(postTypeDb) session.commit() session.close()
def createVerificationStatus(key): creator = alchemySessionCreator() session = creator() assert isinstance(session, Session) if not session.query(exists().where(VerificationStatusMapped.Key == key)).scalar(): verificationStatus = VerificationStatusMapped() verificationStatus.Key = key session.add(verificationStatus) session.commit() session.close()
def has_permission_for_outing(request, outing_id): """Check if the user with the given id has permission to change an outing. That is only users that are currently assigned to the outing can modify it. """ if request.has_permission('moderator'): # moderators can change everything return True user_id = request.authenticated_userid return DBSession.query(exists().where( and_(Association.parent_document_id == user_id, Association.child_document_id == outing_id))).scalar()
def _index_query_restricted_by_export_recipient( self, q: Query) -> Optional[Query]: """ For exports. Filters via our :class:`camcops_server.cc_modules.cc_exportrecipient.ExportRecipient`, except for the bits already implemented via our :class:`camcops_server.cc_modules.cc_taskfilter.TaskFilter`. The main job here is for incremental exports: to find tasks that have not yet been exported. Compare :meth:`_task_query_restricted_by_export_recipient`. Args: q: the starting SQLAlchemy ORM Query Returns: the original query, a modified query, or ``None`` if no tasks would pass the filter """ from camcops_server.cc_modules.cc_exportmodels import ( ExportedTask, ) # delayed import r = self.export_recipient if not r.is_incremental(): # Full database export; no restrictions return q # Otherwise, restrict to tasks not yet sent to this recipient. # Remember: q is a query on TaskIndexEntry. # noinspection PyUnresolvedReferences q = q.filter( # "There is not a successful export record for this task/recipient" ~exists().select_from( ExportedTask.__table__.join( ExportRecipient.__table__, ExportedTask.recipient_id == ExportRecipient.id, )). where( and_( ExportRecipient.recipient_name == r.recipient_name, ExportedTask.basetable == TaskIndexEntry.task_table_name, # ... don't use ".tablename" as a property doesn't play # nicely with SQLAlchemy here ExportedTask.task_server_pk == TaskIndexEntry.task_pk, ExportedTask.success == True, # noqa: E712 ExportedTask.cancelled == False, # noqa: E712 ))) return q
def _struct_filter(self, class_, bindParam, rpt): ''' Private method to filter reagent or product by smarts pattern @param class_: class to filter, either RxnReagent or RxnProduct @type class_: class @param bindParam: name of bind parameter for SMARTS pattern @type bindParam: str @param rpt: report to filter @type rpt: app.model.gridconf.Report ''' rpt.filter(exists().where(and_(class_.mol_id == RxnMol.mol_id, class_.rxn_id == Rxn.rxn_id, func.chem.compareSmartsToMol(bindparam(bindParam), RxnMol.mol_struct), RxnMol.fingerprint.op('|')(func.chem.convertSmilesToFingerprint(bindparam(bindParam))) == RxnMol.fingerprint)))
def _task_query_restricted_by_export_recipient( self, q: Query, cls: Type[Task]) -> Optional[Query]: """ For exports. Filters via our :class:`camcops_server.cc_modules.cc_exportrecipient.ExportRecipient`, except for the bits already implemented via our :class:`camcops_server.cc_modules.cc_taskfilter.TaskFilter`. The main job here is for incremental exports: to find tasks that have not yet been exported. We look for any tasks not yet exported to a recipient of the same name (regardless of ``ExportRecipient.id``, which changes when the export recipient is reconfigured). Compare :meth:`_index_query_restricted_by_export_recipient`. Args: q: the starting SQLAlchemy ORM Query cls: the task class Returns: the original query, a modified query, or ``None`` if no tasks would pass the filter """ from camcops_server.cc_modules.cc_exportmodels import ( ExportedTask, ) # delayed import r = self.export_recipient if not r.is_incremental(): # Full database export; no restrictions return q # Otherwise, restrict to tasks not yet sent to this recipient. # noinspection PyUnresolvedReferences q = q.filter( # "There is not a successful export record for this task/recipient" ~exists().select_from( ExportedTask.__table__.join( ExportRecipient.__table__, ExportedTask.recipient_id == ExportRecipient.id, )).where( and_( ExportRecipient.recipient_name == r.recipient_name, ExportedTask.basetable == cls.__tablename__, ExportedTask.task_server_pk == cls._pk, ExportedTask.success == True, # noqa: E712 ExportedTask.cancelled == False, # noqa: E712 ))) return q
def _buildQuery(self, languageId=None, adminId=None, collaboratorId=None, q=None): ''' Builds the general query for blogs. ''' sql = self.session().query(BlogMapped) if languageId: sql = sql.filter(BlogMapped.Language == languageId) userFilter = None if adminId: userFilter = (BlogMapped.Creator == adminId) | exists().where( (AdminEntry.adminId == adminId) & (AdminEntry.Blog == BlogMapped.Id)) if collaboratorId: userFilter |= exists().where((CollaboratorMapped.Person == collaboratorId) \ & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \ & (BlogCollaboratorMapped.Blog == BlogMapped.Id)) if userFilter is not None: sql = sql.filter(userFilter) if q: assert isinstance(q, QBlog), 'Invalid query %s' % q sql = buildQuery(sql, q, BlogMapped) return sql
def where_clause_fn(id) : linked_list_fwd = bound_range(id, generic_next, name="fwd_list") linked_list_bwd = bound_range(id, generic_next, forward=False, name = "bwd_list") linked_list = select([linked_list_bwd]).union(select([linked_list_fwd])).cte(name="combined_lists") stmt = select([linked_list]).select_from(linked_list).where(linked_list.c.elt == table_to_delete_on.c.id) # the second clause guarantees that we are only deleting when # we are getting a new referent if not generic_anchor_statement : stmt = stmt.where(generic_anchor.c.id == id) return exists(stmt)
def _is_last_route_of_outing(association): if not(association.parent_document_type == ROUTE_TYPE and association.child_document_type == OUTING_TYPE): # other association type, nothing to check return False outing_has_other_routes = exists(). \ where(Association.parent_document_type == ROUTE_TYPE). \ where(Association.child_document_type == OUTING_TYPE). \ where(Association.parent_document_id != association.parent_document_id). \ where( Association.child_document_id == association.child_document_id) return not DBSession.query(outing_has_other_routes).scalar()
def _is_last_route_of_outing(association): if not (association.parent_document_type == ROUTE_TYPE and association.child_document_type == OUTING_TYPE): # other association type, nothing to check return False outing_has_other_routes = exists(). \ where(Association.parent_document_type == ROUTE_TYPE). \ where(Association.child_document_type == OUTING_TYPE). \ where(Association.parent_document_id != association.parent_document_id). \ where( Association.child_document_id == association.child_document_id) return not DBSession.query(outing_has_other_routes).scalar()
def _is_last_waypoint_of_route(association): if not (association.parent_document_type == WAYPOINT_TYPE and association.child_document_type == ROUTE_TYPE): # other association type, nothing to check return False route_has_other_waypoints = exists(). \ where(Association.parent_document_type == WAYPOINT_TYPE). \ where(Association.child_document_type == ROUTE_TYPE). \ where(Association.parent_document_id != association.parent_document_id). \ where( Association.child_document_id == association.child_document_id) return not DBSession.query(route_has_other_waypoints).scalar()
def has_permission_for_outing(request, outing_id): """Check if the user with the given id has permission to change an outing. That is only users that are currently assigned to the outing can modify it. """ if request.has_permission('moderator'): # moderators can change everything return True user_id = request.authenticated_userid return DBSession.query(exists().where( and_( Association.parent_document_id == user_id, Association.child_document_id == outing_id ))).scalar()
def _is_last_waypoint_of_route(association): if not(association.parent_document_type == WAYPOINT_TYPE and association.child_document_type == ROUTE_TYPE): # other association type, nothing to check return False route_has_other_waypoints = exists(). \ where(Association.parent_document_type == WAYPOINT_TYPE). \ where(Association.child_document_type == ROUTE_TYPE). \ where(Association.parent_document_id != association.parent_document_id). \ where( Association.child_document_id == association.child_document_id) return not DBSession.query(route_has_other_waypoints).scalar()
def list_volume_files(self): volume = self._get_volume(self.request.matchdict["volume_reference"]) j = file_table.join(blob_table, file_table.c.blob_id == blob_table.c.id) s = (select([ file_table, blob_table.c.handle ]).select_from(j).where(file_table.c.volume_id == volume["id"])) if "without_statements" in self.request.GET: filter_query = (select( [statement_table.c.id]).select_from(statement_table).where( statement_table.c.object_blob_id == blob_table.c.id)) s = s.where(not_(exists(filter_query))) if "path" in self.request.GET: paths = [ base64.urlsafe_b64decode(p) for p in self.request.GET.getall("path") ] s = s.where(file_table.c.path.in_(paths)) if "after" in self.request.GET: after = base64.urlsafe_b64decode(self.request.GET["after"]) s = s.where(file_table.c.path > after) limit = 1000 if "limit" in self.request.GET: limit = min(int(self.request.GET["limit"]), self.max_limit) s = s.order_by(file_table.c.path).limit(limit) files = [{ "path": os.fsdecode(r[file_table.c.path]), "size": r[file_table.c.size], "mtime": r[file_table.c.mtime].isoformat(), "lastverify": r[file_table.c.lastverify].isoformat(), "handle": base64.urlsafe_b64encode(r[blob_table.c.handle]).decode("utf-8"), } for r in self.db.execute(s)] return { "results": files, "limit": limit, }
def _buildQuery(self, languageId=None, userId=None, q=None): ''' Builds the general query for blogs. ''' sql = self.session().query(BlogMapped) if languageId: sql = sql.filter(BlogMapped.Language == languageId) if userId: userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \ & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \ & (BlogCollaboratorMapped.Blog == BlogMapped.Id)) sql = sql.filter(userFilter) if q: assert isinstance(q, QBlog), 'Invalid query %s' % q sql = buildQuery(sql, q, BlogMapped) return sql
def create_file(): """ File from request will be saved to database """ file = request.files["file"] filename = secure_filename(file.filename) if db.session.query(exists().where(File.name == filename)).scalar() == False: if file.mimetype.split('/', 1)[0] == "image": file.save(os.path.join(os.path.realpath('.') + '/static/', filename)) file_object = File(name=filename, path='', owner_id=login.current_user.id) save_to_db(file_object, "file saved") flash("Image added") else: flash("The selected file is not an image. Please select a image file and try again.") else: flash("A file named \"" + filename + "\" already exists")
def _buildQuery(self, languageId=None, adminId=None, q=None): """ Builds the general query for blogs. """ sql = self.session().query(BlogMapped) if languageId: sql = sql.filter(BlogMapped.Language == languageId) if adminId: sql = sql.filter( (BlogMapped.Creator == adminId) | exists().where((AdminEntry.adminId == adminId) & (AdminEntry.Blog == BlogMapped.Id)) ) if q: assert isinstance(q, QBlog), "Invalid query %s" % q sql = buildQuery(sql, q, BlogMapped) return sql
def _domain_exists(self, domain, search_like=False): q = self.db.query(Domain.domain_id, Domain.domain_name) ret = {} if search_like: res = self._search_domain(domain) for r in res: ret[r] = True else: res = self.db.query(exists().where(Domain.domain_name == domain)).scalar() if res: ret[domain] = True else: ret[domain] = False return ret
def handle(self, *args, **options): # set up config = get_config() if config is None: raise CommandError('Unable to process configuration file p_to_p.yml') connection = get_connection(config) pedsnet_session = init_pedsnet(connection) init_pcornet(connection) pedsnet_pcornet_valueset_map = aliased(ValueSetMap) # extract the data from the death table death_pedsnet = pedsnet_session.query(DeathPedsnet.death_date, coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'), bindparam("death_match_confidence", None), bindparam("death_source", "L"), DeathPedsnet.person_id, min(DeathPedsnet.site) ). \ outerjoin(pedsnet_pcornet_valueset_map, and_(pedsnet_pcornet_valueset_map.source_concept_class == 'Death date impute', cast(DeathPedsnet.death_impute_concept_id, String(200)) == pedsnet_pcornet_valueset_map.source_concept_id)) \ .filter(and_(exists().where(DeathPedsnet.person_id == PersonVisit.person_id), DeathPedsnet.death_type_concept_id == 38003569)) \ .group_by(DeathPedsnet.person_id, DeathPedsnet.death_date, coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \ .all() # transform data to pcornet names and types # load to demographic table odo(death_pedsnet, DeathPcornet.__table__, dshape='var * {death_date: date, death_date_impute: string, death_match_confidence: string,' 'death_source: string, patid:string, site: string}' ) # close session pedsnet_session.close() # output result self.stdout.ending = '' print('Death ETL completed successfully', end='', file=self.stdout)