Обсуждение: Update with function

Hello,
I am new to this list, and a fairly unsophisticated user of postgresql
and psycopg.

I try to update one field in all records of a table.
The new value is too complicated for a simple expression, so I thought
about a python function. That function uses another field, recno, from
each record.
My question is: how do I pass that second field to the function.

The relevant parts of my script are now:

define makeyear(nummer):
    # ...
    return value

cur.execute("update books set inyear=%s", (makeyear(recno),))

The error message says that recno is not defined.
egbert
--
Egbert Bouwman
Keizersgracht 197-II
1016 DS  Amsterdam
Tel 0(031)20 6257991

On 03/30/2012 03:58 AM, egbert wrote:
> Hello,
> I am new to this list, and a fairly unsophisticated user of postgresql
> and psycopg.
>
> I try to update one field in all records of a table.
> The new value is too complicated for a simple expression, so I thought
> about a python function. That function uses another field, recno, from
> each record.
> My question is: how do I pass that second field to the function.
>
> The relevant parts of my script are now:
>
> define makeyear(nummer):
>      # ...
>      return value
>
> cur.execute("update books set inyear=%s", (makeyear(recno),))
>
> The error message says that recno is not defined.


The parameter list you are passing lives outside the database you are
using so it has no reference to the field recno.

A simple solution that demonstrates one way to do the above:


cur.execute("select recno from some_table")

for row in cur:
    recno = cur[0]
    cur.execute("update books set inyear=%s", (makeyear(recno),))

> egbert


--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, 2012-03-30 at 06:45 -0700, Adrian Klaver wrote:

> A simple solution that demonstrates one way to do the above:
>
>
> cur.execute("select recno from some_table")
>
> for row in cur:
>     recno = cur[0]
>     cur.execute("update books set inyear=%s", (makeyear(recno),))
>
Thanks for your suggestion, Adrian.
Actually, that was something I tried first.
But my some_table is about 165000 records, and it took nearly three
hours (on my not so young system) to run the individual updates.
So I looked for a one-pass solution.
egbert

--
Egbert Bouwman
Keizersgracht 197-II
1016 DS  Amsterdam
Tel 0(031)20 6257991

On 03/30/2012 09:02 AM, egbert wrote:
> On Fri, 2012-03-30 at 06:45 -0700, Adrian Klaver wrote:
>
>> A simple solution that demonstrates one way to do the above:
>>
>>
>> cur.execute("select recno from some_table")
>>
>> for row in cur:
>>     recno = cur[0]
>>     cur.execute("update books set inyear=%s", (makeyear(recno),))
>>
> Thanks for your suggestion, Adrian.
> Actually, that was something I tried first.
> But my some_table is about 165000 records, and it took nearly three
> hours (on my not so young system) to run the individual updates.
> So I looked for a one-pass solution.

Another option is write function in Postgres that does what you want and
call that function.

> egbert
>


--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Mar 30, 2012 at 5:05 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 03/30/2012 09:02 AM, egbert wrote:
>>
>> On Fri, 2012-03-30 at 06:45 -0700, Adrian Klaver wrote:
>>
>>> A simple solution that demonstrates one way to do the above:
>>>
>>>
>>> cur.execute("select recno from some_table")
>>>
>>> for row in cur:
>>>        recno = cur[0]
>>>        cur.execute("update books set inyear=%s", (makeyear(recno),))
>>>
>> Thanks for your suggestion, Adrian.
>> Actually, that was something I tried first.
>> But my some_table is about 165000 records, and it took nearly three
>> hours (on my not so young system) to run the individual updates.
>> So I looked for a one-pass solution.
>
>
> Another option is write function in Postgres that does what you want and
> call that function.

Which could also be implemented in Python, via pl/python.

-- Daniele

On Fri, 2012-03-30 at 17:07 +0100, Adrian Klaver and Daniele Varrazzo
wrote:
> > Another option is write function in Postgres that does what you want and
> > call that function.
>
> Which could also be implemented in Python, via pl/python.
>
I will install and dive into plpython. Thanks.
egbert
--
Egbert Bouwman
Keizersgracht 197-II
1016 DS  Amsterdam
Tel 0(031)20 6257991