I see. Greatly appreciated. SA is amazing.
On May 20, 9:09 am, Conor <conor.edward.da...@gmail.com> wrote:
> 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
> athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
