Execute SQL Query on Multiple Values Psycopg2

Question

I'm using psycopg2 to execute some PostGIS queries but I'm running into issues. Lets say I run a query:

cursor.execute("SELECT locations FROM locationtable")

This gives me a python variable which is a list of point geometries. Later, say I want to transform them to a different SRID I want to be able to do:

cursor.execute("SELECT ST_Transform(%s,32146)",(locs))

But this gives me an error:

TypeError: not all arguments converted during string formatting

I realize in this case I could have just combined these two SQL commands, but this is a general problem I have been encountering and want to know if there is a way to pass the list of tuples with psycopg2 and have the SQL command execute on all the values.

The error is likely because the first query returns a list (I guess)and you try to pass that list to a single placeholder. Does executemany work? — roganjosh
– roganjosh, Commented Sep 1, 2017 at 22:01

PRMoureu · Accepted Answer · 2017-09-01 22:01:52Z

1

It could work with the method executemany :

cursor.executemany("SELECT ST_Transform(%s,32146)",(locs))

But locs need to be a list of tuples or other sequence type, in example :

locs = [(val,) for val in locs]

answered Sep 1, 2017 at 22:01

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

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

2 Comments

Bootstrap Over a year ago

It didn't give me any errors when executing but for some reason when fetching it says there is nothing to fetch. I saw in the psycopg2 documentation that executemany isn't faster than using execute in a for loop so I decided to just do that because it is easier.

PRMoureu Over a year ago

yes, it may be easier with a loop & execute, in every cases you need a loop, choose what is the more flexible

Collectives™ on Stack Overflow

Execute SQL Query on Multiple Values Psycopg2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related