Why psycopg cursor.fecthone return a list of None if no row was hit from select query?

Question

My function to query account by email:

CREATE FUNCTION get_account_by_email(account_email varchar(64)) RETURNS account AS $$
SELECT id,
    name,
    email
FROM account
WHERE account.email = account_email;
$$ LANGUAGE SQL;

My python code to call the function ("psycopg[binary]"==3.1.9):

async def get_account_by_email(self, email: str):
    async with self.pool.connection() as conn:
        resp = await conn.execute(f"SELECT * FROM get_account_by_email(%s);", (email,))
        print(resp.rowcount)
        print(resp)
        return await resp.fetchone()

I tried query use an email that doesn't exist. But I get following result: The row count returned is 1. The fetchone returned: (None, None, None, None, None, None)

It is non sense.

Answer 1

You declared a non-set-returning function. It always returns exactly one value of type "account". If the query in the function finds no rows, then the function returns NULL as that value.

If you want your function to be able to return either more than 1 row or less than 1 row then you need to declare that it returns a TABLE or a SETOF.

Answer 2

Reason fetchOne() method is returning a list of None because when there are no rows returned by the query, the fetchOne() method will return a single tuple with None values for each column in the result set. In your case, get_account_by_email is executing successfully, but as there are no rows that match the provided email, resp.fetchone() returns a single tuple with None values for each column. So it is returning the result set of (None, None, None, None, None, None).

To handle this situation:

async def get_account_by_email(self, email: str):
    async with self.pool.connection() as conn:
        resp = await conn.execute(f"SELECT * FROM get_account_by_email(%s);", (email,))
        row = await resp.fetchone()
        if row is None:
            return None  # No rows found
        else:
            return row  # Returning the row

By checking row is None, you can return None if no row matches.