Python sql.delete函数代码示例

    def update_changes(self, table, changes,
                       insert_f=None, where_f=None, delete_f=None):

        for action, obj in changes:
            if action == 'new':
                if insert_f is not None:
                    iquery = table.insert().values(
                        **insert_f(obj)
                    )
                    self.session.execute(iquery)
                else:
                    self.conn.update(obj)
            elif action == 'remove_by_id':
                dquery = delete(table)\
                    .where(table.c.id==obj)
                self.session.execute(dquery)
            elif action == 'remove':
                if delete_f is not None:
                    dquery = delete(table).where(
                        delete_f(obj)
                    )
                    self.session.execute(dquery)
                elif not hasattr(obj, '_alchemy_pk') or not obj._alchemy_pk:
                    raise NotImplementedError
                else:
                    raise NotImplementedError
            else:
                raise NotImplementedError(action)

    def toolbar_icon_clicked(self, widget, movie):
        if question(_('Are you sure you want to remove %d movies?') % self.app.total):
            session = self.db.Session()

            # first: remove all dependend data (associated tags, languages, ...)
            query = select([movies_table.c.movie_id])
            # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters)
            query = update_whereclause(query, self.app._search_conditions)
            query = query.where(movies_table.c.loaned==False) # don't delete loaned movies
            for movie_entry in session.execute(query):
                # tags
                query_movie_tags = delete(movie_tag_table)
                query_movie_tags = query_movie_tags.where(movie_tag_table.c.movie_id==movie_entry.movie_id)
                session.execute(query_movie_tags)
                # languages
                query_movie_lang = delete(movie_lang_table)
                query_movie_lang = query_movie_lang.where(movie_lang_table.c.movie_id==movie_entry.movie_id)
                session.execute(query_movie_lang)
                # TODO: removing posters if no longer used by another movie?

            # second: remove the movie entries
            query = delete(movies_table)
            # FIXME: self.app._search_conditions contains advfilter conditions only (no other filters)
            query = update_whereclause(query, self.app._search_conditions)
            query = query.where(movies_table.c.loaned==False) # don't delete loaned movies

            session.execute(query)
            session.commit()

            self.app.populate_treeview()

 def test_delete_extra_froms_alias(self):
     a1 = table("t1", column("c1")).alias("a1")
     t2 = table("t2", column("c1"))
     q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
     self.assert_compile(
         q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
     )
     self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")

    def customer_del(self, customer_id):
        """Delete a customer from database, bringing along all it's machines
        and services
        """
        # Get customer
        cust = query(model.Customer).options(model.eagerload('machines'))\
                                    .get(int(customer_id))

        if not cust:
            return vaultMsg(True, "No such customer: c#%s" % customer_id)

        # Get all the services that will be deleted
        servs = query(model.Service).join(['machine', 'customer']) \
                     .filter(model.Customer.id == customer_id) \
                     .all()
        servs_ids = [s.id for s in servs]

        # Make sure no service is child of this one
        childs = query(model.Service) \
                     .filter(model.Service.parent_service_id.in_(servs_ids))\
                     .all()

        # Don't bother for parents/childs if we're going to delete it anyway.
        remnants = list(set(childs).difference(set(servs)))

        if len(remnants):
            # There are still some childs left, we can't delete this one.
            retval = []
            for x in remnants:
                retval.append({'id': x.id, 'url': x.url})
                
            return vaultMsg(False, "Services still child of this customer's machine's services",
                            {'childs': retval})

        # Delete all related groupciphers
        d = sql.delete(model.servicegroups_table) \
               .where(model.servicegroups_table.c.service_id.in_(servs_ids))
        # Delete the services related to customer_id's machines
        d2 = sql.delete(model.services_table) \
                .where(model.services_table.c.id.in_(servs_ids))
        # Delete the machines related to customer_id
        mach_ids = [m.id for m in cust.machines]
        d3 = sql.delete(model.machines_table) \
                .where(model.machines_table.c.id.in_(mach_ids))
        # Delete the customer
        d4 = sql.delete(model.customers_table) \
                .where(model.customers_table.c.id == customer_id)

        meta.Session.execute(d)
        meta.Session.execute(d2)
        meta.Session.execute(d3)
        meta.Session.execute(d4)
        
        meta.Session.commit()

        return vaultMsg(True,
                        'Deleted customer c#%s successfully' % customer_id)

    def delete(self, scheme):
        if hasattr(scheme, '_alchemy_pk') and scheme._alchemy_pk is not None:
            query = delete(tbl.scheme)\
                .where(tbl.scheme.c.id==scheme._alchemy_pk)
        else:
            query = delete(tbl.scheme)\
                .where(tbl.scheme.c.name==scheme.name)

        self.session.execute(query)

    def clean_stats(self, tags=None):
        eng = self.treatment.experiment.database.engine
        se = delete(StatsRecord).where(and_(
            # StatsRecord.kind.in_(names),
            StatsRecord.replicate_id == self.seq,
            StatsRecord.treatment_id == self.treatment.seq))
        eng.execute(se)

        ge = delete(StatsRecord).where(and_(
            # StatsRecord.kind.in_(names),
            StatsRecord.replicate_id == self.seq,
            StatsRecord.treatment_id == self.treatment.seq))
        eng.execute(ge)

def delete_vocabulary(id, cascade=True):
    """
    Delete a vocabulary, by id

    :param id: vocabulary id
    :param cascade: if True, delete all tags in this vocabulary first
    """
    conn = Session.connection()
    with conn.begin():
        if cascade:
            query = delete(tag_table).where(tag_table.c.vocabulary_id == id)
            query.execute()
        query = delete(vocabulary_table).where(vocabulary_table.c.id == id)

    def machine_del(self, machine_id):
        """Delete a machine from database, bringing on all child services."""
        
        # Get machine
        machine = query(model.Machine).get(int(machine_id))

        if not machine:
            return vaultMsg(True, "No such machine: m#%s" % machine_id)

        # Get all the services that will be deleted
        servs = query(model.Service).join('machine') \
                     .filter(model.Machine.id == machine_id).all()
        servs_ids = [s.id for s in servs]

        # Make sure no service is child of this one
        childs = query(model.Service) \
                     .filter(model.Service.parent_service_id.in_(servs_ids))\
                     .all()

        # Don't bother for parents/childs if we're going to delete it anyway.
        remnants = list(set(childs).difference(set(servs)))

        if len(remnants):
            # There are still some childs left, we can't delete this one.
            retval = []
            for x in remnants:
                retval.append({'id': x.id, 'url': x.url})
                
            return vaultMsg(False, "Services still child of this machine's services",
                            {'childs': retval})


        # Delete all related groupciphers
        d = sql.delete(model.servicegroups_table) \
               .where(model.servicegroups_table.c.service_id.in_(servs_ids))
        # Delete the services related to machine_id
        d2 = sql.delete(model.services_table) \
                .where(model.services_table.c.id.in_(servs_ids))
        # Delete the machine
        d3 = sql.delete(model.machines_table) \
                .where(model.machines_table.c.id == machine_id)

        meta.Session.execute(d)
        meta.Session.execute(d2)
        meta.Session.execute(d3)
        
        meta.Session.commit()

        return vaultMsg(True, 'Deleted machine m#%s successfully' % machine_id)

    def clear_reset_code(self, user_id):
        if self._engine is None:
            raise NotImplementedError()

        query = delete(reset_codes).where(reset_codes.c.username == user_id)
        res = safe_execute(self._engine, query)
        return res.rowcount > 0

def upgrade(ver, session):
    if ver is 0:
        table_names = [
            "rottentomatoes_actors",
            "rottentomatoes_alternate_ids",
            "rottentomatoes_directors",
            "rottentomatoes_genres",
            "rottentomatoes_links",
            "rottentomatoes_movie_actors",
            "rottentomatoes_movie_directors",
            "rottentomatoes_movie_genres",
            "rottentomatoes_movies",
            "rottentomatoes_posters",
            "rottentomatoes_releasedates",
            "rottentomatoes_search_results",
        ]
        tables = [table_schema(name, session) for name in table_names]
        for table in tables:
            session.execute(table.delete())
        table_add_column("rottentomatoes_actors", "rt_id", String, session)
        ver = 1
    if ver is 1:
        table = table_schema("rottentomatoes_search_results", session)
        session.execute(sql.delete(table, table.c.movie_id == None))
        ver = 2
    return ver

 def test_delete_extra_froms(self):
     t1 = table("t1", column("c1"))
     t2 = table("t2", column("c1"))
     q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
     self.assert_compile(
         q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
     )

    async def _test_execute(self, engines, binds):
        test_table1 = self.test_models['db1'].test_table1
        test_table2 = self.test_models['db2'].test_table2
        async with Session(engines, binds) as session:
            q = sql.insert(test_table1).values(id=5, title='test_title')
            result = await session.execute(q)
            self.assertEqual(result.lastrowid, 5)
            q = sql.select(test_table1.c).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title')

            q = sql.update(test_table1).where(test_table1.c.id == 5).\
                    values(title='test_title2')
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title2')

            q = sql.delete(test_table1).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 0)

    def delete(self, concept):
        if hasattr(concept, '_alchemy_pk') and concept._alchemy_pk is not None:
            query = delete(tbl.concept)\
                .where(tbl.concept.c.id==concept._alchemy_pk)
        else:
            raise NotImplementedError

        self.session.execute(query)

 def forget_user(self, uid=None):
     if uid is None:
         return False
     if type(uid) is str:  # Sorry little Billy '); Drop Tables *;
         return False
     query = delete([user_info]).where(user_info.c.uid == uid)
     self._db.execute(query)
     return True

 def delete(self, id=None):
     id = request.params.getone('id')
     post_q = meta.Session.query(model.Post)
     post = post_q.filter_by(id=id).first()
     if post is None:
         abort(404)
     # delete tags and comments associated with post
     meta.Session.execute(delete(model.poststags_table, model.poststags_table.c.post_id==post.id))
     meta.Session.execute(delete(model.comments_table, model.comments_table.c.post_id==post.id))
     meta.Session.delete(post)
     meta.Session.commit()
     if request.is_xhr:
         response.content_type = 'application/json'
         return "{'success':true,'msg':'The post has been deleted'}"
     else:
         session['flash'] = 'Post successfully deleted.'
         session.save()
         return redirect_to(controller='post', action='list')