Re: How to execute an UPDATE query without string concatenation/interpolation?
От | Adrian Klaver |
---|---|
Тема | Re: How to execute an UPDATE query without string concatenation/interpolation? |
Дата | |
Msg-id | 201101081412.44380.adrian.klaver@gmail.com обсуждение исходный текст |
Ответ на | Re: How to execute an UPDATE query without string concatenation/interpolation? (Daniele Varrazzo <daniele.varrazzo@gmail.com>) |
Список | psycopg |
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
В списке psycopg по дате отправления:
Предыдущее
От: Daniele VarrazzoДата:
Сообщение: Re: How to execute an UPDATE query without string concatenation/interpolation?