Aha, I love it -- that's really good thinking. I prefer your second option. Partly because it's probably going to mean a simpler implementation, but also because overloading transaction (a very specific database concept) seems like it'd be confusing.
So for an API, how about "with db.multiple_updater() as mu: ...", where mu supports all the db write operations: .update(), .insert(), .multiple_insert(), and .delete(). The multipler_updater() instance stores all the <sql> objects (via _test=True?) and writes them on __exit__. If we make it support all the write operations like this, should it be called something else, or is multiple_updater() still okay? multiple_writer() feels odd. Let me know what you think, and then I can submit a patch. -Ben -Ben On Tue, Apr 2, 2013 at 7:00 PM, Anand Chitipothu <[email protected]>wrote: > I'm fine with a separate function, but I'm not happy with the API. > > >>> row1 = ({'name': 'Bob'}, {'id': 42}) > >>> row2 = ({'name': 'Sarah', 'age': 30}, {'id': 23}) > >>> query = multiple_update('foo', 'id = $id', [row1, row2], > _test=True, database=database) > > Passing row as tuple of two elements looks very strange. It doesn't fit > any of the existing usage pattern. > > wouldn't it look nice to have the transaction takes care of it > automatically? If required with a separate keyword argument to transaction. > > with db.transaction(combine_updates=True): > db.update("foo", "id=$id", vars={"id": 42}, name="Bob") > db.update("foo", "id=$id", vars={"id": 42}, name="Sarah", age=30) > > or, something like: > > with db.multiple_updater() as x: > x.update("foo", "id=$id", vars={"id": 42}, name="Bob") > x.update("foo", "id=$id", vars={"id": 42}, name="Sarah", age=30) > > Anand > > > On Mon, Apr 1, 2013 at 4:31 AM, Ben Hoyt <[email protected]> wrote: > >> Anand, what do you think of >> >> 1) my response here (re having a separate function)? >> 2) do you think this would be a good addition to web.py proper, and do >> you want a patch for that? >> >> -Ben >> >> >> On Thu, Mar 28, 2013 at 3:52 PM, Ben Hoyt <[email protected]> wrote: >> >>> No, I don't think so, because values needs to be a list of dicts too, >>> and db.update() takes its values as keyword args. >>> >>> Also, I guess I don't love APIs with too much overloading for >>> substantially different functionality. >>> >>> -Ben >>> >>> >>> On Thu, Mar 28, 2013 at 3:46 PM, Anand Chitipothu >>> <[email protected]>wrote: >>> >>>> Nice! >>>> >>>> Why do we need a different function? Can't the same update function >>>> handle this when vars is a list instead of a dict? >>>> >>>> Anand >>>> >>>> >>>> On Thursday, March 28, 2013, Ben Hoyt wrote: >>>> >>>>> Okay, so I was definitely doing the unicode and joining wrong. New >>>>> version below. I *think* this is correct. (It definitely works now, at >>>>> least.) >>>>> >>>>> ----- >>>>> def multiple_update(table, where, values_vars, database=None, >>>>> _test=False): >>>>> r"""Execute multiple separate update statements in one query. >>>>> >>>>> >>> database = web.DB(None, {}) >>>>> >>> row1 = ({'name': 'Bob'}, {'id': 42}) >>>>> >>> row2 = ({'name': 'Sarah', 'age': 30}, {'id': 23}) >>>>> >>> query = multiple_update('foo', 'id = $id', [row1, row2], >>>>> _test=True, database=database) >>>>> >>> query >>>>> <sql: "UPDATE foo SET name = 'Bob' WHERE id = 42;\nUPDATE foo SET >>>>> age = 30, name = 'Sarah' WHERE id = 23"> >>>>> >>> query.query() >>>>> 'UPDATE foo SET name = %s WHERE id = %s;\nUPDATE foo SET age = %s, >>>>> name = %s WHERE id = %s' >>>>> >>> query.values() >>>>> ['Bob', 42, 30, 'Sarah', 23] >>>>> >>>>> >>> query = multiple_update('bar', 'a = $b', [({'c': >>>>> decimal.Decimal(2)}, {'b': 3})], _test=True, database=database) >>>>> >>> query >>>>> <sql: "UPDATE bar SET c = Decimal('2') WHERE a = 3"> >>>>> >>> query.query() >>>>> 'UPDATE bar SET c = %s WHERE a = %s' >>>>> >>> query.values() >>>>> [Decimal('2'), 3] >>>>> >>>>> >> 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: >>>>> updates.append( >>>>> 'UPDATE ' + table + >>>>> ' SET ' + web.db.sqlwhere(values, ', ') + >>>>> ' WHERE ' + database._where(where, vars) >>>>> ) >>>>> >>>>> query = web.SQLQuery.join(updates, sep=';\n') >>>>> if _test: >>>>> return query >>>>> database.query(query) >>>>> ----- >>>>> >>>>> -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. >>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Anand >>>> http://anandology.com/ >>>> >>>> -- >>>> You received this message because you are subscribed to a topic in the >>>> Google Groups "web.py" group. >>>> To unsubscribe from this topic, visit >>>> https://groups.google.com/d/topic/webpy/dTeS_5Vm7BM/unsubscribe?hl=en. >>>> To unsubscribe from this group and all its topics, 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. >>>> >>>> >>>> >>> >>> >> > > > -- > Anand > http://anandology.com/ > -- 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.
