postgresql Python psycopg2 检查行是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20449048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python psycopg2 check row exists
提问by André Ricardo
In Python psycopg2 how can I check if a row exists?
在 Python psycopg2 中，如何检查行是否存在？
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__'
回答by Martijn Pieters
Don't use fetchall()(which returns a list, which is never 'larger than 0'), use fetchone():
不要使用fetchall()（它返回一个永远不会“大于 0”的列表），使用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 Noneif there is nothing to fetch, and testing against is not Nonegives you a handy boolean value to return directly.
fetchone()None如果没有任何东西可获取，则返回，并且针对is not None它进行测试会为您提供一个方便的布尔值以直接返回。
回答by Clodoaldo Neto
Using existswill allow Postgresql to stop searching at the first occurrence in instead of searching until exhausted:
使用exists将允许 Postgresql 在第一次出现时停止搜索，而不是搜索直到用完：
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.
另一个优点是它将始终返回包含布尔值的单行，该值可以直接使用而无需进一步解释。
回答by Kushan Gunasekera
You can easily handled it by using rowcount. This is what psycopgdocumentation mention about rowcount,
您可以使用 轻松处理它rowcount。这就是psycopg文档提到的内容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.
此只读属性指定最后一次 execute*() 生成（对于 SELECT 之类的 DQL 语句）或受影响（对于 UPDATE 或 INSERT 之类的 DML 语句）的行数。
如果没有对游标执行 execute*()或接口无法确定的最后一次操作的行数，则该属性为 -1 。
So below examples will give you better understand about how to use rowcount.
所以下面的例子会让你更好地理解如何使用rowcount.
example - 1
示例 - 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
示例 - 2
>>> query_2 = 'SELECT * FROM some_table LIMIT 1;'
>>> cursor.execute(query)
>>> cursor.rowcount
1
example - 3
示例 - 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
示例 - 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 UPDATEquery, you'll get updated row count for rowcount. So basically rowcountwill display how many rows affected by your query. CREATEquery won't affect to any of the rows, so that's why you get -1for rowcount.
请注意：如果您执行UPDATE查询，您将获得更新的rowcount. 所以基本上rowcount会显示受查询影响的行数。CREATE查询将不会影响到任何行的，所以这就是为什么你会得到-1的rowcount。
回答by user8139501
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
