Python sqlalchemy.sql.table() Examples

def upgrade():
    relative_modifier = table('relative_modifier',
            column('id', sa.Integer),
            column('value', sa.Float),
            column('numeric_value', sa.Numeric(precision=8, scale=5)))
    op.add_column('relative_modifier',
            sa.Column('numeric_value', sa.Numeric(precision=8, scale=5)))
    conn = op.get_bind()
    sel = select([relative_modifier.c.id, relative_modifier.c.value])
    results = conn.execute(sel)
    q = Decimal(10) ** -5
    for id_, float_value in results:
        decimal_value = Decimal(float_value).quantize(q)
        up = update(relative_modifier).where(relative_modifier.c.id == id_)\
                .values({'numeric_value': decimal_value})
        conn.execute(up)
    op.drop_column('relative_modifier', 'value')
    op.alter_column('relative_modifier', 'numeric_value', nullable=True,
            new_column_name='value', existing_type=sa.Numeric(precision=8,
                    scale=5)) 

def test_bulk_insert_as_sql_mssql(self):
        context = self._test_bulk_insert("mssql", True)
        # SQL server requires IDENTITY_INSERT
        # TODO: figure out if this is safe to enable for a table that
        # doesn't have an IDENTITY column
        context.assert_(
            "SET IDENTITY_INSERT ins_table ON",
            "GO",
            "INSERT INTO ins_table (id, v1, v2) "
            "VALUES (1, 'row v1', 'row v5')",
            "GO",
            "INSERT INTO ins_table (id, v1, v2) "
            "VALUES (2, 'row v2', 'row v6')",
            "GO",
            "INSERT INTO ins_table (id, v1, v2) "
            "VALUES (3, 'row v3', 'row v7')",
            "GO",
            "INSERT INTO ins_table (id, v1, v2) "
            "VALUES (4, 'row v4', 'row v8')",
            "GO",
            "SET IDENTITY_INSERT ins_table OFF",
            "GO",
        ) 

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 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 _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 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 api_private_runs_by_month():
    # The query takes ~6s on local SSD @ AMS on 2018-04-04.
    # It was taking ~20s when it was fetching all the table from DB and doing grouping locally.
    # TODO: use-count-table
    # FIXME: support fastpath
    now = datetime.now()
    end_date = datetime(now.year, now.month, 1)
    start_date = end_date - relativedelta(months=24)
    rawsql = """SELECT
        date_trunc('month', report.test_start_time) AS test_start_month,
        count(*) AS count_1
        FROM report
        WHERE report.test_start_time >= :start_date
        AND report.test_start_time < :end_date
        GROUP BY test_start_month
    """
    params = dict(start_date=start_date, end_date=end_date)
    q = current_app.db_session.execute(rawsql, params)
    delta = relativedelta(months=+1, days=-1)
    result = [
        {"date": (bkt + delta).strftime("%Y-%m-%d"), "value": value}
        for bkt, value in sorted(q.fetchall())
    ]
    return jsonify(result) 

def setUp(self):

        convention = {
            "ix": "ix_%(custom)s_%(column_0_label)s",
            "uq": "uq_%(custom)s_%(table_name)s_%(column_0_name)s",
            "ck": "ck_%(custom)s_%(table_name)s",
            "fk": "fk_%(custom)s_%(table_name)s_"
            "%(column_0_name)s_%(referred_table_name)s",
            "pk": "pk_%(custom)s_%(table_name)s",
            "custom": lambda const, table: "ct",
        }

        self.metadata = MetaData(naming_convention=convention)

        ctx_opts = {
            "sqlalchemy_module_prefix": "sa.",
            "alembic_module_prefix": "op.",
            "target_metadata": MetaData(),
        }
        context = MigrationContext.configure(
            dialect_name="postgresql", opts=ctx_opts
        )
        self.autogen_context = api.AutogenContext(context) 

def test_render_table_w_unsupported_constraint(self):
        from sqlalchemy.sql.schema import ColumnCollectionConstraint

        class SomeCustomConstraint(ColumnCollectionConstraint):
            __visit_name__ = "some_custom"

        m = MetaData()

        t = Table("t", m, Column("id", Integer), SomeCustomConstraint("id"))
        op_obj = ops.CreateTableOp.from_table(t)
        with assertions.expect_warnings(
            "No renderer is established for object SomeCustomConstraint"
        ):
            eq_ignore_whitespace(
                autogenerate.render_op_text(self.autogen_context, op_obj),
                "op.create_table('t',"
                "sa.Column('id', sa.Integer(), nullable=True),"
                "[Unknown Python object "
                "SomeCustomConstraint(Column('id', Integer(), table=<t>))])",
            ) 

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 upgrade():
    # re-size existing data if necessary
    identifier_map = table('cisco_csr_identifier_map',
                           column('ipsec_site_conn_id', sa.String(36)))
    ipsec_site_conn_id = identifier_map.columns['ipsec_site_conn_id']

    op.execute(identifier_map.update(values={
        ipsec_site_conn_id: expr.case([(func.length(ipsec_site_conn_id) > 36,
                                      func.substr(ipsec_site_conn_id, 1, 36))],
                                      else_=ipsec_site_conn_id)}))

    # Need to drop foreign key constraint before mysql will allow changes

    with migration.remove_fks_from_table('cisco_csr_identifier_map'):
        op.alter_column(table_name='cisco_csr_identifier_map',
                        column_name='ipsec_site_conn_id',
                        type_=sa.String(36),
                        existing_nullable=False) 

def setup_class(cls):
        cls.bind = config.db
        with config.db.connect() as conn:
            conn.execute(
                text(
                    """
                create table tab (
                    col varchar(50)
                )
            """
                )
            )
            conn.execute(
                text(
                    """
                insert into tab (col) values
                    ('old data 1'),
                    ('old data 2.1'),
                    ('old data 3')
            """
                )
            ) 

def test_bulk_insert_inline_literal(self):
        class MyType(TypeEngine):
            pass

        t1 = table("foo", column("id", Integer), column("data", MyType()))

        self.op.bulk_insert(
            t1,
            [
                {"id": 1, "data": self.op.inline_literal("d1")},
                {"id": 2, "data": self.op.inline_literal("d2")},
            ],
            multiinsert=False,
        )

        eq_(
            self.conn.execute(text("select id, data from foo")).fetchall(),
            [(1, "d1"), (2, "d2")],
        ) 

def setUp(self):
        self.conn = config.db.connect()
        self.conn.execute(
            text(
                """
            create table foo(
                id integer primary key,
                data varchar(50),
                x integer
            )
        """
            )
        )
        context = MigrationContext.configure(self.conn)
        self.op = op.Operations(context)
        self.t1 = table("foo", column("id"), column("data"), column("x")) 

def test_bulk_insert_inline_literal_as_sql(self):
        context = op_fixture("postgresql", True)

        class MyType(TypeEngine):
            pass

        t1 = table("t", column("id", Integer), column("data", MyType()))

        op.bulk_insert(
            t1,
            [
                {"id": 1, "data": op.inline_literal("d1")},
                {"id": 2, "data": op.inline_literal("d2")},
            ],
        )
        context.assert_(
            "INSERT INTO t (id, data) VALUES (1, 'd1')",
            "INSERT INTO t (id, data) VALUES (2, 'd2')",
        ) 

def test_render_executesql_sqlexpr_notimplemented(self):
        sql = table("x", column("q")).insert()
        op_obj = ops.ExecuteSQLOp(sql)
        assert_raises(
            NotImplementedError,
            autogenerate.render_op_text,
            self.autogen_context,
            op_obj,
        ) 

def test_render_executesql_plaintext(self):
        op_obj = ops.ExecuteSQLOp("drop table foo")
        eq_(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.execute('drop table foo')",
        ) 

def _get_table(self):
        return self.__dict__['table'] 

def _from_objects(self):
        t = self.table
        if t is not None:
            return [t]
        else:
            return [] 

def get_recent_network_coverage(probe_cc, test_groups):
    where_clause = [
        sql.text("test_day >= current_date - interval '31 day'"),
        sql.text("test_day < current_date"),
        sql.text("probe_cc = :probe_cc"),
    ]
    if test_groups is not None:
        tg_or = []
        for tg in test_groups:
            try:
                tg_names = TEST_GROUPS[tg]
                tg_or += [
                    sql.literal_column("test_name") == tg_name for tg_name in tg_names
                ]
            except KeyError:
                raise BadRequest("invalid test_group")
        where_clause.append(or_(*tg_or))

    s = (
        select([sql.text("COUNT(DISTINCT probe_asn)"), sql.text("test_day")])
        .where(and_(*where_clause))
        .group_by(sql.text("test_day"))
        .order_by(sql.text("test_day"))
        .select_from(sql.table("ooexpl_daily_msm_count"))
    )

    network_map = {k: 0 for k in TEST_GROUPS.keys()}
    q = current_app.db_session.execute(s, {"probe_cc": probe_cc})
    for count, date in q:
        network_map[date.strftime("%Y-%m-%d")] = count

    network_coverage = []
    for test_day in last_30days():
        network_coverage.append(
            {"count": network_map.get(test_day, 0), "test_day": test_day}
        )
    return network_coverage 

def teardown_class(cls):
        with cls.bind.connect() as conn:
            conn.execute(text("drop table tab")) 

def _render_label_in_columns_clause(self):
        return self.table is not None 

def _gen_label(self, name):
        t = self.table

        if self.is_literal:
            return None

        elif t is not None and t.named_with_column:
            if getattr(t, 'schema', None):
                label = t.schema.replace('.', '_') + "_" + \
                    t.name + "_" + name
            else:
                label = t.name + "_" + name

            # propagate name quoting rules for labels.
            if getattr(name, "quote", None) is not None:
                if isinstance(label, quoted_name):
                    label.quote = name.quote
                else:
                    label = quoted_name(label, name.quote)
            elif getattr(t.name, "quote", None) is not None:
                # can't get this situation to occur, so let's
                # assert false on it for now
                assert not isinstance(label, quoted_name)
                label = quoted_name(label, t.name.quote)

            # ensure the label name doesn't conflict with that
            # of an existing column
            if label in t.c:
                _label = label
                counter = 1
                while _label in t.c:
                    _label = label + "_" + str(counter)
                    counter += 1
                label = _label

            return _as_truncated(label)

        else:
            return name 

def test_drop_table_comment(self):
        # this is handled by SQLAlchemy's compilers
        context = op_fixture("postgresql")
        op.drop_table_comment("t2", existing_comment="t2 table", schema="foo")
        context.assert_("COMMENT ON TABLE foo.t2 IS NULL") 

def test_create_table_comment(self):
        # this is handled by SQLAlchemy's compilers
        context = op_fixture("postgresql")
        op.create_table_comment("t2", comment="t2 table", schema="foo")
        context.assert_("COMMENT ON TABLE foo.t2 IS 't2 table'") 

def _corresponding_column_or_error(fromclause, column,
                                   require_embedded=False):
    c = fromclause.corresponding_column(column,
                                        require_embedded=require_embedded)
    if c is None:
        raise exc.InvalidRequestError(
            "Given column '%s', attached to table '%s', "
            "failed to locate a corresponding column from table '%s'"
            %
            (column,
             getattr(column, 'table', None),
             fromclause.description)
        )
    return c 

def __init__(self, element, values):
        Annotated.__init__(self, element, values)
        ColumnElement.comparator._reset(self)
        for attr in ('name', 'key', 'table'):
            if self.__dict__.get(attr, False) is None:
                self.__dict__.pop(attr) 

def define_tables(cls, metadata):
        cls.tables.percent_table = Table('percent%table', metadata,
                                         Column("percent%", Integer),
                                         Column(
                                             "spaces % more spaces", Integer),
                                         )
        cls.tables.lightweight_percent_table = sql.table(
            'percent%table', sql.column("percent%"),
            sql.column("spaces % more spaces")
        ) 

def __init__(self, *clauses, **kw):
        """Return a :class:`.Tuple`.

        Main usage is to produce a composite IN construct::

            from sqlalchemy import tuple_

            tuple_(table.c.col1, table.c.col2).in_(
                [(1, 2), (5, 12), (10, 19)]
            )

        .. warning::

            The composite IN construct is not supported by all backends,
            and is currently known to work on Postgresql and MySQL,
            but not SQLite.   Unsupported backends will raise
            a subclass of :class:`~sqlalchemy.exc.DBAPIError` when such
            an expression is invoked.

        """

        clauses = [_literal_as_binds(c) for c in clauses]
        self._type_tuple = [arg.type for arg in clauses]
        self.type = kw.pop('type_', self._type_tuple[0]
                           if self._type_tuple else type_api.NULLTYPE)

        super(Tuple, self).__init__(*clauses, **kw) 

def upgrade():
    op.add_column('request',
            sa.Column('numeric_base_payout', sa.Numeric(precision=15, scale=2),
                    default=0.0)
    )
    request = table('request',
            column('id', sa.Integer),
            column('base_payout', sa.Float),
            column('numeric_base_payout', sa.Numeric(precision=15, scale=2)),
    )
    conn = op.get_bind()
    requests_sel = select([request.c.id, request.c.base_payout])
    requests = conn.execute(requests_sel)
    for request_id, float_payout in requests:
        decimal_payout = Decimal.from_float(float_payout)
        decimal_payout *= 1000000
        update_stmt = update(request)\
                .where(request.c.id == request_id)\
                .values({
                    'numeric_base_payout': decimal_payout,
                })
        conn.execute(update_stmt)
    requests.close()
    op.drop_column('request', 'base_payout')
    op.alter_column('request',
            column_name='numeric_base_payout',
            new_column_name='base_payout',
            existing_type=sa.Numeric(precision=15, scale=2),
            existing_server_default=0.0)