Python psycopg2 check row exists
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
.
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.
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.