Python sqlalchemy.bindparam() Examples

def cleanup_expired(self, max_age_in_seconds):
        cutoff = timestamp() - max_age_in_seconds * 1000
        with self.db.begin() as conn:
            to_delete = conn.execute(
                sa.select([clusters.c.id]).where(clusters.c.stop_time < cutoff)
            ).fetchall()

            if to_delete:
                to_delete = [i for i, in to_delete]

                conn.execute(
                    clusters.delete().where(clusters.c.id == sa.bindparam("id")),
                    [{"id": i} for i in to_delete],
                )

                for i in to_delete:
                    cluster = self.id_to_cluster.pop(i)
                    self.name_to_cluster.pop(cluster.name, None)
                    user_clusters = self.username_to_clusters[cluster.username]
                    user_clusters.pop(cluster.name)
                    if not user_clusters:
                        self.username_to_clusters.pop(cluster.username)

        return len(to_delete) 

def get_pending_or_processing_ops(context, object_uuid, operation=None):
    # NOTE (sai): For performance reasons, we expect this method to use baked
    # query (http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html)
    baked_query = bakery(lambda s: s.query(
        models.OpenDaylightJournal))
    baked_query += lambda q: q.filter(
        or_(models.OpenDaylightJournal.state == odl_const.PENDING,
            models.OpenDaylightJournal.state == odl_const.PROCESSING),
        models.OpenDaylightJournal.object_uuid == bindparam('uuid'))
    if operation:
        if isinstance(operation, (list, tuple)):
            baked_query += lambda q: q.filter(
                models.OpenDaylightJournal.operation.in_(bindparam('op',
                                                         expanding=True)))
        else:
            baked_query += lambda q: q.filter(
                models.OpenDaylightJournal.operation == bindparam('op'))

    return baked_query(context.session).params(
        uuid=object_uuid, op=operation).all() 

def __init__(self, api, db):
        super().__init__(api, db)

        ins_user = sa.dialects.postgresql.insert(db.user)
        ins_user_groups = sa.dialects.postgresql.insert(db.user_groups)

        self.sql = {
            ("insert", "user"):
                ins_user.on_conflict_do_update(
                    index_elements=[db.user.c.user_id],
                    set_={
                        "user_name":         ins_user.excluded.user_name,
                        "user_registration": ins_user.excluded.user_registration,
                        "user_editcount":    ins_user.excluded.user_editcount,
                    }),
            ("update", "user"):
                db.user.update() \
                    .where(db.user.c.user_name == sa.bindparam("b_olduser")),
            ("insert", "user_groups"):
                ins_user_groups.on_conflict_do_nothing(),
            ("delete", "user_groups"):
                db.user_groups.delete().where(
                    db.user_groups.c.ug_user == sa.bindparam("b_ug_user")),
        } 

def __init__(self, api, db):
        super().__init__(api, db)

        self.sql = {
            ("delete", "user"):
                db.user.delete() \
                    .where(db.user.c.user_id == sa.bindparam("b_oldid")),
            ("update", "logging"):
                db.logging.update() \
                    .where(db.logging.c.log_user == sa.bindparam("b_oldid")),
            ("update", "ipb"):
                db.ipblocks.update() \
                    .where(db.ipblocks.c.ipb_by == sa.bindparam("b_oldid")),
            ("update", "archive"):
                db.archive.update() \
                    .where(db.archive.c.ar_user == sa.bindparam("b_oldid")),
            ("update", "revision"):
                db.revision.update() \
                    .where(db.revision.c.rev_user == sa.bindparam("b_oldid")),
        } 

def __init__(self, api, db):
        super().__init__(api, db)

        ins_iw = sa.dialects.postgresql.insert(db.interwiki)

        self.sql = {
            ("insert", "interwiki"):
                db.interwiki.insert(),
            ("delete", "interwiki"):
                db.interwiki.delete().where(db.interwiki.c.iw_prefix == sa.bindparam("b_iw_prefix")),
            # iw_api is not visible in the logs so it is not updated
            ("update", "interwiki"):
                ins_iw.on_conflict_do_update(
                    index_elements=[db.interwiki.c.iw_prefix],
                    set_={
                        "iw_url":   ins_iw.excluded.iw_url,
                        "iw_local": ins_iw.excluded.iw_local,
                        "iw_trans": ins_iw.excluded.iw_trans,
                    }),
        } 

def __init__(self, api, db):
        super().__init__(api, db)

        ins_pt = sa.dialects.postgresql.insert(db.protected_titles)

        self.sql = {
            ("insert", "protected_titles"):
                ins_pt.on_conflict_do_update(
                    index_elements=[
                        db.protected_titles.c.pt_namespace,
                        db.protected_titles.c.pt_title,
                    ],
                    set_={
                        "pt_level":  ins_pt.excluded.pt_level,
                        "pt_expiry": ins_pt.excluded.pt_expiry,
                    }),
            ("delete", "protected_titles"):
                db.protected_titles.delete().where(
                    (db.protected_titles.c.pt_namespace == sa.bindparam("b_pt_namespace")) &
                    (db.protected_titles.c.pt_title == sa.bindparam("b_pt_title"))),
        } 

def params(self, *optionaldict, **kwargs):
        """Return a copy with :func:`_expression.bindparam` elements
        replaced.

        Returns a copy of this ClauseElement with
        :func:`_expression.bindparam`
        elements replaced with values taken from the given dictionary::

          >>> clause = column('x') + bindparam('foo')
          >>> print(clause.compile().params)
          {'foo':None}
          >>> print(clause.params({'foo':7}).compile().params)
          {'foo':7}

        """
        return self._replace_params(False, optionaldict, kwargs) 

def _replace_params(self, unique, optionaldict, kwargs):
        if len(optionaldict) == 1:
            kwargs.update(optionaldict[0])
        elif len(optionaldict) > 1:
            raise exc.ArgumentError(
                "params() takes zero or one positional dictionary argument"
            )

        def visit_bindparam(bind):
            if bind.key in kwargs:
                bind.value = kwargs[bind.key]
                bind.required = False
            if unique:
                bind._convert_to_unique()

        return cloned_traverse(self, {}, {"bindparam": visit_bindparam}) 

def test_multi_update_rowcount(self, connection):
        employees_table = self.tables.employees
        stmt = (
            employees_table.update()
            .where(employees_table.c.name == bindparam("emp_name"))
            .values(department="C")
        )

        r = connection.execute(
            stmt,
            [
                {"emp_name": "Bob"},
                {"emp_name": "Cynthia"},
                {"emp_name": "nonexistent"},
            ],
        )

        eq_(r.rowcount, 2) 

def test_multi_delete_rowcount(self, connection):
        employees_table = self.tables.employees

        stmt = employees_table.delete().where(
            employees_table.c.name == bindparam("emp_name")
        )

        r = connection.execute(
            stmt,
            [
                {"emp_name": "Bob"},
                {"emp_name": "Cynthia"},
                {"emp_name": "nonexistent"},
            ],
        )

        eq_(r.rowcount, 2) 

def _get_alarm_definition(self, conn, tenant_id, _id):
        ad = self.ad_s
        query = (self.base_query
                 .select_from(self.base_query_from)
                 .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                 .where(ad.c.id == bindparam('b_id'))
                 .where(ad.c.deleted_at == null()))

        row = conn.execute(query,
                           b_tenant_id=tenant_id,
                           b_id=_id).fetchone()

        if row is not None:
            return dict(row)
        else:
            raise exceptions.DoesNotExistException 

def test_exception_format_hide_parameters_nondbapi_round_trip(self):
        foo = Table("foo", MetaData(), Column("data", String))

        with self.no_param_engine.connect() as conn:
            assert_raises_message(
                tsa.exc.StatementError,
                r"\(sqlalchemy.exc.InvalidRequestError\) A value is required "
                r"for bind parameter 'the_data_2'\n"
                r"\[SQL: SELECT foo.data \nFROM foo \nWHERE "
                r"foo.data = \? OR foo.data = \?\]\n"
                r"\[SQL parameters hidden due to hide_parameters=True\]",
                conn.execute,
                select([foo]).where(
                    or_(
                        foo.c.data == bindparam("the_data_1"),
                        foo.c.data == bindparam("the_data_2"),
                    )
                ),
                {"the_data_1": "some data"},
            ) 

def test_no_clobs_for_string_params(self):
        """test that simple string params get a DBAPI type of
        VARCHAR, not CLOB. This is to prevent setinputsizes
        from setting up cx_oracle.CLOBs on
        string-based bind params [ticket:793]."""

        class FakeDBAPI(object):
            def __getattr__(self, attr):
                return attr

        dialect = oracle.OracleDialect()
        dbapi = FakeDBAPI()

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") 

def test_bindparam_quote(self):
        """test that bound parameters take on quoting for reserved words,
        column names quote flag enabled."""
        # note: this is only in cx_oracle at the moment.  not sure
        # what other hypothetical oracle dialects might need

        self.assert_compile(bindparam("option"), ':"option"')
        self.assert_compile(bindparam("plain"), ":plain")
        t = Table("s", MetaData(), Column("plain", Integer, quote=True))
        self.assert_compile(
            t.insert().values(plain=5),
            'INSERT INTO s ("plain") VALUES (:"plain")',
        )
        self.assert_compile(
            t.update().values(plain=5), 'UPDATE s SET "plain"=:"plain"'
        ) 

def test_update_executemany(self):
        with testing.db.connect() as conn:
            timestamp = datetime.datetime(2015, 4, 17, 18, 5, 2)
            conn.execute(
                self.tables.t.insert(),
                [
                    {"x": 5, "data": timestamp},
                    {"x": 6, "data": timestamp},
                    {"x": 7, "data": timestamp},
                ],
            )

            conn.execute(
                self.tables.t.update()
                .values(data=func.utc_timestamp())
                .where(self.tables.t.c.x == bindparam("xval")),
                [{"xval": 5}, {"xval": 6}, {"xval": 7}],
            ) 

def test_update_executemany_w_default(self):
        with testing.db.connect() as conn:
            timestamp = datetime.datetime(2015, 4, 17, 18, 5, 2)
            conn.execute(
                self.tables.t_default.insert(),
                [
                    {"x": 5, "idata": timestamp},
                    {"x": 6, "idata": timestamp},
                    {"x": 7, "idata": timestamp},
                ],
            )

            conn.execute(
                self.tables.t_default.update()
                .values(idata=func.utc_timestamp())
                .where(self.tables.t_default.c.x == bindparam("xval")),
                [{"xval": 5}, {"xval": 6}, {"xval": 7}],
            ) 

def test_functions_with_cols(self):
        users = table(
            "users", column("id"), column("name"), column("fullname")
        )
        calculate = select(
            [column("q"), column("z"), column("r")],
            from_obj=[
                func.calculate(bindparam("x", None), bindparam("y", None))
            ],
        )

        with testing.expect_deprecated(
            "The SelectBase.c and SelectBase.columns attributes are "
            "deprecated and will be removed"
        ):
            self.assert_compile(
                select([users], users.c.id > calculate.c.z),
                "SELECT users.id, users.name, users.fullname "
                "FROM users, (SELECT q, z, r "
                "FROM calculate(:x, :y)) AS anon_1 "
                "WHERE users.id > anon_1.z",
            ) 

def test_empty_insert(self):
        """test that execute() interprets [] as a list with no params"""
        users_autoinc = self.tables.users_autoinc

        testing.db.execute(
            users_autoinc.insert().values(user_name=bindparam("name", None)),
            [],
        )
        eq_(testing.db.execute(users_autoinc.select()).fetchall(), [(1, None)]) 

def test_string_dates_passed_raise(self, connection):
        assert_raises(
            exc.StatementError,
            connection.execute,
            select([1]).where(bindparam("date", type_=Date)),
            date=str(datetime.date(2007, 10, 30)),
        ) 

def test_numeric_bind_round_trip(self, connection):
        eq_(
            connection.scalar(
                select(
                    [
                        literal_column("2", type_=Integer())
                        + bindparam("2_1", value=2)
                    ]
                )
            ),
            4,
        ) 

def test_expanding_quote_roundtrip(self, connection):
        t = Table("asfd", self.metadata, Column("foo", Integer))
        t.create(connection)

        connection.execute(
            select([t]).where(t.c.foo.in_(bindparam("uid", expanding=True))),
            uid=[1, 2, 3],
        ) 

def test_should_create(self):
        expression = ('AVG(hpcs.compute{flavor_id=777, image_id=888,'
                      ' metric_name=cpu}) > 10')
        description = ''
        match_by = ['flavor_id', 'image_id']
        sub_expr_list = (alarm_expr_parser.AlarmExprParser(expression).sub_expr_list)
        alarm_actions = ['29387234', '77778687']
        alarmA_id = self.repo.create_alarm_definition('555',
                                                      '90% CPU',
                                                      expression,
                                                      sub_expr_list,
                                                      description,
                                                      'LOW',
                                                      match_by,
                                                      alarm_actions,
                                                      None,
                                                      None)

        alarmB = self.repo.get_alarm_definition('555', alarmA_id)

        self.assertEqual(alarmA_id, alarmB['id'])

        query_sad = (select([self.sad.c.id])
                     .select_from(self.sad)
                     .where(self.sad.c.alarm_definition_id == alarmA_id))

        query_sadd = (select([func.count()])
                      .select_from(self.sadd)
                      .where(self.sadd.c.sub_alarm_definition_id == bindparam('id')))

        with self.engine.connect() as conn:
            count_sad = conn.execute(query_sad).fetchall()
            self.assertEqual(len(count_sad), 1)
            count_sadd = conn.execute(query_sadd, id=count_sad[0][0]).fetchone()
            self.assertEqual(count_sadd[0], 3) 

def test_baked_query_cols_only(n):
    """test a baked query of only the entity columns."""
    bakery = baked.bakery()
    s = Session(bind=engine)
    for id_ in random.sample(ids, n):
        q = bakery(
            lambda s: s.query(Customer.id, Customer.name, Customer.description)
        )
        q += lambda q: q.filter(Customer.id == bindparam("id"))
        q(s).params(id=id_).one() 

def setUpClass(cls):
        engine = create_engine('sqlite://')

        qry = open('monasca_api/tests/sqlite_alarm.sql', 'r').read()
        sconn = engine.raw_connection()
        c = sconn.cursor()
        c.executescript(qry)
        sconn.commit()
        c.close()
        cls.engine = engine

        def _fake_engine_from_config(*args, **kw):
            return cls.engine
        cls.fixture = fixtures.MonkeyPatch(
            'sqlalchemy.create_engine', _fake_engine_from_config)
        cls.fixture.setUp()

        metadata = MetaData()
        cls.nm = models.create_nm_model(metadata)
        cls._delete_nm_query = delete(cls.nm)
        cls._insert_nm_query = (insert(cls.nm)
                                .values(
                                    id=bindparam('id'),
                                    tenant_id=bindparam('tenant_id'),
                                    name=bindparam('name'),
                                    type=bindparam('type'),
                                    address=bindparam('address'),
                                    period=bindparam('period'),
                                    created_at=bindparam('created_at'),
                                    updated_at=bindparam('updated_at'))) 

def _params(self, unique, optionaldict, kwargs):
        if len(optionaldict) == 1:
            kwargs.update(optionaldict[0])
        elif len(optionaldict) > 1:
            raise exc.ArgumentError(
                "params() takes zero or one positional dictionary argument")

        def visit_bindparam(bind):
            if bind.key in kwargs:
                bind.value = kwargs[bind.key]
                bind.required = False
            if unique:
                bind._convert_to_unique()
        return cloned_traverse(self, {}, {'bindparam': visit_bindparam}) 

def params(self, *optionaldict, **kwargs):
        """Return a copy with :func:`bindparam()` elements replaced.

        Returns a copy of this ClauseElement with :func:`bindparam()`
        elements replaced with values taken from the given dictionary::

          >>> clause = column('x') + bindparam('foo')
          >>> print clause.compile().params
          {'foo':None}
          >>> print clause.params({'foo':7}).compile().params
          {'foo':7}

        """
        return self._params(False, optionaldict, kwargs) 

def unique_params(self, *optionaldict, **kwargs):
        """Return a copy with :func:`bindparam()` elements replaced.

        Same functionality as ``params()``, except adds `unique=True`
        to affected bind parameters so that multiple statements can be
        used.

        """
        return self._params(True, optionaldict, kwargs) 

def update_workers(self, updates):
        """Update multiple workers' states"""
        if not updates:
            return
        with self.db.begin() as conn:
            conn.execute(
                workers.update().where(workers.c.id == sa.bindparam("_id")),
                [{"_id": w.id, **u} for w, u in updates],
            )
            for w, u in updates:
                for k, v in u.items():
                    setattr(w, k, v) 

def update_clusters(self, updates):
        """Update multiple clusters' states"""
        if not updates:
            return
        with self.db.begin() as conn:
            conn.execute(
                clusters.update().where(clusters.c.id == sa.bindparam("_id")),
                [{"_id": c.id, **u} for c, u in updates],
            )
            for c, u in updates:
                for k, v in u.items():
                    setattr(c, k, v) 

def _get_pipe_info(self, pipename):
        ''' Retrieve the pipeline Info for a given pipeline version name '''

        assert pipename.lower() in ['drp', 'dap'], 'Pipeline Name must either be DRP or DAP'

        # bindparam values
        bindname = 'drpver' if pipename.lower() == 'drp' else 'dapver'
        bindvalue = self._drpver if pipename.lower() == 'drp' else self._dapver

        # class names
        if pipename.lower() == 'drp':
            inclasses = self._table_in_query('cube') or 'cube' in str(self.query.statement.compile())
        elif pipename.lower() == 'dap':
            inclasses = self._table_in_query('file') or 'file' in str(self.query.statement.compile())

        # set alias
        pipealias = self._drp_alias if pipename.lower() == 'drp' else self._dap_alias

        # get the pipeinfo
        if inclasses:
            pipeinfo = marvindb.session.query(pipealias).\
                join(marvindb.datadb.PipelineName, marvindb.datadb.PipelineVersion).\
                filter(marvindb.datadb.PipelineName.label == pipename.upper(),
                       marvindb.datadb.PipelineVersion.version == bindparam(bindname, bindvalue)).one()
        else:
            pipeinfo = None

        return pipeinfo