Hi folks,
In our own utility file, I've added a function called multiple_update()
that executes multiple separate update statements in one query. Note that
this is not the same as a single db.update() that updates multiple rows --
with this function, the columns and vars for each row can be different.
In my testing (Windows 64-bit, db is PostgreSQL running on another machine)
I've found this to be about 3-4x as fast as individual db.update()s wrapped
in a transaction, so a worthwhile speed increase. The speed increase factor
seems to be about constant regardless of the number of rows (initially I
was hoping it go up with N, but it didn't in my tests.)
Any thoughts on the API signature, and whether this would be a good
addition to web.DB? Any comments on this approach in general? Is the way
I'm doing the cast to unicode and then '\n'.join(updates) the correct
approach?
Couple of notes:
* If this were part of web.py, obviously it'd be a method on DB instead of
a stand-alone function.
* dbconn.db_connection is our "current database object". Again, this would
go away if it were a DB method.
Code below:
-----
def multiple_update(table, where, values_vars, database=None, _test=False):
"""Execute multiple separate update statements in one query.
>>> database = web.DB(None, {})
>>> row1 = ({'name': 'Bob'}, {'id': 42})
>>> row2 = ({'name': 'Sarah', 'age': 30}, {'id': 23})
>>> print multiple_update('foo', 'id = $id', [row1, row2], _test=True,
database=database)
UPDATE foo SET name = 'Bob' WHERE id = 42;
UPDATE foo SET age = 30, name = 'Sarah' WHERE id = 23;
>>> print multiple_update('foo', 'id = $id', [], _test=True,
database=database)
None
"""
if not values_vars:
return
if database is None:
database = dbconn.db_connection
updates = []
for values, vars in values_vars:
update = u'UPDATE {table} SET {values} WHERE {where};'.format(
table=table,
values=web.db.sqlwhere(values, ', '),
where=database._where(where, vars))
updates.append(unicode(update))
query = '\n'.join(updates)
if _test:
return query
database.query(query)
-----
Thanks,
Ben.
--
You received this message because you are subscribed to the Google Groups
"web.py" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/webpy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
