Using cursor.execute() with PostGIS

Question

I do not understand how the operator works some times.

Here is an example that works,

"SELECT ST_Distance(ST_GeomFromText('POINT(-3.16496271127842 55.9262620593642)'),ST_GeomFromWKB(a.geometry)),ST_AsText(ST_Centroid(a.geometry)) FROM <table> As a;"

and here ia an example that fails,I use a simple execute method like this,

cc = conn.cursor()
a = ('POINT(-3.16496271127842 55.9262620593642)')
b = "SELECT ST_Distance(ST_GeomFromText(%s),ST_GeomFromWKB(a.geometry)),ST_AsText(ST_Centroid(a.geometry)) FROM <table> As a;"
cc.execute(b,a)

sometimes, the operator works fine and sometimes it does not. I have read documentation about the operators and how to use python with SQL but I have not found WHY it acts like this in a random kind of way.

Does anyone have any hints and tips?

Answer 1

I think I found the reason in the docs:

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple:

 cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
 cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
 cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
 cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

So in your case this should work:

 a = ("POINT(-3.16496271127842 55.9262620593642)",)