Import a zipped shapefile to a new postgis table

django_import_shp_zip_to_postgres.py

import pygeoif
import shapefile
from django.db import connection, transaction
from zipfile import ZipFile
from io import BytesIO


def shp_files_sorter(file_name):
    """
    :param file_name:
    :return: .dbf first, .shx second , .shp third then  *_ other files
    """

    return file_name.endswith('shp'), file_name.endswith('shx'), file_name.endswith('dbf')


@transaction.atomic
def import_shp_to_postgres(shp_zip_path, table_name, srid=4326):
    with ZipFile(shp_zip_path, 'r') as zipfile:
        # get and sort the files. The order will always be [.dbf,.shx,.shp *_others]
        filenames = [y for y in sorted(zipfile.namelist(), key=shp_files_sorter) for ending in
                     ['dbf', 'prj', 'shp', 'shx'] if
                     y.endswith(ending)]
        
        # we just need the mandatory shp files
        dbf, shx, shp, *_ = [BytesIO(zipfile.read(filename)) for filename in filenames]

        with connection.cursor() as cursor:
            sf = shapefile.Reader(shp=shp, shx=shx, dbf=dbf)
            fields = sf.fields

            columns = [field[0] for field in fields][1:]

            # TODO: Match shp data types to postgresql Data types

            # create table
            cursor.execute(
                'CREATE TABLE IF NOT EXISTS %s (id serial, geom geometry(GEOMETRY,%s), %s text)' % (
                    table_name, srid, ' text, '.join(columns)))

            # create index
            cursor.execute('CREATE INDEX IF NOT EXISTS %s_gix on %s USING GIST (geom)' % (table_name, table_name))

            # insert data
            shape_records = sf.shapeRecords()
            for shape_record in shape_records:
                record = shape_record.record.as_dict()
                field_name = ', '.join([str(i) for i in record.keys()])
                # quotes within text were giving me trouble. Just replaced them for now
                value = ', '.join([repr(str(i).replace("'", "")) for i in record.values()])

                # create WKT with SRID
                geom = pygeoif.geometry.as_shape(shape_record.shape)
                wkt = repr(f"SRID={srid};{geom.wkt}")

                sql = "INSERT INTO %s (%s, geom) VALUES (%s, %s)" % (table_name, field_name, value, wkt)

                cursor.execute(sql)