odd transaction behavior

Hello all!  I'm a newbie to this mailing list but I've been using psycopg
for quite a while.  I hope you can help me!

I recently changed our isolation level from 'READ COMMIT' to 'AUTOCOMMIT'
and have seen some odd behavior when I want to post a multiple line
transaction.  I am using
'conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)'
in the connection class to make all transactions auto commit.  Then I am
using cursor.execute('BEGIN') to start a longer transaction.  Is this what I
should be doing?  Because conn.commit() doesn't seem to work on these longer
transactions.  BUT, if I do cursor.execute('commit'), then the transactions
are posted. Should conn.commit() work?  If so, what am I doing wrong?  This
is a viable way to commit the data, but I'm just wondering if it is the
'correct' way.

Below is some code to reproduce the problem.  Before I started, I added the
table 'test' to my database with the command:
CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);

I am running:
psycopg2 version '2.0.8 (dec mx dt ext pq3)'
postgres version: 8.4
python version: 2.6.2

Thanks in Advance!
Kerri

----
import psycopg2
import psycopg2.extensions

def getConnection():
        # connect to db
        conn = psycopg2.connect("dbname=yourdb user=yourusr")

        # set auto commit

conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        return
conn

def test1():
        # prints None
        conn = getConnection()

        cur = conn.cursor()
        print 'test with conn.commit() after closing cursor()'
        cur.execute('BEGIN')
        cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100,
"abc'def"))

cur.close()

conn.commit()

conn.close()

        conn = getConnection()
        cur = conn.cursor()
        cur.execute("SELECT * FROM test;")
        x = cur.fetchone()
        print x
        cur.close()
        conn.close()

def test2():
        # prints None
        conn = getConnection()

        cur = conn.cursor()
        print 'test with conn.commit() before closing cursor'
        cur.execute('BEGIN')
        cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100,
"abc'def"))

conn.commit()

cur.close()

conn.close()

        conn = getConnection()
        cur = conn.cursor()
        cur.execute("SELECT * FROM test;")
        x = cur.fetchone()
        print x
        cur.close()
        conn.close()

def test3():
       # prints (10, 100, "abc'def")
        conn = getConnection()

        cur = conn.cursor()
        print "test with curs.execute('commit')"
        cur.execute('BEGIN')
        cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100,
"abc'def"))
        cur.execute('commit')
        cur.close()
        conn.close()

        conn = getConnection()
        cur = conn.cursor()
        cur.execute("SELECT * FROM test;")
        x = cur.fetchone()
        print x
        cur.close()
        conn.close()

if __name__ == '__main__':
        test1()
        test2()
        test3()

-- 
If you have an emergency, and can't reach me, please call Craig at
928-257-4980 or the help desk at 928-502-4202.
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[email protected]      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg