Python OS PostreSQL and quotes
Question: I can find plenty here about quotes in PSQL but nothing that quite fits this
problem.

First it's a kludge. I know it's a kludge but I think I'm stuck with it (open
to other alternatives though)

I have a near black box third-party linux appliance to which I have limited
access I have bash, python and psql to work with. I don't have psycopg2 or any
other pg libraries.

The DB I have to work with uses case-sensitive table names that need to be
quoted (don't ask...)

So, at the moment I write OS shell commands to get data which I then fiddle
about with and convert to JSON for my needs

A simple example:

    
    
    pg_str = "psql -U pword dbname -A -t -c "
    sql = "SELECT * FROM \"Addresses\" WHERE id=999"
    os_str = pg_str + "\'" + sql + "\'" + ";"
    data = string.split(os.popen(os_str).read())
    

No problem with that. I'm not claiming it's pretty, but it works (remember I
can't import any db libraries...)

It all goes wrong when I have a where clause on a text field:

    
    
    pg_str = "psql -U pword dbname -A -t -c "
    sql = "SELECT * FROM \"Addresses\" WHERE town='london'"
    os_str = pg_str + "\'" + sql + "\'" + ";"
    data = string.split(os.popen(os_str).read())
    

Too many quote combinations to cope with...?

I've obviously tried lots of escape combinations and have been googling for
several hours, but every solution seems to require libraries that I haven't
got access to.

I'm no python or psql expert - this is about my current limit. I feel sure
that I'm going about it the wrong way but am currently beaten on figuring out
the right way...


Answer:   1. There's no need to `\` escape `'` characters inside of `"` strings.
  2. You can use `"""` strings to avoid needing to escape `"` characters.
  3. Use string.replace to quote `'` characters for the shell, by replacing them with `'\''`. The resulting string will need to be surrounded by unescaped `'` characters when passed to the shell.

Using these rules, the SQL string can be made easily readable and editable:

    
    
    pg_str = "psql -U pword dbname -A -t -c "
    sql = """SELECT * FROM "Addresses" WHERE town='london'"""
    sql = sql.replace("'", "'\\''")
    os_str = pg_str + "'" + sql + "'" + ";"
    data = string.split(os.popen(os_str).read())
    

