sql - 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, next(zip(*data))))))

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)