Python psycopg2 check row exists

问题:

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__'

回答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.

回答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.

回答3:

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 len(cur.fetchall()) > 0:
        return true
    else:
        return false