Python Psql \copy CSV To Remote Server

I am attempting to copy a csv (which has a header and quote character ') with python 3.6 to a table on a remote postgres 10 server. It is a large CSV (2.5M rows, 800MB) and while I

Solution 1:

Try not to use shell=True if you can avoid it. better to tokenize the command yourself to help sh.

subprocess.call(["psql", "-U", "{user}", "-h", "{ip}", "-d", "{db}", "-w", "{pw}", "-c", "{copy statement}"])

In this case then your copy statement could be as it is passed to psql verbatim, because there are no shell quoting issues to take into account. (N.B. still have to quote this for python, so the string would remain as is).

---

If you still want to use shell=True then you have to escape the string literal for both python and shell

"\"\copy table (col1, col2) FROM file_location CSV HEADER QUOTE '\\\"' NULL ''\""

will create a string in python which will be

"\copy table (col1, col2) FROM file_location CSV HEADER QUOTE '\"' NULL ''\"

Which is what we found out we needed on our shell in the first place!

---

Edit (clarifying something from the comments):

subprocess.call, when not using shell=True, takes an iterable of arguments.

So you could have

psql_command = "\"\copy table (col1, col2) FROM file_location CSV HEADER QUOTE '\\\"' NULL ''\""
# user, hostname, password, dbname all defined elsewhere above.
command = ["psql",
    "-U", user,
    "-h", hostname,
    "-d", dbname,
    "-w", password,
    "-c", psql_command,
]

subprocess.call(command)

See https://docs.python.org/2/library/subprocess.html#subprocess.call or https://docs.python.org/3/library/subprocess.html#subprocess.call

extra edit :- Please note that to avoid shell injection, you should be using the method described here. See the warning section of https://docs.python.org/2/library/subprocess.html#frequently-used-arguments