python 2.6 subprocess enter psql password C shell

Question

I am not able to install any additional modules on an AIX server I am scripting on. It has Python 2.6.2 installed and basically I am trying to run the below command

psql -c "select * from cms_agprm;" -o u350932.txt -d tctmsv80 -U tctmsv80

Now I have done some goggling and found a few different recommendation on how to do this. None with much success. I'm also a little paranoid because I am dealing with processes and want to make sure that i get things right in this script otherwise things could go pear shaped very quickly.

I have tried setting the enviroment variable so i am not prompted for a password when making a psql call.

p_env = subprocess.Popen(["setenv", "PGPASSWORD", "password"], shell=True)
print_env = subprocess.Popen(["env"], shell=True)
print 'sending psql command'
p = subprocess.Popen(["psql", "-c", "select * from cms_agprm;", "-o", "/apps/ctm/80/devsv/temp/old_jobs/u350932.txt", "-d", "tctmsv80", "-U", "tctmsv80"], shell=True, stdin=subprocess.PIPE, stderr=subprocess.PIPE)

this seems to be the most elegant way of doing this as I do not have to deal with input and output of this process command (password prompts etc). However the code seems to run without error but i am not left with an output file of that query. The environment variable also appears to be set. I have no idea what is going on, any help on what I might be doing wrong here would be greatly appreciated.

UPDATE:

I realised that I need to pass all the environmental variables when using env=.... This will be more of a challenge as I have to set quite a few. It would be easier just to enter the password. I have tried the below code with no luck.

print 'sending psql command'
p = subprocess.Popen(["psql", "-c", "'select * from cms_agprm;'", "-d", "tctmsv80", "-U", "tctmsv80"], shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
p.stdout.read()
p.stdin.write('password\n')
p.stdin.flush()
p.kill()

hanging output:

ctmtest1-tctmsv80 [8] python clean_jobs_main.py
script to clean up old jobs
sending psql command
Password:

after I press enter:

Traceback (most recent call last):
  File "clean_jobs_main.py", line 21, in <module>
    sys.exit(main())
  File "clean_jobs_main.py", line 18, in main
    old_job_list = get_old_jobs()
  File "clean_jobs_main.py", line 12, in get_old_jobs
    p.stdin.write('password\n')
IOError: [Errno 32] Broken pipe

UPDATE 2:

I went back to the drawing board and feel I have gotten closer.

command_line = "psql -c 'select * from cms_agprm;' -o u350932.txt -d tctmsv80 -U tctmsv80"
print(command_line)
p_cmd = shlex.split(command_line)
print p_cmd
print 'sending psql command'
p = subprocess.Popen(p_cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
r = p.communicate('password\n')
print r

output: it hangs until i press enter

ctmtest1-tctmsv80 [6] python clean_jobs_main.py
script to clean up old jobs
psql -c 'select * from cms_agprm;' -o u350932.txt -d tctmsv80 -U tctmsv80
['psql', '-c', 'select * from cms_agprm;', '-o', 'u350932.txt', '-d', 'tctmsv80', '-U', 'tctmsv80']
sending psql command
Password for user tctmsv80:

when I press enter:

('', 'psql: fe_sendauth: no password supplied\n')

So it doesn't look like my communicate method is working but im not sure why?

Cheers