Python psycopg2 check row exists

user_388038 0
Viewed 19990

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__'
psycopg2 postgresql sql python
4 Answers

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.

user_61940970
Related