Python sql.bindparam方法代碼示例

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _assemble_tasks(self, task_list=None):
        """Attach all the appropriate result messages to the tasks in the list.

        :param task_list: a list of objects.Task instances to attach result messages to
        """
        if task_list is None:
            return None

        with self.db_engine.connect() as conn:
            query = sql.select([
                self.result_message_tbl
            ]).where(self.result_message_tbl.c.task_id == sql.bindparam(
                'task_id')).order_by(self.result_message_tbl.c.sequence.asc())
            query.compile(self.db_engine)

            for t in task_list:
                rs = conn.execute(query, task_id=t.task_id.bytes)
                error_count = 0
                for r in rs:
                    msg = objects.TaskStatusMessage.from_db(dict(r))
                    if msg.error:
                        error_count = error_count + 1
                    t.result.message_list.append(msg)
                t.result.error_count = error_count 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def add_tags(context, tags):
    session = get_session()
    get_query = (model_query(context, models.Tag, session=session).
                 filter_by(project_id=context.project_id,
                           # NOTE(ft): item_id param name is reserved for
                           # sqlalchemy internal use
                           item_id=bindparam('tag_item_id'),
                           key=bindparam('tag_key')))
    with session.begin():
        for tag in tags:
            tag_ref = models.Tag(project_id=context.project_id,
                                 item_id=tag['item_id'],
                                 key=tag['key'],
                                 value=tag['value'])
            try:
                with session.begin(nested=True):
                    tag_ref.save(session)
            except db_exception.DBDuplicateEntry as ex:
                if ('PRIMARY' not in ex.columns and
                        ex.columns != ['project_id', 'item_id', 'key']):
                    raise
                (get_query.params(tag_item_id=tag['item_id'],
                                  tag_key=tag['key']).
                 update({'value': tag['value']})) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def returning_clause(self, stmt, returning_cols):
        self.returning_cols = list(
            expression._select_iterables(returning_cols))

        # within_columns_clause=False so that labels (foo AS bar) don't render
        columns = [self.process(c, within_columns_clause=False)
                   for c in self.returning_cols]

        if not hasattr(self, 'returning_parameters'):
            self.returning_parameters = []

        binds = []
        for i, col in enumerate(self.returning_cols):
            dbtype = col.type.dialect_impl(
                self.dialect).get_dbapi_type(self.dialect.dbapi)
            self.returning_parameters.append((i + 1, dbtype))

            bindparam = sql.bindparam(
                "ret_%d" % i, value=ReturningParam(dbtype))
            self.binds[bindparam.key] = bindparam
            binds.append(
                self.bindparam_string(self._truncate_bindparam(bindparam)))

        return 'RETURNING ' + ', '.join(columns) + " INTO " + ", ".join(binds) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def returning_clause(self, stmt, returning_cols):
        self.returning_cols = list(
            expression._select_iterables(returning_cols))

        # within_columns_clause=False so that labels (foo AS bar) don't render
        columns = [self.process(c, within_columns_clause=False,
                                result_map=self.result_map)
                   for c in self.returning_cols]

        if not hasattr(self, 'returning_parameters'):
            self.returning_parameters = []

        binds = []
        for i, col in enumerate(self.returning_cols):
            dbtype = col.type.dialect_impl(
                self.dialect).get_dbapi_type(self.dialect.dbapi)
            self.returning_parameters.append((i + 1, dbtype))

            bindparam = sql.bindparam(
                "ret_%d" % i, value=ReturningParam(dbtype))
            self.binds[bindparam.key] = bindparam
            binds.append(
                self.bindparam_string(self._truncate_bindparam(bindparam)))

        return 'RETURNING ' + ', '.join(columns) + " INTO " + ", ".join(binds) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def returning_clause(self, stmt, returning_cols):
        self.returning_cols = list(expression._select_iterables(returning_cols))

        # within_columns_clause=False so that labels (foo AS bar) don't render
        columns = [self.process(c, within_columns_clause=False, result_map=self.result_map)
                   for c in self.returning_cols]

        if not hasattr(self, 'returning_parameters'):
            self.returning_parameters = []

        binds = []
        for i, col in enumerate(self.returning_cols):
            dbtype = col.type.dialect_impl(self.dialect).get_dbapi_type(self.dialect.dbapi)
            self.returning_parameters.append((i + 1, dbtype))

            bindparam = sql.bindparam("ret_%d" % i, value=ReturningParam(dbtype))
            self.binds[bindparam.key] = bindparam
            binds.append(self.bindparam_string(self._truncate_bindparam(bindparam)))

        return 'RETURNING ' + ', '.join(columns) + " INTO " + ", ".join(binds) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def get_columns(self, connection, table_name, schema=None, **kw):
        SQL_COLS = """
            SELECT CAST(a.attname AS VARCHAR(128)) as name,
                   a.atttypid as typeid,
                   not a.attnotnull as nullable,
                   a.attcolleng as length,
                   a.format_type
            FROM _v_relation_column a
            WHERE a.name = :tablename
            ORDER BY a.attnum
        """
        s = text(SQL_COLS,
                 bindparams=[bindparam('tablename', type_=sqltypes.String)],
                 typemap={'name': NAME,
                          'typeid': sqltypes.Integer,
                          'nullable': sqltypes.Boolean,
                          'length': sqltypes.Integer,
                          'format_type': sqltypes.String,
                          })
        c = connection.execute(s, tablename=table_name)
        rows = c.fetchall()
        # format columns
        columns = []
        for name, typeid, nullable, length, format_type in rows:
            coltype_class, has_length = oid_datatype_map[typeid]
            if coltype_class is sqltypes.Numeric:
                precision, scale = re.match(
                    r'numeric\((\d+),(\d+)\)', format_type).groups()
                coltype = coltype_class(int(precision), int(scale))
            elif has_length:
                coltype = coltype_class(length)
            else:
                coltype = coltype_class()
            columns.append({
                'name': name,
                'type': coltype,
                'nullable': nullable,
            })
        return columns 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _assemble_tasks(self, task_list=None):
        """Attach all the appropriate result messages to the tasks in the list.

        :param task_list: a list of objects.Task instances to attach result messages to
        """
        if task_list is None:
            return None

        conn = self.db_engine.connect()
        query = sql.select([
            self.result_message_tbl
        ]).where(self.result_message_tbl.c.task_id == sql.bindparam(
            'task_id')).order_by(self.result_message_tbl.c.sequence.asc())
        query.compile(self.db_engine)

        for t in task_list:
            rs = conn.execute(query, task_id=t.task_id.bytes)
            error_count = 0
            for r in rs:
                msg = objects.TaskStatusMessage.from_db(dict(r))
                if msg.error:
                    error_count = error_count + 1
                t.result.message_list.append(msg)
            t.result.error_count = error_count

        conn.close() 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _create_table_version(cls, engine, repository, version):
        """
        Creates the versioning table in a database.

        :raises: :exc:`DatabaseAlreadyControlledError`
        """
        # Create tables
        tname = repository.version_table
        meta = MetaData(engine)

        table = Table(
            tname, meta,
            Column('repository_id', String(250), primary_key=True),
            Column('repository_path', Text),
            Column('version', Integer), )

        # there can be multiple repositories/schemas in the same db
        if not table.exists():
            table.create()

        # tests for existing repository_id
        s = table.select(table.c.repository_id == bindparam("repository_id"))
        result = engine.execute(s, repository_id=repository.id)
        if result.fetchone():
            raise exceptions.DatabaseAlreadyControlledError

        # Insert data
        engine.execute(table.insert().values(
                           repository_id=repository.id,
                           repository_path=repository.path,
                           version=int(version)))
        return table 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
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 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def test_literal_binds(self):
        def fixture():
            return (
                bindparam(None, value="x", literal_execute=True),
                bindparam(None, value="y", literal_execute=True),
            )

        self._run_cache_key_fixture(
            fixture, True,
        ) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def test_compare_binds(self):
        b1 = bindparam("foo", type_=Integer())
        b2 = bindparam("foo", type_=Integer())
        b3 = bindparam("foo", type_=String())

        def c1():
            return 5

        def c2():
            return 6

        b4 = bindparam("foo", type_=Integer(), callable_=c1)
        b5 = bindparam("foo", type_=Integer(), callable_=c2)
        b6 = bindparam("foo", type_=Integer(), callable_=c1)

        b7 = bindparam("foo", type_=Integer, value=5)
        b8 = bindparam("foo", type_=Integer, value=6)

        is_false(b1.compare(b4))
        is_true(b4.compare(b6))
        is_false(b4.compare(b5))
        is_true(b1.compare(b2))

        # currently not comparing "key", as we often have to compare
        # anonymous names.  however we should really check for that
        # is_true(b1.compare(b3))

        is_false(b1.compare(b3))
        is_false(b1.compare(b7))
        is_false(b7.compare(b8))
        is_true(b7.compare(b7)) 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def upgrade():
    op.execute("""\
    CREATE INDEX tmp ON dependency(name,epoch,version,release,arch);
    CREATE TABLE deptmp AS SELECT DISTINCT build_id, min(id) OVER (PARTITION BY name, epoch, version, release, arch) AS newid FROM dependency;
    ALTER TABLE build ADD COLUMN dependency_array integer[];
    UPDATE build SET dependency_array = a FROM (SELECT build_id, array_agg(newid ORDER BY newid) AS a FROM deptmp GROUP BY build_id) AS q WHERE q.build_id = build.id;
    DROP INDEX tmp;
    DROP INDEX ix_dependency_build_id;
    CREATE TABLE deptmp2 AS SELECT DISTINCT newid FROM deptmp;
    DELETE FROM dependency WHERE NOT EXISTS (SELECT 1 FROM deptmp2 WHERE deptmp2.newid=dependency.id);
    ALTER TABLE dependency DROP COLUMN build_id;
    ALTER TABLE dependency DROP COLUMN distance;
    DROP TABLE deptmp;
    DROP TABLE deptmp2;
    ALTER TABLE build ADD COLUMN dependency_keys bytea;
    CREATE UNIQUE INDEX ix_dependency_composite ON dependency(name, epoch, version, release, arch);
    """)
    connection = op.get_bind()
    build_table = sa.Table('build', sa.MetaData(),
                           sa.Column('id', sa.Integer, primary_key=True),
                           sa.Column('dependency_array', ARRAY(sa.Integer)),
                           sa.Column('dependency_keys', CompressedKeyArray))
    updated = []

    def persist():
        connection.execute(build_table.update().where(build_table.c.id == bindparam('i'))\
                           .values({'id': bindparam('i'), 'dependency_keys': bindparam('d')}),
                           updated)
        updated[:] = []

    for row in connection.execution_options(stream_results=True)\
            .execute("SELECT id, dependency_array FROM build WHERE dependency_array IS NOT NULL"):
        updated.append({'i': row.id, 'd': row.dependency_array})
        if len(updated) > 1000:
            persist()
    if updated:
        persist()
    op.execute("ALTER TABLE build DROP COLUMN dependency_array") 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _get_all_results(self, optimization_ids: List[Union[int, str]] = None):
        """Returns all the results objects (trajectory) of each optmization
        Returns list(list) """

        if optimization_ids is None:
            self._raise_missing_attribute("all_results", "List of optimizations ids")

        # row_to_json(result.*)
        sql_statement = text(
            """
            select * from base_result
            join (
                select opt_id, result.* from result
                join opt_result_association as traj
                on result.id = traj.result_id
                where traj.opt_id in :optimization_ids
            ) result
            on base_result.id = result.id
        """
        )

        # bind and expand ids list
        sql_statement = sql_statement.bindparams(bindparam("optimization_ids", expanding=True))

        # column types:
        columns = inspect(ResultORM).columns
        sql_statement = sql_statement.columns(opt_id=Integer, *columns)
        query_result = self.execute_query(sql_statement, optimization_ids=list(optimization_ids))

        ret = {}
        for rec in query_result:
            self._remove_excluded_keys(rec)
            key = rec.pop("opt_id")
            if key not in ret:
                ret[key] = []

            ret[key].append(ResultRecord(**rec))

        return ret 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _get_final_results(self, optimization_ids: List[Union[int, str]] = None):
        """Return the actual results objects of the best result in each optimization"""

        if optimization_ids is None:
            self._raise_missing_attribute("final_result", "List of optimizations ids")

        sql_statement = text(
            """
            select * from base_result
            join (
                select opt_id, result.* from result
                join (
                    select opt.opt_id, opt.result_id, max_pos from opt_result_association as opt
                    inner join (
                            select opt_id, max(position) as max_pos from opt_result_association
                            where opt_id in :optimization_ids
                            group by opt_id
                        ) opt2
                    on opt.opt_id = opt2.opt_id and opt.position = opt2.max_pos
                ) traj
                on result.id = traj.result_id
            ) result
            on base_result.id = result.id
        """
        )

        # bind and expand ids list
        sql_statement = sql_statement.bindparams(bindparam("optimization_ids", expanding=True))

        # column types:
        columns = inspect(ResultORM).columns
        sql_statement = sql_statement.columns(opt_id=Integer, *columns)
        query_result = self.execute_query(sql_statement, optimization_ids=list(optimization_ids))

        ret = {}
        for rec in query_result:
            self._remove_excluded_keys(rec)
            key = rec.pop("opt_id")
            ret[key] = ResultRecord(**rec)

        return ret 

# 需要導入模塊: from sqlalchemy import sql [as 別名]
# 或者: from sqlalchemy.sql import bindparam [as 別名]
def _get_initial_molecules(self, optimization_ids=None):

        if optimization_ids is None:
            self._raise_missing_attribute("initial_molecule", "List of optimizations ids")

        sql_statement = text(
            """
                select opt.id as opt_id, molecule.* from molecule
                join optimization_procedure as opt
                on molecule.id = opt.initial_molecule
                where opt.id in :optimization_ids
        """
        )

        # bind and expand ids list
        sql_statement = sql_statement.bindparams(bindparam("optimization_ids", expanding=True))

        # column types:
        columns = inspect(MoleculeORM).columns
        sql_statement = sql_statement.columns(opt_id=Integer, *columns)
        query_result = self.execute_query(sql_statement, optimization_ids=list(optimization_ids))

        ret = {}
        for rec in query_result:
            self._remove_excluded_keys(rec)
            key = rec.pop("opt_id")
            rec = {k: v for k, v in rec.items() if v is not None}
            ret[key] = Molecule(**rec)

        return ret