pgsafeimport.py

import subprocess
import sys
import os

'''usage: pgimport path/to/fgdb layername database schema.tablename host user pass
    This lets you import fgdbs into postgis tables that have views on them.
    Uses an intermediate table to minimize downtime'''


fgdb, layer, db, target, host, user, password = sys.argv[1:]

env = os.environ.copy()
env['PGPASSWORD'] = password

sqlconn = 'psql -U%s -h %s -d %s ' % (user, host, db)

targetschema,targettable = target.split(".")
temp = "import.import_%s" % (targettable)

#ogr2ogr in overwrite mode to temp import table
ogrcmd = 'ogr2ogr -skipfailures -overwrite -f "PostgreSQL" PG:"host=%s dbname=%s user=%s password=%s" "%s" "%s" -nln "%s"' \
        % (host, db, user, password, fgdb, layer, temp)

print ogrcmd
subprocess.call(ogrcmd, shell=True)

#check if target table exists, otherwise create it

sqlcmd = sqlconn + '-c "select tablename from pg_tables where schemaname=\'%s\' and tablename=\'%s\';"' % (targetschema,targettable)
print sqlcmd

if targettable not in subprocess.Popen(sqlcmd, shell=True, env=env).communicate()[0]:
    sqlcmd = sqlconn + '-c "CREATE TABLE %s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );"' \
            % (target, temp)
    print sqlcmd
    subprocess.call(sqlcmd, shell=True, env=env)

#copy the records over from the import table to the target table
sqlcmd = sqlconn + '-c "TRUNCATE %s; INSERT INTO %s SELECT * FROM %s;"' % \
        (target, target, temp)
print sqlcmd
subprocess.call(sqlcmd, shell=True, env=env)