Python alembic.op.inline_literal() Examples

def test_inline_literal(self):
        context = op_fixture()
        from sqlalchemy.sql import table, column
        from sqlalchemy import String, Integer

        account = table(
            "account", column("name", String), column("id", Integer)
        )
        op.execute(
            account.update()
            .where(account.c.name == op.inline_literal("account 1"))
            .values({"name": op.inline_literal("account 2")})
        )
        op.execute(
            account.update()
            .where(account.c.id == op.inline_literal(1))
            .values({"id": op.inline_literal(2)})
        )
        context.assert_(
            "UPDATE account SET name='account 2' "
            "WHERE account.name = 'account 1'",
            "UPDATE account SET id=2 WHERE account.id = 1",
        ) 

def test_auto_literals(self):
        context = op_fixture(as_sql=True, literal_binds=True)
        from sqlalchemy.sql import table, column
        from sqlalchemy import String, Integer

        account = table(
            "account", column("name", String), column("id", Integer)
        )
        op.execute(
            account.update()
            .where(account.c.name == op.inline_literal("account 1"))
            .values({"name": op.inline_literal("account 2")})
        )
        op.execute(text("update table set foo=:bar").bindparams(bar="bat"))
        context.assert_(
            "UPDATE account SET name='account 2' "
            "WHERE account.name = 'account 1'",
            "update table set foo='bat'",
        ) 

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_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 upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    query_tbl = sa.sql.table('query', sa.sql.column('platform', sa.String))
    pack_tbl = sa.sql.table('pack', sa.sql.column('platform', sa.String))
    op.execute(
        query_tbl.update() \
            .where(
                sa.or_(
                    query_tbl.c.platform==op.inline_literal('redhat,centos'),
                    query_tbl.c.platform==op.inline_literal('ubuntu'),
                )
            ).values({'platform': op.inline_literal('linux')})
    )
    op.execute(
        pack_tbl.update() \
            .where(
                sa.or_(
                    query_tbl.c.platform==op.inline_literal('redhat,centos'),
                    query_tbl.c.platform==op.inline_literal('ubuntu'),
                )
            ).values({'platform': op.inline_literal('linux')})
    )
    op.add_column('query', sa.Column('shard', sa.Integer(), nullable=True))
    ### end Alembic commands ### 

def downgrade(engine_name):
    hazardtype = HazardType.__table__

    op.execute(
        hazardtype.delete() \
            .where(hazardtype.c.mnemonic==op.inline_literal('UF')))
    op.execute(
        hazardtype.delete() \
            .where(hazardtype.c.mnemonic==op.inline_literal('EH')))
    op.execute(
        hazardtype.delete() \
            .where(hazardtype.c.mnemonic==op.inline_literal('WF')))
    op.execute(
        hazardtype.delete() \
            .where(hazardtype.c.mnemonic==op.inline_literal('AP')))
    pass 

def upgrade(engine_name):

    for htype in ['UF', 'WF', 'AP']:
        for level in ['VLO', 'LOW', 'MED', 'HIG']:
            op.execute(hazardcategory.insert().values(
                hazardtype_id=sa.select(
                    [hazardtype.c.id],
                    hazardtype.c.mnemonic==op.inline_literal(htype)
                ),
                hazardlevel_id=sa.select(
                    [hazardlevel.c.id],
                    hazardlevel.c.mnemonic==op.inline_literal(level)
                ),
                general_recommendation=op.inline_literal(
                    'General recommendation for %s %s' % (htype, level))
            )) 

def upgrade():
    state_enum = sa.Enum(*istate.States.all(), name='node_state')
    state_enum.create(op.get_bind())

    op.add_column('nodes', sa.Column('version_id', sa.String(36),
                                     server_default=''))
    op.add_column('nodes', sa.Column('state', state_enum,
                                     nullable=False,
                                     default=istate.States.finished,
                                     server_default=istate.States.finished))
    # correct the state: finished -> error if Node.error is not null
    stmt = Node.update().where(Node.c.error != sql.null()).values(
        {'state': op.inline_literal(istate.States.error)})
    op.execute(stmt) 

def upgrade():
    op.add_column("resource_type", sa.Column('tablename', sa.String(18),
                                             nullable=True))

    resource_type = sa.Table(
        'resource_type', sa.MetaData(),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('tablename', sa.String(18), nullable=True)
    )
    op.execute(resource_type.update().where(
        resource_type.c.name == "instance_network_interface"
    ).values({'tablename': op.inline_literal("'instance_net_int'")}))
    op.execute(resource_type.update().where(
        resource_type.c.name != "instance_network_interface"
    ).values({'tablename': resource_type.c.name}))

    op.alter_column("resource_type", "tablename", type_=sa.String(18),
                    nullable=False)
    op.create_unique_constraint("uniq_resource_type0tablename",
                                "resource_type", ["tablename"]) 

def downgrade(engine_name):
    hazardtype = HazardType.__table__

    op.execute(
        hazardtype.update() \
            .where(hazardtype.c.mnemonic==op.inline_literal('DG')) \
            .values({'title': op.inline_literal('Drought')}))
    pass 

def upgrade(engine_name):
    hazardtype = HazardType.__table__

    op.execute(
        hazardtype.update() \
            .where(hazardtype.c.mnemonic==op.inline_literal('DG')) \
            .values({'title': op.inline_literal('Water scarcity')}))
    pass 

def downgrade(engine_name):
    op.execute(hazardcategory.delete().where(
        hazardcategory.c.hazardtype_id==sa.select(
            [hazardtype.c.id],
            hazardtype.c.mnemonic==op.inline_literal('EH')
        )
    ))
    pass 

def downgrade(engine_name):
    for htype in ['UF', 'WF', 'AP']:
        op.execute(hazardcategory.delete().where(
            hazardcategory.c.hazardtype_id==sa.select(
                [hazardtype.c.id],
                hazardtype.c.mnemonic==op.inline_literal(htype)
            )
        )) 

def upgrade(engine_name):
    hazardtype = HazardType.__table__
    op.add_column('enum_hazardtype', sa.Column('ready', sa.Boolean(), nullable=True), schema='datamart')
    # Set all hazardtype to ready=True
    op.execute(
        hazardtype.update().values({'ready': True}))
    # Except Air Pollution
    op.execute(
        hazardtype.update().values({'ready': False})
            .where(hazardtype.c.mnemonic==op.inline_literal('AP'))) 

def upgrade():
    # Add columns, but with null allowed
    op.add_column('request', sa.Column('constellation', sa.String(length=25),
            nullable=True))
    op.add_column('request', sa.Column('region', sa.String(length=25),
            nullable=True))
    op.add_column('request', sa.Column('system', sa.String(length=25),
            nullable=True))
    op.create_index('ix_request_constellation', 'request', ['constellation'],
            unique=False)
    op.create_index('ix_request_region', 'request', ['region'], unique=False)
    op.create_index('ix_request_system', 'request', ['system'], unique=False)
    # Update existing requests
    conn = op.get_bind()
    kill_id_sel = select([request.c.id])
    kill_ids = conn.execute(kill_id_sel)
    for kill_id in kill_ids:
        kill_id = kill_id[0]
        system_id = get_system_id(kill_id)
        system = systems.system_names[system_id]
        constellation = systems.systems_constellations[system]
        region = systems.constellations_regions[constellation]
        update_stmt = update(request)\
                .where(request.c.id==op.inline_literal(kill_id))\
                .values({
                    'system': system,
                    'constellation': constellation,
                    'region': region,
                })
        conn.execute(update_stmt)
    kill_ids.close()
    # Add non-null constraint
    op.alter_column('request', 'constellation', nullable=False,
            existing_server_default=None,
            existing_type=sa.String(length=25))
    op.alter_column('request', 'region', nullable=False,
            existing_server_default=None,
            existing_type=sa.String(length=25))
    op.alter_column('request', 'system', nullable=False,
            existing_server_default=None,
            existing_type=sa.String(length=25)) 

def test_cant_op(self):
        if hasattr(op, "_proxy"):
            del op._proxy
        assert_raises_message(
            NameError,
            "Can't invoke function 'inline_literal', as the "
            "proxy object has not yet been established "
            "for the Alembic 'Operations' class.  "
            "Try placing this code inside a callable.",
            op.inline_literal,
            "asdf",
        ) 

def upgrade(engine_name):
    hazardtype = HazardType.__table__

    op.execute(
        hazardtype.insert() \
            .values({'mnemonic': op.inline_literal('UF'),
                     'title': op.inline_literal('Urban flood'),
                     'order': 2}))
    op.execute(
        hazardtype.insert() \
            .values({'mnemonic': op.inline_literal('EH'),
                     'title': op.inline_literal('Extreme heat'),
                     'order': 10}))
    op.execute(
        hazardtype.insert() \
            .values({'mnemonic': op.inline_literal('WF'),
                     'title': op.inline_literal('Wildfire'),
                     'order': 11}))
    op.execute(
        hazardtype.insert() \
            .values({'mnemonic': op.inline_literal('AP'),
                     'title': op.inline_literal('Air pollution'),
                     'order': 12}))
    op.execute(
        hazardtype.update().values({'order': 3}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('CF')))
    op.execute(
        hazardtype.update().values({'order': 4}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('EQ')))
    op.execute(
        hazardtype.update().values({'order': 5}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('LS')))
    op.execute(
        hazardtype.update().values({'order': 6}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('TS')))
    op.execute(
        hazardtype.update().values({'order': 7}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('VO')))
    op.execute(
        hazardtype.update().values({'order': 8}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('CY')))
    op.execute(
        hazardtype.update().values({'order': 9}) \
            .where(hazardtype.c.mnemonic==op.inline_literal('DG')))
    pass