Psql command line command script in python

Question

Background ... I have a black box application with a black box on which there is a postgres database on the rear panel. Although I have access to the command line, psql and a fairly simple Python 2.7 installation, this is quite limited (there is no way to install additional python libraries, for example, yes, I know that I could hack this, but there is a contract as a practical element of this)

Problem ... A table in the database stores images in the bytea format. based on some parameters passed from the browser when calling ajax, I need to extract the image in / 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...

Return to Python.

I don't have sql libraries, but I have os and subprocess

So, to request db, I would use os for:

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

os_str - psql SQL

script :

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()

, , /, .

, , ,

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

, , ,

Answer 1

, os.popen. subprocess. , , subprocess, : " sql, os subprocess."

, subprocess, , , , ; , :

; '+ uid + SQL. , , .

, , ; SQL, pgsql. , .

:

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)

---

, , , ( ' " ; ):

• "\'" - , "'". , - , , , r"\'".
• \' . pgsql, pgsql . , \'. , ", .
• , , . , , ', , ' , . ( Unix Windows.) , , `sql.replace( "'", r "\'".
• , . , "\c" , r"\c", , , ; escape-, , .

, :

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