Example using Python coroutine to insert large dataset into PostgreSQL in batches

shp-to-pg.py

#
# CAVEAT: untested code intended to demonstrate idea only
#
from osgeo import ogr, osr
import psycopg2

# Read gory details http://www.dabeaz.com/coroutines/Coroutines.pdf
def coroutine(func):
    def start(*args,**kwargs):
        cr = func(*args,**kwargs)
        cr.next()
        return cr
    return start
    
@coroutine
def batch_sql(conn=None, sql=None, batch_size=1000):
    """Use executemany() in batches of batch_size"""
    def execute_sql(conn, sql, namedicts):
        if len(namedicts) <= 0:
            return
        cur = conn.cursor()
        cur.executemany(sql, namedicts)
        conn.commit()        
        cur.close()
        del namedicts[:]
        
    pending = []
    try:
        while True:
            namedict = (yield)
            pending.append(namedict)
            if len(pending) == batch_size:
                execute_sql(conn, sql, pending)
    except GeneratorExit:
        execute_sql(conn, sql, pending)
        
def load_wgs84shape2database(connection_dict,table,shapefile,tileset,yyyymm):
    connect_string = "user=%(user)s password=%(password)s host=%(host)s dbname=%(dbname)s" %connection_dict
    connection = psycopg2.connect(connect_string)

    inserter = batch_sql(
      conn=connection, 
      sql="INSERT INTO "+table+"(tileset,yyyymm,burnday,the_geom) VALUES ('%(tileset)s',%(yyyymm)s,%(burnday)s,st_multi(st_GeomFromText('%(the_geom)s',4326)));"
    )  
  
    # Add data to a spatial table
    src_shapefile = ogr.Open(shapefile)
    layer = src_shapefile.GetLayer(0)
    for feature in layer:
        geom = feature.GetGeometryRef()
        wkt = geom.ExportToWkt()
        params = {
                   'tileset':feature.GetField('tileset'),
                   'yyyymm':feature.GetField('YYYYMM'),
                   'burnday':feature.GetField('BurnDay'),
                   'the_geom':wkt
                 }
        inserter.send(params)
    inserter.close()
    print 'Loading shapefile %s was successful' %shapefile