Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.

Sign up to join this community

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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? 
  
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)",)
                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, 2012 at 17:20
                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, 2012 at 17:32
        
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.