Execute ogr2ogr from python

Question

I tried to execute ogr2ogr from python with this shell command:

ogr2ogr.exe --config PG_LIST_ALL_TABLES YES -f "PostgreSQL" -append PG:"host=hostnamet user=username password=password dbname=dbname" nasfile.xml

The quick and dirty solution with os.system doesn't work. There is no data in the database after run this script:

import os
loadfile = path\\file.xml
command = "C:\\Program Files\\QGIS Chugiak\\bin\\ogr2ogr.exe --config PG_LIST_ALL_TABLES YES -f \"PostgreSQL\" -append PG:\"host=hostname user=username password=password dbname=dbname\" " + loadfile
os.system(command)

What is the right syntax to execute it with subprocess.call?

Answer 1

subprocess.call and subprocess.check_call both take lists with strings as input and separate each argument with a blank space. Your command would be:

import os, subprocess
base_path = "some/file/path"
loadfile = os.path.join(base_path, "file.xml")
command = ["C:\\Program Files\\QGIS Chugiak\\bin\\ogr2ogr.exe", 
 "--config", "PG_LIST_ALL_TABLES", "YES", "-f", "PostgreSQL", "-append",
 "PG:\"host=hostname user=username password=password dbname=dbname\"",
 loadfile]
subprocess.check_call(command)

Some things I learned the hard way:

1. Use subprocess.check_call with the GDAL command line utilities since, contrary to call, it raises an error when the return code is non-zero, i.e. your command failed.

2. Try to use os.path whenever possible since it takes care of connecting your file paths with the correct separators depending on the system the code is executed on.

edit: As of GDAL 2.1 you can also call ogr2ogr as a function with gdal.VectorTranslate - see changelog with examples.

Answer 2

I'm adding this answer here as an alternative to Kersten's, because Kersten's quotation and escape strings in the answer will throw errors on linux ("cannot connect to database"). The following demonstrates the correct annotation for connecting to a PostgreSQL database, while observing GDAL's strict adherence to string / variable inputs.

*Take specific note of the absence of quotations enclosing the database connection parameters following the PG: part of the string, which runs tangential to what most programmers are used to when using PostGIS, GDAL, and ogr2ogr.

def somefunction(): 
 # import the data
 try:
 def load(args):
 options = ['/usr/bin/ogr2ogr']
 options.extend(args)
 # record the output!
 subprocess.check_call(options,stderr=subprocess.STDOUT)
 load(['-t_srs', srcsrs, '-s_srs', srcsrs, '-explodecollections', '-f', 'PostgreSQL', 'PG:dbname=% host=localhost port=5432 user=postgres password=somepass' % somedb, '/some/path/file.ext', '--config', 'PG_USE_COPY', 'YES', '-nln', 'tablename', '-overwrite','-skipfailures'])
 # record an error if there is one
 except subprocess.CalledProcessError as e:
 print(str(e.output))

Answer 3

Kersten's answer gives me the following error when trying to insert into a table in PostgreSQL:

ERROR 1: PQconnectdb failed.
invalid connection option ""host"

I had to remove the quotation marks in "PG:\"host=hostname user=username password=password dbname=dbname\"".

Using "PG:host=hostname user=username password=password dbname=dbname" worked for me.