如何使用 Python 检查 SQLite 中一行的存在? [英] How to check the existence of a row in SQLite with Python?

问题描述

我有如下查询语句的游标:

I have the cursor with the query statement as follows:

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

我想检查组件是否存在:名称并返回一个python变量.我该怎么做?

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

推荐答案

由于 name 是唯一的，我真的很喜欢你(OP 的)使用 fetchone 的方法或 Alex Martelli 使用 SELECT count(*) 的方法，而不是我最初使用 fetchall 的建议.

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.

fetchall 将结果(通常是多行数据)包装在一个列表中.由于 name 是唯一的，fetchall 返回列表中只有一个元组的列表(例如 [(rowid,),] 或一个空列表 [].如果你想知道 rowid，那么使用 fetchall 需要你在列表和元组中挖掘rowid.

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.

在这种情况下使用 fetchone 更好，因为您只得到一行，(rowid,) 或 None.要获得 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.

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

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,).

这是一些示例代码:

首先是一些样板代码来设置一个玩具 sqlite 表:

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

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',))

使用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))))))

产量:

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

使用fetchone:

Using 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]))

产量:

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

使用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))

产量:

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

这篇关于如何使用 Python 检查 SQLite 中一行的存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！