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

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(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

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(dot)klaver(at)gmail(dot)com