Python sql.bindparam函数代码示例

def gdft_xhr(request):
    if request.method == 'POST':
        form = GenDev_FailType(request.POST)
        if form.is_valid():
            conn = engine.connect()


            d = select([device.brand_name, func.count(device.MDR_report_key),
                problem_code.problem_description, master_record.manufacturer_name],
                and_(
                    device.MDR_report_key==master_record.MDR_report_key,
                    master_record.event_type==bindparam("event_type"),
                    device_problem.MDR_report_key==master_record.MDR_report_key,
                    device_problem.problem_code_key==problem_code.device_problem_code,
                    device.generic_name.contains(bindparam("generic_name")),
                    master_record.date_recieved > bindparam("date_after"),
                    )).group_by(device_problem.problem_code_key)

            res = conn.execute(d, generic_name=form.cleaned_data["gendev"], event_type=form.cleaned_data["failtype"],
                    date_after=form.cleaned_data["date_after"])
            li = res.fetchall()
            li = sorted(li, key=lambda x: x[1], reverse=True)
            s= ""
            for i in li:
                s = s + str(i) + "<br>"
            return HttpResponse(s)

    return HttpResponse("Error in form")

 def has_table(self, connection, table_name, schema=None):
     # seems like case gets folded in pg_class...
     if schema is None:
         cursor = connection.execute(
             sql.text(
                 "select name "
                 "from sys.tables "
                 "where system = false "
                 "and type = 0 "
                 "and name=:name",
                 bindparams=[
                     sql.bindparam('name', util.text_type(table_name),
                                   type_=sqltypes.Unicode)]
             )
         )
     else:
         cursor = connection.execute(
             sql.text(
                 "SELECT tables.name "
                 "FROM sys.tables, sys.schemas "
                 "WHERE tables.system = FALSE "
                 "AND tables.schema_id = schemas.id "
                 "AND type = 0 "
                 "AND tables.name = :name "
                 "AND schemas.name = :schema",
                 bindparams=[
                     sql.bindparam('name',
                                   util.text_type(table_name),
                                   type_=sqltypes.Unicode),
                     sql.bindparam('schema',
                                   util.text_type(schema),
                                   type_=sqltypes.Unicode)]
             )
         )
     return bool(cursor.first())

    def init(self, session, api):
        self.session = session
        self.api = api

        # compile queries
        self.command_queries = {}
        self.command_queries['character'] = \
            session.query(Command).\
            filter(Command.actor_id == bindparam('actor_id'))

        self.command_queries['children'] = \
            session.query(Actor, Command).\
            filter(Actor.id == bindparam('actor_id')).\
            join(Actor.parent, aliased=True).\
            filter(Command.actor_id == Actor.id)

        location = aliased(Actor)
        self.command_queries['location'] = \
            session.query(Command).\
            join(location).\
            join(Actor, location.id == Actor.parent_id).\
            filter(Actor.id == bindparam('actor_id'))

        location = aliased(Actor)
        children = aliased(Actor)
        self.command_queries['location_children'] = \
            session.query(Command).\
            join(children).\
            join(location, location.id == children.parent_id).\
            join(Actor, location.id == Actor.parent_id).\
            filter(Actor.id == bindparam('actor_id')).\
            filter(Command.actor_id == children.id)

        self.command_precedence = ['character', 'children', 'location', 'location_children']

    def __init__(self, config):
        self._orm_engine = engine_from_config(config["database"]["orm"], prefix="")

        metadata = MetaData()

        aa = models.create_alarm_action_model(metadata).alias("aa")
        nm = models.create_notification_method_model(metadata).alias("nm")
        nmt = models.create_notification_method_type_model(metadata).alias("nmt")
        a = models.create_alarm_model(metadata).alias("a")

        self._orm_query = (
            select([nm.c.id, nm.c.type, nm.c.name, nm.c.address, nm.c.period])
            .select_from(aa.join(nm, aa.c.action_id == nm.c.id))
            .where(
                and_(
                    aa.c.alarm_definition_id == bindparam("alarm_definition_id"),
                    aa.c.alarm_state == bindparam("alarm_state"),
                )
            )
        )

        self._orm_get_alarm_state = select([a.c.state]).where(a.c.id == bindparam("alarm_id"))

        self._orm_nmt_query = select([nmt.c.name])

        self._orm_get_notification = select([nm.c.name, nm.c.type, nm.c.address, nm.c.period]).where(
            nm.c.id == bindparam("notification_id")
        )

        self._orm = None

 def has_table(self, connection, table_name, schema=None):
     # seems like case gets folded in pg_class...
     if schema is None:
         cursor = connection.execute(
             sql.text(
             "select relname from pg_class c join pg_namespace n on "
             "n.oid=c.relnamespace where n.nspname=current_schema() and "
             "lower(relname)=:name",
             bindparams=[
                     sql.bindparam('name', unicode(table_name.lower()),
                     type_=sqltypes.Unicode)]
             )
         )
     else:
         cursor = connection.execute(
             sql.text(
             "select relname from pg_class c join pg_namespace n on "
             "n.oid=c.relnamespace where n.nspname=:schema and "
             "lower(relname)=:name",
                 bindparams=[
                     sql.bindparam('name', 
                     unicode(table_name.lower()), type_=sqltypes.Unicode),
                     sql.bindparam('schema', 
                     unicode(schema), type_=sqltypes.Unicode)] 
             )
         )
     return bool(cursor.first())

    def _save_concepts(cls, concepts):
        r'''Prepare to save concepts in a temporary table.

        >>> concepts = dict(
        ...     names=['apples', 'bananas', 'cherries'],
        ...     keys=[r'\\tk\a', r'\\tk\b', r'\\tk\c'])
        >>> tmp, ins, bind = DataExtract._save_concepts(concepts)

        >>> print tmp.delete()
        DELETE FROM global_temp_fact_param_table

        >>> print ins
        ... # doctest: +NORMALIZE_WHITESPACE
        INSERT INTO global_temp_fact_param_table (char_param1, char_param2)
        VALUES (:path, :name)

        >>> sorted(bind[0].keys())
        ['name', 'path']

        >>> sorted(bind[0].values())
        ['\\a', 'apples']

        '''
        names = concepts['names']
        paths = I2B2MetaData.keys_to_paths(concepts['keys'])
        bind = [dict(name=name,
                     path=path)
                for (path, name) in zip(paths, names)]
        tmp = i2b2_star.t_global_temp_fact_param_table
        ins = tmp.insert().values(char_param1=bindparam('path'),
                                  char_param2=bindparam('name'))
        return tmp, ins, bind

    def has_sequence(self, connection, sequence_name, schema=None):
        if schema is None:
            cursor = connection.execute(
                sql.text(
                    "SELECT relname FROM pg_class c join pg_namespace n on "
                    "n.oid=c.relnamespace where relkind='S' and "
                    "n.nspname=current_schema() "
                    "and lower(relname)=:name",
                    bindparams=[
                        sql.bindparam('name', unicode(sequence_name.lower()),
                        type_=sqltypes.Unicode)
                    ] 
                )
            )
        else:
            cursor = connection.execute(
                sql.text(
                "SELECT relname FROM pg_class c join pg_namespace n on "
                "n.oid=c.relnamespace where relkind='S' and "
                "n.nspname=:schema and lower(relname)=:name",
                bindparams=[
                    sql.bindparam('name', unicode(sequence_name.lower()),
                     type_=sqltypes.Unicode),
                    sql.bindparam('schema', 
                                unicode(schema), type_=sqltypes.Unicode)
                ]
            )
            )

        return bool(cursor.first())

    def __init__(self):
        self._orm_engine = engine_from_config({
            'url': CONF.orm.url
        }, prefix='')

        metadata = MetaData()

        aa = models.create_alarm_action_model(metadata).alias('aa')
        nm = models.create_notification_method_model(metadata).alias('nm')
        nmt_insert = models.create_notification_method_type_model(metadata)
        nmt = nmt_insert.alias('nmt')
        a = models.create_alarm_model(metadata).alias('a')

        self._orm_query = select([nm.c.id, nm.c.type, nm.c.name, nm.c.address, nm.c.period])\
            .select_from(aa.join(nm, aa.c.action_id == nm.c.id))\
            .where(
                and_(aa.c.alarm_definition_id == bindparam('alarm_definition_id'),
                     aa.c.alarm_state == bindparam('alarm_state')))

        self._orm_get_alarm_state = select([a.c.state]).where(a.c.id == bindparam('alarm_id'))

        self._orm_nmt_query = select([nmt.c.name])

        self._orm_get_notification = select([nm.c.name, nm.c.type, nm.c.address, nm.c.period])\
            .where(nm.c.id == bindparam('notification_id'))

        self._orm_add_notification_type = insert(nmt_insert).values(name=bindparam('b_name'))

        self._orm = None

        def export_terms(dest_star, job):
            v = self.variable_table(dest_star)
            keys = job.concepts['keys']
            names = job.concepts['names']
            paths = I2B2MetaData.keys_to_paths(keys)
            v.create(bind=dest_db)
            dest_db.execute(v.insert(),
                            [dict(id=id,
                                  item_key=key,
                                  concept_path=path,
                                  name_char=name,
                                  name=strip_counts(name))
                             for (id, (path, key, name)) in
                             enumerate(zip(paths, keys, names))])

            [(q_cd, result_cd), (q_md, result_md)] = job.term_info()
            cd = dest_star.tables['concept_dimension']
            md = dest_star.tables['modifier_dimension']

            values = lambda _: dict(concept_path=bindparam('concept_path'),
                                    concept_cd=bindparam('concept_cd'))
            tc.copy_in_chunks(dest_db, result_cd, cd,
                              'concept_dimension', [],
                              values=values)
            values = lambda _: dict(concept_path=bindparam('modifier_path'),
                                    concept_cd=bindparam('modifer_cd'))
            tc.copy_in_chunks(dest_db, result_cd, md,
                              'modifier_dimension', [])

def get_query(name, user_id=None):
    """Get the named pre-built query, sharding on user_id if given.

    This is a helper function to return an appropriate pre-built SQL query
    while taking sharding of the WBO table into account.  Call it with the
    name of the query and optionally the user_id on which to shard.
    """
    if user_id is None:
        table = wbo
    else:
        table = get_wbo_table(user_id)

    queries['ITEM_ID_COL_USER'] = and_(
        table.c.collection == bindparam('collection_id'),
        table.c.username == bindparam('user_id'),
        table.c.id == bindparam('item_id'),
        table.c.ttl > bindparam('ttl'))

    query = queries.get(name)
    if query is None:
        raise ValueError(name)

    if isinstance(query, str):
        if '%(wbo)s' in query:
            query = query % {'wbo': table.name}
        query = text(query)

    return query

 def has_type(self, connection, type_name, schema=None):
     bindparams = [
         sql.bindparam('typname',
             unicode(type_name), type_=sqltypes.Unicode),
         sql.bindparam('nspname',
             unicode(schema), type_=sqltypes.Unicode),
         ]
     if schema is not None:
         query = """
         SELECT EXISTS (
             SELECT * FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n
             WHERE t.typnamespace = n.oid
             AND t.typname = :typname
             AND n.nspname = :nspname
             )
             """
     else:
         query = """
         SELECT EXISTS (
             SELECT * FROM pg_catalog.pg_type t
             WHERE t.typname = :typname
             AND pg_type_is_visible(t.oid)
             )
             """
     cursor = connection.execute(sql.text(query, bindparams=bindparams))
     return bool(cursor.scalar())

 def get(self, database, query, qual):
     stmt = qualstat_getstatdata()
     c = inner_cc(stmt)
     stmt = stmt.alias()
     stmt = (stmt.select()
         .where((c.qualid == bindparam("qualid")))
         .where(stmt.c.occurences > 0)
         .column((c.queryid == bindparam("query")).label("is_my_query")))
     quals = list(self.execute(
         stmt,
         params={"query": query,
                 "from": self.get_argument("from"),
                 "to": self.get_argument("to"),
                 "qualid": qual}))
     my_qual = None
     other_queries = {}
     for qual in quals:
         if qual['is_my_query']:
             my_qual = resolve_quals(self.connect(database=database),
                                     [qual])[0]
         else:
             other_queries[qual['queryid']] = qual['query']
     if my_qual is None:
         self.render("xhr.html", content="nodata")
         return
     self.render("database/query/qualdetail.html",
                 qual=my_qual,
                 database=database,
                 other_queries=other_queries)

    def get_table_oid(self, connection, table_name, schema=None, **kw):
        """Fetch the oid for schema.table_name.

        Several reflection methods require the table oid.  The idea for using
        this method is that it can be fetched one time and cached for
        subsequent calls.

        """
        table_oid = None
        if schema is not None:
            schema_where_clause = "n.nspname = :schema"
        else:
            schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)"
        query = """
            SELECT c.oid
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (%s)
            AND c.relname = :table_name AND c.relkind in ('r', 'v', 'f')
        """ % schema_where_clause
        # Since we're binding to unicode, table_name and schema_name must be
        # unicode.
        table_name = str(table_name)
        bindparams = [sql.bindparam('table_name', type_=sqltypes.Unicode)]
        if schema is not None:
            schema = str(schema)
            bindparams.append(sql.bindparam('schema', type_=sqltypes.Unicode))
        s = sql.text(
            query, bindparams=bindparams, typemap={'oid': sqltypes.Integer})
        c = connection.execute(s, table_name=table_name, schema=schema)
        table_oid = c.scalar()
        if table_oid is None:
            raise exc.NoSuchTableError(table_name)
        return table_oid

    def get(self, database, query):
        bs = block_size.c.block_size
        stmt = powa_getstatdata_detailed_db()
        stmt = stmt.where(
            (column("datname") == bindparam("database")) &
            (column("queryid") == bindparam("query")))
        stmt = stmt.alias()
        from_clause = outerjoin(powa_statements, stmt,
                           and_(powa_statements.c.queryid == stmt.c.queryid, powa_statements.c.dbid == stmt.c.dbid))
        c = stmt.c
        rblk = mulblock(sum(c.shared_blks_read).label("shared_blks_read"))
        wblk = mulblock(sum(c.shared_blks_hit).label("shared_blks_hit"))
        stmt = (select([
            column("query"),
            sum(c.calls).label("calls"),
            sum(c.runtime).label("runtime"),
            rblk,
            wblk,
            (rblk + wblk).label("total_blks")])
            .select_from(from_clause)
            .where(powa_statements.c.queryid == bindparam("query"))
            .group_by(column("query"), bs))

        value = self.execute(stmt, params={
            "query": query,
            "database": database,
            "from": self.get_argument("from"),
            "to": self.get_argument("to")
        })
        if value.rowcount < 1:
            self.render("xhr.html", content="No data")
            return
        self.render("database/query/detail.html", stats=value.first())

    def test_cloned_alias(self):
        entity = table(
            "entity", column("id"), column("employer_id"), column("name")
        )
        tag = table("tag", column("tag"), column("entity_id"))

        tags = (
            select([tag.c.entity_id, func.array_agg(tag.c.tag).label("tags")])
            .group_by(tag.c.entity_id)
            .cte("unaliased_tags")
        )

        entity_tags = tags.alias(name="entity_tags")
        employer_tags = tags.alias(name="employer_tags")

        q = (
            select([entity.c.name])
            .select_from(
                entity.outerjoin(
                    entity_tags, tags.c.entity_id == entity.c.id
                ).outerjoin(
                    employer_tags, tags.c.entity_id == entity.c.employer_id
                )
            )
            .where(entity_tags.c.tags.op("@>")(bindparam("tags")))
            .where(employer_tags.c.tags.op("@>")(bindparam("tags")))
        )

        self.assert_compile(
            q,
            "WITH unaliased_tags AS "
            "(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags "
            "FROM tag GROUP BY tag.entity_id)"
            " SELECT entity.name "
            "FROM entity "
            "LEFT OUTER JOIN unaliased_tags AS entity_tags ON "
            "unaliased_tags.entity_id = entity.id "
            "LEFT OUTER JOIN unaliased_tags AS employer_tags ON "
            "unaliased_tags.entity_id = entity.employer_id "
            "WHERE (entity_tags.tags @> :tags) AND "
            "(employer_tags.tags @> :tags)",
        )

        cloned = q.params(tags=["tag1", "tag2"])
        self.assert_compile(
            cloned,
            "WITH unaliased_tags AS "
            "(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags "
            "FROM tag GROUP BY tag.entity_id)"
            " SELECT entity.name "
            "FROM entity "
            "LEFT OUTER JOIN unaliased_tags AS entity_tags ON "
            "unaliased_tags.entity_id = entity.id "
            "LEFT OUTER JOIN unaliased_tags AS employer_tags ON "
            "unaliased_tags.entity_id = entity.employer_id "
            "WHERE (entity_tags.tags @> :tags) AND "
            "(employer_tags.tags @> :tags)",
        )