How's this? The doctests are a bit unwieldly, but at least it's tested. :-)
And for convenience, you could add a "multiple_updater" function to DB
which just returned "MultipleUpdater(self)".
Thanks again for the with-statement idea.
-----
class MultipleUpdater(object):
r"""Used to group multiple database writes into one query to minimize
I/O
to and from the database. This can result in a speed increase of 2-4x on
bulk updates. Intended usage:
with MultipleUpdater(db) as mu:
for row_id, name in lots_of_ids_and_names_to_update:
mu.update('my_table', where='id = $id', vars={'id': row_id},
name=name)
On the MultipleUpdater instance, you can use all the database write
functions: update(), insert(), multiple_insert, and delete(). If any
exception occurs inside the with statement, no updates will be done.
>>> import decimal
>>> db = web.DB(None, {})
>>> db.supports_multiple_insert = True
>>> mu = MultipleUpdater(db, _test=True)
>>> with mu:
... mu.update('foo', where='id = $id', name='Bob', vars={'id': 42})
... mu.update('foo', where='id = $id', name='Sarah', age=30,
vars={'id': 23})
>>> mu.sql
<sql: "UPDATE foo SET name = 'Bob' WHERE id = 42;\nUPDATE foo SET age =
30, name = 'Sarah' WHERE id = 23">
>>> mu.sql.query()
'UPDATE foo SET name = %s WHERE id = %s;\nUPDATE foo SET age = %s, name
= %s WHERE id = %s'
>>> mu.sql.values()
['Bob', 42, 30, 'Sarah', 23]
>>> with mu:
... mu.update('bar', where='a = $b', c=decimal.Decimal(2),
vars={'b': 3})
>>> mu.sql
<sql: "UPDATE bar SET c = Decimal('2') WHERE a = 3">
>>> bool(mu.sql)
True
>>> mu.sql.query()
'UPDATE bar SET c = %s WHERE a = %s'
>>> mu.sql.values()
[Decimal('2'), 3]
>>> with mu:
... pass
>>> mu.sql
<sql: ''>
>>> bool(mu.sql)
False
>>> with mu:
... mu.update('foo', where='id = 1', name='Bob')
... mu.insert('bar', age=32)
... mu.multiple_insert('baz', [{'name': 'John'}, {'name': 'Sally'}])
... mu.delete('qux', where='id = 2')
>>> print str(mu.sql)
UPDATE foo SET name = 'Bob' WHERE id = 1;
INSERT INTO bar (age) VALUES (32);
INSERT INTO baz (name) VALUES ('John'), ('Sally');
DELETE FROM qux WHERE id = 2
>>> mu.sql.query()
'UPDATE foo SET name = %s WHERE id = 1;\nINSERT INTO bar (age) VALUES
(%s);\nINSERT INTO baz (name) VALUES (%s), (%s);\nDELETE FROM qux WHERE id
= 2'
>>> mu.sql.values()
['Bob', 32, 'John', 'Sally']
>>> with MultipleUpdater(db, _test=True) as mu2:
... mu2.update('foo', where='id = 1', name='Bob')
>>> mu2.sql
<sql: "UPDATE foo SET name = 'Bob' WHERE id = 1">
"""
def __init__(self, database, _test=False):
self.database = database
self.test = _test
def __enter__(self):
self.updates = []
self.sql = None
return self
def __exit__(self, exc_type, exc_value, traceback):
if exc_type is not None:
# Don't update anything if an exception occurred
return
self.sql = web.SQLQuery.join(self.updates, sep=';\n')
if self.sql and not self.test:
self.database.query(self.sql)
def update(self, *args, **kwargs):
self.updates.append(self.database.update(*args, _test=True,
**kwargs))
def insert(self, *args, **kwargs):
self.updates.append(self.database.insert(*args, _test=True,
**kwargs))
def multiple_insert(self, *args, **kwargs):
self.updates.append(self.database.multiple_insert(*args,
_test=True, **kwargs))
def delete(self, *args, **kwargs):
self.updates.append(self.database.delete(*args, _test=True,
**kwargs))
-----
--
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 webpy+unsubscr...@googlegroups.com.
To post to this group, send email to webpy@googlegroups.com.
Visit this group at http://groups.google.com/group/webpy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
