Re: [psycopg] UPDATE command

On 07/22/2017 08:31 AM, Graeme Gemmill wrote:
> On 21/07/17 20:36, Jonathan Rogers wrote:
>> On 07/21/2017 12:24 PM, Graeme Gemmill wrote:
>>> I wish to issue an UPDATE command to update 4 columns of a row that has
>>> a unique primary key vindex:
>>>
>>> SQL = "UPDATE contact SET firstname, addnlnames, surname, rev WHERE
>>> vindex =? (%s, %s, %s, %s);"
>>> data = (.......)
>>> cursor.execute(SQL, data)
>>>
>>> How is the value of vindex specified? Is my ? to be replaced by %s where
>>> the value of vindex is the first of the data values?
>> It seems you may be confused about both SQL syntax and psycopg2's
>> handling of parameters. Start with the Posgtgres documentation about the
>> UPDATE statement:
>>
>> https://www.postgresql.org/docs/9.6/static/sql-update.html
>>
>> As explained in its documentation, "%s" is the psycopg2 placeholder. The
>> SQL string you should pass to pyscopg2 is "UPDATE contact SET firstname
>> = %s, addnlnames = %s, surname = %s, rev =% WHERE vindex = %s".
>>
>> http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
>>
>>
> Thank you John Evan, Jonathan for replies. The structure I used is valid
> and even recommended for an INSERT command; I couldn't see how to use it
> for an UPDATE. Anyway, sorted now.
> Graeme

Syntactic consistency isn't a strength of SQL and UPDATEs always look
different from INSERTs. Today I learned that there is an alternative
column-list syntax for UPDATEs but that's not quite like an INSERT. Your
statement would like like this:

UPDATE contact SET (firstname, addnlnames, surname, rev) = (%s, %s, %s,
%s) WHERE vindex = %s;


--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com