Python psycopg2 check row exists

Question

In Python psycopg2 how can I check if a row exists?

def track_exists(self, track_id):
    cur = self.conn.cursor()
    cur.execute("SELECT fma_track_id FROM tracks WHERE fma_track_id = %s", (track_id,))
    if cur.fetchall() > 0:
        return true
    else:
        return false

Currently I am getting

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "mumu.py", line 38, in track_exists
if cur.fetchall() > 0:
TypeError: 'NoneType' object has no attribute '__getitem__'

Answer 1

Don't use fetchall() (which returns a list, which is never 'larger than 0'), use fetchone():

def track_exists(self, track_id):
    cur = self.conn.cursor()
    cur.execute("SELECT fma_track_id FROM tracks WHERE fma_track_id = %s", (track_id,))
    return cur.fetchone() is not None

fetchone() returns None if there is nothing to fetch, and testing against is not None gives you a handy boolean value to return directly.

Answer 2

Using exists will allow Postgresql to stop searching at the first occurrence in instead of searching until exhausted:

exists_query = '''
    select exists (
        select 1
        from tracks
        where fma_track_id = %s
    )'''
cursor.execute (exists_query, (track_id,))
return cursor.fetchone()[0]

Another advantage is that it will always return a single row containing a boolean value which can be used directly without further interpretation.

Answer 3

You can easily handled it by using rowcount. This is what psycopg documentation mention about rowcount,

This read-only attribute specifies the number of rows that the last execute*() produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE or INSERT).

The attribute is -1 in case no execute*() has been performed on the cursor or the row count of the last operation if it can’t be determined by the interface.

So below examples will give you better understand about how to use rowcount.

---

example - 1

>> # if your SELECT query doesn't have any values you'll get 0 as the output
>>> query_1 = 'SELECT * FROM some_table LIMIT 0;'
>>> cursor.execute(query)
>>> cursor.rowcount
0

---

example - 2

>>> query_2 = 'SELECT * FROM some_table LIMIT 1;'
>>> cursor.execute(query)
>>> cursor.rowcount
1

---

example - 3

>>> # no LIMIT in the query, so you'll get the whole row count
>>> query_3 = 'SELECT * FROM some_table;'
>>> cursor.execute(query)
>>> cursor.rowcount
14000

---

example - 4

>>> # this query won't return anything, so you'll get -1 as the rowcount
>>> query_4 = 'CREATE TABLE new_table AS SELECT * FROM old_table;'
>>> cursor.execute(query)
>>> cursor.rowcount
-1

---

So you can modify your function like below,

def track_exists(self, track_id):
    cur = self.conn.cursor()
    cur.execute("SELECT fma_track_id FROM tracks WHERE fma_track_id = %s", (track_id,))

    # if cur.rowcount > 0:
    #     return True
    # else:
    #     return False

    return cur.rowcount > 0  # more Pythonic way

PLEASE NOTE: If you execute UPDATE query, you'll get updated row count for rowcount. So basically rowcount will display how many rows affected by your query. CREATE query won't affect to any of the rows, so that's why you get -1 for rowcount.