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 <benh...@gmail.com> 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 <benh...@gmail.com> 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
>> <anandol...@gmail.com>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 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.
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> 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
>>> 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.
>>>
>>>
>>>
>>
>>
>
--
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 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.
