Load a spatial dataset from feature class to a table in postgres.

feature_class_to_postgres_v3.py

# -*- coding: utf-8 -*-
#------------------------------------------------------------------------------
# feature_class_to_postgres_v3.py
# Load county dataset from feature class to a table in postgres.
# Hayden Elza ([email protected])
# Created: 2018-08-09
# Modified: 2018-08-20
#------------------------------------------------------------------------------


import arcpy
import psycopg2
import datetime
from os import path



# Environmental variables
workspace = path.dirname(arcpy.GetParameterAsText(0))  #"H:/2018-08-09_Submission_Import/PRICE_OTHER_LAYERS.gdb"
feature_class = path.basename(arcpy.GetParameterAsText(0))  #"PRICE_PLSS_2018"
table_name = arcpy.GetParameterAsText(1)  #"price_0_s"
dbname = arcpy.GetParameterAsText(2)
dbuser = arcpy.GetParameterAsText(3)
dbhost = arcpy.GetParameterAsText(4)
dbpwd = arcpy.GetParameterAsText(5)

arcpy.env.workspace = workspace
epsg = arcpy.Describe(path.join(workspace,feature_class)).spatialReference.factoryCode



def build_create_query(table_name, fields):
	arc_to_pg_fields = {
		"Blob": "bytea",
		"Date": "date",
		"Double": "double precision",
		"Geometry": "geometry(POINT,{})".format(epsg),
		"GlobalID": "text",
		"Guid": "text",
		"Integer": "integer",
		"OID": "integer",
		"Raster": "bytea",
		"Single": "double precision",
		"SmallInteger": "integer",
		"String": "text"
	}

	create_query = u"CREATE TABLE {} (".format(table_name)

	for field in fields: create_query += u'"{}" {},'.format(field.name, arc_to_pg_fields[field.type])

	create_query = create_query[:-1]  # strip last comma

	create_query += ");"

	return create_query

def build_insert_query(table_name, field_names, records):
	# Format field_names into useable string
	field_names = ', '.join(map(lambda x: '"' + x + '"', field_names))

	insert_query = u"INSERT INTO {} ({}) VALUES ".format(table_name, field_names)

	for record in records: insert_query += u"({}),".format(record)

	insert_query = insert_query[:-1]  # strip last comma

	insert_query += u";"

	return insert_query



# Get fields
fields = arcpy.ListFields(feature_class)
field_names = [field.name for field in fields]


# Initialize records
records = []

# Get records
with arcpy.da.SearchCursor(feature_class, field_names) as cursor:
	for row in cursor:
		# Create record
		record = u""
		
		for value in row[0:]:
			if value is None:
				record += "NULL, "
			if isinstance(value, datetime.date):
				record += "to_date('{:02d}/{:02d}/{}','MM/DD/YYYY'), ".format(value.month,value.day,value.year)
			if isinstance(value, tuple):
				if value[0] is None or value[1] is None: record += "NULL, "
				else: record += "ST_GeomFromText('POINT({} {})',{}), ".format(value[0],value[1],epsg)
			if isinstance(value, int) or isinstance(value, float):
				record += str(value)+", "
			if isinstance(value, unicode):
				record += u"'{}', ".format(value.replace("'","''"))
        
		record = record[:-2]  # strip last comma and space

		# Add record to list of records
		records.append(record)

#print build_create_query(table_name, fields)
#print build_insert_query(table_name, field_names, records)

# Export to postgres
conn = None
try: 
	# Connect to database
	conn = psycopg2.connect("dbname={} user={} host={} password={}".format(dbname,dbuser,dbhost,dbpwd))
	cur = conn.cursor()

	# Create table in database
	create_query = build_create_query(table_name, fields)
	cur.execute(create_query)
	
	# Insert values into table
	insert_query = build_insert_query(table_name, field_names, records)
	cur.execute(insert_query)

	# Commit changes and close connection
	conn.commit()
	conn.close()

except psycopg2.DatabaseError as error:
	arcpy.AddError(error)

finally:
	if conn is not None:
		conn.close()