Python subprocess (shell=True), not working for postgres command

Question

Using the command line, I confirm that the following commands executes correctly

echo '\c mydatabase;\i db-reset.sql' | psql -U postgres -h localhost

However, in Python, I can confirm that the following lines do absolutely nothing, and return an status code of 0.

import subprocess

code = subprocess.call(r"echo '\c mydatabase;\i db-reset.sql' | psql -U postgres -h localhost", shell=True)
assert code == 0 # This comes to true

Essentially, why is the command invoked using subprocess not actually doing anything?

Igor Chubin · Accepted Answer · 2014-01-02 17:56:57Z

6

It works, but you need more backslashes. Also, I would recommend you don't use shell=True here.

That is what you do, but without shell:

p = subprocess.Popen(['psql', '-U', 'postgres', '-h', 'localhost'], shell=False, stdin=subprocess.PIPE)
p.communicate(r"\c mydatabase;\i db-reset.sql")

answered Jan 2, 2014 at 17:56

Igor Chubin

65.1k14 gold badges132 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bill Over a year ago

You are right, actually. I just figured this out myself. I needed one extra slash before each one.

Brenda J. Butler Over a year ago

I don't think you need extra backslashes because you are using the r prefix to the string. But the code sample that Igor gave is good.

Igor Chubin Over a year ago

@BrendaJ.Butler: No, Brenda, you need extra backslashes, but that is not because of python, but because of shell. Take a look here: pastebin.com/N3teJ9zh

Brenda J. Butler Over a year ago

but you have shell=False? In your dpaste you have shell=True

Igor Chubin Over a year ago

@BrendaJ.Butler: in my answer with shell=False I have no extra backslashes

Craig Ringer · Accepted Answer · 2014-01-02 19:35:25Z

Igor has the right approach without a doubt - though it'd be a good idea to close the session afterwards. However, there's a bigger picture issue here, which is that you should not generally be invoking psql to communicate with PostgreSQL from Python.

Use the psycopg2 module, which is widespread and available almost everywhere, to talk to PostgreSQL directly. This will immensely simplify your database communications.

For cases where you actually need psql, like running scripts, please use psql -f and a database argument. Your command in this case should be:

try:
    subprocess.check_call([
        'psql', '-q',
        '-U', 'postgres', 
        '-h', 'localhost',
        '-f', 'db-reset.sql', 
        'mydatabase'
     ])
except subprocess.CalledProcessError, ex:
    print("Failed to invoke psql: {0}".format(ex))

... or even better, use check_output if you're on a new enough Python version, so you capture error output too. Note the -q (quiet mode) flag, too.

(Note that subprocess will do its own escaping when you're running on a platform like Windows where there's no sensible execv variant system calls or equivalents. So you don't need to care about painful shell escaping quirks.)

Collectives™ on Stack Overflow

Python subprocess (shell=True), not working for postgres command

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related