Make sure all values exist in a table with one query

Question

So I have a simple table of towns with Finnish and Swedish names:

CREATE TABLE town (id SERIAL PRIMARY KEY, name_fi TEXT, name_sv TEXT);

And I need to check if a particular town exists. It's rather easy:

cursor.execute(
    "SELECT EXISTS ( SELECT 1 FROM town WHERE name_fi=%s OR name_sv=%s )",
    (town, town)
)
result = cursor.fetchone()  # True/False

But now I need to do this for multiple towns to make sure they all exist:

for town in towns:
    # Code for one town from above
    if result is False:
        return False
return True

Is there a way to do this with one query instead of using a Python for loop?

`where name_fi in (...) or name_sv in (..)` – Feb 27 '17 at 12:04 — , Feb 27 '17 at 12:04

score 0 · Accepted Answer · edited May 23 '17 at 12:17

Pass the towns as a list:

cities = ['Paris', 'London']
template = ','.join(['%s'] * len(cities))

Join and aggregate as necessary:

query = '''
    select bool_and(name_fi is not null or name_sv is not null)
    from
        town
        right join
        (values {}) s (name) on name in (name_fi, name_sv)
'''.format(template)

print (cursor.mogrify(query, [tuple((city,)) for city in cities]).decode('utf8'))
cursor.execute(query, [tuple((city,)) for city in cities])

The result is either True or False

print cursor.fetchone()[0]

Output:

select bool_and(name_fi is not null or name_sv is not null)
from
    town
    right join
    (values ('Paris'),('London')) s (name) on name in (name_fi, name_sv)

True

For the Python part check this answer

Make sure all values exist in a table with one query

1 Answers1