Django：制作原始SQL查询，传递多个/重复参数？ [英] Django: making raw SQL query, passing multiple/repeated params?

问题描述

希望这应该是一个相当简单的问题，我只是不了解Python和Django来回答。

我在Django中有一个原始的SQL查询，它具有六个不同的参数，前两个（centreLat和centreLng）每个都重复：

  query =SELECT units，（SQRT（（（lat-％s）*（lat-％s））+（（lng-％s ）*（lng-％s））））AS距离FROM位置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）
  

如何构造 params 对象和查询字符串，所以他们知道要重复的参数和哪里？

解决方案

你有（至少）两个选项。您可以按照需要显示的顺序将这些重复的参数包含在列表中，这意味着您会在列表中以相同的值重复多次，如下所示：

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

或者，您可以使用字典命名每个参数，而不是仅使用％s，您可以使用％（name）s：

  query =SELECT units，（SQRT（（（lat  - ％（lat）s）*（lat  - ％（lat）s））+（（lng - ％（lng）s）*（lng  - ％（lng）s））））AS距离FROM位置WHERE lat<％（lat）s AND lat>％（lat）s AND lon<％（lng） ;％（lng）s ORDER BY distance; 
 params = {'lat'：centreLat，'lng'：centreLng} 
  

不要把代码逐字地复制，我相信这不是你需要的）

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?

解决方案

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)

这篇关于Django：制作原始SQL查询，传递多个/重复参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！