Django: making raw SQL query, passing multiple/repeated params?

Question

Hopefully this should be a fairly straightforward question, I just don't know enough about Python and Django to answer it.

I've got a raw SQL query in Django that takes six different parameters, the first two of which (centreLat and centreLng) are each repeated:

query = "SELECT units, (SQRT(((lat-%s)*(lat-%s)) + ((lng-%s)*(lng-%s)))) AS distance FROM places WHERE lat<%s AND lat>%s AND lon<%s AND lon>%s ORDER BY distance;"
params = [centreLat,centreLng,swLat,neLat,swLng,neLng]
places = Place.objects.raw(query, params)

How do I structure the params object and the query string so they know which parameters to repeat and where?

Matthew J Morrison · Accepted Answer · 2010-05-31 15:18:55Z

13

You have (at least) two options. You could either include those repeated parameters in your list in the order that they need to appear - meaning that you would end up with the same values in your list multiple times like this:

params = [centreLat,centreLng,swLat,neLat,swLng,neLng,centreLat,centreLng]

OR, you could name each parameter using a dictionary and instead of using just a "%s" you could use "%(name)s" like this:

query = "SELECT units, (SQRT(((lat-%(lat)s)*(lat-%(lat)s)) + ((lng-%(lng)s)*(lng-%(lng)s)))) AS distance FROM places WHERE lat<%(lat)s AND lat>%(lat)s AND lon<%(lng)s AND lon>%(lng)s ORDER BY distance;"
params = {'lat':centreLat,'lng':centreLng}

(don't copy that code verbatim, i'm sure that's not EXACTLY what you need)

answered May 31, 2010 at 15:18

Matthew J Morrison

4,4333 gold badges30 silver badges46 bronze badges

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

1 Comment

Sam Stoelinga Over a year ago

The dictionary answer seems better to me. I always like to use named parameters. It's better for readabilty also.

Collectives™ on Stack Overflow

Django: making raw SQL query, passing multiple/repeated params?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related