#!/usr/local/bin/python
import sys, os, string
from pyPgSQL import PgSQL
 
def instructions():
    print """
This program is a basic example of using PgSQL and Python to access a
PostgreSQL database.

Usage:	basic.py DSN user password

Where	DSN is "{host:{port}:}database".  Note: Items between { } are optional.
	user is the user ID to use when connecting to the database.
	password is the password to use when connecting to the database.
	"""

class basic:
    """
basic - a short example of using PgSQL loosely based on the basic.java
	program supplied with the PostgreSQL jdbc driver. """

    def __init__(self, *args):
	if len(args) < 3:
	    raise Exception, "Wrong number of arguments"

	dsn, user, pswd = args

	dsnc = string.split(dsn, ":")

	ldsnc = len(dsnc)

	if ldsnc == 1:
	    dbase = dsnc[0]
	    dsn = "::%s:%s:%s" % (dsn, user, pswd)
	elif ldsnc == 3:
	    dbase = dsnc[2]
	    dsn = "%s:%s:%s" % (dsn, user, pswd)
	else:
	    raise Exception, "Invalid DSN value"

	# If the dbase variable is empty, then put the user name of the user
	# running the program into dbase.
	if dbase == None or dbase == "":
	    if os.environ.has_key("LOGNAME"):
		dbase = os.environ["LOGNAME"]
	    elif os.environ.has_key("USER"):
		dbase = os.environ["USER"]
	    elif os.environ.has_key("USERNAME"):
		dbase = os.environ["USERNAME"]
	    else:
		dbase = "*UNKNOWN*"

	# Connect to the database
	print "Connecting to database %s as user %s" % (dbase, user)
	self.db = PgSQL.connect(dsn)

	print "Connected ... Now creating a cursor"
	self.st = self.db.cursor()

	# Clean up from a previous run, if needed.
	self.cleanup()

	# Run the tests
	self.doexample()

	# Clean up the database.
	self.cleanup()

	print "\nNow closing the connection"
	self.st.close()
	self.db.close()

    def cleanup(self):
	try:
	    self.st.execute("DROP TABLE basic")
	except:
	    pass

    def doexample(self):
	print "\nRunning tests:"

	# First we need a table to store data in
	self.st.execute("CREATE TABLE basic (a int2, b int2)")

	# Now insert some data, using the cursor
	self.st.execute("INSERT INTO basic VALUES (1, 1)")
	self.st.execute("INSERT INTO basic VALUES (2, 1)")
	self.st.execute("INSERT INTO basic VALUES (3, 1)")

	# This shows how to get the OID of the just inserted row
	self.st.execute("INSERT INTO basic VALUES (4, 1)")
	insertedOID = self.st.oidValue
	print "Inserted row with oid %d" % insertedOID

	# Now change the value of b from 1 to 8
	self.st.execute("UPDATE basic SET b = 8")
	print "Updated %s rows" % (self.st.res.cmdTuples, )

	# Now delete two rows
	self.st.execute("DELETE FROM basic WHERE a < 3")
	print "Deleted %s rows" % (self.st.res.cmdTuples, )

	# Now add some rows using executemany()
	# The values (4, 2), (4, 3), and (4, 4) will be added.
	parms = []
	for i in range(2, 5):
	    parms.append((4, i))
	self.st.executemany("INSERT INTO basic VALUES (%s, %s)", parms)

	# Finally perform a query on the table, showing how to access the
	# data by column number.
	print "\nPerforming a query, referencing the data by column number"
	print """i.e. 'print "    a=%d b=%d" % (res[0], res[1])'"""
	self.st.execute("SELECT a, b FROM basic")
	res = self.st.fetchone()
	while res:
	    print "    a=%d b=%d" % (res[0], res[1])
	    res = self.st.fetchone()

	# show how to access the data by column name.
	print "\nDisplaying results, referencing the data by column name"
	print """i.e. 'print "    a=%d b=%d" % (res["a"], res["b"])'"""
	self.st.rewind()
	res = self.st.fetchone()
	while res:
	    print "    a=%d b=%d" % (res["a"], res["b"])
	    res = self.st.fetchone()

	# Finally perform a query on the table, showing how to access the
	# data by column name.
	print "\nDisplaying results, using column name as an attribue"
	print """i.e. 'print "    a=%d b=%d" % (res.a, res.b)'"""
	self.st.rewind()
	res = self.st.fetchone()
	while res:
	    print "    a=%d b=%d" % (res.a, res.b)
	    res = self.st.fetchone()

	# Redisplay the results, showing how to access the data using the
	# result as a dictionary.
	print "\nDisplaying results, using the ResultSet as a dictionary"
	print """i.e. 'print "    a=%(a)d b=%(b)d" % res'"""
	self.st.rewind()
	res = self.st.fetchone()
	while res:
	    print "    a=%(a)s b=%(b)s" % res
	    res = self.st.fetchone()

if __name__ == "__main__":
    try:
	if len(sys.argv) < 4:
	    raise Exception, "Wrong number of arguments"
	basic(sys.argv[1], sys.argv[2], sys.argv[3])
    except Exception, m:
	print m
	instructions()