Thread: server side cursors update & delete,
Hi, The section '39.7.3.3. UPDATE/DELETE WHERE CURRENT OF' of http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html specifies the syntax as: UPDATE table SET ... WHERE CURRENT OF cursor; DELETE FROM table WHERE CURRENT OF cursor; Is there a way to generate these syntax for update & delete ? Right now, if i get the error that .execute() can't be called more than once ! -- TIA Vardhan
On 02/09/2016 01:31 AM, Vardhan wrote: > Hi, > > The section '39.7.3.3. UPDATE/DELETE WHERE CURRENT OF' of > http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html > specifies the syntax as: > > UPDATE table SET ... WHERE CURRENT OF cursor; > DELETE FROM table WHERE CURRENT OF cursor; > > Is there a way to generate these syntax for update & delete ? > > Right now, if i get the error that .execute() can't be called more than once ! Have you looked at?: http://initd.org/psycopg/docs/usage.html#server-side-cursors and http://initd.org/psycopg/docs/connection.html#connection.cursor > > -- > TIA > Vardhan > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Feb 9, 2016 at 8:43 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 02/09/2016 01:31 AM, Vardhan wrote: >> >> Hi, >> >> The section '39.7.3.3. UPDATE/DELETE WHERE CURRENT OF' of >> http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html >> specifies the syntax as: >> >> UPDATE table SET ... WHERE CURRENT OF cursor; >> DELETE FROM table WHERE CURRENT OF cursor; >> >> Is there a way to generate these syntax for update & delete ? >> >> Right now, if i get the error that .execute() can't be called more than >> once ! > > > Have you looked at?: > > http://initd.org/psycopg/docs/usage.html#server-side-cursors > > and > > http://initd.org/psycopg/docs/connection.html#connection.cursor >> Hi Adrian, I did went thru the documentation *twice*, before posting, but still possible that I missed sth. obvious. Actually, the first link states something which conflicts with pg doc: "Server side cursor are created in PostgreSQL using the DECLARE command and subsequently handled using MOVE, FETCH and CLOSE commands." Where as, the pg doc clearly states that UPDATE & DELETE .. where CURRENT OF <cursor> are also allowed. I was able to achieve what i wanted with this function: def myexec (c,query,vars=None): c = self.connection.cursor() c.execute(query + ' WHERE CURRENT OF "' + self.name + '"',vars) c.close() and call as : c = conn.cursor('c1') c.execute('select * from TEST;') c.itersize = 1 for r in c: print (r) myexec(c,'update TEST set ( num ) = ( %s ) ',(r[1]+7,)) Now , I totally understand that this is very inefficient because of several round trips, and attempt should be made to do these kind of things with server side functions, or may be collect id's and update in one go etc. Thanks Vardhan >> >> -- >> TIA >> Vardhan >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
On 02/09/2016 07:34 AM, Vardhan wrote: > On Tue, Feb 9, 2016 at 8:43 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 02/09/2016 01:31 AM, Vardhan wrote: >>> >>> Hi, >>> >>> The section '39.7.3.3. UPDATE/DELETE WHERE CURRENT OF' of >>> http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html >>> specifies the syntax as: >>> >>> UPDATE table SET ... WHERE CURRENT OF cursor; >>> DELETE FROM table WHERE CURRENT OF cursor; >>> >>> Is there a way to generate these syntax for update & delete ? >>> >>> Right now, if i get the error that .execute() can't be called more than >>> once ! >> >> >> Have you looked at?: >> >> http://initd.org/psycopg/docs/usage.html#server-side-cursors >> >> and >> >> http://initd.org/psycopg/docs/connection.html#connection.cursor >>> > > > Hi Adrian, > I did went thru the documentation *twice*, before posting, but > still possible that I missed sth. obvious. > > Actually, the first link states something which conflicts with pg doc: > > "Server side cursor are created in PostgreSQL using the DECLARE > command and subsequently handled using MOVE, FETCH and CLOSE > commands." > > Where as, the pg doc clearly states that UPDATE & DELETE .. where > CURRENT OF <cursor> are also allowed. Yeah, I was going back through the docs when you made this post. Realized the form you wanted is not directly supported. What you want is a variation of UPDATE/DELETE that walks through a CURSOR. I could see replicating that by using the psycopg2 named cursor support to 'attach' to a server-side cursor that has the WHERE condition you want and then walking through that and then, from docs: cur2 = conn.cursor('curname') for record in cur2: # or cur2.fetchone, fetchmany... # do something with record pass where 'do something' is the UPDATE or DELETE. > > I was able to achieve what i wanted with this function: > > def myexec (c,query,vars=None): > c = self.connection.cursor() > c.execute(query + ' WHERE CURRENT OF "' + self.name + '"',vars) > c.close() > > and call as : > > c = conn.cursor('c1') > c.execute('select * from TEST;') > c.itersize = 1 > for r in c: > print (r) > myexec(c,'update TEST set ( num ) = ( %s ) ',(r[1]+7,)) > > > Now , I totally understand that this is very inefficient because of > several round trips, > and attempt should be made to do these kind of things with server side > functions, > or may be collect id's and update in one go etc. > > > Thanks > Vardhan > > >>> >>> -- >>> TIA >>> Vardhan >>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com -- Adrian Klaver adrian.klaver@aklaver.com