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

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?

Thanks in advance.



--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com

On Sat, Jan 8, 2011 at 5:33 PM, W. Matthew Wilson <matt@tplus1.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

On Saturday 08 January 2011 1:38:14 pm Daniele Varrazzo wrote:
> On Sat, Jan 8, 2011 at 5:33 PM, W. Matthew Wilson <matt@tplus1.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

Ways I have dealt with it:

First from Postgres 8.2 on the UPDATE command supports a column list syntax that
is the  same as INSERT:
http://www.postgresql.org/docs/8.2/interactive/sql-update.html
"
Use the alternative column-list syntax to do the same update:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';
"

Second Psycopg supports pyformat for variables. Combine this with the DictCursor
from psycopg2.extras and a lot of the work is already done for you:)

A quick example:

sql_update = 'UPDATE '+self.table+' SET('+db_fields+')='
        sql_update += '('+pyformat_str+')'+' WHERE '
        sql_update += self.table_code+'_id='+row['id']
        cur_update=self.con.cursor()
        cur_update.execute(sql_update,row)

Where row is returned from a DictCursor. db_fields and pyformat_str are
constructed from the same field with db_fields being of
form  'field_1,field_2,field_3' and pyformat_str of format '%(field_1)s,
%(field_2)s,%(field_3)s'



--
Adrian Klaver
adrian.klaver@gmail.com