On Jun 2, 3:58 pm, Paul Boddie <p...@boddie.org.uk> wrote: > On 2 Jun, 15:32, someone <petshm...@googlemail.com> wrote: > > > Hi, > > I'm using pyPgSQL for accessing Postgres and do some update and select > > queries. > > and getting WARNING: there is already a transaction in progress if I > > run runUpdate more than once. > > I think this is because you're using explicit transaction statements > amongst the SQL statements you're sending to the database system, > whereas pyPgSQL probably starts transactions on your behalf if you've > not enabled autocommit. > > > So, what happens is following: > > > 1. SELECT address FROM address WHERE LOWER(address) = LOWER(%s); -- > > __existRecord > > 2. BEGIN;DELETE FROM failed WHERE uquery = %s;COMMIT; -- __delQuery > > 3. SELECT address FROM address WHERE LOWER(address) = LOWER(%s); -- > > again __existRecord > > and here I'm getting the warning. > > Here, statement #3 may well start a new transaction - a convenience > introduced by pyPgSQL in order to provide DB-API compliance and > automatic transactions - and when __delQuery is invoked, PostgreSQL > will complain that you are trying to start another transaction.
Ok, that make sense > > Really, you should use the commit method on the cursor object You mean connection object, do you? I've tried that, but forgotten to remove BEGIN;COMMIT; statements from my queries Now, I do commit on connection object after _each_ query and it seems to work :) > (self.db, I presume) and the rollback method when you want to start a > new transaction without changing anything. Alternatively, you could > set autocommit to true on the connection object and be sure to always > use transaction statements (BEGIN, COMMIT, ROLLBACK) where > appropriate. In that way it works too, which means, everything is clear now Thanks for help! > > Paul -- http://mail.python.org/mailman/listinfo/python-list
