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?

  • 1
    add the full path of shp2pgsql, for example cmd = C:\postgres\bin\shp2pgsql -s 4326... or you can add the bin path to your environment variable – obchardon May 28 '18 at 10:26
  • @obchardon Thanks for your response. I tried it. Edited the code as mentioned: import os import subprocess cmd = 'C\Program Files\PostgreSQL\9.6\bin\postgisgui\shp2pgsql-gui -s 4326 E\JharkhandForest\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) Still gives error: The system cannot find the path specified. – Aroop Chakraborty May 28 '18 at 10:37
  • shp2pgsql should be in the bin folder, do not call the GUI ! Also C\ is not a valid path in windows. – obchardon May 28 '18 at 10:43
  • 1
    "C:\Program Files\PostgreSQal\9.6\bin\shp2pgsql.exe" - it also has to be a string literal, otherwise the space character is interpreted as command separator (note the " enclosing), or have Python escape that for you. – geozelot May 28 '18 at 10:50
  • ah man...bad typo in my comment, it's ...\PostgreSQL\... of course! – geozelot May 28 '18 at 12:26

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

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

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

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 !

| improve this answer | |
  • Nothing changed till this line command2 = [psql, '-f', 'temp.sql', '-a', 'postgresql://postgres:postgres@localhost:5432/scripting'] process = Popen(command1, stdout = PIPE, stderr = PIPE) stdout = process.communicate()[0].decode('utf-8').strip() print(stdout) process = Popen(command2, stdout = PIPE, stderr = PIPE) stdout = process.communicate()[0].decode('utf-8').strip() print(stdout) – Aroop Chakraborty May 29 '18 at 6:40
  • Thanks for your reply, the code works without any error..but no new rows are inserted. I have changed the code at some points, shown in the above comment. – Aroop Chakraborty May 29 '18 at 6:42
  • @AroopChakraborty I see that you have removed the shell = TRUE part of the first command. This means that the result won't be piped into the temporary sql file. As I mentionned, it's usually better not to use it, but here it is sadly necessary. There are definitely other approaches that could work, but in the mean time that's ly only solution. – GHRF May 29 '18 at 7:48
  • Thanks for your reply. Ignore the previous comment. I had included the shell command. The query ran fine, but did not insert any rows in the column. Can you share any link, where I can check the construct for the query. – Aroop Chakraborty May 29 '18 at 15:36
  • @AroopChakraborty you could try the subprocess module documentation (which I linked in my post), the postgresql documentation for how to connect properly to the database (could be the issue), and both shp2pgsql and psql documentations. Does your shell return anything suspicious ? Could you perhaps share what it prints ? – GHRF May 29 '18 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.