How to check the existence of a row in SQLite with Python?

Question

I have the cursor with the query statement as follows:

cursor.execute("select rowid from components where name = ?", (name,))

I want to check for the existence of the components: name and return to a python variable. How do I do that?

Answer 1

Since the names are unique, I really favor your (the OP's) method of using fetchone or Alex Martelli's method of using SELECT count(*) over my initial suggestion of using fetchall.

fetchall wraps the results (typically multiple rows of data) in a list. Since the names are unique, fetchall returns either a list with just one tuple in the list (e.g. [(rowid,),] or an empty list []. If you desire to know the rowid, then using fetchall requires you to burrow through the list and tuple to get to the rowid.

Using fetchone is better in this case since you get just one row, (rowid,) or None. To get at the rowid (provided there is one) you just have to pick off the first element of the tuple.

If you don't care about the particular rowid and you just want to know there is a hit, then you could use Alex Martelli's suggestion, SELECT count(*), which would return either (1,) or (0,).

Here is some example code:

First some boiler-plate code to setup a toy sqlite table:

import sqlite3
connection = sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('create table components (rowid int,name varchar(50))')    
cursor.execute('insert into components values(?,?)', (1,'foo',))

Using fetchall:

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchall()
    if len(data)==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowids %s'%(name,','.join(map(str,zip(*data)[0]))))

yields:

There is no component named bar
Component foo found with rowids 1

Using fetchone:

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()
    if data is None:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowid %s'%(name,data[0]))

yields:

There is no component named bar
Component foo found with rowid 1

Using SELECT count(*):

for name in ('bar','foo'): 
    cursor.execute("SELECT count(*) FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()[0]
    if data==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found in %s row(s)'%(name,data))

yields:

There is no component named bar
Component foo found in 1 row(s)

Answer 2

Ooops, I have found the answer

exist = cursor.fetchone()
if exist is None:
  #not exists
else:
  #exists

Answer 3

As both existing answers (your own and @unutbu's) point out, the trick is that you do need to do some sort of fetching, after executing the SELECT, to check whether there have been any results to the select or not (whether you do it with a single fetch and check for none, or a fetch-all and check for an empty list, is a marginal difference -- given that you mention a UNIQUE constraint they're basically equivalent approaches).

For a very direct answer, you could select count(*) from components where name = ?, rather than selecting rowid, if all you care is whether the given value for name is present or not (as opposed to, caring about what row id it's on, if at all;-). Executing this select, and fetching the result, gives you 0 if the value is absent, 1 if it's present (no other result is possible given what you mentioned in a comment about the UNIQUE constraint on column name;-).