#!/usr/bin/python

import psycopg2
import os

def testpg():
  conn = psycopg2.connect(dbstr())
  cur = conn.cursor()

  print "PRODUCTS BY COLOR:"
  cur.execute("select color, count(*) from PROD group by color;")
  for rec in cur:
    (color, cnt) = rec
    print color, cnt

  print "INDUSTRIES BY CUSTOMERS:"
  cur.execute("select indus, count(*) from CUST group by indus;")
  for rec in cur:
    (indus, cnt) = rec
    print indus, cnt

  print "SALES BY DATE (limit 5):"
  cur.execute("select sdate, count(*) from SALES group by sdate order by sdate limit 5;")
  for rec in cur:
    (sdate, cnt) = rec
    print sdate, cnt

  print "SALES BY EMPLOYEES:"
  cur.execute("select emp, salary, count(*) from SALES join EMP on emp=eid group by emp, salary;")
  fp = open("sales.dat", "w")
  fp.write("salary sales\n")
  for rec in cur:
    (emp, salary, cnt) = rec
    print emp, salary, cnt
    fp.write(str(salary) + " " + str(cnt) + "\n")
  fp.close()
  os.system("R --vanilla < sales.r")

  cur.close()
  conn.close()

def main():
  testpg()

def dbstr():
  return open("db.txt").read()

if __name__ == '__main__': main()