Re: Transaction persistence between execute() calls

2008/6/2 Clodoaldo <[email protected]>:
> I need that 5 queries fired by the same ajax request to a web python
> application see the same database snapshot and i don't understand how
> exactly to do it. Could it be like this:?
>
> import psycopg2 as db
> dsn = 'host=localhost dbname=dbname user=user password=passwd'
> connection = db.connect(dsn)
> cursor = connection.cursor()
>
> cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
> rs1 = cursor.execute(query_1, (param1,))
> rs2 = cursor.execute(query_2, (param2,))
> cursor.execute('COMMIT;');
>
> cursor.close()
> connection.close()
>
> Or:
>
> cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
> rs1 = cursor.execute(query_1, (param1,))
> rs2 = cursor.execute(query_2, (param2,))
>
> cursor.close()
> connection.commit()
> connection.close()
>
> Or something else? BTW i can't find the html doc with all the classes.
> Where is it?

Directly issuing BEGIN, COMMIT and ROLLBACK statements directly is
likely to confuse psycopg (unless you've manually switched to
autocommit mode).

Just use the standard DB-API connection.commit() and
connection.rollback() methods: the transaction will automatically be
started on the furst execute() call.

By default it'll probably use "read commit" isolation.  If you want
serializable, add the following call after creating the connection:

    from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
    connection.set_isolation_level(ISOLATION_LEVEL_SERIALIZABLE)

James.