Check PostgreSQL table column for value and return boolean

Question

I am just learning SQL and apologize if this is repetitive, but I have searched and not found an answer. Writing in python with a PostgreSQL DB.

I want to check a table in my database to see if the "Port" column contains a specific location. I can do this interactively, but it returns a table result, eg:

select exists(select 1 from ref_locations where "Port"='ORAN');

it returns:

exists
-------
t
(1 row)

... I want it to return JUST a boolean value, and I want to do it in my python script. In my python script I have:

exists = False
cnx.execute("""select exists(select 1 from ref_locations where "Port"='BAKU');""")
print "Exists: ",exists

but exists is always filled with False. Any help greatly appreciated.

print "Exists: ",exists your exists variable is not changed by the result of the query. You need to retrieve the result from the query, and assign it to your variable. There probably is some function in your cursor, like exists = cnx.get_result(); — wildplasser
– wildplasser, Commented Feb 1, 2017 at 21:57
Thank you! Great answers. And at least in the process of my working through this, I realized that since I am testing many words for their existence in my "Port" table, it is actually faster for me to read my table in to a hash or pandas table rather than query the database every time. — Callie
– Callie, Commented Feb 2, 2017 at 22:26

Roman Tkachuk · Accepted Answer · 2017-02-01 21:55:21Z

2

Exists many variants, but simpliest is:

select count(*) > 0 from ref_locations where "Port"='ORAN'

answered Feb 1, 2017 at 21:55

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

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

Comments

Vic Colborn · Accepted Answer · 2017-02-01 22:18:48Z

1

Your postgresql statement isn't problem, though you might shorten it a tad to this:

select exists(select from ref_locations where Port='ORAN');

The problem you're having is that the result of the execute statement isn't assigned to anything.

Consider trying this instead (after your execute statement):

exists = cnx.fetchone()

or just

print cnx.fetchone()

answered Feb 1, 2017 at 22:18

Vic Colborn

2,0861 gold badge19 silver badges21 bronze badges

Collectives™ on Stack Overflow

Check PostgreSQL table column for value and return boolean

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related