Talk:Using psycopg2 with PostgreSQL

Please note this is in progress, I just started on this (today -> Feb 12, 2010) and I will keep editing today, and the rest of this week.

FINISH ADDING EXAMPLES FROM THIS CRAP:

#!/usr/bin/python
#test postgres connection
import psycopg2
print "Content-type: text\html"
print
conn = psycopg2.connect("\
	dbname='db_username'\
	user='username'\
	host='postgres.int.devisland.net'\
	password='password'\
");
c = conn.cursor()
c.execute("SELECT * FROM test")
records = c.fetchall()
import pprint
pprint.pprint(records)

You can iterate over records like this:

for row in curs.fetchall():
    name, ext = row[1].split('.')
    new_name = name + '_S.' + ext
    print "  writing %s to %s ..." % (name+'.'+ext, new_name),
    open(new_name, 'wb').write(row[2])
    print "done"
    print "  python type of image data is", type(row[2])

Here is an example of using the rollack and commit methods

curs = conn.cursor()
try:
    curs.execute("CREATE TABLE test_fetch (val int4)")
except:
    conn.rollback()
    curs.execute("DROP TABLE test_fetch")
    curs.execute("CREATE TABLE test_fetch (val int4)")
conn.commit()




Creating and dealing with a Cursor in Postgres:

# does some nice tricks with the transaction and postgres cursors
# (remember to always commit or rollback before a DECLARE)
#
# we don't need to DECLARE ourselves, psycopg now support named
# cursors (but we leave the code here, comments, as an example of
# what psycopg is doing under the hood)
#
#curs.execute("DECLARE crs CURSOR FOR SELECT * FROM test_fetch")
#curs.execute("FETCH 10 FROM crs")
#print "First 10 rows:", flatten(curs.fetchall())
#curs.execute("MOVE -5 FROM crs")
#print "Moved back cursor by 5 rows (to row 5.)"
#curs.execute("FETCH 10 FROM crs")
#print "Another 10 rows:", flatten(curs.fetchall())
#curs.execute("FETCH 10 FROM crs")
#print "The remaining rows:", flatten(curs.fetchall())

ncurs = conn.cursor("crs")
ncurs.execute("SELECT * FROM test_fetch")
print "First 10 rows:", flatten(ncurs.fetchmany(10))
ncurs.scroll(-5)
print "Moved back cursor by 5 rows (to row 5.)"
print "Another 10 rows:", flatten(ncurs.fetchmany(10))
print "Another one:", list(ncurs.fetchone())
print "The remaining rows:", flatten(ncurs.fetchall())
conn.rollback()




# libs for database interface
# we are exclusively using Postgres
# load the adapter
import psycopg2
# load the psycopg extras module
# we are loading this because we want to use named columns / dictionaries
import psycopg2.extras

# adapt allows us to quote strings directly :)
from psycopg2.extensions import adapt


cursor2.execute("SELECT pg_backend_pid()")

q_pid = cursor2.fetchone()[0]




cursor.execute("SELECT * FROM pg_catalog.pg_stat_activity WHERE datname=%s AND procpid=%s", (options.db_name,q_pid), async=1)

if(cursor.isready()):
			try:
				db_process = cursor.fetchall()
				db_process = db_process[0]
				query_active = False
				m_status = "Fetching Status"
			except:
				db_process = False


tdata['rowset'] = cursor.fetchall()


	collist = ', '.join(collist)
	collist = '(%s)' % (collist)
	colvals = ",".join(colvals)
	colvals = "(%s)" % (colvals)
	try:
		#execute can accept a LIST of arguments.
		#not sure if it has any automation for inserts or not.
		#does support bound variables through the 2nd argument
		cursor.execute("INSERT INTO %s %s VALUES %s;" % (table, collist, colvals))

	except:
		exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
		return False, "Database Insert Failed -> %s" % (exceptionValue)