pyPgSql there is already a transaction in progres

[Tep]

On Jun 2, 3:58 pm, Paul Boddie <p... at boddie.org.uk> wrote:
> On 2 Jun, 15:32, someone <petshm... at 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