Check if a row exists in sqlite3?

Question

How do I properly check if a row exists in my database table using Python 3 and sqlite3?

Here's my original code:

cursor.execute("SELECT name, age FROM people WHERE pin=?", (pin, ))
name, age = cursor.fetchone() or ('', 0)

So currently I'm setting them a default value with the or ('', 0), and I really don't like this.

Is there a way to check if the person already exists? Something like this (pseudo):

if cursor.exists("pin IN people"):
    cursor.execute("SELECT name, age FROM people WHERE pin=?", (pin, ))
    name, age = cursor.fetchone()

doesnt fetchone raise an exception if there are none? – Joran Beasley May 05 '15 at 00:05 — Joran Beasley, May 05 '15 at 00:05
It returns None if there is no selection – ODiogoSilva May 05 '15 at 00:06 — ODiogoSilva, May 05 '15 at 00:06

score 4 · Accepted Answer · answered May 05 '15 at 07:34

4

Don't call fetchone(); just treat the cursor as an iterator:

for row in c.execute("SELECT name, age FROM ..."):
    name, age = row
    break
else:
    print("not found")

answered May 05 '15 at 07:34

CL.

173,858
17
217
259

Didn't know you could do this, I'm at work currently so can't test it, but I'll accept if it works! – Skamah One May 05 '15 at 09:36

score 2 · Answer 2 · answered May 05 '15 at 00:06

2

Something like this?

vals = cursor.execute("SELECT name, age FROM people WHERE pin=?", (pin, )).fetchone()

if vals:
    name, age = vals

answered May 05 '15 at 00:06

ODiogoSilva

2,394
1
19
20

score 2 · Answer 3 · edited May 23 '17 at 12:26

Alternatively you may use EXISTS to validate the query if your table is large and you will benefit with index on pin, take a look at this SO, so you may do:

c = cursor.execute("""SELECT EXISTS (SELECT 1 
                                     FROM people 
                                     WHERE pin=?
                                     LIMIT 1)""", (pin, )).fetchone()[0]
if c:
    ...

The cursor will return either (1,) (if 1 or more rows returned) or (0,) (if None returned), c will be either 1/0 as fetchone()[0] will get the integer value 1/0 from above.

Exists could be more efficient depending on the size of your table and if you have index set on the field you query.

score 1 · Answer 4 · answered May 05 '15 at 00:10

1

You could use a count query:

if c.execute("SELECT count(*) FROM people WHERE pin=?", (pin, )).fetchone()[0] > 0:
    ...

answered May 05 '15 at 00:10

Reto Aebersold

16,306
5
55
74

Check if a row exists in sqlite3?

4 Answers4