Python checking sql database column for value

Question

How would one check an entire column in sqlite database for a given value and if found set a variable to true

something like

hasvalue = 'false'

cur = con.cursor()
cur.execute("SELECT id FROM column WHERE hasvalue = '1' LIMIT 1;")
***IF execute returned rows set hasvalue = true here***

if hasvalue == 'true':
   do something
else:
   dosomethingelse

How would I make hasvalue turn to true if the sql query returned with something and if it returned 0 rows stay false?

Basically I want to execute something ONLY if something in a column equals 1

So You want do it with python or do it with SQL coz good SQL-QUerry can do it too. — Take_Care_
– Take_Care_, Commented Sep 2, 2016 at 1:26
python as I want to execute some code only if the database returns true for my search — Ranga Sarin
– Ranga Sarin, Commented Sep 2, 2016 at 1:27
So Make an proper SQL like : 'SELECT 1 FROM <your_table_name> WHERE <column_name>=<your value> ;' Now just use fetch and if there will be zero rows with this value You will get NULL back, and 1 otherwise — Take_Care_
– Take_Care_, Commented Sep 2, 2016 at 1:32

mhawke · Accepted Answer · 2016-09-02 02:01:08Z

2

I'm a little confused by your question. The SQL query suggests that you are using a table named column? If I can ignore that and assume you have a table named test which has columns id (an int) and name (a string). Then the query:

SELECT id FROM test where name = 'something'

would select all rows that have name set to the string 'something'.

In Python this would be:

cur = con.cursor()
cur.execute("SELECT id FROM test where name = 'something' LIMIT 1")

if cur.fetchone():
    do_something()
else:
    do_something_else()

The key here is to use cursor.fetchone() which will try and retrieve a row from the cursor. If there are no rows fetchone() will return None, and None evaluates to False when used as a condition in an if statement.

You could create a function to generalise:

def has_value(cursor, table, column, value):
    query = 'SELECT 1 from {} WHERE {} = ? LIMIT 1'.format(table, column)
    return cursor.execute(query, (value,)).fetchone() is not None

if has_value(cur, 'test', 'name', 'Ranga'):
    do_something()
else:
    do_something_else()

This code constructs a query for the given table, column, and value and returns True if there is at least one row with the required value, otherwise it returns False.

edited Sep 2, 2016 at 2:01

answered Sep 2, 2016 at 1:50

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

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

1 Comment

Ranga Sarin Over a year ago

if not cur.fetchone() is exactly what I'm looking for. Thank you very much for the detailed explanation!

Collectives™ on Stack Overflow

Python checking sql database column for value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related