Обсуждение: 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