Python sqlalchemy.sql.update() 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 downgrade():
    relative_modifier = table('relative_modifier',
            column('id', sa.Integer),
            column('value', sa.Numeric(precision=8, scale=5)),
            column('float_value', sa.Float))
    op.add_column('relative_modifier', sa.Column('float_value', sa.Float))
    conn = op.get_bind()
    sel = select([relative_modifier.c.id, relative_modifier.c.value])
    results = conn.execute(sel)
    for id_, decimal_value in results:
        float_value = float(decimal_value)
        up = update(relative_modifier).where(relative_modifier.c.id == id_)\
                .values({'float_value': float_value})
        conn.execute(up)
    op.drop_column('relative_modifier', 'value')
    op.alter_column('relative_modifier', 'float_value', nullable=True,
            new_column_name='value', existing_type=sa.Float) 

def from_schedule(cls, session, schedule):
        spec = {
            'minute': schedule._orig_minute,
            'hour': schedule._orig_hour,
            'day_of_week': schedule._orig_day_of_week,
            'day_of_month': schedule._orig_day_of_month,
            'month_of_year': schedule._orig_month_of_year,
        }
        if schedule.tz:
            spec.update({
                'timezone': schedule.tz.zone
            })
        model = session.query(CrontabSchedule).filter_by(**spec).first()
        if not model:
            model = cls(**spec)
            session.add(model)
            session.commit()
        return model 

def _pk_strategy_refetch(query, mapper, values, surrogate_key):

    surrogate_key_name, surrogate_key_value = surrogate_key
    surrogate_key_col = mapper.attrs[surrogate_key_name].expression

    rowcount = query.\
        filter(surrogate_key_col == surrogate_key_value).\
        update(values, synchronize_session=False)

    _assert_single_row(rowcount)
    # SELECT my_table.id AS my_table_id FROM my_table
    # WHERE my_table.y = ? AND my_table.z = ?
    # LIMIT ? OFFSET ?
    fetch_query = query.session.query(
        *mapper.primary_key).filter(
        surrogate_key_col == surrogate_key_value)

    primary_key = fetch_query.one()

    return primary_key 

def test_evaluate_hybrid_attr_indirect_w_update_expr(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.uname: "moonbeam sunshine"}, synchronize_session="evaluate"
        )
        eq_(jill.uname, "moonbeam sunshine") 

def update(self, **kw):
        for attr, value in kw.items():
            setattr(self, attr, value)
        return self 

def test_fetch_hybrid_attr_w_update_expr(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.name: "moonbeam sunshine"}, synchronize_session="fetch"
        )
        eq_(jill.name, "moonbeam sunshine") 

def test_evaluate_hybrid_attr_w_update_expr(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.name: "moonbeam sunshine"}, synchronize_session="evaluate"
        )
        eq_(jill.name, "moonbeam sunshine") 

def test_fetch_hybrid_attr_indirect(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.fname2: "moonbeam"}, synchronize_session="fetch"
        )
        eq_(jill.fname2, "moonbeam") 

def test_evaluate_hybrid_attr_plain(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.fname: "moonbeam"}, synchronize_session="evaluate"
        )
        eq_(jill.fname, "moonbeam") 

def test_evaluate_hybrid_attr_indirect(self):
        Person = self.classes.Person

        s = Session()
        jill = s.query(Person).get(3)

        s.query(Person).update(
            {Person.fname2: "moonbeam"}, synchronize_session="evaluate"
        )
        eq_(jill.fname2, "moonbeam") 

def test_update_expr(self):
        Person = self.classes.Person

        statement = update(Person).values({Person.name: "Dr. No"})

        self.assert_compile(
            statement,
            "UPDATE person SET first_name=:first_name, last_name=:last_name",
            params={"first_name": "Dr.", "last_name": "No"},
        ) 

def test_update_plain(self):
        Person = self.classes.Person

        statement = update(Person).values({Person.fname: "Dr."})

        self.assert_compile(
            statement,
            "UPDATE person SET first_name=:first_name",
            params={"first_name": "Dr."},
        ) 

def _update_stmt_from_query(mapper, query, values):
    upd_values = dict(
        (
            mapper.column_attrs[key], value
        ) for key, value in values.items()
    )
    query = query.enable_eagerloads(False)
    context = query._compile_context()
    primary_table = context.statement.froms[0]
    update_stmt = sql.update(primary_table,
                             context.whereclause,
                             upd_values)
    return update_stmt 

def save_logbook(self, book):
        try:
            logbooks = self._tables.logbooks
            with self._engine.begin() as conn:
                q = (sql.select([logbooks]).
                     where(logbooks.c.uuid == book.uuid))
                row = conn.execute(q).first()
                if row:
                    e_lb = self._converter.convert_book(row)
                    self._converter.populate_book(conn, e_lb)
                    e_lb.merge(book)
                    conn.execute(sql.update(logbooks)
                                 .where(logbooks.c.uuid == e_lb.uuid)
                                 .values(e_lb.to_dict()))
                    for fd in book:
                        e_fd = e_lb.find(fd.uuid)
                        if e_fd is None:
                            e_lb.add(fd)
                            self._insert_flow_details(conn, fd, e_lb.uuid)
                        else:
                            self._update_flow_details(conn, fd, e_fd)
                    return e_lb
                else:
                    conn.execute(sql.insert(logbooks, book.to_dict()))
                    for fd in book:
                        self._insert_flow_details(conn, fd, book.uuid)
                    return book
        except sa_exc.DBAPIError:
            exc.raise_with_cause(
                exc.StorageFailure,
                "Failed saving logbook '%s'" % book.uuid) 

def _update_flow_details(self, conn, fd, e_fd):
        e_fd.merge(fd)
        conn.execute(sql.update(self._tables.flowdetails)
                     .where(self._tables.flowdetails.c.uuid == e_fd.uuid)
                     .values(e_fd.to_dict()))
        for ad in fd:
            e_ad = e_fd.find(ad.uuid)
            if e_ad is None:
                e_fd.add(ad)
                self._insert_atom_details(conn, ad, fd.uuid)
            else:
                self._update_atom_details(conn, ad, e_ad) 

def _update_atom_details(self, conn, ad, e_ad):
        e_ad.merge(ad)
        conn.execute(sql.update(self._tables.atomdetails)
                     .where(self._tables.atomdetails.c.uuid == e_ad.uuid)
                     .values(e_ad.to_dict())) 

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 upgrade():
    conn = op.get_bind()
    negative_base_payout_id_sel = select([request.c.id])\
            .where(request.c.base_payout < 0.0)
    negative_ids = conn.execute(negative_base_payout_id_sel)
    for result_row in negative_ids:
        negative_id = result_row[0]
        update_stmt = update(request)\
                .where(request.c.id == negative_id)\
                .values({
                        'base_payout': 0.0,
                })
        conn.execute(update_stmt)
    negative_ids.close() 

def downgrade():
    op.add_column('request',
            sa.Column('float_base_payout', sa.Float, default=0.0)
    )
    request = table('request',
            column('id', sa.Integer),
            column('base_payout', sa.Numeric(precision=15, scale=2)),
            column('float_base_payout', sa.Float),
    )
    conn = op.get_bind()
    requests_sel = select([request.c.id, request.c.base_payout])
    requests = conn.execute(requests_sel)
    for request_id, decimal_payout in requests:
        decimal_payout = decimal_payout / 1000000
        float_payout = float(decimal_payout)
        update_stmt = update(request)\
                .where(request.c.id == request_id)\
                .values({
                    'float_base_payout': float_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.Float,
            existing_server_default=0.0) 

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) 

def downgrade():
    # Add type_ and value columns back
    op.add_column('modifier',
            sa.Column('type_', sa.Enum('absolute', 'percentage',
                    name='modifier_type')))
    op.add_column('modifier', sa.Column('value', sa.Float, nullable=True))
    # populate type_ and value columns with data from the subclass tables
    abs_select = select([abs_table.c.id, abs_table.c.value])
    rel_select = select([rel_table.c.id, rel_table.c.value])
    conn = op.get_bind()
    for select_stmt in (abs_select, rel_select):
        modifier_rows = conn.execute(select_stmt)
        for modifier_id, modifier_value in modifier_rows:
            if select_stmt == abs_select:
                modifier_value = float(modifier_value / 1000000)
                type_ = 'absolute'
            else:
                type_ = 'percentage'
            update_stmt = update(modifier)\
                    .where(modifier.c.id == modifier_id)\
                    .values({
                        'value': modifier_value,
                        'type_': type_
                    })
            conn.execute(update_stmt)
        modifier_rows.close()
    # Drop the old _type column and the subclass tables
    op.drop_column('modifier', '_type')
    op.drop_table('absolute_modifier')
    op.drop_table('relative_modifier')
    # Add not-null constraint back to type_
    op.alter_column('modifier',
            column_name='type_',
            nullable=False,
            existing_type=sa.Enum('absolute', 'percentage',
                    name='modifier_type')) 

def update_changed(cls, mapper, connection, target):
        """
        :param mapper: the Mapper which is the target of this event
        :param connection: the Connection being used
        :param target: the mapped instance being persisted
        """
        s = connection.execute(select([PeriodicTaskChanged]).
                               where(PeriodicTaskChanged.id == 1).limit(1))
        if not s:
            s = connection.execute(insert(PeriodicTaskChanged),
                                   last_update=dt.datetime.now())
        else:
            s = connection.execute(update(PeriodicTaskChanged).
                                   where(PeriodicTaskChanged.id == 1).
                                   values(last_update=dt.datetime.now())) 

def upgrade():
    op.add_column('request',
            sa.Column('payout', sa.Numeric(precision=15, scale=2), index=True,
                nullable=True))

    bind = op.get_bind()
    absolute = select([abs_table.c.value.label('value'),
                       mod_table.c.request_id.label('request_id')])\
            .select_from(join(abs_table, mod_table,
                    mod_table.c.id == abs_table.c.id))\
            .where(mod_table.c.voided_user_id == None)\
            .alias()
    relative = select([rel_table.c.value.label('value'),
                       mod_table.c.request_id.label('request_id')])\
            .select_from(join(rel_table, mod_table,
                    mod_table.c.id == rel_table.c.id))\
            .where(mod_table.c.voided_user_id == None)\
            .alias()
    abs_sum = select([request.c.id.label('request_id'),
                      request.c.base_payout.label('base_payout'),
                      func.sum(absolute.c.value).label('sum')])\
            .select_from(outerjoin(request, absolute,
                    request.c.id == absolute.c.request_id))\
            .group_by(request.c.id)\
            .alias()
    rel_sum = select([request.c.id.label('request_id'),
                      func.sum(relative.c.value).label('sum')])\
            .select_from(outerjoin(request, relative,
                    request.c.id == relative.c.request_id))\
            .group_by(request.c.id)\
            .alias()
    total_sum = select([abs_sum.c.request_id.label('request_id'),
                        ((
                            abs_sum.c.base_payout +
                            case([(abs_sum.c.sum == None, Decimal(0))],
                                    else_=abs_sum.c.sum)) *
                         (
                            1 +
                            case([(rel_sum.c.sum == None, Decimal(0))],
                                    else_=rel_sum.c.sum))).label('payout')])\
            .select_from(join(abs_sum, rel_sum,
                    abs_sum.c.request_id == rel_sum.c.request_id))
    payouts = bind.execute(total_sum)
    for request_id, payout in payouts:
        up = update(request).where(request.c.id == request_id).values(
                payout=payout)
        bind.execute(up)
    op.alter_column('request', 'payout', nullable=False,
            existing_type=sa.Numeric(precision=15, scale=2)) 

def upgrade():
    # Add discriminator column
    op.add_column('modifier', sa.Column('_type', sa.String(length=20)))
    # Create new subclass tables
    op.create_table('absolute_modifier',
            sa.Column('id', sa.Integer,
                    sa.ForeignKey('modifier.id'),
                    primary_key=True),
            sa.Column('value', sa.Numeric(precision=15, scale=2),
                    nullable=False, server_default='0.0'))
    op.create_table('relative_modifier',
            sa.Column('id', sa.Integer, sa.ForeignKey('modifier.id'),
                    primary_key=True),
            sa.Column('value', sa.Float, nullable=False, server_default='0.0'))
    # Add new entries to the subclass tables for each modifier
    conn = op.get_bind()
    modifier_sel = select([modifier.c.id, modifier.c.value, modifier.c.type_])
    modifiers = conn.execute(modifier_sel)
    absolutes = []
    relatives = []
    for modifier_id, modifier_value, modifier_type in modifiers:
        if modifier_type == 'absolute':
            discriminator = 'AbsoluteModifier'
            absolutes.append({
                    'id': modifier_id,
                    'value': Decimal.from_float(modifier_value) * 1000000,
            })
        elif modifier_type == 'percentage':
            discriminator = 'RelativeModifier'
            relatives.append({
                    'id': modifier_id,
                    'value': modifier_value / 100,
            })
        update_stmt = update(modifier)\
                .where(modifier.c.id == modifier_id)\
                .values({
                        '_type': discriminator,
                })
        conn.execute(update_stmt)
    modifiers.close()
    op.bulk_insert(abs_table, absolutes)
    op.bulk_insert(rel_table, relatives)
    # Drop the old value and type_ columns from modifier
    op.drop_column('modifier', 'value')
    op.drop_column('modifier', 'type_')
    # Add the not-null constraint to the _type column
    op.alter_column('modifier',
            column_name='_type',
            nullable=True,
            existing_type=sa.String(length=20),
    )