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()
