I want to import shapefiles to PostGIS using Python with arcpy & psycopg2 to connect to PostGIS. I don't want to use the existing shapefile loader nor ogr, just write my own import tool.
My steps and where I got stuck:
- Define shapefile
- Select column names from shapefile into a list
- Create SQL CREATE TABLE query with column names of shapefile
- Insert values from shapefile into PostGIS database
I got stuck at point 4 since I don't really know how to extract the values from the shapefile and insert it into the database taking account of the correct column.
import arcpy import psycopg2 arcpy.env.workspace = r"path\to\data" shp = "BRD.shp" shp2 = shp[:3] desc_shp = arcpy.Describe(shp) #set variables and create list wo OID and Shape attributes if desc_shp.hasOID: print desc_shp.OIDFieldName shp_OID = desc_shp.OIDFieldName print desc_shp.shapeFieldName shp_geom = desc_shp.shapeFieldName fields = desc_shp.fields field_names =  for field in fields: if not (field.name == shp_OID or field.name == shp_geom): field_names.append(field.name) new_list = field_names print new_list sc_da_cursor = arcpy.da.SearchCursor(shp,shp_geom) for sc_da_row in sc_da_cursor: print sc_da_row # PostGIS part after here user = "postgres" pw = "postgres" db = "pypg" host = "localhost" port = 5432 conn_obj = psycopg2.connect(database=db, user=user, password=pw, host=host, port=port) cur_obj = conn_obj.cursor() sql_createtable = "CREATE TABLE " + shp2 + "(" + shp_OID + " serial PRIMARY KEY," + shp_geom + " geometry," + new_list + " VARCHAR(100)," + new_list + " VARCHAR(100))" sql_insert = "INSERT INTO " + shp2 + "(" + shp_geom + ") VALUES(" + str(sc_da_row) + ")" cur_obj.execute(sql_insert) conn_obj.commit() conn_obj.close() cur_obj.close()