Python sql.update函数代码示例

def api_token_redeem():
    try:
        auth_token = request.form["auth_token"]

        token_code = request.form["token_code"]
    except KeyError as e:
        raise APIMissingField(e.args[0])

    caller = get_user_from_token(auth_token)

    conn = engine.connect()

    query = sql.select([Token]).where(Token.code == token_code).limit(1)
    res = conn.execute(query)
    try:
        token = res.fetchall()[0]
    except IndexError:
        raise APIInvalidField("token_code", 200)

    query1 = sql.update(Token).where(Token.id == token.id).values({
        Token.redeemed: True,
        Token.redeemer: caller.id
    })
    query2 = sql.update(User).where(User.id == caller.id).values({
        User.credits: caller.credits + token.value
    })
    res1 = conn.execute(query1)
    res2 = conn.execute(query2)

    if res1.inserted_primary_key and res2.inserted_primary_key:
        return make_response(jsonify({
            "status": "success",
            "message": "Token successfully redeemed",
            "new_balance": caller.credits + token.value
        }))

def update_changed(src_table, dim_table):
	"""Update changed records"""

	# create condition
	where_clause = and_(or_(src_table.c.name != dim_table.c.name, src_table.c.address != dim_table.c.address), dim_table.c.to_date == None)

	# join table based on their ids
	joined_table = src_table.join(dim_table, src_table.c.id == dim_table.c.id)

	# create statement for table with only changed records
	change_table = sql.select(src_table.c, whereclause=where_clause, from_obj=joined_table)

	# update records in dim table and create new records
	for row in change_table.execute():
		# create condition
		where_clause = and_(dim_table.c.id == row.id, dim_table.c.to_date == None)

		# create current timestamp
		timestamp = sql.functions.current_timestamp()

    	# update record
		sql.update(dim_table, whereclause=where_clause, values={dim_table.c.to_date:timestamp}).execute()
		print "Updating row", str(row)

    	# insert record
		engine.execute(dim_table.insert(), row)

def fix_foreign_links(session, oldnet, newnet):
    """
    Fix foreign keys that point to the network table

    When a network is split or multiple networks are merged, foreign keys
    must be updated accordingly. Do not use the size of the old network,
    as it has already been updated when this function gets called.
    """
    session.execute(
        update(AddressAssignment.__table__,
               values={'network_id': newnet.id})
        .where(and_(AddressAssignment.network_id == oldnet.id,
                    AddressAssignment.ip >= newnet.ip,
                    AddressAssignment.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['assignments'])
    session.expire(newnet, ['assignments'])

    session.execute(
        update(ARecord.__table__,
               values={'network_id': newnet.id})
        .where(and_(ARecord.network_id == oldnet.id,
                    ARecord.ip >= newnet.ip,
                    ARecord.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['dns_records'])
    session.expire(newnet, ['dns_records'])

def _add_shards(engine, table, dst_col, src_col):
    dialect = engine.url.get_dialect().name
    if dialect.startswith('mysql'):
        sql = "UPDATE %s SET %s = CONV(SUBSTR(%s, 1, 3), 16, 10)"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    elif dialect.startswith('postgres'):
        sql = "UPDATE %s SET %s = ('x'||lpad(substr(%s::text, 1, 3), 8, '0')"\
            ")::bit(32)::int"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    else:
        rows = select(columns=[table.c.id]).execute().fetchall()
        for r in rows:
            shard = int(r.id[0:3], 16)
            values = {dst_col.name: shard}
            update(table).where(table.id == r.id).values(values)

def upgrade(migrate_engine):
    TableBase.metadata.bind = migrate_engine

    Discussion.__table__.create()
    Comment.__table__.create()

    Artwork.__table__.c.discussion_id.nullable = True
    Artwork.__table__.c.discussion_id.create()
    User.__table__.c.discussion_id.nullable = True
    User.__table__.c.discussion_id.create()

    # Create a new discussion for each artwork and user
    conn = migrate_engine.connect()
    tr = conn.begin()

    for table in Artwork, User:
        for id, in conn.execute( sql.select([table.id]) ):
            res = conn.execute( sql.insert(Discussion.__table__) )
            discussion_id = res.inserted_primary_key[0]

            conn.execute(sql.update(
                table.__table__,
                table.__table__.c.id == id,
                dict(discussion_id=discussion_id),
            ))

    tr.commit()

    Artwork.__table__.c.discussion_id.alter(nullable=False)
    User.__table__.c.discussion_id.alter(nullable=False)

 def renameItem(self):
     self.nodeName(textToShow=self.treeWidget.currentItem().text(0))
     try:
         self.treeWidget.currentItem().parent().indexOfChild(self.treeWidget.currentItem())
     except:
         parentUniqueID = self.treeWidget.currentItem().statusTip(1)
         renNode = update(self.nodes).where(self.nodes.c.node_id == parentUniqueID).values(name=self.inputNodeName)
         renNode.execute()
         self.redraw()
     else:
         parentUniqueID = self.treeWidget.currentItem().parent().statusTip(0)
         childIndex = self.treeWidget.currentItem().statusTip(1)
         renSubNode = update(self.children).where(self.children.c.node_id == childIndex and self.children.c.father_id == parentUniqueID).values(name=self.inputNodeName)
         renSubNode.execute()
         self.redraw()
     del self.inputNodeName

def update_tag(id, obj):
    query = (
        update([tag_table])
        .where(tag_table.c.id == id)
        .values(**obj)
        .returning(tag_table.c.id))
    return query.execute().first()[0]

 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 update_password(self, user_id, password, old_password=None, key=None):
        """Change the user password

        Args:
            user_id: user id
            password: new password

        Returns:
            True if the change was successful, False otherwise
        """
        if old_password is None:
            if key:
                #using a key, therefore we should check it
                if self._get_reset_code(user_id) == key:
                    self.clear_reset_code(user_id)
                else:
                    logger.error("bad key used for update password")
                    return False
            else:
                return False

        password_hash = ssha256(password)
        query = update(users).where(users.c.id == user_id)
        res = safe_execute(self._engine,
                           query.values(password_hash=password_hash))
        return res.rowcount == 1

def update_helper(ikey, table, session, obj, mapping):
    raise ValueError
    if hasattr(obj, '_alchemy_pk') and obj._alchemy_pk is not None:
        query = update(table)\
            .where(table.c.id==obj._alchemy_pk)\
            .values(**mapping)
        result = session.execute(query)

        if result.rowcount == 0:
            raise RuntimeError("Can't update object")
            # obj._alchemy_pk = None
            # return update_helper(session, obj, mapping)
    else:
        # TODO search for an object
        # query = select([table]).where(table.c.uri==obj.uri)
        # result = session.execute(query).fetchone()
        # if result:
        #     obj._alchemy_pk = result[0]
        #     return update_helper(session, obj, mapping)
        # else:

        # print "INSERT", obj, session.execute(select([table])).fetchall()
        aquery = table.insert().values(**mapping)
        result = session.execute(aquery)
        obj._alchemy_pk = result.inserted_primary_key[0]

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    dialect = migrate_engine.url.get_dialect().name

    domains_table = Table('domains', meta, autoload=True)

    if dialect.startswith('sqlite'):
        # SQLite can't drop a constraint. Yay. This will be fun..

        # Create a new name column without the unique index
        name_tmp_column = Column('name_tmp', String(255))
        name_tmp_column.create(domains_table)

        # Copy the data over.
        query = update(domains_table).values(name_tmp=domains_table.c.name)
        migrate_engine.execute(query)

        # Delete the name column
        domains_table.c.name.drop()

        # Rename the name_tmp column to name
        domains_table.c.name_tmp.alter(name='name')
    elif dialect.startswith('postgresql'):
        constraint = UniqueConstraint('name', name='domains_name_key',
                                      table=domains_table)
        constraint.drop()
    else:
        constraint = UniqueConstraint('name', name='name', table=domains_table)
        constraint.drop()

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 update_session():
        now = datetime.datetime.now()
        if 17 <= now.hour <= 23:
            lower_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day,
                19
            )
            upper_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day + 1,
                7
            )

            db.session.execute(
                sql.update(Session).where(
                    Session.id == 1
                ).values(
                    upper_limit=upper_limit,
                    lower_limit=lower_limit
                )
            )
            db.session.commit()

 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 downgrade():
    # Add ship and pilot transformer columns back to division
    op.add_column('division', sa.Column('ship_transformer', sa.PickleType))
    op.add_column('division', sa.Column('pilot_transformer', sa.PickleType))
    # Convert transformerrefs back to the old columns
    conn = op.get_bind()
    columns = [
        transformerref.c.division_id,
        transformerref.c.attribute_name,
        transformerref.c.transformer,
    ]
    transformer_sel = select(columns)\
            .where(or_(
                    transformerref.c.attribute_name == 'ship_type',
                    transformerref.c.attribute_name == 'pilot',
            ))
    transformer_rows = conn.execute(transformer_sel)
    for division_id, attribute_name, transformer in transformer_rows:
        if attribute_name == 'ship_type':
            colname = 'ship'
            transformer_class = evesrp.transformers.ShipTransformer
        elif attribute_name == 'pilot':
            colname = 'pilot'
            transformer_class = evesrp.transformers.PilotTransformer
        colname += '_transformer'
        transformer = transformer_class(transformer.name, transformer.slug)
        update_stmt = update(division)\
                .where(division.c.id == division_id)\
                .values({
                        colname: transformer
                })
        conn.execute(update_stmt)
    transformer_rows.close()
    # Drop the transformerref table. This is going to be lossy.
    op.drop_table('transformerref')