Inserting an array of points into postgres from python

Question

I have a table, region, defined

 place_id     | integer    | 
 pts          | point[]    | 
 region_name  | text       | 
 region_id    | integer    | not null default nextval('region_region_id_seq'::regclass)

and would like to insert a row from python. I have an array pgon

'{{-171.01155,-11.05869},{-171.01116,-11.06022},{-171.01138,-11.07814},{-171.01708,-11.08953},{-171.01963,-11.09463},{-171.03217,-11.10509},{-171.04886,-11.10929},{-171.07441,-11.11013},{-171.09834,-11.10787},{-171.10588,-11.10552},{-171.12063,-11.10091},{-171.13384,-11.09055},{-171.13385,-11.09055},{-171.13386,-11.09054},{-171.13387,-11.09053},{-171.13386,-11.09053},{-171.13384,-11.09053},{-171.13383,-11.09053},{-171.13529,-11.08719},{-171.13966,-11.07717},{-171.14112,-11.07383},{-171.14113,-11.07382},{-171.14114,-11.07382},{-171.14115,-11.07382},{-171.14191,-11.06076},{-171.14118,-11.04661},{-171.14116,-11.04622},{-171.13745,-11.03009},{-171.12577,-11.0156},{-171.1114,-11.00379},{-171.09488,-10.9978},{-171.08485,-10.9975},{-171.07463,-10.9972},{-171.05771,-10.99903},{-171.05163,-11.00014},{-171.03634,-11.00291},{-171.03358,-11.00409},{-171.0279,-11.00652},{-171.01928,-11.01225},{-171.01876,-11.01339},{-171.01407,-11.0237},{-171.01238,-11.04712}}'

place_id = 6, name = "American Samoa" My python command line is:

cur.execute("insert into region (place_id,pts,region_name) values (%,%,%)",(place_id, pgon, name))

But I get ValueError: "unsupported format character ',' (0x2c) at index 55"

I have done inserts before, but not with point[] in the target. Can someone tell me how I should be doing this? Thanks.

Clodoaldo Neto · Accepted Answer · 2016-02-06 16:13:36Z

create table t (p point[]);

Using the adapter example from the documentation:

from psycopg2.extensions import adapt, register_adapter, AsIs

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

def adapt_point(point):
    x = adapt(point.x).getquoted()
    y = adapt(point.y).getquoted()
    return AsIs("'(%s, %s)'" % (x, y))

register_adapter(Point, adapt_point)

my_tuples_list = [(-171.01155,-11.05869),(-171.01116,-11.06022),(-171.01138,-11.07814),(-171.01708,-11.08953),(-171.01963,-11.09463),(-171.03217,-11.10509),(-171.04886,-11.10929),(-171.07441,-11.11013),(-171.09834,-11.10787),(-171.10588,-11.10552),(-171.12063,-11.10091),(-171.13384,-11.09055),(-171.13385,-11.09055),(-171.13386,-11.09054),(-171.13387,-11.09053),(-171.13386,-11.09053),(-171.13384,-11.09053),(-171.13383,-11.09053),(-171.13529,-11.08719),(-171.13966,-11.07717),(-171.14112,-11.07383),(-171.14113,-11.07382),(-171.14114,-11.07382),(-171.14115,-11.07382),(-171.14191,-11.06076),(-171.14118,-11.04661),(-171.14116,-11.04622),(-171.13745,-11.03009),(-171.12577,-11.0156),(-171.1114,-11.00379),(-171.09488,-10.9978),(-171.08485,-10.9975),(-171.07463,-10.9972),(-171.05771,-10.99903),(-171.05163,-11.00014),(-171.03634,-11.00291),(-171.03358,-11.00409),(-171.0279,-11.00652),(-171.01928,-11.01225),(-171.01876,-11.01339),(-171.01407,-11.0237),(-171.01238,-11.04712)]
my_point_list = [Point(p[0], p[1]) for p in my_tuples_list]

conn = psycopg2.connect(database='cpn')
cursor = conn.cursor()

insert = "insert into t (p) values (%s::point[])"
cursor.execute(insert, (my_point_list,))
conn.commit()

Works as promised. Thanks. One problem, if you then try cursor.fetchall() there is an exception: ValueError: 'could not convert string to float: (-171.01155,-11.05869)' — LenB, Commented Feb 6, 2016 at 18:29

LenB · Accepted Answer · 2016-02-06 18:02:19Z

I got this running. Code is below. The point[] gets to PG. Now unfortunately, cur.fetchall() throws an exception after cur.execute("select * from test;")

#!/usr/bin/python
import psycopg2
from config import *

dbname="test"

try:
    dbconn = psycopg2.connect(dbname=dbname, host=dbHost, port=5432, user=dbUser,password=dbPassword)
    cur = dbconn.cursor()
except:
    print "Error : Unable to open database\n"

poly="-94.26958,33.56679 -94.26926,33.56763 -94.23197,33.55209"
poly=poly.replace(" ",")\",\"(")
poly="{\"("+poly+")\"}"
try:
    cur.execute("drop table if exists test;");
    cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, anum integer[],pt point, apt point[],nm text);")
    cur.execute("insert into test (num,apt,nm) values (%s,%s,%s) returning *",[1,poly,"fred"])
    dbconn.commit()
    cur.execute("select * from test;")
    print cur.fetchall()
except psycopg2.Error as e :
    print e.pgerror

Benjamín Vasquez · Accepted Answer · 2021-09-03 19:11:48Z

targetConn = pg.connect(dbname='', user='', host='127.0.0.1', password='root')

targetCursor = targetConn.cursor()

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

def adapt_point(point):
    x = adapt(point.x)
    y = adapt(point.y)
    return AsIs("'(%s, %s)'" % (x, y))


register_adapter(Point, adapt_point)

targetCursor.execute("INSERT INTO point (point) VALUES (%s)",
      (Point(1.23, 4.56),))
targetConn.commit()

targetConn.close()

Collectives™ on Stack Overflow

Inserting an array of points into postgres from python

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related