Python sqlalchemy.sql.column() Examples

def columns(self):
        """The set of columns exported by this :class:`.FunctionElement`.

        Function objects currently have no result column names built in;
        this method returns a single-element column collection with
        an anonymously named column.

        An interim approach to providing named columns for a function
        as a FROM clause is to build a :func:`.select` with the
        desired columns::

            from sqlalchemy.sql import column

            stmt = select([column('x'), column('y')]).\
                select_from(func.myfunction())


        """
        return ColumnCollection(self.label(None)) 

def downgrade():
    ''' Remove column access_id from user_projects and projects_groups '''

    # this removes the current constraints as well.
    op.drop_column('user_projects', 'access')
    op.drop_column('projects_groups', 'access')

    # recreate the previous constraints
    op.create_unique_constraint(
            None,
            'user_projects',
            ['project_id', 'user_id'],
    )
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id'],
    )
    op.drop_table('access_levels') 

def test_add_check_constraint_name_is_none(self):
        context = op_fixture(naming_convention={"ck": "ck_%(table_name)s_foo"})
        op.create_check_constraint(
            None, "user_table", func.len(column("name")) > 5
        )
        context.assert_(
            "ALTER TABLE user_table ADD CONSTRAINT ck_user_table_foo "
            "CHECK (len(name) > 5)"
        ) 

def _clone(self):
        """Create a shallow copy of this ClauseElement.

        This method may be used by a generative API.  Its also used as
        part of the "deep" copy afforded by a traversal that combines
        the _copy_internals() method.

        """
        c = self.__class__.__new__(self.__class__)
        c.__dict__ = self.__dict__.copy()
        ClauseElement._cloned_set._reset(c)
        ColumnElement.comparator._reset(c)

        # this is a marker that helps to "equate" clauses to each other
        # when a Select returns its list of FROM clauses.  the cloning
        # process leaves around a lot of remnants of the previous clause
        # typically in the form of column expressions still attached to the
        # old table.
        c._is_clone_of = self

        return c 

def test_add_check_constraint(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        op.create_check_constraint(
            "foo", "user_table", func.len(column("name")) > 5
        )
        context.assert_(
            "ALTER TABLE user_table ADD CONSTRAINT ck_user_table_foo "
            "CHECK (len(name) > 5)"
        ) 

def batch_drop_column(cls, operations, column_name, **kw):
        """Issue a "drop column" instruction using the current
        batch migration context.

        .. seealso::

            :meth:`.Operations.drop_column`

        """
        op = cls(
            operations.impl.table_name,
            column_name,
            schema=operations.impl.schema,
            **kw
        )
        return operations.invoke(op) 

def batch_add_column(
        cls, operations, column, insert_before=None, insert_after=None
    ):
        """Issue an "add column" instruction using the current
        batch migration context.

        .. seealso::

            :meth:`.Operations.add_column`

        """

        kw = {}
        if insert_before:
            kw["insert_before"] = insert_before
        if insert_after:
            kw["insert_after"] = insert_after

        op = cls(
            operations.impl.table_name,
            column,
            schema=operations.impl.schema,
            **kw
        )
        return operations.invoke(op) 

def upgrade():
    # Add collumn redirect_prefix
    op.add_column(
        u'l7policy',
        sa.Column(u'redirect_prefix', sa.String(255), nullable=True)
    )
    insert_table = sql.table(
        u'l7policy_action',
        sql.column(u'name', sa.String),
        sql.column(u'description', sa.String)
    )

    op.bulk_insert(
        insert_table,
        [
            {'name': 'REDIRECT_PREFIX'}
        ]
    ) 

def dimensions(self):
        _dimensions = []
        for dim in self.model_desc.get('simplified_dimensions'):
            table_alias = dim.get('column').split('.')[0]
            table = dict(self._model_lookups).get(table_alias)
            table = table.get('table') if table else self.fact_table.fullname
            table_clz = _Table(table, table_alias)

            column = dim['column'].split('.')[1]
            column_alias = dim['name']
            tbl_map = self.tables_and_columns
            description = dict(tbl_map[table_clz.fullname].get('columns')).get(column)
            if description:
                ke4_dim_id = dim.get('id')
                ke4_dim_status = dim.get('status')
                column_clz = _Column(column, column_alias, description)
                _dimensions.append(_CubeDimension(table_clz, column_clz, ke4_dim_id, ke4_dim_status))
        return _dimensions 

def dimensions(self):
        _dimensions = []
        for dim in self.cube_desc.get('dimensions'):
            table_alias = dim.get('table')
            table = dict(self._model_lookups).get(table_alias)
            table = table.get('table') if table else self.fact_table.fullname
            table_clz = _Table(table, table_alias)

            column = dim['column'] if dim['derived'] is None else dim['derived'][0]
            column_alias = dim['name']
            tbl_map = self.tables_and_columns
            description = dict(tbl_map[table_clz.fullname].get('columns')).get(column)
            column_clz = _Column(column, column_alias, description)

            _dimensions.append(_CubeDimension(table_clz, column_clz))
        return _dimensions 

def _get_selectable(self, engine_or_conn, columns=None):
        import sqlalchemy as sa
        from sqlalchemy import sql
        from sqlalchemy.exc import NoSuchTableError

        # process table_name
        if self._selectable is not None:
            selectable = self._selectable
        else:
            if isinstance(self._table_or_sql, sa.Table):
                selectable = self._table_or_sql
                self._table_or_sql = selectable.name
            else:
                m = sa.MetaData()
                try:
                    selectable = sa.Table(self._table_or_sql, m, autoload=True,
                                          autoload_with=engine_or_conn, schema=self._schema)
                except NoSuchTableError:
                    temp_table_name = 'temp_' + binascii.b2a_hex(uuid.uuid4().bytes).decode()
                    if columns:
                        selectable = sql.text(self._table_or_sql).columns(*[sql.column(c) for c in columns])
                    else:
                        selectable = sql.select(
                            '*', from_obj=sql.text('(%s) AS %s' % (self._table_or_sql, temp_table_name)))
                    self._selectable = selectable
        return selectable 

def exclude_topic_mutes(conditions: List[Selectable],
                        user_profile: UserProfile,
                        stream_id: Optional[int]) -> List[Selectable]:
    query = MutedTopic.objects.filter(
        user_profile=user_profile,
    )

    if stream_id is not None:
        # If we are narrowed to a stream, we can optimize the query
        # by not considering topic mutes outside the stream.
        query = query.filter(stream_id=stream_id)

    query = query.values(
        'recipient_id',
        'topic_name',
    )
    rows = list(query)

    if not rows:
        return conditions

    def mute_cond(row: Dict[str, Any]) -> Selectable:
        recipient_id = row['recipient_id']
        topic_name = row['topic_name']
        stream_cond = column("recipient_id") == recipient_id
        topic_cond = topic_match_sa(topic_name)
        return and_(stream_cond, topic_cond)

    condition = not_(or_(*list(map(mute_cond, rows))))
    return conditions + [condition] 

def _collect_info(self, engine_or_conn, selectable, columns, test_rows):
        from sqlalchemy import sql

        # fetch test DataFrame
        if columns:
            query = sql.select([sql.column(c) for c in columns], from_obj=selectable).limit(test_rows)
        else:
            query = sql.select('*', from_obj=selectable).limit(test_rows)
        test_df = pd.read_sql(query, engine_or_conn, index_col=self._index_col,
                              coerce_float=self._coerce_float,
                              parse_dates=self._parse_dates)
        if len(test_df) == 0:
            self._row_memory_usage = None
        else:
            self._row_memory_usage = \
                test_df.memory_usage(deep=True, index=True).sum() / len(test_df)

        if self._method == 'offset':
            # fetch size
            size = list(engine_or_conn.execute(
                sql.select([sql.func.count()]).select_from(selectable)))[0][0]
            shape = (size, test_df.shape[1])
        else:
            shape = (np.nan, test_df.shape[1])

        return test_df, shape 

def reverse(self):
        return DropColumnOp.from_column_and_tablename(
            self.schema, self.table_name, self.column
        ) 

def topic_match_sa(topic_name: str) -> Any:
    # _sa is short for Sql Alchemy, which we use mostly for
    # queries that search messages
    topic_cond = func.upper(column("subject")) == func.upper(literal(topic_name))
    return topic_cond 

def topic_column_sa() -> Any:
    return column("subject") 

def to_index(self, migration_context=None):
        if self._orig_index is not None:
            return self._orig_index

        schema_obj = schemaobj.SchemaObjects(migration_context)

        # need a dummy column name here since SQLAlchemy
        # 0.7.6 and further raises on Index with no columns
        return schema_obj.index(
            self.index_name,
            self.table_name,
            ["x"],
            schema=self.schema,
            **self.kw
        ) 

def reverse(self):
        if self._orig_column is None:
            raise ValueError(
                "operation is not reversible; "
                "original column is not present"
            )

        return AddColumnOp.from_column_and_tablename(
            self.schema, self.table_name, self._orig_column
        ) 

def test_drop_col(self):
        impl = self._simple_fixture()
        impl.drop_column("tname", column("x"))
        new_table = self._assert_impl(impl, colnames=["id", "y"])
        assert "y" in new_table.c
        assert "x" not in new_table.c 

def name(self):
        return '{}.{}'.format(self.table.alias, self.column.name) 

def value_tables(self):
        _values_columns = self._get_parameter_values(
            self._function.get('parameter'), 'column')
        if _values_columns:
            _columns = _values_columns.split(', ')
            return set(c.split('.')[0] for c in _columns)
        return None 

def __repr__(self):
        return '<Dimension: {}.{}>'.format(self.table.alias, self.column.alias) 

def verbose(self):
        return self.column.alias 

def name(self):
        return '{}.{}'.format(self.table.alias, self.column.name) 

def datatype(self):
        return self.column.datatype 

def from_clause(self):
        _from_clause = self._get_table_clause(self.fact_table)

        for lookup in self.lookups:
            _join_clause_and = []
            for (idx, pk) in enumerate(lookup['join']['primary_key']):
                fk = lookup['join']['foreign_key'][idx]
                fk_table, fk_column = fk.split('.')
                pk_table, pk_column = pk.split('.')
                fk_table_quoted = sql.quoted_name(fk_table, True)
                fk_column_quoted = sql.quoted_name(fk_column, True)
                pk_table_quoted = sql.quoted_name(pk_table, True)
                pk_column_quoted = sql.quoted_name(pk_column, True)

                pk_column = sql.column(fk_column_quoted,
                                       _selectable=sql.table(fk_table_quoted))
                fk_column = sql.column(pk_column_quoted,
                                       _selectable=sql.table(pk_table_quoted))
                _join_clause_and.append(pk_column == fk_column)

            _lookup = _Table(lookup.get('table'), lookup.get('alias'))
            _is_left_join = lookup['join']['type'].lower() == 'left'
            _from_clause = sql.join(
                left=_from_clause,
                right=self._get_table_clause(_lookup),
                onclause=sql.and_(*_join_clause_and),
                isouter=_is_left_join,
            )
        return _from_clause 

def _get_aggregations_exp(self, aggregations_key, column_value):
        """return aggregations expression with the column value"""
        metrics_expression = {
            'COUNT_DISTINCT': 'COUNT (DISTINCT {})'.format(column_value),
            'COUNT': 'COUNT ({})'.format(column_value),
            'SUM': 'SUM ({})'.format(column_value),
            'AVG': 'AVG ({})'.format(column_value),
            'MIN': 'MIN ({})'.format(column_value),
            'MAX': 'MAX ({})'.format(column_value),
        }
        return metrics_expression.get(aggregations_key) 

def value_tables(self):
        _values_columns = self._get_parameter_values(self._description.get('parameter_value'), 'column')
        if _values_columns:
            _columns = _values_columns.split(', ')
            return set(c.split('.')[0] for c in _columns)
        return None 

def __repr__(self):
        return '<Dimension: {}.{}>'.format(self.table.alias, self.column.alias) 

def test_drop_col_remove_fk(self):
        impl = self._fk_fixture()
        impl.drop_column("tname", column("user_id"))
        new_table = self._assert_impl(
            impl, colnames=["id", "email"], ddl_not_contains="FOREIGN KEY"
        )
        assert "user_id" not in new_table.c
        assert not new_table.foreign_keys