英文:
Why psycopg cursor.fecthone return a list of None if no row was hit from select query?
问题
以下是您要翻译的内容：
我的按电子邮件查询帐户的函数：
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;
我的用于调用该函数的Python代码（"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()
我尝试使用一个不存在的电子邮件进行查询。但我得到以下结果：
返回的行数为1。
fetchone 返回：
(None, None, None, None, None, None)
这是毫无意义的。
英文:
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.
答案1
得分: 1
你声明了一个非集合返回的函数。它始终返回类型为 "account" 的一个值。如果函数中的查询未找到行，则函数将返回 NULL 作为该值。
如果你希望函数能够返回多于1行或少于1行的情况，那么你需要声明它返回一个表格（TABLE）或一个集合（SETOF）。
英文:
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.
答案2
得分: 0
fetchOne()方法返回None列表的原因是，当查询未返回任何行时，fetchOne()方法将返回一个包含每列的None值的单个元组。在您的情况下，get_account_by_email成功执行，但由于没有与提供的电子邮件匹配的行，resp.fetchone()返回一个包含每列None值的单个元组。因此，它返回结果集（None, None, None, None, None, None）。
要处理这种情况：
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 # 未找到任何行
else:
return row # 返回行
通过检查row是否为None，您可以在没有匹配的行时返回None。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论