python + postgres psql + os.popen

[damacy]

hello, everyone.

i am trying to write a program which executes SQL commands stored in
..sql files.

i wrote a function called psql() whose contents look like the
following.

....
os.popen(comman d)
file = os.popen(comman d, 'w')
file.write(pass word)
file.close()
....

where command looks like
psql -h [host] -d [dbname] -U [username] -W -f "[filename]"

this works well. however, it does not show me any warning nor error
messages if there is one. for example, i am trying to create a table
which already exists in the database, it should show me a warning/error
message saying there already is one present in the database, or
something like that.

can anyone help me?

[Laszlo Nagy]

Re: python + postgres psql + os.popen

damacy írta:[color=blue]
> hello, everyone.
>
> i am trying to write a program which executes SQL commands stored in
> .sql files.
>
> i wrote a function called psql() whose contents look like the
> following.
>
> ...
> os.popen(comman d)
> file = os.popen(comman d, 'w')
> file.write(pass word)
> file.close()
> ...
>
> where command looks like
> psql -h [host] -d [dbname] -U [username] -W -f "[filename]"
>
> this works well. however, it does not show me any warning nor error
> messages if there is one. for example, i am trying to create a table
> which already exists in the database, it should show me a warning/error
> message saying there already is one present in the database, or
> something like that.
>
> can anyone help me?
>[/color]
You can put this in the beginning of your SQL file:

\set ON_ERROR_STOP

If you also want to know what command caused the error:

\set ECHO all

You can also use a library written for Python. For example, psycopg

http://initd.org/projects/psycopg1

Best,

Laszlo

[Bruno Desthuilliers]

Re: python + postgres psql + os.popen

damacy wrote:[color=blue]
> hello, everyone.
>
> i am trying to write a program which executes SQL commands stored in
> .sql files.
>
> i wrote a function called psql() whose contents look like the
> following.
>
> ...
> os.popen(comman d)
> file = os.popen(comman d, 'w')
> file.write(pass word)
> file.close()
> ...
>
> where command looks like
> psql -h [host] -d [dbname] -U [username] -W -f "[filename]"
>
> this works well.[/color]

But is a very strange way to access a RDBMS from Python code. Are you
aware of the existence of db modules ?
[color=blue]
> can anyone help me?[/color]

http://www.python.org/dev/peps/pep-0249/

http://initd.org/projects/psycopg1

--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'onurb@xiludom. gro'.split('@')])"

[Simon Forman]

Re: python + postgres psql + os.popen

damacy wrote:[color=blue]
> hello, everyone.[/color]
....[color=blue]
> this works well. however, it does not show me any warning nor error
> messages if there is one. for example, i am trying to create a table
> which already exists in the database, it should show me a warning/error
> message saying there already is one present in the database, or
> something like that.
>
> can anyone help me?[/color]

I recently needed to use psql from python on a computer that I couldn't
install psycopg on and I used something similar to this to do it (I
edited the code slightly to make it clearer):

from subprocess import Popen, PIPE

# Pass the password through an environment
# variable to prevent psql asking for it.
psql_env = dict(PGPASSWORD ='********')

# Create the subprocess.
proc = Popen(cmd, shell=True, env=psql_env, stdout=PIPE, stderr=PIPE)

# Try reading it's data.
data = proc.stdout.rea d()

# Check for errors.
err = proc.stderr.rea d()
if err: raise Exception(err)


It worked nicely for me, YMMV.


Hope that helps,

~Simon

[damacy]

Re: python + postgres psql + os.popen

hi, there. thanks for the help.

now i have a different problem now. i decided to use 'subprocess' and
'Popen' objects instead of 'os.popen()' function, which i believe do
not make much difference.

my code is like the following...

[1] link = subprocess.Pope n(command, stdin = subprocess.PIPE , stdout =
subprocess.PIPE , stderr = subprocess.PIPE , shell = True)
[2] link.communicat e(password)
[3] link.wait()
[4] err = link.communicat e()[1]
[5] if err != None: print str(err)

i have read several threads about 'subprocess' posted on this group and
still i have way too much confusion regarding the above section of
code.

1. i'm currently using MS Windows.
i remember some have said that communicate() function is not usable on
this OS.
could anyone confirm this?

2. i'm expecting an error message, as i am trying to create a table
which does already exist in the database.
but if i try to print out the error message as [5], it is just an EMPTY
string.
and, if i try the SAME THING using command-line, i get a correct error
message this time ('psql:createst udent.sql:12: ERROR: relation
"student" already exists').

HOWEVER, if i comment out [2] link.communicat e(password), meaning i do
not supply a password, it shows an error message, 'psql: fe_sendauth:
no password supplied', which is correct as expected.

my question is...
why does it work (i.e. showing a correct error message) when no
password supplied but NOT when creating a table which already exists in
the database? it should work for both cases.

thank you very much.


Simon Forman wrote:[color=blue]
> damacy wrote:[color=green]
> > hello, everyone.[/color]
> ...[color=green]
> > this works well. however, it does not show me any warning nor error
> > messages if there is one. for example, i am trying to create a table
> > which already exists in the database, it should show me a warning/error
> > message saying there already is one present in the database, or
> > something like that.
> >
> > can anyone help me?[/color]
>
> I recently needed to use psql from python on a computer that I couldn't
> install psycopg on and I used something similar to this to do it (I
> edited the code slightly to make it clearer):
>
> from subprocess import Popen, PIPE
>
> # Pass the password through an environment
> # variable to prevent psql asking for it.
> psql_env = dict(PGPASSWORD ='********')
>
> # Create the subprocess.
> proc = Popen(cmd, shell=True, env=psql_env, stdout=PIPE, stderr=PIPE)
>
> # Try reading it's data.
> data = proc.stdout.rea d()
>
> # Check for errors.
> err = proc.stderr.rea d()
> if err: raise Exception(err)
>
>
> It worked nicely for me, YMMV.
>
>
> Hope that helps,
>
> ~Simon[/color]

[damacy]

Re: python + postgres psql + os.popen

hi, there. thanks for the help.

now i have a different problem now. i decided to use 'subprocess' and
'Popen' objects instead of 'os.popen()' function, which i believe do
not make much difference.

my code is like the following...

[1] link = subprocess.Pope n(command, stdin = subprocess.PIPE , stdout =
subprocess.PIPE , stderr = subprocess.PIPE , shell = True)
[2] link.communicat e(password)
[3] link.wait()
[4] err = link.communicat e()[1]
[5] if err != None: print str(err)

i have read several threads about 'subprocess' posted on this group and
still i have way too much confusion regarding the above section of
code.

1. i'm currently using MS Windows.
i remember some have said that communicate() function is not usable on
this OS.
could anyone confirm this?

2. i'm expecting an error message, as i am trying to create a table
which does already exist in the database.
but if i try to print out the error message as [5], it is just an EMPTY
string.
and, if i try the SAME THING using command-line, i get a correct error
message this time ('psql:createst udent.sql:12: ERROR: relation
"student" already exists').

HOWEVER, if i comment out [2] link.communicat e(password), meaning i do
not supply a password, it shows an error message, 'psql: fe_sendauth:
no password supplied', which is correct as expected.

my question is...
why does it work (i.e. showing a correct error message) when no
password supplied but NOT when creating a table which already exists in
the database? it should work for both cases.

thank you very much.


Simon Forman wrote:[color=blue]
> damacy wrote:[color=green]
> > hello, everyone.[/color]
> ...[color=green]
> > this works well. however, it does not show me any warning nor error
> > messages if there is one. for example, i am trying to create a table
> > which already exists in the database, it should show me a warning/error
> > message saying there already is one present in the database, or
> > something like that.
> >
> > can anyone help me?[/color]
>
> I recently needed to use psql from python on a computer that I couldn't
> install psycopg on and I used something similar to this to do it (I
> edited the code slightly to make it clearer):
>
> from subprocess import Popen, PIPE
>
> # Pass the password through an environment
> # variable to prevent psql asking for it.
> psql_env = dict(PGPASSWORD ='********')
>
> # Create the subprocess.
> proc = Popen(cmd, shell=True, env=psql_env, stdout=PIPE, stderr=PIPE)
>
> # Try reading it's data.
> data = proc.stdout.rea d()
>
> # Check for errors.
> err = proc.stderr.rea d()
> if err: raise Exception(err)
>
>
> It worked nicely for me, YMMV.
>
>
> Hope that helps,
>
> ~Simon[/color]