Python check if exists in SQLite3

Question

I'm trying to check whether a variable exists in an SQLite3 db. Unfortunately I can not seem to get it to work. The airports table contains 3 colums, with ICAO as the first column.

if c.execute("SELECT EXISTS(SELECT 1 FROM airports WHERE ICAO='EHAM')") is True:
    print("Found!")
else:
    print("Not found...")

The code runs without any errors, but the result is always the same (not found).

What is wrong with this code?

zero323 · Accepted Answer · 2015-03-14 20:58:44Z

9

Try this instead:

c.execute("SELECT EXISTS(SELECT 1 FROM airports WHERE ICAO='EHAM')")

if c.fetchone():
    print("Found!")

else:
    print("Not found...")

Return value of cursor.execute is cursor (or to be more precise reference to itself) and is independent of query results. You can easily check that:

 >>> r = c.execute("SELECT EXISTS(SELECT 1 FROM airports WHERE ICAO='EHAM')")
 >>> r is True
 False
 >>> r is False
 False
 >>> r is None
 False

 >>> r is c
 True

From the other hand if you call cursor.fetchone result tuple or None if there is no row that passes query conditions. So in your case if c.fetchone(): would mean one of the below:

if (1, ):
    ...

or

if None:
    ...

edited Mar 14, 2015 at 20:58

answered Sep 15, 2013 at 21:38

zero323

331k108 gold badges981 silver badges958 bronze badges

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

1 Comment

eric Over a year ago

This doesn't work for me, as EXISTS either returns a 0 or 1 in sqlite3. See the docs (sqlite.org/lang_expr.html): "The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0. " So you can change the answer from None to (0,). I will edit if nobody objects in the next few days.

John Lin · Accepted Answer · 2020-11-30 13:24:02Z

Let's prepare a database to test it.

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE airports (ICAO STRING, col2 STRING, col3 STRING)")
c.execute("INSERT INTO airports (ICAO, col2, col3) VALUES (?, ?, ?)", ('EHAM', 'value2', 'value3'))

Since your SELECT 1 FROM airports WHERE ICAO = 'EHAM' already serves the purpose of checking existence, let's use it directly, without the redundant SELECT EXISTS()

if c.execute("SELECT 1 FROM airports WHERE ICAO = 'EHAM'").fetchone():
    print("Found!")
else:
    print("Not found...")

the result is

Found!

Let's check a non-existent case

if c.execute("SELECT 1 FROM airports WHERE ICAO = 'NO-SUCH'").fetchone():
    print("Found!")
else:
    print("Not found...")

the result is

Not found...

If you just want to fix your code, you can try

if c.execute("SELECT EXISTS(SELECT 1 FROM airports WHERE ICAO = 'EHAM')").fetchone() == (1,):
    print("Found!")
else:
    print("Not found...")

the result is

Found!

Marten · Accepted Answer · 2021-07-02 12:56:22Z

1

Thanks for the answer from zero323, although the code snippet is wrong, as fetchone() does not return True or False. It only returns 1 for True and 0 for False. (binary) The following code works without problems in Python3:

response = self.connection.execute("SELECT EXISTS(SELECT 1 FROM invoices WHERE id=?)", (self.id, ))
fetched = response.fetchone()[0]
if fetched == 1:
    print("Exist")
else:
    print("Does not exist")

answered Jul 2, 2021 at 12:56

Marten

87313 silver badges28 bronze badges

Collectives™ on Stack Overflow

Python check if exists in SQLite3

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related