Re: How to execute an UPDATE query without string concatenation/interpolation?

On Sat, Jan 8, 2011 at 5:33 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:
> I want to write a function that I can use like this
>
>>>> update_foo(foo_id=1, colA=11)
>
> and it do this internally:
>
> cursor.execute("""
>    update foo
>    set colA = (%s)
>    where foo_id (%s)""", [11, 1])
>
> And I want to pass in more than just a single column to update, like this, too:
>
>>>> update_foo(foo_id=1, colA=11, colB=12, colC=13)
>
> and it should do this:
>
> cursor.execute("""
>    update foo
>    set
>        colA = (%s),
>        colB = (%s),
>        colC = (%s)
>
>    where foo_id (%s)""", [11, 12, 13, 1])
>
> I'm having a really hard time doing this without building up strings
> and then appending them together.  Is there some better way?

No, not at the adapter level. It isn't hard to do this kind of string
operations, but it's admittedly annoying for many reasons: the
difference between INSERT and UPDATE syntax, the different escaping
rules of the identifiers, having extra placeholders to be defined as
%%s for two levels of parameters passage ecc.

If you want to deal gracefully with this kind of dynamic SQL
generation I suggest you to use a higher level library: specifically
SQLAlchemy allows you to generate select/insert/update statements
without throwing in the ORM part. See this example
<http://www.sqlalchemy.org/docs/core/tutorial.html#insert-expressions>
for a taste of it. And in any moment you can access the underlying
psycopg connection if needed.

Something that would be handy to make dynamic SQL generation easier
would be a way to pass an identifier (such a table or field name) to a
query, with different escaping rules than the strings. This can be
already done now with an customized adapter but wouldn't work with
prepared statement... with we currently don't do, but which may be
added at some point. I'll try to gather all the points and have a
discussion about the topic.

-- Daniele