Arcpy to PostgreSQL using psycopg2 with MULTIPOLYGON using WKT

Question

I am trying to create a MULTIPOLGYON in PostgreSQL from WKT format using arcpy.da module and psycopg2. I have had success creating point and multi point shapes this way however I cannot seem to get how to format the code for a MULTIPOLGYON.

I have read over the documentation http://postgis.net/docs/manual-1.4/ST_GeomFromText.html but it does not give any examples of inserting a variable holding the WKT geometry.

this is my part of the code I am having trouble with

cur = conn.cursor()  #setting up connection cursor
cur.execute('''drop table polygon''')
cur.execute('''CREATE TABLE polygon
            (pid serial primary key,
             Name TEXT,
             geom geometry);''')
conn.commit()
for row in arcpy.da.SearchCursor(shp, ["name","SHAPE@"]):
    name =  row[0]
    geome = row[1].WKT
    cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText('MULTIPOLYGON'(%s)))''', (name,geome))
    conn.commit()

this is the error

traceback (most recent call last):
  File "<module1>", line 20, in <module>
ProgrammingError: syntax error at or near "("
LINE 1: ...values('Minnesota', ST_GeomFromText('MULTIPOLYGON'('MULTIPOL...

I have tried taking out the 'MULTIPOLYGON' but it still has not worked! what else can I do!

Answer 1

You have too many brackets. Based on your code and comments,

cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText('MULTIPOLYGON'(%s)))''', (name,geome))

should be

cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText(%s))''', (name,geome))

I also recommend looking into GeoAlchemy 2 as a nicer, more generic, and safer way of writing this.