Passing parameters to PostGIS SQL queries in Python: weird behaviour

Question

The following code always goes to the except section and I do not understand why..Any help would be much appreciated.

l = None
c = conn.cursor()
try:
  s = "SELECT ST_NPoints(ST_GeomFromText(%s))"
  d = (line,)
  c.execute(s,d)
  l = c.fetchall()
except:
  print "could not get the line vertices"

print line 

MULTILINESTRING((-3.16420835153456 55.9269166007097,-3.164222 55.926918),
(-3.1642070167833 55.9269296196706,-3.16421351659546 55.9268662214904),
(-3.16421351659546 55.9268662214904,-3.16421636372824 55.9268384509897),
(-3.16421636372824 55.9268384509897,-3.16422182573761 55.9267851753802),
(-3.16422182573761 55.9267851753802,-3.16422870102352 55.926718114886),
(-3.16422870102352 55.926718114886,-3.16423309121073 55.926675293667),
(-3.16423309121073 55.926675293667,-3.16423565148822 55.9266503211093))

If I execute this in psql, it works fine. I also have the impression that I am following documentation perfectly regardging passign parameters to sql queries etc. When I call the function, I pass the argument as a string like this 'MULTILINESTRING((-3.164....565148822 55.9266503211093))'

Answer 1

You didn't post the exception so I don't now what is wrong with your query but this should always work: (assuming that your projection is WGS84 or in other words the srid of your data is 4326)

SELECT ST_NPoints(ST_GeomFromEWKT('SRID=4326;MULTILINESTRING((-3.16420835153456 55.9269166007097,-3.164222 55.926918),
(-3.1642070167833 55.9269296196706,-3.16421351659546 55.9268662214904),
(-3.16421351659546 55.9268662214904,-3.16421636372824 55.9268384509897),
(-3.16421636372824 55.9268384509897,-3.16422182573761 55.9267851753802),
(-3.16422182573761 55.9267851753802,-3.16422870102352 55.926718114886),
(-3.16422870102352 55.926718114886,-3.16423309121073 55.926675293667),
(-3.16423309121073 55.926675293667,-3.16423565148822 55.9266503211093))'));

or in python:

l = None
c = conn.cursor()
try:
  s = "SELECT ST_NPoints(ST_GeomFromEWKT(%s))"
  d = ("SRID=4326;"+line,)
  c.execute(s,d)
  l = c.fetchall()
except Except, ex: # catch the exception so you can know what happened
  print "Error: ",str(ex)