Scripting a command line psql command in python

Question

Background... I have an almost black box web application appliance that has a postgres DB on the back end Although I have access to a command line, to psql and and to a fairly basic Python 2.7 install, this is fairly limited (no ability to install additional python libs for example - yes, I know I could hack this but there is a contractual as well as practical element to this)

Problem... A table in the DB stores images in bytea format. based on some parameters passed from a browser in an ajax call, I need to extract the image to /tmp

To do this from psql I can do:

\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = '12345678')) TO '/tmp/12345678.jpg'

So...

Back to Python.

I have no sql libraries but I do have os and subprocess

So normally, to query the db I'd use os to:

something = os.popen(os_str).read()

where os_str is a psql shell command with an SQL statement appended

At the moment my test script looks like:

import os, sys, cgi, cgitb

form = cgi.FieldStorage()

uid = form.getvalue('uid')
if uid is None : # missing user_id
    uid = "12345678"

imgType = form.getvalue('imgType')
if imgType is None : # missing imgType
    imgType = "png"

imgName = uid + "." + imgType

pg_str = "psql -U xxx yyy -A -t -c "
sql = "???"
os_str = pg_str + "\'" + sql + "\'" + ";"
os.popen(os_str).read()

I'm fairly certain that I'm in quote/escape hell here

I've tried seemingly endless combinations to do

sql = "\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = '+ uid + "')) TO '/tmp/" + imgName + "'"

Obviously, I know that's wrong, but it seems the simplest way to illustrate what I need

As a side note, you almost always want a raw string if you're writing literal strings with backslashes in them. In this case, you got lucky, because "\c" happens to be the same as r"\c", but that's not true in general. (Try a command that starts with an n and see what happens.) — abarnert
– abarnert, Commented Dec 11, 2013 at 1:29

abarnert · Accepted Answer · 2013-12-11 01:43:08Z

6

I have no idea why you're trying to use os.popen. The docs explicitly call it obsolete and deprecated, and tell you to use the subprocess module instead. And you obviously knew about subprocess because you said, "I have no sql libraries but I do have os and subprocess."

Anyway, if you use subprocess, you don't have to worry about getting the escaping correct to make a string that the shell can parse to the list of arguments you want it to get; just pass the list of arguments you want as a list:

And you've confused yourself with your single and double quotes; you've got a literal '+ uid + in the middle of your SQL string. This is one of the many reasons it's easier to use string formatting than concatenation.

Meanwhile, I'm pretty sure the ; is a terminator for the SQL statement you're passing to pgsql. This means it should be part of the argument.

So:

fmt = r"\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = {} TO '/tmp/{}';"
sql = fmt.format(uid, imgName)
pg = ['pgsql', '-U', 'xxx', 'yyy', '-A' ,'-t', '-c', sql]
output = subprocess.check_output(pg)

Anyway, as for what you got wrong in your quoting attempt, there's quite a bit, in addition to the two problems above (mixing up ' and " and putting the ; outside the quoted argument):

"\'" is the same string as "'". if you want to put literal backslashes into your strings literals, you need to escape them—or, better, just use raw strings, like r"\'".
But you don't want \' in the first place. You're not trying to pass single quotes through the shell to pgsql, you're just trying to quote the pgsql command for the shell. You can't do that by wrapping it in \'. You can do that by wrapping it in ", which is a lot easier.
You have single quotes within the string, which you can't do inside a single-quoted string without escaping them. So, everything from the start of the string to the first ' is quoted, then everything up to the next ' is not, and so on. (And the rules are again different on Unix and Windows.) You need to escape the single quotes—e.g., `sql.replace("'", r"\'".
You have backslashes within the non-raw string literal which you didn't escape. In this case, you get away with it, because "\c" happens to be the same as r"\c", but that's not true in general; unless you have the list of escape characters that escape to themselves memorized, don't ever rely on it.

So, if you really wanted to do it this way:

# same first two lines as above to create sql
escaped = sql.replace("'", r"\'")
os_str = 'psql -U xxx yyy -A -t -c "{}"'.format(escaped)

answered Dec 11, 2013 at 1:43

abarnert

368k54 gold badges626 silver badges691 bronze badges

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

5 Comments

PerryW Over a year ago

That's looking good, I'll give it a go BTW, sorry if I didn't make it clear, but the example string was intended as nothing more than a kind of pseudocode - a "here's what I want to do" rather than a "here's what I'm doing". I've tried so many variations of quotes, escapes and with or without semi-colons that I tried to pare it back to basics. As for os over subprocess - You're spot on - force of habit on my part. I have something that works for me so I've got into the (bad) habit of copy - paste- adapt

abarnert Over a year ago

@PerryW: One of the many reasons that subprocess was created, and popen eventually deprecated, is to let you avoid problems like this. I think the code I wrote at the end will separate, quote, and escape the arguments properly for the shell, at least on Unix, but I wouldn't guarantee it. Whereas it's blatantly obvious that the code at the beginning separates the arguments (because they're separate strings), and quotes and escapes them properly (because they don't need to be quoted and escaped at all).

PerryW Over a year ago

@abarnet Thanks - agree, this is a much better way of doing it. Currently trying to track down a " \copy: parse error at end of line " error. It also turn out that I'm stuck with Python 2.6 not 2.7 as I thought, so no check_output (trying subprocess.Popen) and I've changed to numbered params

PerryW Over a year ago

@abarnet Got there - missing a bracket. Many thanks. Working perfectly now

abarnert Over a year ago

@PerryW: You can either install the subprocess32 backport and use that, or just copy the source to check_output from 2.7. Not that it's that hard to do it yourself, but there are things I always forget to do (like checking the retcode)…

Collectives™ on Stack Overflow

Scripting a command line psql command in python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related