On 05/20/2010 10:53 AM, Bryan wrote:
> Including the expressing in the values() clause w/ a bindparam like in
> your second example did the trick. I haven't checked the MySQL logs
> if I loose the executemany() benefits (not sure off the top of my head
> how the MySQL logs would show those benefits), but at least my tests
> are passing now.
>
> I'm curious, isn't this still better than looping through all the
> updates I need to do and building an update object for each row?
> MySQL may see the same amount of unique UPDATE queries either way, but
> at least SA only needs to create one update object when using
> bindparams and a list of values.
>
>
The performance advantage of executemany is that the DBAPI client only
needs to send one UPDATE statement to the server along with a bunch of
values instead of a bunch of UPDATE statements. So you only pay the
parsing/planning/network latency penalty once.
The cost of SQLAlchemy generating update objects and compiling them to
SQL is generally negligible compared to the cost of actually executing
them. If all your updates are of the form "col1=<constant>, col2=col1 *
<constant>", then by all means use executemany. I had just figured that
you wanted some rows to use col2=<constant> while other rows used
col2=col1 * <constant>, which is not supported by a single executemany
statement.
-Conor
> My actual code doesn't modify the update object in place, sorry about
> the typo.
>
> On May 20, 8:41 am, Conor <conor.edward.da...@gmail.com> wrote:
>
>> On 05/20/2010 10:28 AM, Bryan wrote:
>>
>>
>>> How about some sort of literal: "table1.col1 * 5" (without the quotes)
>>> as the value of a bindparam? So to the bindparam it would look like a
>>> constant, but when it got to the server, it would be interpreted as an
>>> expression? I would be loosing the automatic table/column name
>>> insertion that SA provides.
>>>
>> Bind params don't work like that: you just cannot inject arbitrary SQL
>> via bind params. You have to include the expression as part of the
>> update statement itself. The only downside to this approach is you (may)
>> lose the executemany() behavior you may have been looking for
>> (executemany is when you pass an array of bind params to execute):
>>
>> u = table1.update()
>> u = u.values(col1=5, col2=table1.c.col1 * 5)
>> engine.execute(u)
>>
>> If you really need bind params, the best you can do is this, which is
>> probably not what you want:
>>
>> u = table1.update()
>> u = u.values(col1=bindparam('_col1'), col2=table1.c.col1 *
>> bindparam('_col2'))
>> engine.execute(u, [{'_col1': 5, '_col2': 5}])
>>
>> I also just noticed that your original example:
>>
>> u = table1.update()
>> u.values(col1=bindparam('_col1'), col2=bindparam('_col2') ...
>>
>> tried to modify the update object in place, but the values() method
>> returns a new update object instead of modifying the original.
>>
>> -Conor
>>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
