如何用Python检查SQLite中是否存在行？

I have the cursor with the query statement as follows:

我的光标与查询语句如下：

cursor.execute("select rowid from components where name = ?", (name,))

I want to check for the existence of the components: name and return to a python variable. How do I do that?

我想检查组件的存在：name并返回python变量。我怎么做？

4 个解决方案

#1

Since the names are unique, I really favor your (the OP's) method of using fetchone or Alex Martelli's method of using SELECT count(*) over my initial suggestion of using fetchall.

由于名称是唯一的，我真的很喜欢你的（OP的）方法使用fetchone或Alex Martelli的方法使用SELECT count（*）超过我最初使用fetchall的建议。

fetchall wraps the results (typically multiple rows of data) in a list. Since the names are unique, fetchall returns either a list with just one tuple in the list (e.g. [(rowid,),] or an empty list []. If you desire to know the rowid, then using fetchall requires you to burrow through the list and tuple to get to the rowid.

fetchall将列表中的结果（通常是多行数据）包装起来。由于名称是唯一的，fetchall返回列表中只有一个元组的列表（例如[（rowid，），]或空列表[]。如果你想知道rowid，那么使用fetchall需要你挖洞列表和元组到达rowid。

Using fetchone is better in this case since you get just one row, (rowid,) or None. To get at the rowid (provided there is one) you just have to pick off the first element of the tuple.

在这种情况下使用fetchone会更好，因为你只得到一行（rowid）或None。要获得rowid（只要有一个），你只需要选择元组的第一个元素。

If you don't care about the particular rowid and you just want to know there is a hit, then you could use Alex Martelli's suggestion, SELECT count(*), which would return either (1,) or (0,).

如果你不关心特定的rowid并且你只想知道有一个命中，那么你可以使用Alex Martelli的建议，SELECT count（*），它将返回（1，）或（0，）。

Here is some example code:

这是一些示例代码：

First some boiler-plate code to setup a toy sqlite table:

首先是一些用于设置玩具sqlite表的样板代码：

import sqlite3
connection = sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('create table components (rowid int,name varchar(50))')    
cursor.execute('insert into components values(?,?)', (1,'foo',))

Using fetchall:

使用fetchall：

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchall()
    if len(data)==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowids %s'%(name,','.join(map(str, next(zip(*data))))))

yields:

收益率：

There is no component named bar
Component foo found with rowids 1

Using fetchone:

使用fetchone：

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()
    if data is None:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowid %s'%(name,data[0]))

yields:

收益率：

There is no component named bar
Component foo found with rowid 1

Using SELECT count(*):

使用SELECT count（*）：

for name in ('bar','foo'): 
    cursor.execute("SELECT count(*) FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()[0]
    if data==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found in %s row(s)'%(name,data))

yields:

收益率：

There is no component named bar
Component foo found in 1 row(s)

#2

I have found the answer.

我找到了答案。

exist = cursor.fetchone()
if exist is None:
  ... # does not exist
else:
  ... # exists

#3

As both existing answers (your own and @unutbu's) point out, the trick is that you do need to do some sort of fetching, after executing the SELECT, to check whether there have been any results to the select or not (whether you do it with a single fetch and check for none, or a fetch-all and check for an empty list, is a marginal difference -- given that you mention a UNIQUE constraint they're basically equivalent approaches).

由于现有的答案（你自己的和@ unutbu的）都指出，诀窍是你需要在执行SELECT之后进行某种提取，以检查选择是否有任何结果（不管你是否做）它只需一次获取并检查无，或者fetch-all并检查空列表，这是一个边际差异 - 假设您提到UNIQUE约束它们基本上是等效的方法）。

For a very direct answer, you could select count(*) from components where name = ?, rather than selecting rowid, if all you care is whether the given value for name is present or not (as opposed to, caring about what row id it's on, if at all;-). Executing this select, and fetching the result, gives you 0 if the value is absent, 1 if it's present (no other result is possible given what you mentioned in a comment about the UNIQUE constraint on column name;-).

对于一个非常直接的答案，你可以从name =？的组件中选择count（*），而不是选择rowid，如果你关心的是name的给定值是否存在（相反，关心什么是行id）它在，如果有的话;-)。执行此选择并获取结果，如果值不存在，则给出0;如果存在，则给出1（鉴于您在关于列名称的UNIQUE约束的注释中提到的内容，没有其他结果;-)。

#4

To make it even shorter...:

为了使它更短......：

row = cursor.fetchone()

if row:
  print "row is present"
else:
  print "row is not present"