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.'

• mikewatt: How are you actually executing it? check_output() will raise an exception that contains output that would be helpful for debugging
• pauldzy: Why not use the Python bindings for gdal? pypi.org/project/GDAL
• Theo F: @gberard executing it in IDLE 3.6.5. I'll try that check_output()
• neuhausr: You can probably also get some ideas from the Python OGR Cookbook--there are examples for reading ESRI FileGDB and writing to a PostGIS table that you could probably mash together.
• Theo F: @pauldzy because it's one less dependancy to worry about.

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.