Ranga Sarin Ranga Sarin - 1 year ago 130
Python Question

Python checking sql database column for value

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

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

Answer Source

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():

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'):

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download