A simple script to load/replace a PostGIS table from Shapefile with the shp2pgsql utility. Completely deletes and rebuilds the table in the database.

shape2postgis.py

# ---------------------------------------------------------------------------
#
# Script to copy data from an ESRI Shapefile to PostGIS database
# Requirements:
## shp2pgsql.exe
## psql.exe
## PostgreSQL DB with PostGIS installed
#
# ---------------------------------------------------------------------------
#


# Imports
import pg, os

# Global Variables
name = 'YOUR_SHAPEFILE_NAME' ## No .shp extension
db = 'DESTINATION_DB_NAME'
host = 'YOUR_HOST_NAME'
username = 'YOUR_USERNAME'
password = 'YOUR_PASSWORD'
srid = 'DATA_SRID'

data = " -d -s {srid} '//PATH/TO/SHAPEFILE/{name}.shp' {name}".format(name=name, srid=srid) 
sqlfile = "'//PATH/TO/TEMPORARY/SQLFILE/{name}.sql'".format(name=name)

exe1 = "O:/shp2pgsql.exe"
exe2 = "O:/psql.exe"
options1 = " -h {host} -f ".format(host=host)
options2 = " -d {db} -U {username} ".format(db=db, username=username)


# PostgreSQL DB connection properties
db = pg.connect(dbname = db, host=host, port=5432, user=username,passwd=password)


# Converting processing shapefiles to sql script (shp2postgresql tool).
# Deletes the postgresql table having the same name as the shapefile ( -d flag ).
print 'Creating .sql file for {shape}...'.format(name=name)
cmd1 = exe1 + data + '>' + sqlfile
os.system(cmd1)

# Delete existing PG table, create new PG table, and import data from the sql script generated with cmd1.
print 'Importing {shape} data into PostgreSQL from the .sql file...'.format(name=name) 
cmd2 = exe2 + options1 + sqlfile1 + options2
os.system(cmd2)

# Delete un-needed shapefile legacy fields from PG table.
fieldNames_sql = "select column_name from information_schema.columns where table_name = '{name}'".format(name=name)
fieldNames_pg = db.query(fieldNames_sql)
fieldNames_pg_result = fieldNames_pg.getresult()
print 'Deleteing un-needed fields from production tables in postgresql...'
for field in fieldNames_pg_result:
    if field == ('shape_leng',):
        parcels_drop_field1_sql = "ALTER TABLE {name} DROP COLUMN shape_leng".format(name=name)
        parcels_drop_field1_pg = db.query(parcels_drop_field1_sql)
    elif field == ('shape_area',):
        parcels_drop_field2_sql = "ALTER TABLE {name} DROP COLUMN shape_area".format(name=name)
        parcels_drop_field2_pg = db.query(parcels_drop_field2_sql)
    else:
        pass
print 'Deleted any legacy "shape_leng" and "shape_area" fields from the final production tables.'
print ""

print "Update complete!"