Insert points to PostGIS using a function

Question

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)+""");""") 

• Mozafar Veysipanah: I used Python for this code.
• John Powell: Use something like stackoverflow.com/questions/902408/…. There is no need to build up a string like that.
• Mozafar Veysipanah: Thanks for your response. I changed the code like this: cur.execute ("INSERT INTO gronkarta.test5 (gid, height, geom) VALUES (gid, str(height), ST_GeomFromText('POINT(X Y)', 3008))") Error: There is a column named "gid" in table "test5", but it cannot be referenced from this part of the query.
• kttii: is gid an autonumber column? If so, then just leave that column out and let the database assign the id: cur.execute ("INSERT INTO gronkarta.test5 (height, geom) VALUES (str(height), ST_GeomFromText('POINT(X Y)', 3008))")

Answer 1

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.