Running gdal ogr2ogr command from python

Question

I have an ogr2ogr command which uploads an ESRI File Geodatabase to a PostgreSQL database, updating/overwriting the existing data:

ogr2ogr -f "PostgreSQL" PG:"host=hosting_url port=5432 dbname=database_name user=username password=password" -lco SCHEMA=schema_name "D:\data\Data.gdb" -overwrite -progress -lco OVERWRITE=YES --config PG_USE_COPY YES

This works as expected when run from OSGeo4W Shell (installed with my QGIS installation). However, I'd like to automate this command to run at the same time as an existing python script I have. So my challenge is to run the ogr2ogr command from python. How can I make it compatible?

I've tried:

import os, subprocess
command = ["C:\\Program Files\\QGIS 2.18\\bin\\ogr2ogr.exe", 
        "-f", "PostgreSQL", "PG:\"host=hosting_url port=5432 dbname=database_name user=username password=password\"", "-lco", "SCHEMA=schema_name", "D:\data\Data.gdb", "-overwrite", "-progress", "-lco", "OVERWRITE=YES", "--config", "PG_USE_COPY", "YES"]

I adapted this code from the top answer here: Execute ogr2ogr from python

When I run it I get the error 'returned non-zero exit status 1.'

Answer 1

First, we need gdal installed and set as an environment variable in Windows. Then:

Uploading Shapefile to postgres:

import os
connection = r"host=localhost port=5432 dbname=db1 user=postgres password=password"
schema = "schemaname"
target_shp = r"D:\Data\shapefile.shp"
command = r'start cmd /K ogr2ogr -f "PostgreSQL" PG:"%s" -lco SCHEMA=%s "%s" -overwrite -progress -lco OVERWRITE=YES' % (connection, schema, target_shp)
print(command)
os.system(command,)

Uploading an ESRI FileGeodatabase Feature class to postgres:

import os
connection = r"host=localhost port=5432 dbname=db1 user=postgres password=password"
schema = "schemaname"
target_gdb = r"D:\Data\Geodatabase.gdb"
target_fc = "FeatureClassName"
command = r'start cmd /K ogr2ogr -f "PostgreSQL" PG:"%s" -lco SCHEMA=%s "%s" "%s" -overwrite -progress -lco OVERWRITE=YES' % (connection, schema, target_gdb, target_fc)
print(command)
os.system(command,)

If you want to upload the entire GeoDatabase, not just a Feature Class, then remove the last "%s" in the 'command' variable and remove the 'target_fc' line. Also remove the reference to 'target_fc' at the end of the 'command' variable.