1 # fetch.py -- example about declaring cursors
3 # Copyright (C) 2001-2005 Federico Di Gregorio <fog@debian.org>
5 # This program is free software; you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by the
7 # Free Software Foundation; either version 2, or (at your option) any later
10 # This program is distributed in the hope that it will be useful, but
11 # WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY
12 # or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
16 ## put in DSN your DSN string
20 ## don't modify anything below tis line (except for experimenting)
28 print "Opening connection using dns:", DSN
29 conn = psycopg2.connect(DSN)
30 print "Encoding for this connection is", conn.encoding
34 curs.execute("CREATE TABLE test_fetch (val int4)")
37 curs.execute("DROP TABLE test_fetch")
38 curs.execute("CREATE TABLE test_fetch (val int4)")
41 # we use this function to format the output
44 """Flattens list of tuples l."""
45 return map(lambda x: x[0], l)
47 # insert 20 rows in the table
50 curs.execute("INSERT INTO test_fetch VALUES(%s)", (i,))
53 # does some nice tricks with the transaction and postgres cursors
54 # (remember to always commit or rollback before a DECLARE)
56 # we don't need to DECLARE ourselves, psycopg now support named
57 # cursors (but we leave the code here, comments, as an example of
58 # what psycopg is doing under the hood)
60 #curs.execute("DECLARE crs CURSOR FOR SELECT * FROM test_fetch")
61 #curs.execute("FETCH 10 FROM crs")
62 #print "First 10 rows:", flatten(curs.fetchall())
63 #curs.execute("MOVE -5 FROM crs")
64 #print "Moved back cursor by 5 rows (to row 5.)"
65 #curs.execute("FETCH 10 FROM crs")
66 #print "Another 10 rows:", flatten(curs.fetchall())
67 #curs.execute("FETCH 10 FROM crs")
68 #print "The remaining rows:", flatten(curs.fetchall())
70 ncurs = conn.cursor("crs")
71 ncurs.execute("SELECT * FROM test_fetch")
72 print "First 10 rows:", flatten(ncurs.fetchmany(10))
74 print "Moved back cursor by 5 rows (to row 5.)"
75 print "Another 10 rows:", flatten(ncurs.fetchmany(10))
76 print "Another one:", list(ncurs.fetchone())
77 print "The remaining rows:", flatten(ncurs.fetchall())
80 curs.execute("DROP TABLE test_fetch")