Python expression.cast函数代码示例

    def birth_time(cls):
        hour = cast(func.extract("hour", cls.birth_datetime), String)
        minute = cast(func.extract("minute", cls.birth_datetime), String)

        hour = case([(func.length(hour) == 1, "0" + hour)], else_=hour)
        minute = case([(func.length(minute) == 1, "0" + minute)], else_=minute)
        return hour + ":" + minute

    def _calc():
        stats = {}
        if day_from is not None and day_to is not None:
            # fill in gaps
            for day in util.daterange(day_from,day_to):
                stats[day] = dict(total=0,done=0,help=0)

        # TODO: do the work in the database.
        q = session.query(cast(Article.pubdate,Date), Article)

        if day_from is not None:
            q = q.filter(cast(Article.pubdate, Date) >= day_from)
        if day_to is not None:
            q = q.filter(cast(Article.pubdate, Date) <= day_to)

        for day,art in q:
            if day not in stats:
                stats[day] = dict(total=0,done=0,help=0)
            stats[day]['total'] += 1
            if not art.needs_sourcing:
                stats[day]['done'] += 1

        stats = sorted([(day,row) for day,row in stats.iteritems()], key=lambda x: x[0], reverse=True )

        return [DailyStats(x[0], x[1]['total'], x[1]['done']) for x in stats]

    def get(self, country_id):
        """
        Gather specified country from the database with its data
        country_id a non-zero, positive int
        return a json object representing the country
        """
        session = db.loadSession()

        assert type(country_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # outerjoin defaults to a LEFT outer join, NOT full outer join
            db.Country.id,
            db.Country.name,
            func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name]))
            )\
            .select_from(db.Country)\
            .outerjoin(db.City)\
            .outerjoin(db.Olympics)\
            .filter(
                # What to filter by (where clause)
                db.Country.id==country_id)\
            .group_by(db.Country.id,
            db.Country.name)\
            .first() # Actually executes the query and returns a tuple
        
        session.close()
        
        keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season', 'city')))

        country_dict = add_keys(keys, result)

        return jsonify(country_dict)

    def selectables(cls, bag, agg_spec):
        """ Create a list of statements from spec

        :type bag: mongosql.bag.ModelPropertyBags
        :rtype: list[sqlalchemy.sql.elements.ColumnElement]
        """
        # TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/
        selectables = []
        for comp_field, comp_expression in agg_spec.items():
            # Column reference
            if isinstance(comp_expression, basestring):
                selectables.append(bag.columns[comp_expression].label(comp_field))
                continue

            # Computed expression
            assert isinstance(comp_expression, dict), 'Aggregate: Expression should be either a column name, or an object'
            assert len(comp_expression) == 1, 'Aggregate: expression can only contain a single operator'
            operator, expression = comp_expression.popitem()

            # Expression statement
            if isinstance(expression, int) and operator == '$sum':
                # Special case for count
                expression_stmt = expression
            elif isinstance(expression, basestring):
                # Column name
                expression_stmt = bag.columns[expression]
                # Json column?
                if bag.columns.is_column_json(expression):
                    # PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
                    expression_stmt = cast(expression_stmt, Float)
            elif isinstance(expression, dict):
                # Boolean expression
                expression_stmt = MongoCriteria.statement(bag, expression)
                # Need to cast it to int
                expression_stmt = cast(expression_stmt, Integer)
            else:
                raise AssertionError('Aggregate: expression should be either a column name, or an object')

            # Operator
            if operator == '$max':
                comp_stmt = func.max(expression_stmt)
            elif operator == '$min':
                comp_stmt = func.min(expression_stmt)
            elif operator == '$avg':
                comp_stmt = func.avg(expression_stmt)
            elif operator == '$sum':
                if isinstance(expression_stmt, int):
                    # Special case for count
                    comp_stmt = func.count()
                    if expression_stmt != 1:
                        comp_stmt *= expression_stmt
                else:
                    comp_stmt = func.sum(expression_stmt)
            else:
                raise AssertionError('Aggregate: unsupported operator "{}"'.format(operator))

            # Append
            selectables.append(comp_stmt.label(comp_field))

        return selectables

    def filtering(self):
        search_value = self.request_values.get('sSearch')
        condition = None


        def search(idx, col):
            tmp_column_name = col.column_name.split('.')
            for tmp_name in tmp_column_name:
                if tmp_column_name.index(tmp_name) == 0:
                    obj = getattr(self.sqla_object, tmp_name)
                    parent = self.sqla_object
                elif isinstance(obj.property, RelationshipProperty):
                    parent = obj.property.mapper.class_
                    obj = getattr(parent, tmp_name)
                if not hasattr(obj, 'property'):
                    sqla_obj = parent
                    column_name = tmp_name
                elif isinstance(obj.property, RelationshipProperty):
                    sqla_obj = obj.mapper.class_
                    column_name = tmp_name
                    if not column_name:
                        column_name = obj.property.table.primary_key.columns \
                            .values()[0].name
                else:
                    sqla_obj = parent
                    column_name = tmp_name
            return sqla_obj, column_name
        if search_value:
            search_value_list = str(search_value).split()
            for search_val in search_value_list:
                conditions = []
                for idx, col in enumerate(self.columns):
                    if self.request_values.get('bSearchable_%s' % idx) in (
                            True, 'true') and col.searchable:
                        sqla_obj, column_name = search(idx, col)
                        conditions.append(
                            cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_val))
                condition = or_(*conditions)
                if condition is not None:
                    self.query = self.query.filter(condition)
        conditions = []
        for idx, col in enumerate(self.columns):
            search_value2 = self.request_values.get('sSearch_%s' % idx)
            if search_value2:
                sqla_obj, column_name = search(idx, col)
                if col.search_like:
                    conditions.append(
                        cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_value2))
                else:
                    conditions.append(
                        cast(get_attr(sqla_obj, column_name), String).__eq__(search_value2))
                if condition is not None:
                    condition = and_(condition, and_(*conditions))
                else:
                    condition = and_(*conditions)
        if condition is not None:
            self.query = self.query.filter(condition)
            self.cardinality_filtered = self.query.count()
        else:
            self.cardinality_filtered = self.cardinality

    def get_clustered_locations(location_column,
                                threshold_radius=1000, filter=None):
        """
        SELECT ST_Centroid(
            (ST_Dump(
                ST_Union(
                    ST_Buffer(
                        takeoff_location_wkt::geography, 1000
                    )::geometry
                )
            )
        ).geom) FROM flights WHERE pilot_id=31;
        """

        # Cast the takeoff_location_wkt column to Geography
        geography = cast(location_column, Geography)

        # Add a metric buffer zone around the locations
        buffer = cast(geography.ST_Buffer(threshold_radius), Geometry)

        # Join the locations into one MultiPolygon
        union = buffer.ST_Union()

        # Split the MultiPolygon into separate polygons
        dump = union.ST_Dump().geom

        # Calculate center points of each polygon
        locations = func.ST_Centroid(dump)

        query = db.session.query(locations.label('location'))

        if filter is not None:
            query = query.filter(filter)

        return [Location.from_wkb(row.location) for row in query]

    def lookup(self):
        q = request.params["q"]
        # Assume that the SQL library handles the SQL attack vectors

        person_query = meta.Session.query(
            Person.id, sa.func.concat(Person.fullname, " - ", Person.email_address).label("pretty")
        ).filter(
            sa.or_(Person.lastname.ilike(q + "%"), Person.fullname.ilike(q + "%"), Person.email_address.ilike(q + "%"))
        )

        personid_query = meta.Session.query(Person.id, cast(Person.id, sa.String).label("pretty")).filter(
            cast(Person.id, sa.String).like(q + "%")
        )

        boarding_query = meta.Session.query(FulfilmentGroup.person_id, FulfilmentGroup.code.label("pretty")).filter(
            FulfilmentGroup.code.ilike(q + "%")
        )

        badge_query = meta.Session.query(Fulfilment.person_id, Fulfilment.code.label("pretty")).filter(
            Fulfilment.code.ilike(q + "%")
        )

        union_query = person_query.union(personid_query, boarding_query, badge_query).order_by("pretty").limit(5)

        return dict(r=list(union_query.all()))

 def get_info(cls, location):
     '''Returns a query object of mountain waves around the location'''
     return DBSession.query(cls) \
         .filter(func.ST_DWithin(
             cast(WKTElement(location.to_wkt(), srid=4326), Geography),
             cast(cls.location, Geography),
             5000))

 def by_location(cls, location):
     '''Returns a query object of mountain waves around the location'''
     return cls.query() \
         .filter(db.func.ST_DWithin(
             cast(location.make_point(), Geography),
             cast(cls.location, Geography),
             5000))

def demographic_etl(config):
    # set up
    connection = get_connection(config)
    pedsnet_session = init_pedsnet(connection)
    init_pcornet(connection)

    # multiple aliases for pedsnet_pcornet_valueset_map
    # to allow the three named joins
    gender_value_map = aliased(ValueSetMap)
    ethnicity_value_map = aliased(ValueSetMap)
    race_value_map = aliased(ValueSetMap)

    # extract the data from the person table
    person = pedsnet_session.query(Person.person_id,
                                   Person.birth_date,
                                   Person.birth_time,
                                   coalesce(gender_value_map.target_concept, 'OT'),
                                   coalesce(ethnicity_value_map.target_concept, 'OT'),
                                   coalesce(race_value_map.target_concept, 'OT'),
                                   bindparam("biobank_flag", "N"),
                                   Person.gender_source_value,
                                   Person.ethnicity_source_value,
                                   Person.race_source_value,
                                   Person.site,
                                   bindparam("gender_identity", None),
                                   bindparam("raw_gender_identity", None),
                                   bindparam("sexual_orientation", None),
                                   bindparam("raw_sexual_orientation", None)
                                   ). \
        outerjoin(gender_value_map,
                  and_(gender_value_map.source_concept_class == 'Gender',
                       case([(and_(Person.gender_concept_id == None,
                                   gender_value_map.source_concept_id == None), True)],
                            else_=cast(Person.gender_concept_id, String(200)) ==
                                  gender_value_map.source_concept_id))). \
        outerjoin(ethnicity_value_map,
                  and_(ethnicity_value_map.source_concept_class == 'Hispanic',
                       case([(and_(Person.ethnicity_concept_id == None,
                                   ethnicity_value_map.source_concept_id == None), True)],
                            else_=cast(Person.ethnicity_concept_id, String(200)) ==
                                  ethnicity_value_map.source_concept_id))). \
        outerjoin(race_value_map,
                  and_(race_value_map.source_concept_class == 'Race',
                       case([(and_(Person.race_concept_id == None,
                                   race_value_map.source_concept_id == None), True)],
                            else_=cast(Person.race_concept_id, String(200)) ==
                                  race_value_map.source_concept_id))).all()

    # transform data to pcornet names and types
    # load to demographic table
    odo(person, Demographic.__table__,
        dshape='var * {patid: string, birth_date: date, birth_time: string, sex: string,'
               'hispanic: string, race: string, biobank_flag: string, raw_sex: string,'
               'raw_hispanic: string, raw_race:string, site: string, gender_identity: string,'
               'raw_gender_identity: string, sexual_orientation: string, raw_sexual_orientation: string}'
        )
    # close session

    pedsnet_session.close()

    def get_proxy_address(
        self,
        user_id,
        ip_address=None,
        best=4,
        conn_factor=0.2
    ):
        """Get a usable proxy address for audio resource of user by user_id.
        If there is no available server, None will be returned

        We sort the connection by

            user_rate - (have_conn*conn_factor) then
            res_rate - (have_conn*conn_factor)

        Which means, less user proxy will be selected first, also, if there
        is already a proxy connection there, they will have higher priority
        (introduced by the conn_factor).

        """
        from sqlalchemy.sql.expression import or_, and_, cast, case
        from sqlalchemy.types import Float

        Port = tables.Port
        Proxy = tables.Proxy
        ProxyConnection = tables.ProxyConnection

        # calculate the connection factor
        factor_case = case([
            (ProxyConnection.server_id, conn_factor)
        ], else_=0)

        # Cast the type to make sure the result will be float
        res_rate = (Proxy.resource_count / cast(Proxy.resource_limit, Float))
        res_rate -= factor_case

        user_rate = (Proxy.user_count / cast(Proxy.user_limit, Float))
        user_rate -= factor_case

        query = self.session \
            .query(Port) \
            .join((Proxy, Proxy.id == Port.server_id)) \
            .outerjoin((ProxyConnection,
                        and_(ProxyConnection.server_id == Proxy.id,
                             ProxyConnection.user_id == user_id))) \
            .order_by(user_rate) \
            .order_by(res_rate) \
            .filter(or_(Proxy.user_count < Proxy.user_limit,
                        Proxy.user_limit == 0)) \
            .filter(Proxy.alive) \
            .filter(Proxy.active) \
            .filter(Port.name == 'web')

        # find a random proxy
        ports = query.limit(best).all()
        if not ports:
            return None
        port = random.choice(ports)
        return port.address

	def filtering(self):
		"""Construct the query, by adding filtering(LIKE) on all columns when the datatable's search box is used
		"""

		def resolve_column(column):
			tmp_name = column.data.split('.')
			obj = getattr(self.sqla_object, tmp_name[0], None)
			if obj is None:
				raise DataTablesException('Invalid column data: ' + tmp_name[0])
			if not hasattr(obj, "property"): # Ex: hybrid_property or property
				sqla_obj = self.sqla_object
				column_name = "".join(tmp_name[1:])
			elif isinstance(obj.property, RelationshipProperty): # Ex: ForeignKey
		 		# Ex: address.description
				sqla_obj = obj.mapper.class_
				column_name = "".join(tmp_name[1:])
				if not column_name:
					# Find first primary key
					column_name = obj.property.table.primary_key.columns.values()[0].name
			else: #-> ColumnProperty
				sqla_obj = self.sqla_object
				column_name = column.data
			return sqla_obj, column_name

		condition = None

		search_value = self.request_values.get('search[value]')
		if search_value != "":
			conditions = []
			for column in self.columns:
				# ignore null columns (javascript placeholder) or unsearchable
				if column.data != "" and column.searchable:
					sqla_obj, column_name = resolve_column(column)
					conditions.append(cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_value))
			condition = or_(*conditions)

		conditions = []
		for column in self.columns:
			# ignore null columns (javascript placeholder) or unsearchable
			if column.data != "" and column.search_value != "" and column.searchable:
				sqla_obj, column_name = resolve_column(column)

				#if col.search_like:
				#	conditions.append(cast(get_attr(sqla_obj, column_name), String).like(col.search_like % search_value2))
				#else:
				#	conditions.append(cast(get_attr(sqla_obj, column_name), String).__eq__(search_value2))
				conditions.append(cast(get_attr(sqla_obj, column_name), String).__eq__(column.search_value))

				if condition is not None:
					condition = and_(condition, and_(*conditions))
				else:
					condition= and_(*conditions)

		if condition is not None:
			self.query = self.query.filter(condition)
			# count after filtering
			self.cardinality_filtered = self.query.count()
		else:
			self.cardinality_filtered = self.cardinality

    def by_location(cls, location):
        """Returns a query object of mountain waves around the location"""
        if not isinstance(location, Location):
            raise TypeError('Invalid `location` parameter.')

        return cls.query() \
            .filter(db.func.ST_DWithin(
                cast(location.make_point(), Geography),
                cast(cls.location, Geography),
                5000))

def refine_with_user_area(query):
    """Takes a query and refines it with a spatial constraint
    based on user setting"""
    if 'lon' and 'lat' and 'radius' in session:
        return query.filter(ST_DWithin(
            cast(Task.location, Geography),
            cast(from_shape(Point(session["lon"], session["lat"])), Geography),
            session["radius"]))
    else:
        return query

    def search(self, keywords):
        criteria = []

        for keyword in keywords_split(keywords):
            if keyword:
                keyword = '%{0}%'.format(keyword)
                criteria.append(cast(Article.title, Unicode).ilike(keyword))
                criteria.append(cast(Article.keywords, Unicode).ilike(keyword))
                
        return self.public().filter(db.or_(*criteria))