Can I use a PostgreSQL/PostGIS function in a "INSERT" code in psycopg2 (Python)?

Question

I want to do a INSERT operator in Python using psycopg2 module.

The code is:

cursor.execute('INSERT INTO tb_places (id_street, geom, number, name, first_year, source, id_user, date) VALUES 
(22, ST_GeomFromText("POINT(-518.944 -2698.2069)", 4326), 34, TEST_1, 1950, Almanak96, 6, 2017-08-01)')

When I use concatenation, to generate the INSERT string, it works, however I know that is a bad idea. So I'm trying to use a other alternative to avoid SQL injection.

I tried it:

cursor.execute('INSERT INTO tb_places (id_street, geom, number, name, first_year, source, id_user, date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', 
[22, "ST_GeomFromText('POINT(-518.944 -2698.2069)', 4326)", 34, 'TEST_1', 1931, 'Almanak96', 6, '2017-08-01'])

But I get this error:

psycopg2.InternalError: parse error - invalid geometry
LINE 1: ...e, first_year, source, id_user, date) VALUES (22, 'ST_GeomFr...
                                                             ^
HINT:  "ST" <-- parse error at position 2 within geometry

Basically the problem is that I can't pass a SQL function in a string when I want to insert something.

Someone knows what I can do?

How did the first query even work? Show us the concatenation version not filled values. What is ST_GeomFromText()? Is that a Python or Postgres method? Even if Postgres, Python would have erred out as unable to find the function. — Parfait
– Parfait, Commented Aug 11, 2017 at 2:04

Clodoaldo Neto · Accepted Answer · 2017-08-11 19:33:22Z

1

The easiest:

cursor.execute('''
    insert into tb_places (
        id_street, geom, number, name, first_year, source, id_user, date
    ) values (%s, ST_GeomFromText(%s, 4326), %s, %s, %s, %s, %s, %s)
    ''', 
    [22, 'point(-518.944, -2698.2069)', 34, 'TEST_1', 1931, 'Almanak96', 6, '2017-08-01']
)

answered Aug 11, 2017 at 19:33

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A l w a y s S u n n y Over a year ago

How to insert multiple rows at once using ST_GeomFromText?

oscarvalles · Accepted Answer · 2017-08-11 04:04:51Z

0

Assuming ST_GeomFromText is a function you created, try

geomFromText = ST_GeomFromText('POINT(-518.944 -2698.2069)', 4326) # assign value to var

query = """INSERT INTO tb_places (id_street, geom, number, name, first_year, source, id_user, date) VALUES 
(22, %s, 34, TEST_1, 1950, Almanak96, 6, 2017-08-01)""" % (geomFromText) # make it part of query string

cursor.execute(query) # execute

answered Aug 11, 2017 at 4:04

oscarvalles

1011 silver badge2 bronze badges

Collectives™ on Stack Overflow

Can I use a PostgreSQL/PostGIS function in a "INSERT" code in psycopg2 (Python)?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related