[GIS] Insert points to PostGIS using a function

postgispsycopg2python

I am trying to insert points geometry into PostGIS database. Well I tried to use a function just to read point coordinates and attributes and save them in a table in PostGIS. It works for attributes but not for geometry. This is my python code:

insert(id, Height, X, Y)
....
def insert(gid,height, X, Y):
   gid=str(gid)
   connection = psycopg2.connect(host='', dbname='postgres',   user='', password='')     
    cur = connection.cursor()
   cur.execute ("""INSERT INTO gronkarta.test5 (gid, height, geom) VALUES (""" + gid + """,""" + str(height) + ""","""+ST_GeomFromText('POINT(X Y)', 3008)+""");""")

Best Answer

Something more like this would be better, IMO. Creating the connection is more expensive, you should do that earlier in the program and pass it in. As noted by a commenter, you shouldn't compose SQL strings using string concatenation, you should use placeholders for more secure use of the database. 

def db_insert_point(conn, height, x, y):
    sql = "INSERT INTO gronkarta.test5 (height, geom) VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 3008))"
    with conn.cursor() as cur:
        cur.execute(sql, (height, x, y))
    conn.commit()

If you're running db_insert_point() in a tight loop, you'll want to call conn.commit() at the end of the loop, rather than once per insert, for performance purposes, so you could move the commit outside the function in that case, to get multiple inserts into a single transaction.

Related Question