Using Python Subprocess module to import shapefile into postgres

Question

I am trying to use your mentioned code..but getting an error.

The code used is mentioned below:

import os import subprocess

cmd = 'shp2pgsql -s 4326 E:\Forest\ForestFire\28052018\VNP14IMGTDL_NRT_Global_24h.shp vnp14imgtdl_nrt_global_24h | psql -h localhost -d scripting -U postgres -P postgres -q'

subprocess.call(cmd, shell=True)

The error it reproduces is while running in Python command line :

'shp2pgsql' is not recognized as an internal or external command, operable program or batch file. 255

What can I do?

Answer 1

I have had this sort of problem before, but with raster2pgsql, although from some light reading of the documentation, it seems like the similarities are numerous.

I hope my solution also works with shp2pgsql, with some argument changes :

from subprocess import Popen, PIPE

# Specify the paths to the programs you want to use. r'string' means the path is treated as a literal
shp2pgsql = r'C:\Program Files\PostgreSQL\9.6\bin\shp2pgsql.exe'
psql = r'C:\Program Files\PostgreSQL\9.6\bin\psql.exe'

# Specify your desired input file, and output table names.
input = r'E:\Forest\ForestFire\28052018\VNP14IMGTDL_NRT_Global_24h.shp'
output = 'vnp14imgtdl_nrt_global_24h'

# Turns the shp file into an sql query, temporarily stores it in an sql file.
command1 = [shp2pgsql, '-s', '4326', input, output, '>', 'temp.sql']
# Runs the temporary sql file, using a connection URI like postgresql://username:password@host:port/dbname
command2 = [psql, '-f', 'temp.sql', '-d', 'postgresql://postgres:postgres@localhost:5432/scripting']

# Running command 1.
process = Popen(command1, stdout = PIPE, stderr = PIPE, shell = TRUE)

# Print whatever the shell would normally display.
stdout = process.communicate()[0].decode('utf-8').strip()
print(stdout)

# Running command 2.
process = Popen(command2, stdout = PIPE, stderr = PIPE)

stdout = process.communicate()[0].decode('utf-8').strip()
print(stdout)

You should normally try to avoid two things here.

1. Using shell = TRUE is usually undesirable as it is a big security flaw within your code. If you're using it, make sure that the files you're importing are trustworthy.

2. In our connection URI we're specifying our username and password to the database. This is obviously something we'd like to avoid. Although in this case, you're working with a local database, so I assume that everything is under control.

A few other things :

Generally speaking, try using the subrocess module instead of the os module. Although it can appear less simple or straightforward, it is more up-to-date and well documented

The somewhat complex process.communicate()[0].decode('utf-8').strip() part ensures the shell messages to be displayed correctly.

Since I've never used shp2pgsql before, I'm not sure that the whole of my code works as it should. In any case, it should give you the general gist of a working syntax. I hope this works for you !

Answer 2

Using shell = TRUE is usually undesirable as it is a big security flaw within your code.

Here is an example without shell=True and using the more recent function subprocess.run which then calls lower-levels Popen function and others.

Also using pathlib to have clean paths on Windows.

import os
import subprocess
from pathlib import Path


shp2pgsql = subprocess.run(
    [
        Path(os.path.expandvars("$PROGRAMFILES"), "postgresql", "13", "bin", "shp2pgsql.exe"),
        Path("path", "to", "data file.shp")
    ],
    capture_output=True
)

output = subprocess.run(
    [
        Path(os.path.expandvars("$PROGRAMFILES"), "postgresql", "13", "bin", "psql.exe"),
        "service=my_service_name"
    ],
    input=shp2pgsql.stdout, stderr=subprocess.PIPE)

if output.returncode != 0:
    raise ValueError(output.stderr.decode("cp1252"))