Обсуждение: [psycopg] UPDATE command

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?

Yours in confusion,

Graeme

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

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

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

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