def check_in (puppy_id, shelter_id):
	# query for requested shelter table
	row = session.query(Shelter).filter(Shelter.id==shelter_id).one()
	# if requested shelter is full, iterate through to find an open one
	if row.occupancy >= row.capacity:
		print ("Shelter full. Checking for another shelter...\n")
		table = session.query(Shelter).all()
		for r in table:
			# if newly chosen shelter is open, check puppy in
			if r.occupancy < r.capacity:
				shelter_id = r.id
				result = session.execute ( update(Puppy).where(Puppy.id == puppy_id).values (shelter_id=shelter_id) )
				print ("Found a new shelter! Checking puppy into %s."%(r.name))
				return result
			# keep checking for open shelter
			else:
				pass
		# if iteration returned no shelter, all shelters are full
		print ("All shelters are full. Please open a new one.")
		return None
	# requested shelter isn't full, so check puppy in
	else:
		result = session.execute ( update(Puppy).where(Puppy.id == puppy_id).values (shelter_id=shelter_id) )
		print ("As requested, checking puppy into %s."%(row.name))
		return result

    def set(self, key, value):
        '''Set a value in the cache.

        @param key Keyword of item in cache.
        @param value Value to be inserted in cache.
        '''
        if len(self) > self._max_entries:
            self._cull()
        timeout, cache = self.timeout, self._cache
        # Get expiration time
        expires = datetime.fromtimestamp(
            time.time() + timeout
        ).replace(microsecond=0)
        #try:
        # Update database if key already present
        if key in self:
            update(
                cache,
                cache.c.key == key,
                dict(value=value, expires=expires),
            ).execute()
        # Insert new key if key not present
        else:
            insert(
                cache, dict(key=key, value=value, expires=expires)).execute()

    def apply_default_value(self, column):
        if column.default:
            execute = self.table.migration.conn.execute
            val = column.default.arg
            table = self.table.migration.metadata.tables[self.table.name]
            table.append_column(column)
            cname = getattr(table.c, column.name)
            if column.default.is_callable:
                table2 = alias(select([table]).limit(1).where(cname.is_(None)))
                Table = self.table.migration.metadata.tables['system_model']
                Column = self.table.migration.metadata.tables['system_column']
                j1 = join(Table, Column, Table.c.name == Column.c.model)
                query = select([func.count()]).select_from(table)
                nb_row = self.table.migration.conn.execute(query).fetchone()[0]
                query = select([Column.c.name]).select_from(j1)
                query = query.where(Column.c.primary_key.is_(True))
                query = query.where(Table.c.table == self.table.name)
                columns = [x[0] for x in execute(query).fetchall()]
                where = and_(*[getattr(table.c, x) == getattr(table2.c, x)
                               for x in columns])
                for offset in range(nb_row):
                    # call for each row because the default value
                    # could be a sequence or depend of other field
                    query = update(table).where(where).values(
                        {cname: val(None)})
                    execute(query)

            else:
                query = update(table).where(cname.is_(None)).values(
                    {cname: val})
                execute(query)

def upgrade(ver, session):
    if ver is None:
        log.info('Converting seen imdb_url to imdb_id for seen movies.')
        field_table = table_schema('seen_field', session)
        for row in session.execute(select([field_table.c.id, field_table.c.value], field_table.c.field == 'imdb_url')):
            new_values = {'field': 'imdb_id', 'value': extract_id(row['value'])}
            session.execute(update(field_table, field_table.c.id == row['id'], new_values))
        ver = 1
    if ver == 1:
        field_table = table_schema('seen_field', session)
        log.info('Adding index to seen_field table.')
        Index('ix_seen_field_seen_entry_id', field_table.c.seen_entry_id).create(bind=session.bind)
        ver = 2
    if ver == 2:
        log.info('Adding local column to seen_entry table')
        table_add_column('seen_entry', 'local', Boolean, session, default=False)
        ver = 3
    if ver == 3:
        # setting the default to False in the last migration was broken, fix the data
        log.info('Repairing seen table')
        entry_table = table_schema('seen_entry', session)
        session.execute(update(entry_table, entry_table.c.local == None, {'local': False}))
        ver = 4

    return ver

 def test_update_returning(self):
     dialect = postgresql.dialect()
     table1 = table(
         'mytable',
         column(
             'myid', Integer),
         column(
             'name', String(128)),
         column(
             'description', String(128)))
     u = update(
         table1,
         values=dict(
             name='foo')).returning(
         table1.c.myid,
         table1.c.name)
     self.assert_compile(u,
                         'UPDATE mytable SET name=%(name)s '
                         'RETURNING mytable.myid, mytable.name',
                         dialect=dialect)
     u = update(table1, values=dict(name='foo')).returning(table1)
     self.assert_compile(u,
                         'UPDATE mytable SET name=%(name)s '
                         'RETURNING mytable.myid, mytable.name, '
                         'mytable.description', dialect=dialect)
     u = update(table1, values=dict(name='foo'
                                    )).returning(func.length(table1.c.name))
     self.assert_compile(
         u,
         'UPDATE mytable SET name=%(name)s '
         'RETURNING length(mytable.name) AS length_1',
         dialect=dialect)

 def test_update_returning(self):
     table1 = table(
         'mytable',
         column('myid', Integer),
         column('name', String(128)),
         column('description', String(128)))
     u = update(
         table1,
         values=dict(name='foo')).returning(table1.c.myid, table1.c.name)
     self.assert_compile(u,
                         'UPDATE mytable SET name=:name OUTPUT '
                         'inserted.myid, inserted.name')
     u = update(table1, values=dict(name='foo')).returning(table1)
     self.assert_compile(u,
                         'UPDATE mytable SET name=:name OUTPUT '
                         'inserted.myid, inserted.name, '
                         'inserted.description')
     u = update(
         table1,
         values=dict(
             name='foo')).returning(table1).where(table1.c.name == 'bar')
     self.assert_compile(u,
                         'UPDATE mytable SET name=:name OUTPUT '
                         'inserted.myid, inserted.name, '
                         'inserted.description WHERE mytable.name = '
                         ':name_1')
     u = update(table1, values=dict(name='foo'
                                    )).returning(func.length(table1.c.name))
     self.assert_compile(u,
                         'UPDATE mytable SET name=:name OUTPUT '
                         'LEN(inserted.name) AS length_1')

    def process_item(self, item, spider):
        """Save deals in the database.

        This method is called for every item pipeline component.

        """
        global zipDict
        session = self.Session()
        item['zipcode'] = self.findZip(zipDict, item)
        #TODO: change to below if after a week or two
        #if item['reposts'] == 1:
        old = session.query(Apts.reposts).filter(Apts.craigId==item['craigId'])
        if old.all():
            #TODO:
            #if session.query(Apts.updateDate).filter(Apt
            update(Apts).where(Apts.craigId==item['craigId']).values(reposts=old+1)          
        else:
            deal = Apts(**item)
            try:
                session.add(deal)
                session.commit()
            except:
                session.rollback()
                raise
            finally:
                session.close()
    
            return item

 def test_update_returning(self):
     table1 = table(
         "mytable",
         column("myid", Integer),
         column("name", String(128)),
         column("description", String(128)),
     )
     u = update(table1, values=dict(name="foo")).returning(
         table1.c.myid, table1.c.name
     )
     self.assert_compile(
         u,
         "UPDATE mytable SET name=:name RETURNING "
         "mytable.myid, mytable.name",
     )
     u = update(table1, values=dict(name="foo")).returning(table1)
     self.assert_compile(
         u,
         "UPDATE mytable SET name=:name RETURNING "
         "mytable.myid, mytable.name, "
         "mytable.description",
     )
     u = update(table1, values=dict(name="foo")).returning(
         func.length(table1.c.name)
     )
     self.assert_compile(
         u,
         "UPDATE mytable SET name=:name RETURNING "
         "char_length(mytable.name) AS length_1",
     )

def save_station(session, data):
    """
    Add or update a station in the DB.
    """
    # If this station is set to "start", we must remove that flag from all other
    # stations. We'll just set them all to False, then update the given station
    # with True. This ensures that we only have one starting station.
    if data.get('is_start'):
        session.execute(update(Station).values(is_start=False))

    if data.get('is_end'):
        session.execute(update(Station).values(is_end=False))

    # Ensure we don't have duplicate values for the "order" field
    same_order = session.query(Station).filter(and_(
        Station.order == data['order'],
        Station.id != data['id'])).first()
    while same_order:  # As long as we have a matching entry, increment by 1
        data['order'] += 1
        same_order = session.query(Station).filter(and_(
            Station.order == data['order'],
            Station.id != data['id'])).first()

    station = Station(
        name=data['name'],
        contact=data['contact'],
        phone=data['phone'],
    )
    station.id = data.get('id')
    station.order = data['order']
    station.is_start = data['is_start']
    station.is_end = data['is_end']
    merged = session.merge(station)
    DB.session.commit()
    return merged

def ship_it(order_id):
    s = select([cookies_tables.line_items.c.cookie_id,
                cookies_tables.line_items.c.quantity])
    s = s.where(cookies_tables.line_items.c.order_id == order_id)
    transaction = connection.begin()
    cookies_to_ship = connection.execute(s)

    try:
        for cookie in cookies_to_ship:
            u = update(cookies_tables.cookies).where(
                cookies_tables.cookies.c.cookie_id == cookie.cookie_id
            )
            u = u.values(
                quantity=cookies_tables.cookies.c.quantity - cookie.quantity
            )
            result = connection.execute(u)
        u = update(cookies_tables.orders).where(
            cookies_tables.orders.c.order_id == order_id
        )
        u = u.values(shipped=True)
        result = connection.execute(u)
        print("Shipped order id: {}".format(order_id))
        transaction.commit()
    except IntegrityError as error:
        transaction.rollback()
        print(error)

    def apply_default_value(self, column):
        if column.default:
            execute = self.table.migration.conn.execute
            val = column.default.arg
            table = self.table.migration.metadata.tables[self.table.name]
            table.append_column(column)
            cname = getattr(table.c, column.name)
            if column.default.is_callable:
                Table = self.table.migration.metadata.tables['system_model']
                Column = self.table.migration.metadata.tables['system_column']
                j1 = join(Table, Column, Table.c.name == Column.c.model)
                query = select([Column.c.name]).select_from(j1)
                query = query.where(Column.c.primary_key.is_(True))
                query = query.where(Table.c.table == self.table.name)
                columns = [x[0] for x in execute(query).fetchall()]

                query = select([func.count()]).select_from(table)
                query = query.where(cname.is_(None))
                nb_row = self.table.migration.conn.execute(query).fetchone()[0]
                for offset in range(nb_row):
                    query = select(columns).select_from(table)
                    query = query.where(cname.is_(None)).limit(1)
                    res = execute(query).fetchone()
                    where = and_(
                        *[getattr(table.c, x) == res[x] for x in columns])
                    query = update(table).where(where).values(
                        {cname: val(None)})
                    execute(query)

            else:
                query = update(table).where(cname.is_(None)).values(
                    {cname: val})
                execute(query)

def downgrade():
    conn = op.get_bind()

    # --- User
    op.add_column('user', sa.Column('userid', sa.String(22), nullable=True))
    op.create_unique_constraint('user_userid_key', 'user', ['userid'])
    count = conn.scalar(
        sa.select([sa.func.count('*')]).select_from(user))
    progress = get_progressbar("Users", count)
    progress.start()
    items = conn.execute(sa.select([user.c.id, user.c.uuid]))
    for counter, item in enumerate(items):
        conn.execute(sa.update(user).where(user.c.id == item.id).values(userid=uuid2buid(item.uuid)))
        progress.update(counter)
    progress.finish()
    op.alter_column('user', 'userid', nullable=False)
    op.drop_constraint('user_uuid_key', 'user', type_='unique')
    op.drop_column('user', 'uuid')

    # --- Team
    op.add_column('team', sa.Column('userid', sa.String(22), nullable=True))
    op.create_unique_constraint('team_userid_key', 'team', ['userid'])
    count = conn.scalar(
        sa.select([sa.func.count('*')]).select_from(team))
    progress = get_progressbar("Teams", count)
    progress.start()
    items = conn.execute(sa.select([team.c.id, team.c.uuid]))
    for counter, item in enumerate(items):
        conn.execute(sa.update(team).where(team.c.id == item.id).values(userid=uuid2buid(item.uuid)))
        progress.update(counter)
    progress.finish()
    op.alter_column('team', 'userid', nullable=False)
    op.drop_constraint('team_uuid_key', 'team', type_='unique')
    op.drop_column('team', 'uuid')

    # --- Profile
    op.add_column('profile', sa.Column('userid', sa.String(22), nullable=True))
    op.create_unique_constraint('profile_userid_key', 'profile', ['userid'])
    count = conn.scalar(
        sa.select([sa.func.count('*')]).select_from(profile))
    progress = get_progressbar("Profiles", count)
    progress.start()
    items = conn.execute(sa.select([profile.c.id, profile.c.uuid]))
    for counter, item in enumerate(items):
        conn.execute(sa.update(profile).where(profile.c.id == item.id).values(userid=uuid2buid(item.uuid)))
        progress.update(counter)
    progress.finish()
    op.alter_column('profile', 'userid', nullable=False)
    op.drop_constraint('profile_uuid_key', 'profile', type_='unique')
    op.drop_column('profile', 'uuid')

    # --- Project
    op.drop_constraint('project_uuid_key', 'project', type_='unique')
    op.drop_column('project', 'uuid')

def db_fileInfoUpdate(file_id, usr_id, filehash, iv):
    version = db_fileCurrentVersion(file_id)+1
    session.execute(update(File).where(File.id == file_id).values(
        version=version))
    session.execute(update(File).where(File.id == file_id).values(
        file_hash=filehash))
    session.execute(update(File).where(File.id == file_id).values(
        iv=iv))
    session.execute(update(EditionManagement).where(EditionManagement.file_id == file_id).values(
        user_id=usr_id, change_datetime=(time.strftime("%d/%m/%Y") + ' ' + time.strftime("%H:%M"))))

    session.commit()

def upgrade():
    conn = op.get_bind()

    language = sa.table('language', *map(sa.column, ['pk', 'id', 'name', 'updated']))
    lid = sa.bindparam('id_')
    lbefore = sa.bindparam('before')
    update_lang = sa.update(language, bind=conn)\
        .where(sa.and_(
            language.c.id == lid,
            language.c.name == lbefore))\
        .values(updated=sa.func.now(), name=sa.bindparam('after'))

    walslanguage = sa.table('walslanguage', *map(sa.column, ['pk', 'ascii_name']))
    aname = sa.bindparam('ascii_name')
    update_wals = sa.update(walslanguage, bind=conn)\
        .where(sa.exists().where(sa.and_(
            language.c.pk == walslanguage.c.pk,
            language.c.id == lid))\
        .where(walslanguage.c.ascii_name != aname))\
        .values(ascii_name=aname)

    icols = ['created', 'updated', 'active', 'version', 'type', 'description', 'lang', 'name']
    identifier = sa.table('identifier', *map(sa.column, ['pk'] + icols))
    itype, idesc, ilang = (sa.bindparam(*a) for a in [('type', 'name'), ('description', 'other'), ('lang', 'en')])
    iname = sa.bindparam('name')
    iwhere = sa.and_(
        identifier.c.type == itype,
        identifier.c.description == idesc,
        identifier.c.lang == ilang,
        identifier.c.name == iname)
    insert_ident = sa.insert(identifier, bind=conn).from_select(icols,
        sa.select([sa.func.now(), sa.func.now(), True, 1, itype, idesc, ilang, iname])
        .where(~sa.exists().where(iwhere)))

    licols = ['created', 'updated', 'active', 'version', 'language_pk', 'identifier_pk']
    languageidentifier = sa.table('languageidentifier', *map(sa.column, licols))
    l_pk = sa.select([language.c.pk]).where(language.c.id == lid)
    i_pk = sa.select([identifier.c.pk]).where(sa.and_(iwhere))
    insert_lang_ident = sa.insert(languageidentifier, bind=conn).from_select(licols,
        sa.select([sa.func.now(), sa.func.now(), True, 1, l_pk.as_scalar(), i_pk.as_scalar()])
        .where(~sa.exists().where(sa.and_(
            languageidentifier.c.language_pk == l_pk,
            languageidentifier.c.identifier_pk == i_pk))))

    for id_, (before, after, keep) in sorted(ID_BEFORE_AFTER_KEEP.items()):
        update_lang.execute(id_=id_, before=before, after=after)
        update_wals.execute(id_=id_, ascii_name=ascii_name(after))
        if keep:
            insert_ident.execute(name=before)
            insert_lang_ident.execute(id_=id_, name=before)

def update_entry():
	fav_id = request.form['id']
	name = request.form['name']
	street = request.form['street']
	city = request.form['city']
	state = request.form['state']
	zip = request.form['zip']
	lat, lng = geocode_address(street, city, state, zip)
	
	try:
		update(favorites_table, favorites_table.c.id == fav_id).execute(name=name, street=street, city=city, state=state, zip=zip, lat=lat, lng=lng)
	except:
		print 'There was an error updating this favorite.'

	return redirect(url_for('show_favorites'))

def update_canonicals(canonicals):
    '''
    Update canonical data for android devices.
    '''
    global ENGINE
    binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
    device_table = model.metadata.tables['device']
    stmt = update(device_table).\
        values(device_token_new=bindparam('p_new_token')).\
        where(and_(device_table.c.login_id == bindparam('p_login_id'),
                   func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
    ENGINE.execute(stmt, binding)

    with session_scope() as session:
        query = text('SELECT keep_max_users_per_device( \
                     (:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
        for canonical in canonicals:
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID_TABLET,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
        session.commit()

 def gallery_deleted(self):
     self.expired = True
     with user_database.get_session(self) as session:
         session.execute(update(user_database.Gallery).where(
             user_database.Gallery.id == self.db_id).values({
                 "dead": True
         }))

    def test_update_ordered_parameters_2(self):
        table1 = self.tables.mytable

        # Confirm that we can pass values as list value pairs
        # note these are ordered *differently* from table.c
        values = [
            (table1.c.name, table1.c.name + "lala"),
            ("description", "some desc"),
            (table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))),
        ]
        self.assert_compile(
            update(
                table1,
                (table1.c.myid == func.hoho(4))
                & (
                    table1.c.name
                    == literal("foo") + table1.c.name + literal("lala")
                ),
                preserve_parameter_order=True,
            ).values(values),
            "UPDATE mytable "
            "SET "
            "name=(mytable.name || :name_1), "
            "description=:description, "
            "myid=do_stuff(mytable.myid, :param_1) "
            "WHERE "
            "mytable.myid = hoho(:hoho_1) AND "
            "mytable.name = :param_2 || mytable.name || :param_3",
        )

    def test_update_3(self):
        table1 = self.tables.mytable

        self.assert_compile(
            update(table1, table1.c.myid == 7),
            'UPDATE mytable SET name=:name WHERE mytable.myid = :myid_1',
            params={'name': 'fred'})

 def update_joke_in_db(self, joke_id, joke_data):
     self.storage.connect()
     jokes = meta.tables["joke"]
     query = sqlalchemy.update(jokes).where(jokes.c.id == joke_id).values(joke_data)
     print query
     self.storage.execute(query)
     self.storage.disconnect()