def find_members(self, store, subscriber=None, list_id=None, role=None): """See `ISubscriptionService`.""" # If `subscriber` is a user id, then we'll search for all addresses # which are controlled by the user, otherwise we'll just search for # the given address. user_manager = getUtility(IUserManager) if subscriber is None and list_id is None and role is None: return [] # Querying for the subscriber is the most complicated part, because # the parameter can either be an email address or a user id. query = [] if subscriber is not None: if isinstance(subscriber, str): # subscriber is an email address. address = user_manager.get_address(subscriber) user = user_manager.get_user(subscriber) # This probably could be made more efficient. if address is None or user is None: return [] query.append(or_(Member.address_id == address.id, Member.user_id == user.id)) else: # subscriber is a user id. user = user_manager.get_user_by_id(subscriber) address_ids = list(address.id for address in user.addresses if address.id is not None) if len(address_ids) == 0 or user is None: return [] query.append(or_(Member.user_id == user.id, Member.address_id.in_(address_ids))) # Calculate the rest of the query expression, which will get And'd # with the Or clause above (if there is one). if list_id is not None: query.append(Member.list_id == list_id) if role is not None: query.append(Member.role == role) results = store.query(Member).filter(and_(*query)) return sorted(results, key=_membership_sort_key)
def sa_op(op): #if BooleanOp if isinstance(op, operators.or_): return sa.or_(*[sa_op(o) for o in op.ops]) elif isinstance(op, operators.and_): return sa.and_(*[sa_op(o) for o in op.ops]) #else, assume op is an instance of op if isinstance(op, operators.eq): fn = lambda x,y: x == y elif isinstance(op, operators.ne): fn = lambda x,y: x != y elif isinstance(op, operators.gt): fn = lambda x,y: x > y elif isinstance(op, operators.lt): fn = lambda x,y: x < y elif isinstance(op, operators.gte): fn = lambda x,y: x >= y elif isinstance(op, operators.lte): fn = lambda x,y: x <= y rval = tup(op.rval) if not rval: return '2+2=5' else: return sa.or_(*[fn(op.lval, v) for v in rval])
def constrain_obj_ids(obj, obj_constraint): desc = aliased( ObjectDescription ) no_ids = False no_class = False no_super = False query_constraint = None if obj_constraint.ids and obj_constraint.ids != (0,): query_constraint = obj.id.in_( obj_constraint.ids ) else: no_ids = True if obj_constraint.class_types and obj_constraint.class_types != ['']: query_constraint = or_(query_constraint, or_(*[desc.type == type for type in obj_constraint.class_types]) ) else: no_class = True if obj_constraint.superclass_types and obj_constraint.superclass_types != ['']: query_constraint = or_(query_constraint, or_(*[desc.type.contains(type) for type in obj_constraint.superclass_types]) ) else: no_super = True if no_ids and no_class and no_super: return db().query( obj.id ) else: return db().query( obj.id ).\ filter( obj.relative_description_id == desc.id, query_constraint )
def fix_escaped_quotes(dummy_data, admin_override=False): if admin_override is False and (not current_user.is_mod()): return getResponse(error=True, message="You have to have moderator privileges to do that!") # SELECT * FROM series WHERE title LIKE E'%\\\'%'; bad_title = 0 bad_desc = 0 q = Story.query.filter(or_(Story.title.like(r"%'%"), Story.title.like(r"%’%"), Story.title.like(r"%‘%"), Story.title.like(r"%“%"), Story.title.like(r"%”%"))) items = q.all() print("Name fixing processing query resulted in %s items" % len(items)) for item in items: old = item.title new = old while any([r"\"" in new, r"\'" in new, "’" in new, "‘" in new, "“" in new, "”" in new]): new = new.replace(r"\'", "'") new = new.replace(r'\"', '"') new = new.replace(r"’", "'") new = new.replace(r"‘", "'") new = new.replace(r"“", '"') new = new.replace(r"”", '"') have = Story.query.filter(Story.title == new).scalar() if old != new: if have: print("Duplicate item!", (old, new), old==new) merge_series_ids(have.id, item.id) else: print("Fixing title.") item.title = new db.session.commit() bad_title += 1 # F**K ALL SMART QUOTE BULLSHITS EVER q = Story.query.filter(or_(Story.description.like(r"%'%"), Story.description.like(r"%’%"), Story.description.like(r"%‘%"), Story.description.like(r"%“%"), Story.description.like(r"%”%"))) items = q.all() print("Series description processing query resulted in %s items" % len(items)) for item in items: old = item.description new = old while any([r"\"" in new, r"\'" in new, "’" in new, "‘" in new, "“" in new, "”" in new]): new = new.replace(r"\'", "'") new = new.replace(r'\"', '"') new = new.replace(r"’", "'") new = new.replace(r"‘", "'") new = new.replace(r"“", '"') new = new.replace(r"”", '"') if old != new: print("Fixing description smart-quotes and over-escapes for series: %s" % item.id) item.description = new db.session.commit() bad_desc += 1 print("Update complete.") return getResponse("%s main titles, %s descriptions required fixing. %s" % (bad_title, bad_desc, conflicts), error=False)
def _get_relate_filter(cls, predicate, related_type): """Used for filtering by related_assignee. Returns: Boolean stating whether such an assignee exists. """ # pylint: disable=invalid-name # The upper case variables are allowed here to shorthand the class names. Rel = relationship.Relationship RelAttr = relationship.RelationshipAttr Person = person.Person return db.session.query(Rel).join(RelAttr).join( Person, or_(and_( Rel.source_id == Person.id, Rel.source_type == Person.__name__ ), and_( Rel.destination_id == Person.id, Rel.destination_type == Person.__name__ )) ).filter(and_( RelAttr.attr_value.contains(related_type), RelAttr.attr_name == "AssigneeType", or_(and_( Rel.source_type == Person.__name__, Rel.destination_type == cls.__name__, Rel.destination_id == cls.id ), and_( Rel.destination_type == Person.__name__, Rel.source_type == cls.__name__, Rel.source_id == cls.id )), or_(predicate(Person.name), predicate(Person.email)) )).exists()
def get_graphs_by_edges_and_nodes_and_names(db_session, group_ids=None, names=None, nodes=None, edges=None, tags=None, order=desc(Graph.updated_at), page=0, page_size=10, partial_matching=False, owner_email=None, is_public=None): query = db_session.query(Graph) edges = [] if edges is None else edges nodes = [] if nodes is None else nodes names = [] if names is None else names tags = [] if tags is None else tags edges = [('%%%s%%' % u, '%%%s%%' % v) for u, v in edges] if partial_matching else edges nodes = ['%%%s%%' % node for node in nodes] if partial_matching else nodes names = ['%%%s%%' % name for name in names] if partial_matching else names tags = ['%%%s%%' % tag for tag in tags] graph_filter_group = [] if is_public is not None: graph_filter_group.append(Graph.is_public == is_public) if owner_email is not None: graph_filter_group.append(Graph.owner_email == owner_email) if group_ids is not None: query = query.filter(Graph.shared_with_groups.any(Group.id.in_(group_ids))) if len(graph_filter_group) > 0: query = query.filter(*graph_filter_group) names_filter_group = [Graph.name.ilike(name) for name in names] tags_filter_group = [GraphTag.name.ilike(tag) for tag in tags] nodes_filter_group = [Node.label.ilike(node) for node in nodes] nodes_filter_group.extend([Node.name.ilike(node) for node in nodes]) edges_filter_group = [and_(Edge.head_node.has(Node.name.ilike(u)), Edge.tail_node.has(Node.name.ilike(v))) for u, v in edges] edges_filter_group.extend( [and_(Edge.tail_node.has(Node.name.ilike(u)), Edge.head_node.has(Node.name.ilike(v))) for u, v in edges]) edges_filter_group.extend( [and_(Edge.head_node.has(Node.label.ilike(u)), Edge.tail_node.has(Node.label.ilike(v))) for u, v in edges]) edges_filter_group.extend( [and_(Edge.tail_node.has(Node.label.ilike(u)), Edge.head_node.has(Node.label.ilike(v))) for u, v in edges]) options_group = [] if len(nodes_filter_group) > 0: options_group.append(joinedload('nodes')) if len(edges_filter_group) > 0: options_group.append(joinedload('edges')) if len(options_group) > 0: query = query.options(*options_group) combined_filter_group = [] if len(nodes_filter_group) > 0: combined_filter_group.append(Graph.nodes.any(or_(*nodes_filter_group))) if len(edges_filter_group) > 0: combined_filter_group.append(Graph.edges.any(or_(*edges_filter_group))) if len(names_filter_group) > 0: combined_filter_group.append(*names_filter_group) if len(tags_filter_group) > 0: combined_filter_group.append(*tags_filter_group) if len(combined_filter_group) > 0: query = query.filter(or_(*combined_filter_group)) return query.order_by(order).limit(page_size).offset(page * page_size).all()
def chg_crime(): # Step Seven: Find updates dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata, Column('id', Integer, primary_key=True), extend_existing=True) chg_crime_table.drop(bind=engine, checkfirst=True) chg_crime_table.create(bind=engine) src_cols = [c for c in src_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] dat_cols = [c for c in dat_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] and_args = [] for s, d in zip(src_cols, dat_cols): ors = or_(s != None, d != None) ands = and_(ors, s != d) and_args.append(ands) ins = chg_crime_table.insert()\ .from_select( ['id'], select([src_crime_table.c.id])\ .select_from(src_crime_table.join(dat_crime_table, src_crime_table.c.id == dat_crime_table.c.id))\ .where(or_( and_(dat_crime_table.c.current_flag == True, and_(or_(src_crime_table.c.id != None, dat_crime_table.c.id != None), src_crime_table.c.id != dat_crime_table.c.id)), *and_args)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Changes found'
def get_results_empresas(self, term, page, limit): offset = int((page-1) * limit) results = db.query( models.Empresa ).filter( or_( models.Empresa.razon_social.ilike('%' + term + '%'), models.Empresa.ruc.ilike('%' + term + '%') ) ).limit(limit).offset(offset) count = db.query( models.Empresa ).filter( or_( models.Empresa.razon_social.ilike('%' + term + '%'), models.Empresa.ruc.ilike('%' + term + '%') ) ).count() pagination = self.get_pager(offset, count, page, limit) return [results, pagination]
def web_view_user_posts(request, environment, session, username, page=1, posts_per_page=15): """ returns the <page> <posts_per_page> posts created by <username> as 'posts', <username>'s user object as 'user', an empty array if there aren't any. Possible errortype values are: * 'NoSuchUser' if <username> is unknown to the system. May raise the following Exceptions: * Exception('NoSuchUser') """ u = get_user_obj(username, session) own = session.query(post.id).filter(post.owner == u.identity).subquery() reposts = session.query(post.id).filter( post.reposters.contains(u.identity)).subquery() total_num = session.query(model.post).filter(or_(post.id.in_(reposts), post.id.in_(own))).count() allposts = session.query(model.post).filter( or_(post.id.in_(reposts), post.id.in_(own))).order_by(desc(post.timestamp)).offset((page-1)*posts_per_page).limit(posts_per_page).all() posts = [p.downcast() for p in allposts] return render_template("web_view_user_posts.htmljinja", environment, posts=posts, page_num=page, total_num=total_num, posts_per_page=posts_per_page, user=u)
def search_name( self, name=None, count=None, page=None, label_from_instance=None, name_fields=None, *args, exact=False, **kwargs ): params = kwargs.get('params') join = [] if name: if name_fields is None: name_fields = chain(*(_resolve_fk_search(f, join) for f in self._meta.get_name_fields())) if exact: q = [sa.or_(*[fld.column == name for fld in name_fields])] else: q = [sa.or_(*[fld.column.ilike('%' + name + '%') for fld in name_fields])] if params: q.append(params) kwargs = {'params': q} kwargs['join'] = join qs = self._search(*args, **kwargs) if count: count = qs.count() if page: page = int(page) qs = qs[(page - 1) * CHOICES_PAGE_LIMIT:page * CHOICES_PAGE_LIMIT] else: qs = qs[:CHOICES_PAGE_LIMIT] if isinstance(label_from_instance, list): label_from_instance = lambda obj, label_from_instance=label_from_instance: (obj.pk, ' - '.join([str(getattr(obj, f, '')) for f in label_from_instance if f in self._meta.fields_dict])) if callable(label_from_instance): res = [label_from_instance(obj) for obj in qs] else: res = [obj._get_instance_label() for obj in qs] return { 'count': count, 'items': res, }
def _apply_filters_to_query(self, query, model, filters, context=None): if filters: for key, value in six.iteritems(filters): column = getattr(model, key, None) # NOTE(kevinbenton): if column is a hybrid property that # references another expression, attempting to convert to # a boolean will fail so we must compare to None. # See "An Important Expression Language Gotcha" in: # docs.sqlalchemy.org/en/rel_0_9/changelog/migration_06.html if column is not None: if not value: query = query.filter(sql.false()) return query if isinstance(column, associationproxy.AssociationProxy): # association proxies don't support in_ so we have to # do multiple equals matches query = query.filter( or_(*[column == v for v in value])) else: query = query.filter(column.in_(value)) elif key == 'shared' and hasattr(model, 'rbac_entries'): # translate a filter on shared into a query against the # object's rbac entries query = query.outerjoin(model.rbac_entries) rbac = model.rbac_entries.property.mapper.class_ matches = [rbac.target_tenant == '*'] if context: matches.append(rbac.target_tenant == context.tenant_id) # any 'access_as_shared' records that match the # wildcard or requesting tenant is_shared = and_(rbac.action == 'access_as_shared', or_(*matches)) if not value[0]: # NOTE(kevinbenton): we need to find objects that don't # have an entry that matches the criteria above so # we use a subquery to exclude them. # We can't just filter the inverse of the query above # because that will still give us a network shared to # our tenant (or wildcard) if it's shared to another # tenant. # This is the column joining the table to rbac via # the object_id. We can't just use model.id because # subnets join on network.id so we have to inspect the # relationship. join_cols = model.rbac_entries.property.local_columns oid_col = list(join_cols)[0] is_shared = ~oid_col.in_( query.session.query(rbac.object_id). filter(is_shared) ) query = query.filter(is_shared) for _nam, hooks in six.iteritems(self._model_query_hooks.get(model, {})): result_filter = hooks.get('result_filters', None) if isinstance(result_filter, six.string_types): result_filter = getattr(self, result_filter, None) if result_filter: query = result_filter(query, filters) return query
def get_tokens(self, client_id=None, user_id=None, scopes=(), exclude_revoked=False, exclude_expired=False): tokens = self.tokens if exclude_expired: tokens = tokens.filter(AccessToken.expires_at > datetime.now()) if exclude_revoked: tokens = tokens.filter(AccessToken.base_token_id == BaseToken._id) tokens = tokens.filter(BaseToken.revoked == 0) if client_id: tokens = tokens.filter(AccessToken.base_token_id == BaseToken._id) tokens = tokens.filter(BaseToken.client_id == Client.client_id) tokens = tokens.filter(or_(Client.client_id == client_id, Client.alias == client_id)) if user_id: tokens = tokens.filter(AccessToken.base_token_id == BaseToken._id) tokens = tokens.filter(BaseToken.user_id == User.user_id) tokens = tokens.filter(or_(User.email == user_id, User.user_id == user_id)) for scope in scopes: tokens = tokens.filter(AccessToken.base_token_id == BaseToken._id) tokens = tokens.filter(BaseToken.scope_objects.any(value=scope)) return tokens
def _secure_query(model, *columns): query = b.model_query(model, columns) if not issubclass(model, mb.MistralSecureModelBase): return query shared_res_ids = [] res_type = RESOURCE_MAPPING.get(model, '') if res_type: shared_res = _get_accepted_resources(res_type) shared_res_ids = [res.resource_id for res in shared_res] query_criterion = sa.or_( model.project_id == security.get_project_id(), model.scope == 'public' ) # NOTE(kong): Include IN_ predicate in query filter only if shared_res_ids # is not empty to avoid sqlalchemy SAWarning and wasting a db call. if shared_res_ids: query_criterion = sa.or_( query_criterion, model.id.in_(shared_res_ids) ) query = query.filter(query_criterion) return query
def _check_dvr_serviceable_ports_on_host(self, context, host, subnet_ids): """Check for existence of dvr serviceable ports on host :param context: request context :param host: host to look ports on :param subnet_ids: IDs of subnets to look ports on :return: return True if dvr serviceable port exists on host, otherwise return False """ # db query will return ports for all subnets if subnet_ids is empty, # so need to check first if not subnet_ids: return False Binding = ml2_models.PortBinding IPAllocation = models_v2.IPAllocation Port = models_v2.Port query = context.session.query(Binding) query = query.join(Binding.port) query = query.join(Port.fixed_ips) query = query.filter( IPAllocation.subnet_id.in_(subnet_ids)) device_filter = or_( models_v2.Port.device_owner.startswith( n_const.DEVICE_OWNER_COMPUTE_PREFIX), models_v2.Port.device_owner.in_( n_utils.get_other_dvr_serviced_device_owners())) query = query.filter(device_filter) host_filter = or_( ml2_models.PortBinding.host == host, ml2_models.PortBinding.profile.contains(host)) query = query.filter(host_filter) return query.first() is not None
def whereInEquipement(self,fullQueryJoin,criteria): sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0] sensor = sensorObj['Value'] table = Base.metadata.tables['MonitoredSiteEquipment'] joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID) if sensorObj['Operator'].lower() in ['is','is not'] and sensorObj['Value'].lower() == 'null': subSelect = select([table.c['FK_MonitoredSite']] ).select_from(joinTable).where( and_(MonitoredSite.ID== table.c['FK_MonitoredSite'] ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None) )) if sensorObj['Operator'].lower() == 'is': fullQueryJoin = fullQueryJoin.where(~exists(subSelect)) else : fullQueryJoin = fullQueryJoin.where(exists(subSelect)) else : subSelect = select([table.c['FK_MonitoredSite']] ).select_from(joinTable).where( and_(MonitoredSite.ID== table.c['FK_MonitoredSite'] ,and_(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor) ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None)) )) fullQueryJoin = fullQueryJoin.where(exists(subSelect)) return fullQueryJoin
def predicate(src, dst): return and_( Relationship.source_type == src.type, or_(Relationship.source_id == src.id, src.id == None), # noqa Relationship.destination_type == dst.type, or_(Relationship.destination_id == dst.id, dst.id == None), # noqa )
def main(confdir="/etc/cslbot") -> None: config = configparser.ConfigParser(interpolation=configparser.ExtendedInterpolation()) with open(path.join(confdir, 'config.cfg')) as f: config.read_file(f) session = get_session(config)() channel = '#tjhsst' type_filter = or_(Log.type == 'privmsg', Log.type == 'pubmsg', Log.type == 'action') users = session.query(Log.source).filter(Log.target == channel, type_filter).having(func.count(Log.id) > 500).group_by(Log.source).all() freq = [] for user in users: lines = session.query(Log.msg).filter(Log.target == channel, Log.source == user[0], or_(Log.type == 'privmsg', Log.type == 'pubmsg', Log.type == 'action')).all() text = '\n'.join([x[0] for x in lines]) with open('/tmp/foo', 'w') as f: f.write(text) try: output = subprocess.check_output(['zpaq', 'add', 'foo.zpaq', '/tmp/foo', '-test', '-summary', '1', '-method', '5'], stderr=subprocess.STDOUT, universal_newlines=True) sizes = output.splitlines()[-2] match = re.match(r'.*\((.*) -> .* -> (.*)\).*', sizes) if not match: raise Exception('oh no') before, after = match.groups() # 8 bits = 1 byte count = 1024 * 1024 * 8 * float(after) / len(text) freq.append((user[0], len(lines), float(after) / float(before) * 100, count)) except subprocess.CalledProcessError as e: print(e.stdout) raise e with open('freq.json', 'w') as f: json.dump(freq, f, indent=True) for x in sorted(freq, key=lambda x: x[2]): print("%s: (%d lines) (%f%% compressed) (%f bits per char)" % x)
def update(self): now = datetime.datetime.now() permit_date = now - datetime.timedelta(days=now.weekday()) permit_date = permit_date.replace(hour=0, minute=0, second=0) permit_date1 = permit_date - datetime.timedelta(days=7) last_submitted = self.submit.filter(Submit.submit_time < permit_date, Submit.submit_time > permit_date1) last_solved = ( db.session.query(Submit.oj_name, Submit.pro_id) .distinct() .filter( Submit.user_id == self.id, Submit.submit_time < permit_date, Submit.submit_time > permit_date1, or_(Submit.result == "OK", Submit.result == "Accepted"), ) ) current_submitted = self.submit.filter(Submit.submit_time > permit_date) current_solved = ( db.session.query(Submit.oj_name, Submit.pro_id) .distinct() .filter( Submit.user_id == self.id, Submit.submit_time > permit_date, or_(Submit.result == "OK", Submit.result == "Accepted"), ) ) self.last_week_solved = last_solved.count() self.last_week_submit = last_submitted.count() self.current_week_solved = current_solved.count() self.current_week_submit = current_submitted.count() self.update_score()
def active_users_total(self, realmlist): """ get the total number of users of active tokens for all resolvers which are in allowed realms users are counted per resolver, so if resolver is in more than one realm, its uers will only be counted once :param realmlist: list of (existing and allowed) realms :return: number of users in allowed realms who own an active token """ realm_cond = tuple() for realm in realmlist: realm_cond += (or_(Realm.name == realm),) user_and_resolver = Session.query(Token.LinOtpUserid, Token.LinOtpIdResolver, Token.LinOtpIdResClass, Token.LinOtpIsactive)\ .join(TokenRealm)\ .join(Realm)\ .filter(or_(*realm_cond), and_(Token.LinOtpIsactive == True, Token.LinOtpIdResolver != ''))\ .group_by(Token.LinOtpUserid, Token.LinOtpIdResolver, Token.LinOtpIsactive, Token.LinOtpIdResClass) all_server_total = user_and_resolver.count() return all_server_total
def get(key, cls, attrs=(), page=0, per_page=50, local=True): # # Local is a flag that determines whether we only return objects local to the # calling key's namespace, or whether we will permit global objects with identical names # to local objects in the response. # if page and per_page: if key.systemwide: return cls.query.filter(or_(cls.key == None, cls.key == key)).paginate(page, per_page).items return cls.query.filter(cls.key == key).paginate(page,per_page).items if attrs: (attr, identifier) = attrs attribute = getattr(cls, attr) if attribute: if key.systemwide: item = cls.query.filter( or_(and_(attribute==identifier, cls.key == None), and_(attribute==identifier, cls.key == key)) ).all() if local: if len(item) == 1: return item[0] for i in item: if i.key == key: return i return item else: item = cls.query.filter(and_(attribute==identifier, cls.key == key)).first() return item raise Exception('Unrecognised attribute "%s" of %s.' % (attr, repr(cls))) if key.systemwide: return cls.query.filter(or_(cls.key == None, cls.key == key)).all() return cls.query.filter(cls.key == key).all()
def evidences(cls): # pylint: disable=no-self-argument """Return evidences related for that instance.""" return db.relationship( Evidence, primaryjoin=lambda: sa.or_( sa.and_( cls.id == Relationship.source_id, Relationship.source_type == cls.__name__, Relationship.destination_type == "Evidence", ), sa.and_( cls.id == Relationship.destination_id, Relationship.destination_type == cls.__name__, Relationship.source_type == "Evidence", ) ), secondary=Relationship.__table__, secondaryjoin=lambda: sa.or_( sa.and_( Evidence.id == Relationship.source_id, Relationship.source_type == "Evidence", ), sa.and_( Evidence.id == Relationship.destination_id, Relationship.destination_type == "Evidence", ) ), viewonly=True, )
def build_initial_query(self, trans, **kwd): clause_list = [] tool_shed_repository_ids = kwd.get('tool_shed_repository_ids', None) if tool_shed_repository_ids: if isinstance(tool_shed_repository_ids, string_types): try: # kwd['tool_shed_repository_ids'] may be a json dump of repo ids like u'['aebaa141e7243ebf']' tool_shed_repository_ids = json.loads(tool_shed_repository_ids) except ValueError: pass tool_shed_repository_ids = util.listify(tool_shed_repository_ids) for tool_shed_repository_id in tool_shed_repository_ids: clause_list.append(self.model_class.table.c.id == trans.security.decode_id(tool_shed_repository_id)) if clause_list: return trans.install_model.context.query(self.model_class) \ .filter(or_(*clause_list)) for tool_shed_repository in trans.install_model.context.query(self.model_class) \ .filter(self.model_class.table.c.deleted == false()): if tool_shed_repository.status in [trans.install_model.ToolShedRepository.installation_status.NEW, trans.install_model.ToolShedRepository.installation_status.CLONING, trans.install_model.ToolShedRepository.installation_status.SETTING_TOOL_VERSIONS, trans.install_model.ToolShedRepository.installation_status.INSTALLING_TOOL_DEPENDENCIES, trans.install_model.ToolShedRepository.installation_status.LOADING_PROPRIETARY_DATATYPES]: clause_list.append(self.model_class.table.c.id == tool_shed_repository.id) if clause_list: return trans.install_model.context.query(self.model_class) \ .filter(or_(*clause_list)) return trans.install_model.context.query(self.model_class) \ .filter(self.model_class.table.c.status == trans.install_model.ToolShedRepository.installation_status.NEW)
def get_permissions_query(model_names, permission_type='read', permission_model=None): """Prepare the query based on the allowed contexts and resources for each of the required objects(models). """ type_queries = [] for model_name in model_names: contexts, resources = query_helpers.get_context_resource( model_name=model_name, permission_type=permission_type, permission_model=permission_model ) if contexts is not None: if resources: resource_sql = and_( MysqlRecordProperty.type == model_name, MysqlRecordProperty.key.in_(resources)) else: resource_sql = false() type_query = or_( and_( MysqlRecordProperty.type == model_name, context_query_filter(MysqlRecordProperty.context_id, contexts) ), resource_sql) type_queries.append(type_query) return and_( MysqlRecordProperty.type.in_(model_names), or_(*type_queries))
def getContacts(search,baoxiang): start=0 limit=30 if search!='': search="%"+search+"%" if baoxiang!="": baoxiang="%"+baoxiang+"%" #objs = Contact.objects.filter((Q(hetongbh__icontains=search) | Q(yiqibh__icontains=search)) & Q(baoxiang=baoxiang)).order_by('-yujifahuo_date')[start:start+limit] objs=session.query(PartsContact).filter( and_( or_(PartsContact.hetongbh.like(search),PartsContact.yiqibh.like(search)), PartsContact.baoxiang.like(baoxiang) ) ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit] else: objs=session.query(PartsContact).filter( or_(PartsContact.hetongbh.like(search),PartsContact.yiqibh.like(search)) ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit] # Contact.objects.filter(Q(hetongbh__icontains=search) | Q(yiqibh__icontains=search)).order_by('-yujifahuo_date')[start:start+limit] else: if baoxiang!="": baoxiang="%"+baoxiang+"%" objs=session.query(PartsContact).filter( PartsContact.baoxiang.like(baoxiang) ).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit] else: objs=session.query(PartsContact).order_by(desc(PartsContact.yujifahuo_date))#[start:start+limit] return objs
def get_loan_info(db, movie_id, volume_id=None, collection_id=None): """Returns current collection/volume/movie loan data""" from sqlalchemy import and_, or_ movie = db.Movie.get_by(movie_id=movie_id) if movie is None: return False # fix or add volume/collection data: if movie.collection_id is not None: collection_id = movie.collection_id if movie.volume_id is not None: volume_id = movie.volume_id if collection_id>0 and volume_id>0: return db.Loan.get_by( and_(or_(db.Loan.c.collection_id==collection_id, db.Loan.c.volume_id==volume_id, db.Loan.c.movie_id==movie_id), db.Loan.c.return_date==None)) elif collection_id>0: return db.Loan.get_by( and_(or_(db.Loan.c.collection_id==collection_id, db.Loan.c.movie_id==movie_id)), db.Loan.c.return_date==None) elif volume_id>0: return db.Loan.get_by(and_(or_(db.Loan.c.volume_id==volume_id, db.Loan.c.movie_id==movie_id)), db.Loan.c.return_date==None) else: return db.Loan.get_by(db.Loan.c.movie_id==movie_id,db.Loan.c.return_date==None)
def filter_single_by_time(cls, type, objects, year=None, week_number=None, day=None): assert (week_number and year) or day start_date, end_date = None, None if year and week_number: start_date, end_date = get_start_and_end_date_from_week_and_year( year, week_number ) if day: start_date = day end_date = day objects = objects.filter( or_( tuple_( cast(type.start_time, Date), cast(type.end_time, Date) ).op('overlaps')( tuple_( start_date, end_date ) ), or_( # First range ends on the start date of the second cast(type.end_time, Date) == start_date, # Second range ends on the start date of the first end_date == cast(type.start_time, Date) ) ) ) return objects
def item_search(classes=None, bodygroups=None, equip_regions=None, item_name=None): items_query = TF2Item.query.filter_by(inactive=False) wildcards = ["%", "_"] if item_name: if any([w in item_name for w in wildcards]): return items_query = items_query.filter(TF2Item.item_name.contains(item_name)) if len(classes) > 0: for class_name in classes: items_query = items_query.filter(TF2Item.class_model.any(TF2ClassModel.class_name == class_name)) sq = db.session.query(TF2ClassModel.defindex, func.count(TF2ClassModel).label("class_count")).group_by(TF2ClassModel.defindex).subquery() items_query = items_query.join(sq, TF2Item.defindex == sq.c.defindex) if len(classes) == 9: pass elif len(classes) > 1: items_query = items_query.filter(sq.c.class_count > 1).filter(sq.c.class_count < 9) elif len(classes) == 1: items_query = items_query.filter(sq.c.class_count == 1) else: return if equip_regions: items_query = items_query.filter(TF2Item.equip_regions.any(or_(*[TF2EquipRegion.equip_region == equip_region for equip_region in equip_regions]))) if bodygroups: items_query = items_query.filter(TF2Item.bodygroups.any(or_(*[TF2BodyGroup.bodygroup == bodygroup for bodygroup in bodygroups]))) bodygroup_count = db.session.query(schema_bodygroup.c.defindex, func.count('*').label("bg_count")).group_by(schema_bodygroup.c.defindex).subquery() items_query = items_query.join(bodygroup_count, TF2Item.defindex == bodygroup_count.c.defindex).filter(bodygroup_count.c.bg_count == len(bodygroups)) else: items_query = items_query.filter(TF2Item.bodygroups == None) return items_query
def comments(cls): # pylint: disable=no-self-argument """Comments related to self via Relationship table.""" return db.relationship( Comment, primaryjoin=lambda: sa.or_( sa.and_( cls.id == Relationship.source_id, Relationship.source_type == cls.__name__, Relationship.destination_type == "Comment", ), sa.and_( cls.id == Relationship.destination_id, Relationship.destination_type == cls.__name__, Relationship.source_type == "Comment", ) ), secondary=Relationship.__table__, secondaryjoin=lambda: sa.or_( sa.and_( Comment.id == Relationship.source_id, Relationship.source_type == "Comment", ), sa.and_( Comment.id == Relationship.destination_id, Relationship.destination_type == "Comment", ) ), viewonly=True, )
def filter_repeating_by_time(cls, type, objects, year=None, week_number=None, day=None): assert (week_number and year) or day start_date, end_date = None, None if year and week_number: start_date, end_date = get_start_and_end_date_from_week_and_year( year, week_number ) if day: start_date = day end_date = day objects = objects.filter( type.week_day == day.isoweekday() ) objects = objects.filter( or_( tuple_( type.start_date, type.end_date ).op('overlaps')( tuple_( start_date, end_date ) ), or_( # First range ends on the start date of the second type.end_date == start_date, # Second range ends on the start date of the first end_date == type.start_date ) ) ) return objects
def removeMinimum(self, m, commit=True): """remove a minimum from the database Remove a minimum and any objects (TransitionState or Distance) pointing to that minimum. """ #delete any distance objects pointing to min2 candidates = self.session.query(Distance).\ filter(or_(Distance.minimum1 == m, Distance.minimum2 == m)) candidates = list(candidates) for d in candidates: self.session.delete(d) #delete any transition states objects pointing to min2 candidates = self.session.query(TransitionState).\ filter(or_(TransitionState.minimum1 == m, TransitionState.minimum2 == m)) candidates = list(candidates) for ts in candidates: self.on_ts_removed(ts) self.session.delete(ts) self.on_minimum_removed(m) #delete the minimum self.session.delete(m) if commit: self.session.commit()
now.strftime('%Y-%m-%d %H:%M')) mess += """ <br> <table border='1' cellpadding="0" cellspacing="0" ><tr><td>исполнитель</td><td>задачи</td></tr> """ ids = {} threads = [ (th.id, th.fio_perf, th.id_global) for th in dbconn.query(Threads).filter( Threads.performer_id.in_([user_id, common_perf[title]]) ).filter(Threads.status == Status.text).filter( Status.id == 1).filter(Threads.title_id == Tasktype.id). filter(Tasktype.system_id == 0).filter( sqlalchemy.or_( Threads.creation_date < delta.strftime('%Y-%m-%d'), sqlalchemy.and_( Threads.creation_date == delta.strftime('%Y-%m-%d'), Threads.creation_time < delta.strftime('%H:%M:%S')))) ] if threads: for (id, fio, glob) in threads: link = "<a href='http://helpdesk.renet.ru/hd/thread?id=%s'>%s</a>" % ( id, glob) if fio in ids: ids[fio].append(link) else: ids[fio] = [link] for fio, task_ids in ids.items(): mess += """ <tr><td>%s</td><td>%s</td></tr>
def test_snapshot_update(self): """Test snapshot update with a simple change""" program = self.create_object(models.Program, {"title": "Test Program Snapshot 1"}) control = self.create_object(models.Control, {"title": "Test Control Snapshot 1"}) self.create_mapping(program, control) control = self.refresh_object(control) self.api.modify_object(control, {"title": "Test Control Snapshot 1 EDIT 1"}) self.create_audit(program) audit = db.session.query(models.Audit).filter( models.Audit.title.like("%Snapshotable audit%")).one() control_snapshot = db.session.query(models.Snapshot).filter( models.Snapshot.child_id == control.id, models.Snapshot.child_type == "Control", models.Snapshot.parent_type == "Audit", models.Snapshot.parent_id == audit.id) self.assertEqual(control_snapshot.count(), 1) self.assertEqual(control_snapshot.first().revision.content["title"], "Test Control Snapshot 1 EDIT 1") # Create a new objective, add it to program and edit control to detect # update. # Map the objective to the control to check snapshot-to-snapshot mappings. objective = self.create_object( models.Objective, {"title": "Test Objective Snapshot UNEDITED"}) self.create_mapping(program, objective) self.create_mapping(objective, control) control = self.refresh_object(control) self.api.modify_object( control, {"title": "Test Control Snapshot 1 Edit 2 AFTER initial snapshot"}) audit = self.refresh_object(audit) # Initiate update operation self.api.modify_object(audit, {"snapshots": {"operation": "upsert"}}) objective_snapshot_query = db.session.query(models.Snapshot).filter( models.Snapshot.child_type == "Objective", models.Snapshot.child_id == objective.id, models.Snapshot.parent_type == "Audit", models.Snapshot.parent_id == audit.id) self.assertEqual(objective_snapshot_query.count(), 1) objective_snapshot = objective_snapshot_query.first() self.assertEqual(objective_snapshot.revision.content["title"], "Test Objective Snapshot UNEDITED") control_snapshot_query = db.session.query(models.Snapshot).filter( models.Snapshot.child_type == "Control", models.Snapshot.child_id == control.id, models.Snapshot.parent_type == "Audit", models.Snapshot.parent_id == audit.id) self.assertEqual(control_snapshot_query.count(), 1) control_snapshot = control_snapshot_query.first() self.assertEqual( control_snapshot.revision.content["title"], "Test Control Snapshot 1 Edit 2 AFTER initial snapshot") snapshot_mapping = db.session.query(models.Relationship).filter( models.Relationship.source_type == "Snapshot", models.Relationship.destination_type == "Snapshot", sa.or_( sa.and_( models.Relationship.source_id == control_snapshot.id, models.Relationship.destination_id == objective_snapshot.id, ), sa.and_( models.Relationship.source_id == objective_snapshot.id, models.Relationship.destination_id == control_snapshot.id, ), ), ) self.assertEqual(snapshot_mapping.count(), 1) control_revisions = db.session.query(models.Revision).filter( models.Revision.resource_type == control.type, models.Revision.resource_id == control.id) # 2 revisions are from the initial creation, and 2 are from edits. self.assertEqual(control_revisions.count(), 4) self.assertEqual( control_revisions.order_by(models.Revision.id.desc()).first().id, control_snapshot.revision_id, )
print('name: %s sex: %s sid: %s' % (stu.stu_name,stu.stu_sex,stu.stu_id)) print('-' * 30) query8 = session.query(Stuinfo).filter(~Stuinfo.stu_name.in_(['小红','张三'])) for stu in query8: print(stu) print('name: %s sex: %s sid: %s' % (stu.stu_name,stu.stu_sex,stu.stu_id)) print('-' * 30) from sqlalchemy import and_,or_ query10 = session.query(Stuinfo)\ .filter(and_(Stuinfo.stu_id>=1,Stuinfo.stu_id<4)) for stu in query10: print(stu) print('name: %s sex: %s sid: %s' % (stu.stu_name,stu.stu_sex,stu.stu_id)) print('-' * 30) query11 = session.query(Stuinfo)\ .filter(or_(Stuinfo.stu_id<=1,Stuinfo.stu_id>4)) for stu in query11: print(stu) print('name: %s sex: %s sid: %s' % (stu.stu_name,stu.stu_sex,stu.stu_id)) print('-' * 30) query12 = session.query(Stuinfo) print(query12.all()) # for stu in query12.all(): # print(stu) # print('name: %s sex: %s sid: %s' % (stu.stu_name, stu.stu_sex, stu.stu_id)) print(query12.first()) print('-' * 30) query13 = session.query(Stuinfo.stu_id,Stuinfo.stu_name).filter(Stuinfo.stu_id==4) print(query13.one()) print(query13.scalar()) print('-' * 30)
def dequeue(queueName, userId, visibility_timeout=None, session=None): """ Dequeue subject to queue configuration (max_outstanding_message and vis timeout). If queue's max_outstanding_messages > 0 then messages are hidden and returned with a receipt handle rather than deleting the message. :param queueName: :param userId: :param session: :return: """ if not session: session = db.Session ret = {} # Is it cached? cached_record = config_cache().lookup(key=(userId, queueName)) if cached_record: outstanding_count_setting = cached_record["max_outstanding_messages"] else: metarecord = ( session.query(QueueMeta) .filter_by(queueName=queueName, userId=userId) .first() ) config_cache().cache_it( key=(userId, queueName), obj=copy.deepcopy(_to_dict(metarecord)) ) outstanding_count_setting = metarecord.max_outstanding_messages metarecord = None if outstanding_count_setting < 0: metarecord = ( session.query(QueueMeta) .filter_by(queueName=queueName, userId=userId) .first() ) result = ( session.query(Queue) .with_for_update(of=Queue) .filter_by(queueName=queueName, userId=userId, popped=False) .order_by(desc(Queue.priority)) .order_by(asc(Queue.queueId)) .first() ) if result: result.update({"popped": True}) dbobj = _to_dict(result) ret.update(dbobj) ret["data"] = json.loads(dbobj["data"]) session.delete(result) # Only update the count if returning a message if ret: rcount = ( session.query(Queue) .filter_by(queueName=queueName, userId=userId, popped=False) .count() ) metarecord.update({"qlen": rcount}) else: # Flush the record from the session and memory. Then reload it with a lock # Refetch with lock metarecord = ( session.query(QueueMeta) .with_for_update(of=QueueMeta) .filter_by(queueName=queueName, userId=userId) .first() ) # Limits are configured on this queue, do appropriate checks if _not_visible_msg_count(queueName, userId, session) < int( metarecord.max_outstanding_messages ): # Will select any unpopped or popped-but-expired messages result = ( session.query(Queue) .with_for_update(of=Queue) .filter_by(queueName=queueName, userId=userId) .filter( or_( and_( Queue.visible_at <= datetime.datetime.utcnow(), Queue.popped == True, ), Queue.popped == False, ) ) .order_by(desc(Queue.priority)) .order_by(asc(Queue.queueId)) .first() ) if result: if visibility_timeout is None: visibility_timeout = metarecord.visibility_timeout result.update( { "popped": True, "receipt_handle": uuid.uuid4().hex, "visible_at": datetime.datetime.utcnow() + datetime.timedelta(seconds=visibility_timeout), } ) dbobj = _to_dict(result) ret.update(dbobj) ret["data"] = json.loads(dbobj["data"]) # Don't update qlen until the message is deleted from the queue with an explicit delete operation else: # Threshold of outstanding messages exceeded. pass return ret
def class2_start_end(class1, class2, class3, start_date, end_date): """Return filtered data between start and end date and filter by class.""" # dates appear in the format : 2012-08-01 if len(start_date) < 10: start_date = start_date + "-01" if len(end_date) < 10: end_date = end_date + "-31" # classification appears in this format : Class I if class1 == 'true' and class2 == 'true' and class3 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class1 == 'true' and class2 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(or_(food_db.classification == "Class I", food_db.classification == "Class II")).\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class1 == 'true' and class3 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(or_(food_db.classification == "Class I", food_db.classification == "Class III")).\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class2 == 'true' and class3 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(or_(food_db.classification == "Class II", food_db.classification == "Class III")).\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class1 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(food_db.classification == "Class I").\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class2 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(food_db.classification == "Class II").\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list elif class3 == 'true': start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ filter(food_db.classification == "Class III").\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list else: # Use Pandas to perform the sql query start_end_class_recalls = db.session.query(food_db).\ filter(food_db.recall_date >= start_date).\ filter(food_db.recall_date <= end_date).\ statement df = pd.read_sql_query(start_end_class_recalls, db.session.bind) recalls_class_list = df.to_json(orient='records') return recalls_class_list
# like session.query(User).filter(User.name.like("%张%")) # in session.query(User).filter(User.id.in_([1, 2, 3])) # not in session.query(User).filter(~User.id.in_([1, 2, 3])) # is null session.query(User).filter(User.age == None) # is not null session.query(User).filter(User.age != None) # and from sqlalchemy import and_ session.query(User).filter(and_(User.id == 1, User.name == '张三')) # or from sqlalchemy import or_ session.query(User).filter(or_(User.id == 1, User.name == '张三')) # limit session.query(User).offset(2).limit(3).all() # ========= from sqlalchemy import func def test_func(): session = Session() result = session.query(func.count(User.id)).scalar() print("count:", result) result = session.query(func.max(User.id)).scalar() print(result) result = session.query(func.min(User.id)).scalar()
def recentThreeItems(self): item = [] for u in price.query.filter(or_(Price.symbol == 'SP', Price.symbol == 'BTC', Price.symbol == 'BINDEX')).order_by(Price.date.desc()).limit(2): item.append(u.__dict__) return item
def demarrer_scanner(hWaitStop=None, batch=None): logginghashscan.info('Starting an Hash scanner instance : ' + threadname) print '' print '\tPlease log in to launch scan' print '' username = raw_input('Username: '******'Password: '******'' # Get user u = session.query(User).filter_by(username=username).first() # No user or bad password if not u or hashPassword(password) != u.password: logginghashscan.critical( 'Username or password incorrect, stopping the initialization...') raw_input() return # Get KEY and decrypt MASTER_KEY keyFromPassword = crypto.keyFromText(password, base64.b64decode(u.b64_kdf_salt)) MASTER_KEY = crypto.decrypt(u.encrypted_master_key, keyFromPassword) mk_cksum = session.query(GlobalConfig).filter_by( key='master_key_checksum').first() # No checksum in config ??? if not mk_cksum: logginghashscan.critical( 'Database is broken, please create a new one, stopping the initialization...' ) del MASTER_KEY raw_input() return # Someone has been playing with the database ! if checksum(MASTER_KEY) != mk_cksum.value: logginghashscan.critical( 'MASTER_KEY may have been altered, stopping the initialization...') del MASTER_KEY raw_input() return logginghashscan.info('Login successful !') # INITIALIZATION # TODO : initialise all IOCs in DB, then link them to CP all_xmliocs = session.query(XMLIOC).order_by(XMLIOC.name.asc()) all_cp = session.query(ConfigurationProfile).order_by( ConfigurationProfile.name.asc()) ioc_by_cp = {} for cp in all_cp: if cp.ioc_list == '': logginghashscan.warning('No IOC defined for profile "%s"' % cp.name) continue ioc_by_cp[cp.id] = [] for e in cp.ioc_list.split(','): ioc_by_cp[cp.id].append(int(e)) tree_by_ioc = {} # Retrieves evaluators for current mode FLAT_MODE = (IOC_MODE == 'flat') allowedElements = {} evaluatorList = hash_modules.flatEvaluatorList if FLAT_MODE else hash_modules.logicEvaluatorList for name, classname in evaluatorList.items(): allowedElements[name] = classname.evalList # Parse XML Ioc into IOC trees according to what we can do for xmlioc in all_xmliocs: content = base64.b64decode(xmlioc.xml_content) oip = openiocparser.OpenIOCParser(content, allowedElements, FLAT_MODE, fromString=True) oip.parse() iocTree = oip.getTree() # Trees may be stripped from non valid elements if iocTree is not None: tree_by_ioc[xmlioc.id] = {'name': xmlioc.name, 'tree': iocTree} # Each configuration profile has a set of trees tree_by_cp = { cpid: {i: tree_by_ioc[i] for i in ioclist} for (cpid, ioclist) in ioc_by_cp.items() } halt = False tache = None batchquery = None # Batch filtering if batch is not None: logginghashscan.info('Filtering for batch "%s"' % batch) batchquery = session.query(Batch).filter(Batch.name == batch).first() if batchquery is None: logginghashscan.error('Unknown batch "%s" ...' % batch) halt = True # LAUNCH # Main loop while not halt: try: # Get targets to be scanned # and that are not currently being scanned # or that don't have any retry left queue = session.query(Task).filter_by( hashscanned=False, reserved_ioc=False, reserved_hash=False).filter(Task.retries_left_hash > 0) # Batch filtering if batchquery is not None: queue = queue.filter_by(batch_id=batchquery.id) taille_queue = queue.count() # Compute the time after which targets are still recovering from last scan # Gets target which last retry is NULL or before that time limite_a_reessayer = datetime.datetime.now() - datetime.timedelta( 0, SECONDES_ENTRE_TENTATIVES) a_scanner = queue.filter( or_(Task.last_retry_hash <= limite_a_reessayer, Task.last_retry_hash == None)) taille_a_scanner = a_scanner.count() # Reads this list while taille_a_scanner > 0: # Max priority priorite_max = a_scanner.order_by( Task.priority_hash.desc()).first().priority_hash taches_priorite_max = a_scanner.filter( Task.priority_hash == priorite_max) nbre_taches_priorite_max = taches_priorite_max.count() if BASE_DE_DONNEES_QUEUE.startswith('sqlite'): tache = taches_priorite_max.order_by(func.random()).first() else: tache = taches_priorite_max.order_by(func.newid()).first() # Mutex on the task tache.reserved_hash = True tache.date_debut = datetime.datetime.now() session.commit() logginghashscan.debug( '===============================================================================' ) logginghashscan.debug('Wake up, there is work to do !') logginghashscan.info('Queue size : ' + str(taille_queue) + ', including ' + str(taille_a_scanner) + ' to scan, including ' + str(nbre_taches_priorite_max) + ' at top priority (' + str(priorite_max) + ')') logginghashscan.debug(' --------------------------------') logginghashscan.info(' Starting Hash Scan') logginghashscan.info(' Target : ' + str(tache.ip)) logginghashscan.debug(' --------------------------------') # Recover Windows Credential and Configuration Profile from Batch batch = session.query(Batch).filter_by( id=tache.batch_id).first() wc = session.query(WindowsCredential).filter_by( id=batch.windows_credential_id).first() cp = session.query(ConfigurationProfile).filter_by( id=batch.configuration_profile_id).first() if not wc: raise Exception('WindowsCredential %d does not exist' % tache.windows_credential_id) if not cp: raise Exception('ConfigurationProfile %d does not exist' % tache.configuration_profile_id) # Decrypt password using MASTER_KEY and create target object targetPassword = crypto.decrypt(wc.encrypted_password, MASTER_KEY) targetObject = { 'ip': tache.ip, 'login': wc.login, 'password': targetPassword, 'domain': wc.domain, } # If high confidentiality is enabled, create local directory if needed if cp.host_confidential: logginghashscan.info('"High confidentiality" mode enabled') testdir = os.path.join(IOC_COMPONENT_ROOT, IOC_CONFIDENTIAL_DIRECTORY) if not os.path.isdir(testdir): logginghashscan.info( 'Creating confidential directory %s' % testdir) os.makedirs(testdir) # Let the scan begin if cp.id in tree_by_cp.keys(): resultats_scan = scan(targetObject, tree_by_cp[cp.id], cp.host_confidential) else: logginghashscan.warning('No IOC to scan (profile=%s)' % cp.name) resultats_scan = {} # Analyze the results analyse(resultats_scan, tache) # Update queue size taille_a_scanner = a_scanner.count() try: # If launched as a service (probably removed soon, TODO) halt = (win32event.WaitForSingleObject( hWaitStop, 2000) == win32event.WAIT_OBJECT_0) except: pass if halt: # Stop signal encountered break if halt: logginghashscan.info('Stopping Hash scanner : ' + threadname) break logginghashscan.debug('(Hash scanner sleeping for ' + str(SLEEP) + ' seconds...)' \ + (' (' + str(taille_queue) + ' waiting)' if taille_queue > 0 else '')) time.sleep(SLEEP) except KeyboardInterrupt: halt = True except Exception, e: logginghashscan.error('Exception caught : %s, %s, %s' % (repr(e), str(e.message), str(e))) # Cancel changes and unreserve task session.rollback() if tache is not None: tache.reserved_hash = False tache.retries_left_hash = max(0, tache.retries_left_hash - 1) session.commit()
def episode_lookup(session=None, only_cached=False, **lookup_params): series_name = lookup_params.get('series_name') or lookup_params.get( 'title') show_id = lookup_params.get('tvmaze_id') or lookup_params.get( 'tvdb_id') lookup_type = lookup_params.get('series_id_type') season_number = lookup_params.get('series_season') episode_number = lookup_params.get('series_episode') episode_date = lookup_params.get('series_date') # Verify we have enough parameters for search if not any([series_name, show_id]): raise LookupError('Not enough parameters to lookup episode') if lookup_type == 'sequence': raise LookupError('TVMaze does not support sequence type searches') if lookup_type == 'ep' and not all([season_number, episode_number]): raise LookupError('Not enough parameters to lookup episode') elif lookup_type == 'date' and not episode_date: raise LookupError('Not enough parameters to lookup episode') # Get series series = APITVMaze.series_lookup(session=session, only_cached=only_cached, **lookup_params) if not series: raise LookupError( 'Could not find series with the following parameters: {0}'. format(lookup_params)) # See if episode already exists in cache log.debug('searching for episode of show {0} in cache'.format( series.name)) episode = session.query(TVMazeEpisodes).filter( and_(TVMazeEpisodes.series_id == series.tvmaze_id, TVMazeEpisodes.season_number == season_number, TVMazeEpisodes.number == episode_number)).one_or_none() # Logic for cache only mode if only_cached: if episode: log.debug( 'forcing cache for episode id {3}, number{0}, season {1} for show {2}' .format(episode.number, episode.season_number, series.name, episode.tvmaze_id)) return episode if episode and not episode.expired: log.debug( 'found episode id {3}, number {0}, season {1} for show {2} in cache' .format(episode.number, episode.season_number, series.name, episode.tvmaze_id)) return episode # Lookup episode via its type (number or airdate) if lookup_type == 'date': episode_date = datetime.strftime(episode_date, '%Y-%m-%d') tvmaze_episode = get_episode(series.tvmaze_id, date=episode_date)[0] else: # TODO will this match all series_id types? log.debug( 'fetching episode {0} season {1} for series_id {2} for tvmaze'. format(episode_number, season_number, series.tvmaze_id)) tvmaze_episode = get_episode(series.tvmaze_id, season=season_number, number=episode_number) # See if episode exists in DB try: episode = session.query(TVMazeEpisodes).filter( or_( TVMazeEpisodes.tvmaze_id == tvmaze_episode['id'], and_( TVMazeEpisodes.number == tvmaze_episode['number'], TVMazeEpisodes.season_number == tvmaze_episode['season'], TVMazeEpisodes.series_id == series.tvmaze_id))).one_or_none() except MultipleResultsFound: # TVMaze must have f****d up and now we have to clean up that mess. Delete any row for this season # that hasn't been updated in the last hour. Can't trust any of the cached data, but deleting new data # might have some unintended consequences. log.warning( 'Episode lookup in cache returned multiple results. Deleting the cached data.' ) deleted_rows = session.query(TVMazeEpisodes).filter( and_(TVMazeEpisodes.season_number == tvmaze_episode['season'], TVMazeEpisodes.series_id == series.tvmaze_id)).filter( TVMazeEpisodes.last_update <= datetime.now() - timedelta(hours=1)).delete() log.debug('Deleted %s rows', deleted_rows) episode = None if episode: log.debug( 'found expired episode {0} in cache, refreshing data.'.format( episode.tvmaze_id)) episode.update(tvmaze_episode) else: log.debug('creating new episode for show {0}'.format(series.name)) episode = TVMazeEpisodes(tvmaze_episode, series.tvmaze_id) session.add(episode) return episode
def get_query(cls, model, info, **args): from sqlalchemy import or_ from sqlalchemy.orm import load_only query = super(FilteringConnectionField, cls).get_query(model, info) from sqlalchemy.orm import joinedload distinct_filter = False # default value for distinct op = 'eq' jsonkey_input = None ALLOWED_OPS = ['gt', 'lt', 'le', 'ge', 'eq', 'ne', '=', '>', '<', '>=', '<=', '!='] cont_fields = ['edges', 'node'] skip_fields = ['totalCount', 'pageInfo'] fields = info.field_asts# [0].selection_set.selections load_fields = {} field_names = [] def convert(name): import re s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name) return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower() include_all = False while isinstance(fields, list): for field in fields: name = field.name.value if name in cont_fields: fields = field.selection_set.selections elif name in skip_fields:# or name[0].isupper(): continue else: if name[0].isupper(): # hybrid property names = models.hybrid_prop_parameters(name) if 'all' in names: include_all = True fields = None continue else: names = [name] for name in names: if field.selection_set is not None: keyname = name field_names.append(name) load_fields.update({keyname: []}) fields = field.selection_set.selections else: load_fields[keyname].append(convert(name)) fields = None if not include_all: query = query.options(load_only(*load_fields[field_names[0]])) if len(field_names) > 1: column = getattr(model, convert(field_names[1]), None) query = query.options(joinedload(column, innerjoin=True).load_only(*load_fields[field_names[1]])) for field, value in args.items(): if field == 'distinct': distinct_filter = value elif field == 'op': if value in ALLOWED_OPS: op = value elif field == 'jsonkey': jsonkey_input = value elif field == 'order': ascending = not value.startswith('-') column_name = value if not value.startswith('-') else value[1:] column = getattr(model, convert(column_name), None) if ascending: query = query.order_by(column) else: query = query.order_by(column.desc()) for field, value in args.items(): if field not in (cls.RELAY_ARGS + cls.SPECIAL_ARGS): from sqlalchemy.sql.expression import func, cast jsonb = False jsonkey = None if '__' in field: field, jsonkey = field.split('__') elif '->' in str(value): jsonkey, value = value.split('->') if jsonkey is None: jsonkey = jsonkey_input column = getattr(model, field, None) if str(column.type) == "TSVECTOR": query = query.filter(column.match("'{}'".format(value))) elif str(column.type) == "JSONB": jsonb = True if jsonkey is not None: query = query.filter(column.has_key(jsonkey)) column = column[jsonkey].astext values = value.split('+') for value in values: value = value.strip() if value.startswith("~"): column = cast(column, sqlalchemy.String) # if field == 'reactants' or field == 'products': # column = func.replace(func.replace(column, 'gas', ''), 'star', '') search_string = '%' + value[1:] + '%' if not value == "~": query = query.filter( column.ilike(search_string)) # else: # query = query.group_by(column) else: if field == 'reactants' or field == 'products': if not 'star' in value and not 'gas' in value: or_statement = or_(column.has_key(value), column.has_key(value + 'gas'), column.has_key(value + 'star')) query = query.filter(or_statement) else: query = query.filter(column.has_key(value)) else: if jsonkey is not None: query = query.filter(column == value) else: query = query.filter(column.has_key(value)) # if distinct_filter: # TO DO: SELECT DISTINCT jsonb_object_keys(reactants) FROM reaction elif isinstance(value, six.string_types): if value.startswith("~") or '+' in value: values = value.split('+') for value in values: value = value.replace('~', '') search_string = '%' + value + '%' if not query == "~": query = query.filter(column.ilike(search_string)) else: query = query.filter(column == value) # if distinct_filter: # query = query.distinct(column)#.group_by(column) else: if op in ['ge', '>=']: query = query.filter(column >= value) elif op in ['gt', '>']: query = query.filter(column > value) elif op in ['lt', '<']: query = query.filter(column < value) elif op in ['le', '<=']: query = query.filter(column <= value) elif op in ['ne', '!=']: query = query.filter(column != value) else: query = query.filter(column == value) if distinct_filter: query = query.distinct(column) # .group_by(getattr(model, field)) return query
def packages(self, with_private=False, limit=None, return_query=False, context=None): '''Return this group's active and pending packages. Returns all packages in this group with VDM revision state ACTIVE or PENDING. :param with_private: if True, include the group's private packages :type with_private: boolean :param limit: the maximum number of packages to return :type limit: int :param return_query: if True, return the SQLAlchemy query object instead of the list of Packages resulting from the query :type return_query: boolean :returns: a list of this group's packages :rtype: list of ckan.model.package.Package objects ''' user_is_org_member = False context = context or {} user_is_admin = context.get('user_is_admin', False) user_id = context.get('user_id') if user_is_admin: user_is_org_member = True elif self.is_organization and user_id: query = meta.Session.query(Member) \ .filter(Member.state == 'active') \ .filter(Member.table_name == 'user') \ .filter(Member.group_id == self.id) \ .filter(Member.table_id == user_id) user_is_org_member = len(query.all()) != 0 query = meta.Session.query(_package.Package).\ filter( or_(_package.Package.state == core.State.ACTIVE, _package.Package.state == core.State.PENDING)). \ filter(group_table.c.id == self.id).\ filter(member_table.c.state == 'active') # orgs do not show private datasets unless the user is a member if self.is_organization and not user_is_org_member: query = query.filter(_package.Package.private == False) # groups (not orgs) never show private datasets if not self.is_organization: query = query.filter(_package.Package.private == False) query = query.join(member_table, member_table.c.table_id == _package.Package.id) query = query.join(group_table, group_table.c.id == member_table.c.group_id) if limit is not None: query = query.limit(limit) if return_query: return query else: return query.all()
def test_consistent_hashing_uniformly_distributed(): fetcher_amount = 3 fqdn_amount = 50 rest.delete_full_database(full=True) rest.create_database(fetcher_amount=fetcher_amount, fqdn_amount=fqdn_amount) fetcher_hashes = database.get_fetcher_hashes(db) hashes_sorted = sorted(fetcher_hashes, key=lambda k: k["hash"]) fetcher_hash_range = [] for i in range(len(hashes_sorted) - 1): fetcher_hash_range.append( dict( uuid=hashes_sorted[i]["uuid"], min_hash=hashes_sorted[i]["hash"], max_hash=hashes_sorted[i + 1]["hash"], )) fetcher_hash_range.append( dict( uuid=hashes_sorted[-1]["uuid"], min_hash=hashes_sorted[-1]["hash"], max_hash=hashes_sorted[0]["hash"], )) fetcher_hash_range_sorted_by_min_hash = sorted(fetcher_hash_range, key=lambda k: k["min_hash"]) for fetcher_hash_range in fetcher_hash_range_sorted_by_min_hash: if fetcher_hash_range["min_hash"] < fetcher_hash_range["max_hash"]: fetcher_hash_range["url_count"] = (db.query( func.count(db_models.Frontier.fqdn)).filter( and_( db_models.Frontier.fqdn_hash >= fetcher_hash_range["min_hash"], db_models.Frontier.fqdn_hash < fetcher_hash_range["max_hash"], ))).first()[0] else: fetcher_hash_range["url_count"] = (db.query( func.count(db_models.Frontier.fqdn)).filter( or_( db_models.Frontier.fqdn_hash >= fetcher_hash_range["min_hash"], db_models.Frontier.fqdn_hash < fetcher_hash_range["max_hash"], )).first()[0]) return_list = defaultdict(int) for d in fetcher_hash_range_sorted_by_min_hash: return_list[d["uuid"]] += d["url_count"] group_summed_hash_list = [{ "id": id_, "count": count_ } for id_, count_ in return_list.items()] url_counts = [f["count"] for f in group_summed_hash_list] assert (len(fetcher_hash_range_sorted_by_min_hash) == fetcher_amount * c.ch_hash_amount) mean = sum(url_counts) / len(url_counts) variance = sum((xi - mean)**2 for xi in url_counts) / len(url_counts) assert variance <= 5 * mean
for name, in session.query(User.name). \ filter(User.fullname == 'Ed Jones'): print(name) # +------------------------------------------------------------------+ # | conjunctions can be passed to filter() as well | # +----------------------------------------------------- (35 / 72) --+ # # >>> from sqlalchemy import or_ # >>> for name, in session.query(User.name). \ # ... filter(or_(User.fullname == 'Ed Jones', User.id < 5)): # ... print(name) from sqlalchemy import or_ for name, in session.query(User.name). \ filter(or_(User.fullname == 'Ed Jones', User.id < 5)): print(name) # +------------------------------------------------------------------+ # | multiple filter() calls join by AND just like select().where() | # +----------------------------------------------------- (36 / 72) --+ # # >>> for user in session.query(User). \ # ... filter(User.name == 'ed'). \ # ... filter(User.fullname == 'Ed Jones'): # ... print(user) # [SQL]: SELECT user.id AS user_id, for user in session.query(User). \ filter(User.name == 'ed'). \ filter(User.fullname == 'Ed Jones'):
def check2(inData): ''' :param name: :param number: :param stationId: 1: success 2: username repeat ret: { 0: 'error', 1: success, 2:'no name in inData', 3:'name is empty', 4:'no number in inData', 5:'number is empty', 6:'no ip in inData', 7:'ip is empty', 8:'no mac in inData', 9:'mac is empty', 10:'no stationId in inData', 11:'stationId is illegal', 12:'name repeat', 13:'number repeat', 14:'mac repeat', } ''' ret = 1 if (not inData.has_key('name')): ret = 2 return ret else: if (inData['name'] == ''): ret = 3 return ret if (not inData.has_key('number')): ret = 4 return ret else: if (inData['number'] == ''): ret = 5 return ret if (not inData.has_key('ip')): ret = 6 return 6 else: if (inData['ip'] == ''): ret = 7 return ret if (not inData.has_key('mac')): ret = 8 return ret else: if (inData['mac'] == ''): ret = 9 return ret if (not inData.has_key('stationId')): ret = 10 return ret else: #print('111111111111', inData['stationId']) if (inData['stationId'] == ''): ret = 11 return ret if (int(inData['stationId']) != 0): tmpAllStation = Station.select( {'id': int(inData['stationId'])}) if (len(tmpAllStation['data']) == 0): ret = 11 return ret query = session.query(Sensor) tmpSensor = query.filter( or_(Sensor.name == inData['name'], Sensor.number == inData['number'], Sensor.mac == inData['mac'])).all() for tSensor in tmpSensor: if (tSensor.name == inData['name']): ret = 12 return ret if (tSensor.number == inData['number']): ret = 13 return ret if (tSensor.mac == inData['mac']): ret = 14 return ret ret = 1 return ret
def get_platform_data(self, platform_handle, variable, uom, start_date, wq_tests_data): start_time = time.time() try: self.logger.debug("Platform: %s Obs: %s(%s) Date: %s query" % (platform_handle, variable, uom, start_date)) station = platform_handle.replace('.', '_') var_name = '%s_avg_%s' % (station, variable) end_date = start_date begin_date = start_date - timedelta(hours=24) dir_id = None sensor_id = self.xenia_obs_db.sensorExists(variable, uom, platform_handle, 1) if variable == 'wind_speed': dir_id = self.xenia_obs_db.sensorExists('wind_from_direction', 'degrees_true', platform_handle, 1) if sensor_id is not -1 and sensor_id is not None: recs = self.xenia_obs_db.session.query(sl_multi_obs) \ .filter(sl_multi_obs.m_date >= begin_date.strftime('%Y-%m-%dT%H:%M:%S')) \ .filter(sl_multi_obs.m_date < end_date.strftime('%Y-%m-%dT%H:%M:%S')) \ .filter(sl_multi_obs.sensor_id == sensor_id) \ .filter(or_(sl_multi_obs.qc_level == qaqcTestFlags.DATA_QUAL_GOOD, sl_multi_obs.qc_level == None)) \ .order_by(sl_multi_obs.m_date).all() if dir_id is not None: dir_recs = self.xenia_obs_db.session.query(sl_multi_obs) \ .filter(sl_multi_obs.m_date >= begin_date.strftime('%Y-%m-%dT%H:%M:%S')) \ .filter(sl_multi_obs.m_date < end_date.strftime('%Y-%m-%dT%H:%M:%S')) \ .filter(sl_multi_obs.sensor_id == dir_id) \ .filter(or_(sl_multi_obs.qc_level == qaqcTestFlags.DATA_QUAL_GOOD, sl_multi_obs.qc_level == None)) \ .order_by(sl_multi_obs.m_date).all() if len(recs): if variable == 'wind_speed': if sensor_id is not None and dir_id is not None: wind_dir_tuples = [] direction_tuples = [] scalar_speed_avg = None speed_count = 0 for wind_speed_row in recs: for wind_dir_row in dir_recs: if wind_speed_row.m_date == wind_dir_row.m_date: # self.logger.debug("Building tuple for Speed(%s): %f Dir(%s): %f" % ( # wind_speed_row.m_date, wind_speed_row.m_value, wind_dir_row.m_date, wind_dir_row.m_value)) if scalar_speed_avg is None: scalar_speed_avg = 0 scalar_speed_avg += wind_speed_row.m_value speed_count += 1 # Vector using both speed and direction. wind_dir_tuples.append((wind_speed_row.m_value, wind_dir_row.m_value)) # Vector with speed as constant(1), and direction. direction_tuples.append((1, wind_dir_row.m_value)) break if len(wind_dir_tuples): avg_speed_dir_components = calcAvgSpeedAndDir(wind_dir_tuples) self.logger.debug("Platform: %s Avg Wind Speed: %f(m_s-1) %f(mph) Direction: %f" % (platform_handle, avg_speed_dir_components[ 0], avg_speed_dir_components[ 0], avg_speed_dir_components[ 1])) # Unity components, just direction with speeds all 1. avg_dir_components = calcAvgSpeedAndDir(direction_tuples) scalar_speed_avg = scalar_speed_avg / speed_count wq_tests_data[var_name] = scalar_speed_avg wind_dir_var_name = '%s_avg_%s' % (station, 'wind_from_direction') wq_tests_data[wind_dir_var_name] = avg_dir_components[1] self.logger.debug( "Platform: %s Avg Scalar Wind Speed: %f(m_s-1) %f(mph) Direction: %f" % (platform_handle, scalar_speed_avg, scalar_speed_avg, avg_dir_components[1])) #Calculate vector direction. elif variable == 'sea_surface_wave_to_direction': direction_tuples = [] for dir_row in recs: # Vector with speed as constant(1), and direction. direction_tuples.append((1, dir_row.m_value)) if len(direction_tuples): # Unity components, just direction with speeds all 1. avg_dir_components = calcAvgSpeedAndDir(direction_tuples) wq_tests_data[var_name] = avg_dir_components[1] self.logger.debug( "Platform: %s Avg Scalar Direction: %f" % (platform_handle, avg_dir_components[1])) else: wq_tests_data[var_name] = sum(rec.m_value for rec in recs) / len(recs) self.logger.debug("Platform: %s Avg %s: %f Records used: %d" % ( platform_handle, variable, wq_tests_data[var_name], len(recs))) if variable == 'water_conductivity': water_con = wq_tests_data[var_name] #if uom == 'uS_cm-1': water_con = water_con / 1000.0 salinity_var = '%s_avg_%s' % (station, 'salinity') wq_tests_data[salinity_var] = 0.47413 / (math.pow((1 / water_con), 1.07) - 0.7464 * math.pow(10, -3)) self.logger.debug("Platform: %s Avg %s: %f Records used: %d" % ( platform_handle, 'salinity', wq_tests_data[salinity_var], len(recs))) else: self.logger.error( "Platform: %s sensor: %s(%s) Date: %s had no data" % (platform_handle, variable, uom, start_date)) else: self.logger.error("Platform: %s sensor: %s(%s) does not exist" % (platform_handle, variable, uom)) self.logger.debug("Platform: %s query finished in %f seconds" % (platform_handle, time.time()-start_time)) except Exception as e: self.logger.exception(e) return False return True
def Run(self): with self.protocol.lockCmd: CBaseCommand.Run(self) user_name = self.body.get(BaseCommand.PN_USERNAME) if user_name is not None: user_name = user_name.strip() password = self.body[BaseCommand.PN_PASSWORD] email = self.body.get(BaseCommand.PN_EMAIL) if email is not None: email = email.strip() mobile_phone = self.body.get(BaseCommand.PN_MOBLEPHONE) if mobile_phone is not None: mobile_phone = mobile_phone.strip() respond = self.GetResp() with SBDB.session_scope() as session: if user_name is None and password is None and email is None: respond.SetErrorCode(BaseCommand.CS_PARAMLACK) elif user_name is not None and ( session.query(SBDB_ORM.Account).filter( or_(SBDB_ORM.Account.user_name == user_name, SBDB_ORM.Account.email == user_name, SBDB_ORM.Account.mobile_phone == user_name)) .first() is not None or len(user_name) < 2): respond.SetErrorCode(BaseCommand.CS_USERNAME) elif email is not None and ( session.query(SBDB_ORM.Account).filter( or_(SBDB_ORM.Account.user_name == email, SBDB_ORM.Account.email == email, SBDB_ORM.Account.mobile_phone == email)) .first() is not None or not Util.validateEmail(email)): respond.SetErrorCode(BaseCommand.CS_EMAIL) elif mobile_phone is not None and ( session.query(SBDB_ORM.Account).filter( or_(SBDB_ORM.Account.user_name == mobile_phone, SBDB_ORM.Account.email == mobile_phone, SBDB_ORM.Account.mobile_phone == mobile_phone)) .first() is not None or not Util.validateMobilePhone(mobile_phone)): respond.SetErrorCode(BaseCommand.CS_MOBILEPHONE) else: try: account = SBDB_ORM.Account() account.language_id = 2 account.email = email account.password = Util.hash_password(password) account.user_name = user_name account.mobile_phone = mobile_phone account.version = 0 apartment = SBDB_ORM.Apartment() apartment.arm_state = BaseCommand.PV_ARM_OFF apartment.name = "Home" apartment.scene_id = None apartment.version = 0 account.apartments.append(apartment) session.add(account) session.commit() respond.body[ BaseCommand.PN_VERSION] = apartment.version respond.body[BaseCommand.PN_APARTMENTID] = apartment.id respond.body[BaseCommand.PN_NAME] = apartment.name except SQLAlchemyError as e: respond.SetErrorCode(BaseCommand.CS_DBEXCEPTION) logging.error("transport %d:%s", id(self.protocol.transport), e) session.rollback() respond.Send()
def exists(session: Session, name: str) -> bool: return session.query(Location).filter( or_(Location.full_name.like(name), Location.alias.like(name))).count() > 0
def query_data(session, agency_code, period, year, page_start, page_stop): """ Request A file data Args: session: DB session agency_code: FREC or CGAC code for generation period: The period for which to get GTAS data year: The year for which to get GTAS data page_start: Beginning of pagination page_stop: End of pagination Returns: The rows using the provided dates and page size for the given agency. """ # set a boolean to determine if the original agency code is frec or cgac frec_provided = len(agency_code) == 4 tas_gtas = tas_gtas_combo(session, period, year) # Make a list of FRECs to compare to for 011 AID entries frec_list = [] if not frec_provided: frec_list = session.query(FREC.frec_code).select_from(outerjoin(CGAC, FREC, CGAC.cgac_id == FREC.cgac_id)).\ filter(CGAC.cgac_code == agency_code).all() # Group agencies together that need to be grouped agency_array = [] if agency_code == '097': agency_array = ['017', '021', '057', '097'] elif agency_code == '1601': agency_array = ['1601', '016'] elif agency_code == '1125': agency_array = ['1125', '011'] # Save the ATA filter agency_filters = [] if not agency_array: agency_filters.append( tas_gtas.c.allocation_transfer_agency == agency_code) else: agency_filters.append( tas_gtas.c.allocation_transfer_agency.in_(agency_array)) # Save the AID filter if agency_code == '097' and not frec_provided: agency_filters.append( and_(tas_gtas.c.allocation_transfer_agency.is_(None), tas_gtas.c.agency_identifier.in_(agency_array))) elif not frec_provided: agency_filters.append( and_(tas_gtas.c.allocation_transfer_agency.is_(None), tas_gtas.c.agency_identifier == agency_code)) else: agency_filters.append( and_(tas_gtas.c.allocation_transfer_agency.is_(None), tas_gtas.c.fr_entity_type == agency_code)) # If we're checking a CGAC, we want to filter on all of the related FRECs for AID 011, otherwise just filter on # that FREC if frec_list: agency_filters.append( and_(tas_gtas.c.allocation_transfer_agency.is_(None), tas_gtas.c.agency_identifier == '011', tas_gtas.c.fr_entity_type.in_(frec_list))) elif not frec_provided: agency_filters.append( and_(tas_gtas.c.allocation_transfer_agency.is_(None), tas_gtas.c.agency_identifier == '011', tas_gtas.c.fr_entity_type == agency_code)) rows = initial_query(session, tas_gtas.c).\ filter(func.coalesce(tas_gtas.c.financial_indicator2, '') != 'F').\ filter(or_(*agency_filters)).\ group_by(tas_gtas.c.allocation_transfer_agency, tas_gtas.c.agency_identifier, tas_gtas.c.beginning_period_of_availa, tas_gtas.c.ending_period_of_availabil, tas_gtas.c.availability_type_code, tas_gtas.c.main_account_code, tas_gtas.c.sub_account_code) # Slice the final query rows = rows.slice(page_start, page_stop) return rows
def get_alarms(self, tenant_id, query_parms=None, offset=None, limit=None): if not query_parms: query_parms = {} with self._db_engine.connect() as conn: parms = {} ad = self.ad am = self.am mdd = self.mdd md = self.md a = self.a query = (self.base_subquery_list.where( ad.c.tenant_id == bindparam('b_tenant_id'))) parms['b_tenant_id'] = tenant_id if 'alarm_definition_id' in query_parms: query = query.where( ad.c.id == bindparam('b_alarm_definition_id')) parms['b_alarm_definition_id'] = query_parms[ 'alarm_definition_id'] if 'metric_name' in query_parms: query = query.where(a.c.id.in_(self.get_a_am_query)) parms['b_md_name'] = query_parms['metric_name'].encode('utf8') if six.PY2 else \ query_parms['metric_name'] if 'severity' in query_parms: severities = query_parms['severity'].split('|') query = query.where( or_(ad.c.severity == bindparam('b_severity' + str(i)) for i in range(len(severities)))) for i, s in enumerate(severities): parms['b_severity' + str(i)] = s if six.PY3 else s.encode('utf-8') if 'state' in query_parms: query = query.where(a.c.state == bindparam('b_state')) parms['b_state'] = query_parms['state'] if 'lifecycle_state' in query_parms: query = (query.where( a.c.lifecycle_state == bindparam('b_lifecycle_state'))) parms['b_lifecycle_state'] = query_parms['lifecycle_state'] \ if six.PY3 else query_parms['lifecycle_state'].encode('utf8') if 'link' in query_parms: query = query.where(a.c.link == bindparam('b_link')) parms['b_link'] = query_parms['link'] if six.PY3 \ else query_parms['link'].encode('utf8') if 'state_updated_start_time' in query_parms: query = (query.where( a.c.state_updated_at >= bindparam('b_state_updated_at'))) date_str = query_parms['state_updated_start_time'] if six.PY3 \ else query_parms['state_updated_start_time'].encode('utf8') date_param = datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S.%fZ') parms['b_state_updated_at'] = date_param if 'metric_dimensions' in query_parms: sub_query = select([a.c.id]) sub_query_from = (a.join(am, am.c.alarm_id == a.c.id).join( mdd, mdd.c.id == am.c.metric_definition_dimensions_id)) sub_query_md_base = select([md.c.dimension_set_id ]).select_from(md) for i, metric_dimension in enumerate( query_parms['metric_dimensions'].items()): md_name = "b_md_name_{}".format(i) values_cond = None values_cond_flag = False if metric_dimension and metric_dimension[1]: if '|' in metric_dimension[1]: values = metric_dimension[1].encode('utf8').split('|') if six.PY2 else \ metric_dimension[1].split('|') sub_values_cond = [] for j, value in enumerate(values): sub_md_value = "b_md_value_{}_{}".format(i, j) sub_values_cond.append( md.c.value == bindparam(sub_md_value)) parms[sub_md_value] = value values_cond = or_(*sub_values_cond) values_cond_flag = True else: md_value = "b_md_value_{}".format(i) values_cond = (md.c.value == bindparam(md_value)) values_cond_flag = True parms[md_value] = metric_dimension[1] sub_query_md = (sub_query_md_base.where( md.c.name == bindparam(md_name))) if values_cond_flag: sub_query_md = (sub_query_md.where(values_cond)) sub_query_md = (sub_query_md.distinct().alias( 'md_{}'.format(i))) sub_query_from = (sub_query_from.join( sub_query_md, sub_query_md.c.dimension_set_id == mdd.c.metric_dimension_set_id)) parms[md_name] = metric_dimension[0].encode('utf8') if six.PY2 else \ metric_dimension[0] sub_query = ( sub_query.select_from(sub_query_from).distinct()) query = query.where(a.c.id.in_(sub_query)) order_columns = [] if 'sort_by' in query_parms: columns_mapper = \ {'alarm_id': a.c.id, 'alarm_definition_id': ad.c.id, 'alarm_definition_name': ad.c.name, 'state_updated_timestamp': a.c.state_updated_at, 'updated_timestamp': a.c.updated_at, 'created_timestamp': a.c.created_at, 'severity': models.field_sort(ad.c.severity, list(map(text, ["'LOW'", "'MEDIUM'", "'HIGH'", "'CRITICAL'"]))), 'state': models.field_sort(a.c.state, list(map(text, ["'OK'", "'UNDETERMINED'", "'ALARM'"])))} order_columns, received_cols = self._remap_columns( query_parms['sort_by'], columns_mapper) if not received_cols.get('alarm_id', False): order_columns.append(a.c.id) else: order_columns = [a.c.id] if limit: query = query.limit(bindparam('b_limit')) parms['b_limit'] = limit + 1 if offset: query = query.offset(bindparam('b_offset')) parms['b_offset'] = offset query = (query.order_by(*order_columns).alias('alarm_id_list')) main_query = (self.base_query.select_from( self.base_query_from.join(query, query.c.id == a.c.id)).distinct()) main_query = main_query.order_by(*order_columns) return [ dict(row) for row in conn.execute(main_query, parms).fetchall() ]
def get(session: Session, name: str) -> 'Location': return session.query(Location).filter( or_(Location.full_name.like(name), Location.alias.like(name))).first()
def raw_data_for_geos(self, geos, db_table=None): """ Pull raw data for a list of geo models. Returns a dict mapping the geo ids to table data. """ # initial values data = {('%s-%s' % (geo.geo_level, geo.geo_code)): { 'estimate': {}, 'error': {} } for geo in geos} db_table = db_table or self.get_db_table() session = get_session() try: geo_values = None fields = [getattr(db_table.model, f) for f in self.fields] rows = session\ .query(db_table.model.geo_level, db_table.model.geo_code, func.sum(db_table.model.total).label('total'), *fields)\ .group_by(db_table.model.geo_level, db_table.model.geo_code, *fields)\ .order_by(db_table.model.geo_level, db_table.model.geo_code, *fields)\ .filter(or_(and_( db_table.model.geo_level == geo.geo_level, db_table.model.geo_code == geo.geo_code, db_table.model.geo_version == geo.version) for geo in geos))\ .all() def permute(level, field_keys, rows): field = self.fields[level] total = None denominator = 0 for key, rows in groupby(rows, lambda r: getattr(r, field)): new_keys = field_keys + [key] col_id = self.column_id(new_keys) if level + 1 < len(self.fields): value = permute(level + 1, new_keys, rows) else: # we've bottomed out rows = list(rows) if all(row.total is None for row in rows): value = None else: value = sum(row.total or 0 for row in rows) if self.denominator_key and self.denominator_key == key: # this row must be used as the denominator total, # rather than as an entry in the table denominator = value continue if value is not None: total = (total or 0) + value geo_values['estimate'][col_id] = value geo_values['error'][col_id] = 0 if self.denominator_key: total = denominator return total # rows for each geo for geo_id, geo_rows in groupby( rows, lambda r: (r.geo_level, r.geo_code)): geo_values = data['%s-%s' % geo_id] total = permute(0, [], geo_rows) # total if self.total_column: geo_values['estimate'][self.total_column] = total geo_values['error'][self.total_column] = 0 finally: session.close() return data
def get_alarms_count(self, tenant_id, query_parms=None, offset=None, limit=None): if not query_parms: query_parms = {} with self._db_engine.connect() as conn: parms = {} ad = self.ad am = self.am mdd = self.mdd mde = self.mde md = self.md a = self.a query_from = a.join(ad, ad.c.id == a.c.alarm_definition_id) parms['b_tenant_id'] = tenant_id group_by_columns = [] if 'group_by' in query_parms: group_by_columns = query_parms['group_by'] sub_group_by_columns = [] metric_group_by = { 'metric_name', 'dimension_name', 'dimension_value' }.intersection(set(query_parms['group_by'])) if metric_group_by: sub_query_columns = [am.c.alarm_id] if 'metric_name' in metric_group_by: sub_group_by_columns.append( mde.c.name.label('metric_name')) if 'dimension_name' in metric_group_by: sub_group_by_columns.append( md.c.name.label('dimension_name')) if 'dimension_value' in metric_group_by: sub_group_by_columns.append( md.c.value.label('dimension_value')) sub_query_columns.extend(sub_group_by_columns) sub_query_from = (mde.join( mdd, mde.c.id == mdd.c.metric_definition_id).join( md, mdd.c.metric_dimension_set_id == md.c.dimension_set_id).join( am, am.c.metric_definition_dimensions_id == mdd.c.id)) sub_query = (select(sub_query_columns).select_from( sub_query_from).distinct().alias('metrics')) query_from = query_from.join( sub_query, sub_query.c.alarm_id == a.c.id) query_columns = [func.count().label('count')] query_columns.extend([column(col) for col in group_by_columns]) query = (select(query_columns).select_from(query_from).where( ad.c.tenant_id == bindparam('b_tenant_id'))) parms['b_tenant_id'] = tenant_id if 'alarm_definition_id' in query_parms: parms['b_alarm_definition_id'] = query_parms[ 'alarm_definition_id'] query = query.where( ad.c.id == bindparam('b_alarm_definition_id')) if 'state' in query_parms: parms['b_state'] = query_parms['state'] if six.PY3 else \ query_parms['state'].encode('utf8') query = query.where(a.c.state == bindparam('b_state')) if 'severity' in query_parms: severities = query_parms['severity'].split('|') query = query.where( or_(ad.c.severity == bindparam('b_severity' + str(i)) for i in range(len(severities)))) for i, s in enumerate(severities): parms['b_severity' + str(i)] = s if six.PY3 else s.encode('utf8') if 'lifecycle_state' in query_parms: parms['b_lifecycle_state'] = query_parms['lifecycle_state'] if six.PY3 else \ query_parms['lifecycle_state'].encode('utf8') query = query.where( a.c.lifecycle_state == bindparam('b_lifecycle_state')) if 'link' in query_parms: parms['b_link'] = query_parms['link'] if six.PY3 else \ query_parms['link'].encode('utf8') query = query.where(a.c.link == bindparam('b_link')) if 'state_updated_start_time' in query_parms: date_str = query_parms['state_updated_start_time'] if six.PY3 \ else query_parms['state_updated_start_time'].encode('utf8') date_param = datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S.%fZ') parms['b_state_updated_at'] = date_param query = query.where( a.c.state_updated_at >= bindparam('b_state_updated_at')) if 'metric_name' in query_parms: query = query.where(a.c.id.in_(self.get_a_am_query)) parms['b_md_name'] = query_parms['metric_name'] if six.PY3 else \ query_parms['metric_name'].encode('utf8') if 'metric_dimensions' in query_parms: sub_query = select([a.c.id]) sub_query_from = (a.join(am, am.c.alarm_id == a.c.id).join( mdd, mdd.c.id == am.c.metric_definition_dimensions_id)) sub_query_md_base = select([md.c.dimension_set_id ]).select_from(md) for i, metric_dimension in enumerate( query_parms['metric_dimensions'].items()): dimension_value = metric_dimension[1] if six.PY3 else \ metric_dimension[1].encode('utf8') if '|' in dimension_value: dimension_value = tuple(dimension_value.split('|')) md_name = "b_md_name_{}".format(i) md_value = "b_md_value_{}".format(i) sub_query_md = (sub_query_md_base.where( md.c.name == bindparam(md_name))) if isinstance(dimension_value, tuple): sub_query_md = (sub_query_md.where( md.c.value.op('IN')(bindparam(md_value)))) else: sub_query_md = (sub_query_md.where( md.c.value == bindparam(md_value))) sub_query_md = (sub_query_md.distinct().alias( 'md_{}'.format(i))) sub_query_from = (sub_query_from.join( sub_query_md, sub_query_md.c.dimension_set_id == mdd.c.metric_dimension_set_id)) parms[md_name] = metric_dimension[0] if six.PY3 else \ metric_dimension[0].encode('utf8') parms[md_value] = dimension_value sub_query = ( sub_query.select_from(sub_query_from).distinct()) query = query.where(a.c.id.in_(sub_query)) if group_by_columns: query = (query.order_by(*group_by_columns).group_by( *group_by_columns)) if limit: query = query.limit(bindparam('b_limit')) parms['b_limit'] = limit + 1 if offset: query = query.offset(bindparam('b_offset')) parms['b_offset'] = offset query = query.distinct() return [dict(row) for row in conn.execute(query, parms).fetchall()]
def or_operation(exp, object_class, target_class, query): """Operator generate sqlalchemy for or operation""" return sqlalchemy.or_( build_expression(exp["left"], object_class, target_class, query), build_expression(exp["right"], object_class, target_class, query))
def search(cls, *, limit=100, page=1, accounts=None, locations=None, properties=None, include_disabled=False, return_query=False): """Search for resources based on the provided filters. If `return_query` a sub-class of `sqlalchemy.orm.Query` is returned instead of the resource list. Args: limit (`int`): Number of results to return. Default: 100 page (`int`): Pagination offset for results. Default: 1 accounts (`list` of `int`): A list of account id's to limit the returned resources to locations (`list` of `str`): A list of locations as strings to limit the search for properties (`dict`): A `dict` containing property name and value pairs. Values can be either a str or a list of strings, in which case a boolean OR search is performed on the values include_disabled (`bool`): Include resources from disabled accounts. Default: False return_query (`bool`): Returns the query object prior to adding the limit and offset functions. Allows for sub-classes to amend the search feature with extra conditions. The calling function must handle pagination on its own Returns: `list` of `Resource`, `sqlalchemy.orm.Query` """ qry = db.Resource.order_by(Resource.resource_id).filter( Resource.resource_type_id == ResourceType.get(cls.resource_type).resource_type_id ) if not include_disabled: qry = qry.join(Account, Resource.account_id == Account.account_id).filter(Account.enabled == 1) if session: qry = qry.filter(Resource.account_id.in_(session['accounts'])) if accounts: qry = qry.filter(Resource.account_id.in_([Account.get(acct).account_id for acct in accounts])) if locations: qry = qry.filter(Resource.location.in_(locations)) if properties: for prop_name, value in properties.items(): alias = aliased(ResourceProperty) qry = qry.join(alias, Resource.resource_id == alias.resource_id) if type(value) == list: where_clause = [] for item in value: where_clause.append(alias.value == item) qry = qry.filter( and_( alias.name == prop_name, or_(*where_clause) ).self_group() ) else: qry = qry.filter( and_( alias.name == prop_name, alias.value == value ).self_group() ) if return_query: return qry total = qry.count() qry = qry.limit(limit) qry = qry.offset((page - 1) * limit if page > 1 else 0) return total, [cls(x) for x in qry.all()]
def get_tie_match(teamid): amountie = models.Result.query.join(models.Match).filter( or_(models.Match.awayteam_id == teamid, models.Match.hometeam_id == teamid), models.Result.typeresult == models.ETypeResult.Tie).all() return amountie
def all_appointments(status="", typ="", emergency="", location="", sequence=""): employee_in_db = g.employee request_type = typ request_status = status request_sequence = sequence if employee_in_db: if request_type == "all": if request_status == "all": if emergency == "all": if location == "all": appointments = Appointment.query.filter().all() else: appointments = Appointment.query.filter( Appointment.location == location).all() else: if location == "all": appointments = Appointment.query.filter( Appointment.emergency == emergency).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.emergency == emergency)).all() elif request_status == "Processing&Operating&Discharged": if emergency == "all": if location == "all": appointments = Appointment.query.filter( or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged")).all() else: appointments = Appointment.query.filter( and_( Appointment.location == location, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() else: if location == "all": appointments = Appointment.query.filter( and_( Appointment.emergency == emergency, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() else: appointments = Appointment.query.filter( and_( Appointment.location == location, Appointment.emergency == emergency, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() elif request_status == "Waiting": if emergency == "all": if location == "all": appointments = Appointment.query.filter( Appointment.status == "").all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.status == "")).all() else: if location == "all": appointments = Appointment.query.filter( and_(Appointment.emergency == emergency, Appointment.status == "")).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.emergency == emergency, Appointment.status == "")).all() else: if emergency == "all": if location == "all": appointments = Appointment.query.filter( Appointment.status == request_status).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.status == request_status)).all() else: if location == "all": appointments = Appointment.query.filter( and_(Appointment.emergency == emergency, Appointment.status == request_status)).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.emergency == emergency, Appointment.status == request_status)).all() elif request_type == "": appointments = Appointment.query.filter().all() else: if request_status == "all": if emergency == "all": if location == "all": appointments = Appointment.query.filter( Appointment.pet_type == request_type).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.pet_type == request_type)).all() else: if location == "all": appointments = Appointment.query.filter( and_(Appointment.emergency == emergency, Appointment.pet_type == request_type)).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.emergency == emergency, Appointment.pet_type == request_type)).all() elif request_status == "Processing&Operating&Discharged": if emergency == "all": if location == "all": appointments = Appointment.query.filter( and_( Appointment.pet_type == request_type, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() else: appointments = Appointment.query.filter( and_( Appointment.pet_type == request_type, Appointment.location == location, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() else: if location == "all": appointments = Appointment.query.filter( and_( Appointment.pet_type == request_type, Appointment.emergency == emergency, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() else: appointments = Appointment.query.filter( and_( Appointment.pet_type == request_type, Appointment.location == location, Appointment.emergency == emergency, or_(Appointment.status == "Processing", Appointment.status == "Operating", Appointment.status == "Discharged"))).all() elif request_status == "Waiting": if emergency == "all": if location == "all": appointments = Appointment.query.filter( and_(Appointment.pet_type == request_type, Appointment.status == "")).all() else: appointments = Appointment.query.filter( and_(Appointment.pet_type == request_type, Appointment.location == location, Appointment.status == "")).all() else: if location == "all": appointments = Appointment.query.filter( and_(Appointment.pet_type == request_type, Appointment.emergency == emergency, Appointment.status == "")).all() else: appointments = Appointment.query.filter( and_(Appointment.pet_type == request_type, Appointment.location == location, Appointment.emergency == emergency, Appointment.status == "")).all() else: if emergency == "all": if location == "all": appointments = Appointment.query.filter( and_(Appointment.pet_type == request_type, Appointment.status == request_status)).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.pet_type == request_type, Appointment.status == request_status)).all() else: if location == "all": appointments = Appointment.query.filter( and_(Appointment.emergency == emergency, Appointment.pet_type == request_type, Appointment.status == request_status)).all() else: appointments = Appointment.query.filter( and_(Appointment.location == location, Appointment.emergency == emergency, Appointment.pet_type == request_type, Appointment.status == request_status)).all() appointment_list = [] for item in appointments: list_item = {} list_item["id"] = item.id list_item["type"] = item.pet_type list_item["symptom"] = item.symptom list_item["date"] = str(item.date) list_item["location"] = item.location list_item["message"] = item.message list_item["priority"] = item.priority if item.emergency == "false": list_item["emergency"] = False elif item.emergency == "true": list_item["emergency"] = True if item.status == "": list_item["status"] = "Waiting" else: list_item["status"] = item.status if item.attendingDoctor == "": list_item["attendingDoctor"] = "Undetermined" else: list_item["attendingDoctor"] = item.attendingDoctor list_item["employeeId"] = item.employee_id if str(item.operationTime) == "1970-01-01": list_item["operationTime"] = "Undetermined" else: list_item["operationTime"] = str(item.operationTime) if str(item.dischargeDate) == "1970-01-01": list_item["dischargeDate"] = "Undetermined" else: list_item["dischargeDate"] = str(item.dischargeDate) appointment_list.append(list_item) if request_sequence == "date": appointment_list.sort(key=lambda item: item["date"]) elif request_sequence == "priority": appointment_list.sort(key=lambda item: item["priority"]) return jsonify({ "code": 200, "msg": "Success", "data": { "appointments": appointment_list } }) else: return jsonify({"code": 401, "msg": "Unauthorized"})
def get_user(username): """Convenience method to look up the id for a username.""" if not username: return None return m.session.query(User).filter( or_(User.email == username, User.username == username)).first()
def countmatch(teamid): count = models.Match.query.filter( or_(models.Match.awayteam_id == teamid, models.Match.hometeam_id == teamid)).count() return count
def content(site_id, g_supply_id, user, compression, finish_year, finish_month, months, now=None): if now is None: now = ct_datetime_now() report_context = {} sess = None month_list = list( c_months_u(finish_year=finish_year, finish_month=finish_month, months=months)) start_date, finish_date = month_list[0][0], month_list[-1][-1] try: sess = Session() base_name = [ "g_monthly_duration", hh_format(start_date).replace(" ", "_").replace(":", "").replace("-", ""), "for", str(months), "months", ] forecast_from = chellow.computer.forecast_date() sites = (sess.query(Site).join(SiteGEra).join(GEra).filter( SiteGEra.is_physical == true()).distinct().order_by(Site.code)) if site_id is not None: site = Site.get_by_id(sess, site_id) sites = sites.filter(Site.id == site.id) base_name.append("site") base_name.append(site.code) if g_supply_id is not None: g_supply = GSupply.get_by_id(sess, g_supply_id) base_name.append("g_supply") base_name.append(str(g_supply.id)) sites = sites.filter(GEra.g_supply == g_supply) running_name, finished_name = chellow.dloads.make_names( "_".join(base_name) + ".ods", user) rf = open(running_name, "wb") site_rows = [] g_era_rows = [] era_header_titles = [ "creation_date", "mprn", "supply_name", "exit_zone", "msn", "unit", "contract", "site_id", "site_name", "associated_site_ids", "month", ] site_header_titles = [ "creation_date", "site_id", "site_name", "associated_site_ids", "month", ] summary_titles = ["kwh", "gbp", "billed_kwh", "billed_gbp"] vb_titles = [] conts = (sess.query(GContract).join(GEra).join(GSupply).filter( GEra.start_date <= finish_date, or_(GEra.finish_date == null(), GEra.finish_date >= start_date), ).distinct().order_by(GContract.id)) if g_supply_id is not None: conts = conts.filter(GEra.g_supply_id == g_supply_id) for cont in conts: title_func = chellow.computer.contract_func( report_context, cont, "virtual_bill_titles") if title_func is None: raise Exception("For the contract " + cont.name + " there doesn't seem " + "to be a 'virtual_bill_titles' function.") for title in title_func(): if title not in vb_titles: vb_titles.append(title) g_era_rows.append(era_header_titles + summary_titles + vb_titles) site_rows.append(site_header_titles + summary_titles) for month_start, month_finish in month_list: for site in sites.filter( GEra.start_date <= month_finish, or_(GEra.finish_date == null(), GEra.finish_date >= month_start), ): site_kwh = site_gbp = site_billed_kwh = site_billed_gbp = 0 for g_era in (sess.query(GEra).join(SiteGEra).filter( SiteGEra.site == site, SiteGEra.is_physical == true(), GEra.start_date <= month_finish, or_(GEra.finish_date == null(), GEra.finish_date >= month_start), ).options( joinedload(GEra.g_contract), joinedload(GEra.g_supply), joinedload(GEra.g_supply).joinedload( GSupply.g_exit_zone), ).order_by(GEra.id)): g_supply = g_era.g_supply if g_supply_id is not None and g_supply.id != g_supply_id: continue ss_start = hh_max(g_era.start_date, month_start) ss_finish = hh_min(g_era.finish_date, month_finish) ss = GDataSource( sess, ss_start, ss_finish, forecast_from, g_era, report_context, None, ) contract = g_era.g_contract vb_function = contract_func(report_context, contract, "virtual_bill") if vb_function is None: raise BadRequest( "The contract " + contract.name + " doesn't have the virtual_bill() function.") vb_function(ss) bill = ss.bill try: gbp = bill["net_gbp"] except KeyError: gbp = 0 bill["problem"] += ( "For the supply " + ss.mprn + " the virtual bill " + str(bill) + " from the contract " + contract.name + " does not contain the net_gbp key.") try: kwh = bill["kwh"] except KeyError: kwh = 0 bill["problem"] += ("For the supply " + ss.mprn + " the virtual bill " + str(bill) + " from the contract " + contract.name + " does not contain the 'kwh' key.") billed_kwh = billed_gbp = 0 g_era_associates = { s.site.code for s in g_era.site_g_eras if not s.is_physical } for g_bill in sess.query(GBill).filter( GBill.g_supply == g_supply, GBill.start_date <= ss_finish, GBill.finish_date >= ss_start, ): bill_start = g_bill.start_date bill_finish = g_bill.finish_date bill_duration = (bill_finish - bill_start ).total_seconds() + (30 * 60) overlap_duration = (min(bill_finish, ss_finish) - max( bill_start, ss_start)).total_seconds() + (30 * 60) overlap_proportion = overlap_duration / bill_duration billed_kwh += overlap_proportion * float(g_bill.kwh) billed_gbp += overlap_proportion * float(g_bill.net) associated_site_ids = ",".join(sorted(g_era_associates)) g_era_rows.append([ make_val(v) for v in [ now, g_supply.mprn, g_supply.name, g_supply.g_exit_zone.code, g_era.msn, g_era.g_unit.code, contract.name, site.code, site.name, associated_site_ids, month_finish, kwh, gbp, billed_kwh, billed_gbp, ] ] + [make_val(bill.get(t)) for t in vb_titles]) site_kwh += kwh site_gbp += gbp site_billed_kwh += billed_kwh site_billed_gbp += billed_gbp linked_sites = ", ".join(s.code for s in site.find_linked_sites( sess, month_start, month_finish)) site_rows.append([ make_val(v) for v in [ now, site.code, site.name, linked_sites, month_finish, site_kwh, site_gbp, site_billed_kwh, site_billed_gbp, ] ]) sess.rollback() write_spreadsheet(rf, compression, site_rows, g_era_rows) except BadRequest as e: site_rows.append(["Problem " + e.description]) write_spreadsheet(rf, compression, site_rows, g_era_rows) except BaseException: msg = traceback.format_exc() sys.stderr.write(msg + "\n") site_rows.append(["Problem " + msg]) write_spreadsheet(rf, compression, site_rows, g_era_rows) finally: if sess is not None: sess.close() try: rf.close() os.rename(running_name, finished_name) except BaseException: msg = traceback.format_exc() r_name, f_name = chellow.dloads.make_names("error.txt", user) ef = open(r_name, "w") ef.write(msg + "\n") ef.close()
def fetch_data(self, **kwargs): self._parse_parameters(kwargs['parameters_dic']) self._get_available_chart_type() portfolios = self._parameters.get(TagTypeChart.PORTFOLIO) esg_factors = self._parameters.get(TagTypeChart.ESG_METRICS) esg_details = self._parameters.get(TagTypeChart.DETAILS) date_parameters = self._parameters.get(TagTypeChart.DATE)[0]['name'] metrics = self._parameters.get('metrics') metric_id = metrics[0]['id'] self.xAxis['title'] = 'Buckets' if metric_id == -3 or (metrics[0]['tag_type_id'] == 8 and metric_id == 1): metric_id = 14 self.yAxis['title'] = 'Sum of the Securities Weight' elif metric_id == 2 and metrics[0]['tag_type_id'] == 3: metric_id = 12 self.yAxis['title'] = 'Total Value of Securities' elif metric_id == 1 and metrics[0]['tag_type_id'] == 3: metric_id = 13 self.yAxis['title'] = 'Number of Securities' elif metric_id == 3 and metrics[0]['tag_type_id'] == 3: metric_id = 10 self.yAxis['title'] = 'Cumulative Return' elif metric_id == 4 and metrics[0]['tag_type_id'] == 3: metric_id = 11 self.yAxis['title'] = 'Return Daily' else: print('No such metric') if esg_details and len(esg_details) == 1: if esg_details[0]['id'] == 20: esg_factor_results = EsgFactorModel.query.filter( or_(EsgFactorModel.level == 1, EsgFactorModel.level == 2)).filter_by( data_provider_id='AE') else: esg_factor_results = EsgFactorModel.query.filter( or_(EsgFactorModel.level == 1, EsgFactorModel.level == 2)).filter_by( data_provider_id='AU') esg_factors = [{ "id": factor.id, "name": factor.name } for factor in esg_factor_results] grouping_ids, _ = zip( *self._get_esg_grouping_ids(esg_factors[0]['id'])) date = get_last_available_day_until_date_in_grouping_metric( date_parameters, portfolios[0], grouping_ids).strftime("%Y-%m-%d") if date: self.subtitle = 'Source date: ' + str(date) if len(metrics) > 1: self.message = 'Two or more metrics was selected, Only the first one will be displayed' for factor in esg_factors: grouping_ids, _ = zip( *self._get_esg_grouping_ids(factor['id'])) portfolio_grouping_metric_result = PortfolioGroupingMetricModel.query. \ filter_by(metric_id=metric_id). \ filter(PortfolioGroupingMetricModel.grouping_id.in_(grouping_ids)). \ join(PortfolioGroupingMetricModel.portfolio_metric_date). \ filter_by(portfolio_id=portfolios[0]['id']). \ filter_by(date_key=date). \ order_by(PortfolioGroupingMetricModel.grouping_id.asc()).all() esg_data = {} esg_data['name'] = factor['name'] esg_data['data'] = [ portfolio_grouping_metric.value for portfolio_grouping_metric in portfolio_grouping_metric_result ] if self._is_empty(esg_data['data']): raise NoDataException self.data.append(esg_data) self.title = 'Multiple Channel ESG Score Distribution Histogram of ' + portfolios[ 0]['name'] else: raise 'No data available on this day.'