Python sql.literal函数代码示例

    def test_positional_binds_2(self):
        orders = table('orders',
                       column('order'),
                       )
        s = select([orders.c.order, literal("x")]).cte("regional_sales")
        s = select([s.c.order, literal("y")])
        dialect = default.DefaultDialect()
        dialect.positional = True
        dialect.paramstyle = 'numeric'
        s1 = select([orders.c.order]).where(orders.c.order == 'x').\
            cte("regional_sales_1")

        s1a = s1.alias()

        s2 = select([orders.c.order == 'y', s1a.c.order,
                     orders.c.order, s1.c.order]).\
            where(orders.c.order == 'z').\
            cte("regional_sales_2")

        s3 = select([s2])

        self.assert_compile(
            s3,
            'WITH regional_sales_1 AS (SELECT orders."order" AS "order" '
            'FROM orders WHERE orders."order" = :1), regional_sales_2 AS '
            '(SELECT orders."order" = :2 AS anon_1, '
            'anon_2."order" AS "order", '
            'orders."order" AS "order", '
            'regional_sales_1."order" AS "order" FROM orders, '
            'regional_sales_1 '
            'AS anon_2, regional_sales_1 '
            'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, '
            'regional_sales_2."order" FROM regional_sales_2',
            checkpositional=('x', 'y', 'z'), dialect=dialect)

def create_mapper(rack_specs_tbl):
    "Mapper factory."
    rs = rack_specs_tbl
    polymorphic_select = select([
        rs,
        (case([(rs.c.has_movable_subitems,
                literal(RACK_SPECS_TYPES.TUBE_RACK_SPECS))],
              else_=literal(RACK_SPECS_TYPES.PLATE_SPECS))).label(
                                                            'rackspecs_type')
        ],
        ).alias('rackspecs')
    m = mapper(RackSpecs, polymorphic_select,
            id_attribute='rack_specs_id',
            slug_expression=lambda cls: as_slug_expression(cls.name),
            properties=dict(
                manufacturer=relationship(Organization),
                shape=relationship(RackShape, uselist=False,
                                   back_populates='specs'),
                rack_specs_type=
                        column_property(polymorphic_select.c.rackspecs_type),
                ),
            polymorphic_on=polymorphic_select.c.rackspecs_type,
            polymorphic_identity=RACK_SPECS_TYPES.RACK_SPECS,
            )
    RackSpecs.has_tubes = synonym('has_movable_subitems')
    return m

    def _by_search_tsearch(self, query, operand, maybe_negate):
        # type: (Query, str, ConditionTransform) -> Query
        tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand))
        ts_locs_array = func.ts_match_locs_array
        query = query.column(ts_locs_array(literal("zulip.english_us_search"),
                                           column("rendered_content"),
                                           tsquery).label("content_matches"))
        # We HTML-escape the subject in Postgres to avoid doing a server round-trip
        query = query.column(ts_locs_array(literal("zulip.english_us_search"),
                                           func.escape_html(column("subject")),
                                           tsquery).label("subject_matches"))

        # Do quoted string matching.  We really want phrase
        # search here so we can ignore punctuation and do
        # stemming, but there isn't a standard phrase search
        # mechanism in Postgres
        for term in re.findall('"[^"]+"|\S+', operand):
            if term[0] == '"' and term[-1] == '"':
                term = term[1:-1]
                term = '%' + connection.ops.prep_for_like_query(term) + '%'
                cond = or_(column("content").ilike(term),
                           column("subject").ilike(term))
                query = query.where(maybe_negate(cond))

        cond = column("search_tsvector").op("@@")(tsquery)
        return query.where(maybe_negate(cond))

 def limit_clause(self, select):
     text = ""
     if select._limit is not None:
         text += " \n LIMIT " + self.process(sql.literal(select._limit))
     if select._offset is not None:
         text += " OFFSET " + self.process(sql.literal(select._offset))
         if select._limit is None:
             text += " ROWS"  # OFFSET n ROW[S]
     return text

def user_requests_aggregate(session, user):
    """Returns all pending requests for this user to approve across groups."""

    members = session.query(
        label("type", literal(1)),
        label("id", Group.id),
        label("name", Group.groupname),
    ).union(session.query(
        label("type", literal(0)),
        label("id", User.id),
        label("name", User.username),
    )).subquery()

    now = datetime.utcnow()
    groups = session.query(
        label("id", Group.id),
        label("name", Group.groupname),
    ).filter(
        GroupEdge.group_id == Group.id,
        GroupEdge.member_pk == user.id,
        GroupEdge.active == True,
        GroupEdge._role.in_(APPROVER_ROLE_INDICES),
        user.enabled == True,
        Group.enabled == True,
        or_(
            GroupEdge.expiration > now,
            GroupEdge.expiration == None,
        )
    ).subquery()

    requests = session.query(
        Request.id,
        Request.requested_at,
        GroupEdge.expiration,
        label("role", GroupEdge._role),
        Request.status,
        label("requester", User.username),
        label("type", members.c.type),
        label("requesting", members.c.name),
        label("reason", Comment.comment),
        label("group_id", groups.c.id),
        label("groupname", groups.c.name),
    ).filter(
        Request.on_behalf_obj_pk == members.c.id,
        Request.on_behalf_obj_type == members.c.type,
        Request.requesting_id == groups.c.id,
        Request.requester_id == User.id,
        Request.status == "pending",
        Request.id == RequestStatusChange.request_id,
        RequestStatusChange.from_status == None,
        GroupEdge.id == Request.edge_id,
        Comment.obj_type == 3,
        Comment.obj_pk == RequestStatusChange.id,
    )
    return requests

 def limit_clause(self, select):
     # CUBRID supports: LIMIT <limit> and LIMIT <offset>, <limit>
     limit, offset = select._limit, select._offset
     if (limit, offset) == (None, None):
         return ""
     elif limit is None and offset is not None:
         return " \n LIMIT %s, 1073741823" % (self.process(sql.literal(offset)))
     elif offset is not None:
         return " \n LIMIT %s, %s" % (self.process(sql.literal(offset)), self.process(sql.literal(limit)))
     else:
         return " \n LIMIT %s" % (self.process(sql.literal(limit)),)

 def limit_clause(self, select):
     text = ""
     if select._limit is not None:
         text +=  "\n LIMIT " + self.process(sql.literal(select._limit))
     if select._offset is not None:
         if select._limit is None:
             text += "\n LIMIT " + self.process(sql.literal(-1))
         text += " OFFSET " + self.process(sql.literal(select._offset))
     else:
         text += " OFFSET " + self.process(sql.literal(0))
     return text

    def get(self):
        query = self.get_argument("query", "")
        offset = int(self.get_argument("offset", 0))
        limit = int(self.get_argument("limit", 100))
        if limit > 9000:
            limit = 9000

        groups = (
            self.session.query(
                label("type", literal("Group")),
                label("id", Group.id),
                label("name", Group.groupname),
            )
            .filter(Group.enabled == True, Group.groupname.like("%{}%".format(query)))
            .subquery()
        )

        permissions = (
            self.session.query(
                label("type", literal("Permission")),
                label("id", Permission.id),
                label("name", Permission.name),
            )
            .filter(Permission.enabled == True, Permission.name.like("%{}%".format(query)))
            .subquery()
        )

        users = (
            self.session.query(
                label("type", literal("User")), label("id", User.id), label("name", User.username)
            )
            .filter(User.enabled == True, User.username.like("%{}%".format(query)))
            .subquery()
        )

        results_query = self.session.query("type", "id", "name").select_entity_from(
            union_all(users.select(), permissions.select(), groups.select())
        )
        total = results_query.count()
        results = results_query.offset(offset).limit(limit).all()

        if len(results) == 1:
            result = results[0]
            return self.redirect("/{}s/{}".format(result.type.lower(), result.name))

        self.render(
            "search.html",
            results=results,
            search_query=query,
            offset=offset,
            limit=limit,
            total=total,
        )

 def test_notin(self):
     left = column('left')
     assert left.comparator.operate(operators.notin_op, [1, 2, 3]).compare(
             BinaryExpression(
                 left,
                 Grouping(ClauseList(
                     literal(1), literal(2), literal(3)
                 )),
                 operators.notin_op
             )
         )
     self._loop_test(operators.notin_op, [1, 2, 3])

 def _get_nodes_from_db(session):
     return session.query(
         label("type", literal("User")),
         label("name", User.username)
     ).filter(
         User.enabled == True
     ).union(session.query(
         label("type", literal("Group")),
         label("name", Group.groupname))
     ).filter(
         Group.enabled == True
     ).all()

    def _get_edges_from_db(session):

        parent = aliased(Group)
        group_member = aliased(Group)
        user_member = aliased(User)
        edges = []

        now = datetime.utcnow()

        query = session.query(
            label("groupname", parent.groupname),
            label("type", literal("Group")),
            label("name", group_member.groupname),
            label("role", GroupEdge._role)
        ).filter(
            parent.id == GroupEdge.group_id,
            group_member.id == GroupEdge.member_pk,
            GroupEdge.active == True,
            parent.enabled == True,
            group_member.enabled == True,
            or_(
                GroupEdge.expiration > now,
                GroupEdge.expiration == None
            ),
            GroupEdge.member_type == 1
        ).union(session.query(
            label("groupname", parent.groupname),
            label("type", literal("User")),
            label("name", user_member.username),
            label("role", GroupEdge._role)
        ).filter(
            parent.id == GroupEdge.group_id,
            user_member.id == GroupEdge.member_pk,
            GroupEdge.active == True,
            parent.enabled == True,
            user_member.enabled == True,
            or_(
                GroupEdge.expiration > now,
                GroupEdge.expiration == None
            ),
            GroupEdge.member_type == 0
        ))

        for record in query.all():
            edges.append((
                ("Group", record.groupname),
                (record.type, record.name),
                {"role": record.role},
            ))

        return edges

    def get_select_precolumns(self, select):
        """Called when building a ``SELECT`` statement, position is just
        before column list Firebird puts the limit and offset right
        after the ``SELECT``...
        """

        result = ""
        if select._limit:
            result += "FIRST %s " % self.process(sql.literal(select._limit))
        if select._offset:
            result += "SKIP %s " % self.process(sql.literal(select._offset))
        if select._distinct:
            result += "DISTINCT "
        return result

    def _test_comparison_op(self, py_op, fwd_op, rev_op):
        dt = datetime.datetime(2012, 5, 10, 15, 27, 18)
        for (lhs, rhs, l_sql, r_sql) in (
            ('a', self.table1.c.myid, ':myid_1', 'mytable.myid'),
            ('a', literal('b'), ':param_2', ':param_1'),  # note swap!
            (self.table1.c.myid, 'b', 'mytable.myid', ':myid_1'),
            (self.table1.c.myid, literal('b'), 'mytable.myid', ':param_1'),
            (self.table1.c.myid, self.table1.c.myid,
                            'mytable.myid', 'mytable.myid'),
            (literal('a'), 'b', ':param_1', ':param_2'),
            (literal('a'), self.table1.c.myid, ':param_1', 'mytable.myid'),
            (literal('a'), literal('b'), ':param_1', ':param_2'),
            (dt, literal('b'), ':param_2', ':param_1'),
            (literal('b'), dt, ':param_1', ':param_2'),
            ):

            # the compiled clause should match either (e.g.):
            # 'a' < 'b' -or- 'b' > 'a'.
            compiled = str(py_op(lhs, rhs))
            fwd_sql = "%s %s %s" % (l_sql, fwd_op, r_sql)
            rev_sql = "%s %s %s" % (r_sql, rev_op, l_sql)

            self.assert_(compiled == fwd_sql or compiled == rev_sql,
                         "\n'" + compiled + "'\n does not match\n'" +
                         fwd_sql + "'\n or\n'" + rev_sql + "'")

    def test_positional_binds_2_asliteral(self):
        orders = table("orders", column("order"))
        s = select([orders.c.order, literal("x")]).cte("regional_sales")
        s = select([s.c.order, literal("y")])
        dialect = default.DefaultDialect()
        dialect.positional = True
        dialect.paramstyle = "numeric"
        s1 = (
            select([orders.c.order])
            .where(orders.c.order == "x")
            .cte("regional_sales_1")
        )

        s1a = s1.alias()

        s2 = (
            select(
                [
                    orders.c.order == "y",
                    s1a.c.order,
                    orders.c.order,
                    s1.c.order,
                ]
            )
            .where(orders.c.order == "z")
            .cte("regional_sales_2")
        )

        s3 = select([s2])

        self.assert_compile(
            s3,
            "WITH regional_sales_1 AS "
            '(SELECT orders."order" AS "order" '
            "FROM orders "
            "WHERE orders.\"order\" = 'x'), "
            "regional_sales_2 AS "
            "(SELECT orders.\"order\" = 'y' AS anon_1, "
            'anon_2."order" AS "order", orders."order" AS "order", '
            'regional_sales_1."order" AS "order" '
            "FROM orders, regional_sales_1 AS anon_2, regional_sales_1 "
            "WHERE orders.\"order\" = 'z') "
            'SELECT regional_sales_2.anon_1, regional_sales_2."order" '
            "FROM regional_sales_2",
            checkpositional=(),
            dialect=dialect,
            literal_binds=True,
        )

    def test_noorderby_parameters_insubquery(self):
        """test that the ms-sql dialect does not include ORDER BY
        positional parameters in subqueries"""

        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String),
            column("description", String),
        )

        q = select(
            [table1.c.myid, sql.literal('bar').label('c1')],
            order_by=[table1.c.name + '-']
        ).alias("foo")
        crit = q.c.myid == table1.c.myid
        dialect = mssql.dialect()
        dialect.paramstyle = "qmark"
        dialect.positional = True
        self.assert_compile(
            select(["*"], crit),
            "SELECT * FROM (SELECT mytable.myid AS "
            "myid, ? AS c1 FROM mytable) AS foo, mytable WHERE "
            "foo.myid = mytable.myid",
            dialect=dialect,
            checkparams={'param_1': 'bar'},
            # if name_1 is included, too many parameters are passed to dbapi
            checkpositional=('bar', )
        )