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

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?


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)",)
| improve this answer | |
  • Nope, it does not work like that either.. Also, my point is ususally passed not hardcoded but loaded from the database etc. So my code looks like this but still does not work, a = "SELECT ST_Distance(ST_GeomFromText(%s),ST_GeomFromWKB(geometry)),ST_AsText(ST_Centroid(geometry)) FROM cameron_toll_nodes As a WHERE name=%s;" b = (point,'exit') # point is POINT(x y) format cc.execute(a,b) Any ideas? :) – Antony Jan 4 '12 at 17:20
  • Please update your question with full information. – underdark Jan 4 '12 at 17:24
  • hmm, I will try your solution! I also tried another method and it worked! I re-assigned my point to a new variable and used that instead. Somethign like this newPoint = point. Right, so what should I do now? should I re-post or leave it like this? – Antony Jan 4 '12 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.