On Jan 18, 2008 4:03 AM, Wolfram Kriesing <[EMAIL PROTECTED]> wrote: > > What about an extension to the Queryset like this: > > User.objects.filter(username__istartswith="a").update(is_active=True) > > it would trigger something like this: > > UPDATE auth_user SET is_active=1 WHERE username LIKE "a%" > > This syntax (if it existed) would solve the following problems > the current DB API is lacking (if i am not wrong) > > 1) direct updates, you always have to retreive the "rows" > (via SELECT) and later update them one by one > 2) you cant update multiple rows at once, you always have to > trigger an update one by one > > Currently you have to do it like this: > > users = User.objects.filter(username__istartswith="a") # SELECT > for u in users: > u.is_active = True > u.save() # UPDATE > > The above triggers as many UPDATEs as there are users. > And first one SELECT statement to retreive the data. > > The above suggested syntax would make that job much easier and > reduce the load on the DB dramatically (in some cases)! > > -- > cu > > Wolfram
At the moment, I use django.db.connection to create a cursor and execute queries directly when I need to perform updates. I've attached something I just knocked this as a first stab at supporting the update API you've postulated above (stick it in django.db.models.query._QuerySet). Sample usage: >>> from django.contrib.auth.models import User >>> from django.db import connection >>> User.objects.filter(username__istartswith="a").update(is_active=True) >>> print connection.queries[-1] {'time': '0.000', 'sql': u'UPDATE "auth_user" SET "auth_user"."is_active"=True WHERE ("auth_user"."username" LIKE a% ESCAPE \'\\\')'} Obviously this won't cover everything you need to do with an update - e.g. "UPDATE some_table SET some_field + some_field + 1". How about adding some update lookups for these? E.g. (purely speculative API here): Node.objects.filter(tree_id=5, lft__gt=1, rght__lte=10).update(lft__add=1, rght__raw='CASE WHEN rght < 5 THEN rght + 2 ELSE rght END') UPDATE node SET lft = lft + 1, rght = CASE WHEN rght < 5 THEN rght + 2 ELSE rght END WHERE tree_id = 5 AND lft > 1 AND rght <= 10 Jonathan. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
def update(self, **kwargs): """ Performs a SQL UPDATE. """ if not kwargs: raise ValueError('No updates were specified.') opts = self.model._meta db_table = connection.ops.quote_name(opts.db_table) set_ = [] set_params = [] for field_name, value in kwargs.items(): field = opts.get_field(field_name, many_to_many=False) set_.append('%s.%s=%%s' % (db_table, connection.ops.quote_name(field.column))) set_params.append(field.get_db_prep_save(value)) joins, where, where_params = self._filters.get_sql(opts) sql = ['UPDATE %s' % db_table] if joins: sql.append(' '.join(['%s %s AS %s ON %s' % (join_type, table, alias, condition) for (alias, (table, join_type, condition)) in joins.items()])) sql.append('SET %s' % ','.join(set_)) if where: sql.append('WHERE %s' % ' AND '.join(where)) print sql cursor = connection.cursor() cursor.execute(' '.join(sql), set_params + where_params) transaction.commit_unless_managed()