我编写了一个应用程序，该应用程序通过HTML页面(对于问题部分，它只有4个复选框(从用户那里获取输入到python应用程序中，该应用程序通过psycopg2向PostgreSQL发送查询并返回一个字典列表，该字典列表使用flask渲染回HTML。
只要用户选择的列(例如想要现场音乐(在数据库/sql 查询中是正确的，一切正常，如果不是，我会得到一个空白输出。(希望这有点道理(。
法典
这就是我根据用户输入创建每个子查询的方式
if request.form.get("latenight"):
latenight = True
latenight = " AND '23:00:00'>={} AND {}<= '04:00:00'".format(dayclose,dayclose)
else:
latenight =""
if request.form.get("food_avail"):
food_avail = True
food_avail =" AND LOCALTIME(0) BETWEEN {} AND {}".format(restdayopen, restdayclose)
else:
food_avail=""
if request.form.get("livemusic"):
livemusic = True
livemusic = " AND LOCALTIME(0) BETWEEN {} AND {}".format(musicdaybegin, musicdayfinish)
else:
livemusic=""
if request.form.get("camra"):
camra = True
camquery = " AND camra = True"
else:
camra = False
camquery = ""
然后，这将显示在传递给PostgreSQL的查询语句中。
query = """SELECT name_, address, postcode, latitude, longitude, pubid, monclose,
monopen, tueopen, tueclose, wedopen, wedclose, thuropen, thurclose, friopen, friclose, satopen,
satclose, sunopen, sunclose,
restopenmon, restclosemon, restopentue, restopenwed, restopenthur, restopenfri, restopensat,
restopensun, restclosetue, restclosewed, restclosethur, restclosefri, restclosesat, restclosesun,
active, musicbeginmon, musicbegintue,
musicbeginwed, musicbeginthur, musicbeginfri, musicbeginsat, musicbeginsun, musicfinishmon,
musicfinishtue, musicfinishwed, musicfinishthur, musicfinishfri, musicfinishsat, musicfinishsun, camra, ROUND(distance::NUMERIC , 2) distance
FROM (
SELECT z.name_,
z.address,z.tueopen,
z.camra, z.monopen,
z.postcode, z.monclose,
z.satclose, z.pubid,
z.latitude, z.longitude,
z.tueclose, z.wedopen, z.wedclose,
z.thuropen, z.thurclose, z.friopen,
z.friclose, z.satopen, z.sunopen, z.sunclose,
z.restopenmon, z.restclosemon, z.restopentue, z.restopenwed, z.restopenthur,
z.restopenfri, z.restopensat, z.restopensun, z.restclosetue, z.restclosewed, z.restclosethur,
z.restclosefri, z.restclosesat, z.restclosesun, z.active, z.musicbeginmon, z.musicbegintue,
z.musicbeginwed, z.musicbeginthur, z.musicbeginfri, z.musicbeginsat, z.musicbeginsun, z.musicfinishmon,
z.musicfinishtue, z.musicfinishwed, z.musicfinishthur, z.musicfinishfri, z.musicfinishsat,
z.musicfinishsun,
p.radius,
p.distance_unit
* DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
* COS(RADIANS(z.latitude))
* COS(RADIANS(p.longpoint - z.longitude))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.latitude))))) AS distance
FROM pubs AS z
JOIN (SELECT """ + latbx + """ AS latpoint, """ + lonbx + """AS longpoint,
100 AS radius, 69 AS distance_unit
) AS p ON 1=1
WHERE z.latitude
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude
BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius AND distance <""" + miles_ + """"""+camquery+""""""+latenight+""""""+food_avail+""""""+livemusic+"""
ORDER BY distance """
似乎如果深夜、food_avail或现场音乐不满意，那么它根本不返回任何行，这与 AND 语句是正确的。
我需要做的是返回符合任何条件的所有行，如果可能的话，将一条消息发送回 python，允许我抓取它并使用它来向用户显示一些东西("没有现场音乐可用"(。
您的pubs表未规范化的事实使这比应有的困难得多。 如果您有兴趣解决这个问题，请告诉我。
简短的回答是，与其将这些标准放在WHERE条款中，不如将它们放入SELECT清单中。
例如，选中"深夜"复选框将显示为：
select . . .,
1 as latenight_checked,
case
when localtime(0) between 'restopentue' and 'restclosetue' then 1
else 0
end as latenight,
. . .
如果未选中，请执行以下操作：
select . . .,
0 as latenight_checked,
case
when localtime(0) between 'restopentue' and 'restclosetue' then 1
else 0
end as latenight,
. . .
对"food_avail"、"现场音乐"和"camra"做类似的事情。
这将返回某些内容对于酒吧是否为真，以及用户是否关心您可以在代码中使用的条件。
更新更好的插图：
latenight_cb = (1 if request.form.get("latenight") else 0)
latenight_sql = "case when '23:00:00' >={} AND {}<= '04:00:00' then 1 else 0 end as latenight, {} as latenight_checked,".format(dayclose, dayclose, latenight_cb)
然后，在查询中：
query = """SELECT name_, address, postcode, latitude, longitude,
pubid, monclose, monopen, tueopen, tueclose, wedopen,
-- Lines snipped here
musicfinishfri, musicfinishsat, musicfinishsun, camra,
""" + latenight_sql + """
ROUND(distance::NUMERIC , 2) distance
当您的结果返回时，您将能够看到酒吧是否在latenight营业，以及表单是否在latenight_checked选中了该框。