如何从Python中的SQL查询中获取单个结果?

发布于 2024-11-28 18:20:39 字数 368 浏览 2 评论 0原文

使用 Python 时,是否有一种优雅的方式从 SQLite SELECT 查询获取单个结果?

例如:

conn = sqlite3.connect('db_path.db')
cursor=conn.cursor()
cursor.execute("SELECT MAX(value) FROM table")

for row in cursor:
    for elem in row:
        maxVal = elem

有没有办法避免那些嵌套的 for 并直接获取值?我尝试过

maxVal = cursor[0][0]

但没有成功。

Is there an elegant way of getting a single result from an SQLite SELECT query when using Python?

for example:

conn = sqlite3.connect('db_path.db')
cursor=conn.cursor()
cursor.execute("SELECT MAX(value) FROM table")

for row in cursor:
    for elem in row:
        maxVal = elem

is there a way to avoid those nested fors and get the value directly? I've tried

maxVal = cursor[0][0]

without any success.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

倾城月光淡如水﹏ 2024-12-05 18:20:39

我认为您正在寻找 Cursor.fetchone()

cursor.fetchone()[0]

I think you're looking for Cursor.fetchone() :

cursor.fetchone()[0]
美胚控场 2024-12-05 18:20:39

请小心,接受的答案可能会导致 TypeError

由于 fetchone() 文档:

获取查询结果集的下一行,返回单个序列,如果没有更多数据可用,则返回 None。

对于某些 SQL 查询 cursor.fetchone()[0] 可能会变成 None[0] ,从而导致引发 TypeError 异常。

获取第一行或None的更好方法是:

first_row = next(cursor, [None])[0]

如果 SQL 查询为空, next 将使用默认值 [None] 并从该列表中获取第一个元素,而不会引发异常。

Be careful, accepted answer might cause TypeError!

Due to fetchone() documentation:

Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.

So with some SQL queries cursor.fetchone()[0] could turn into None[0] which leads to raising TypeError exception.

Better way to get first row or None is:

first_row = next(cursor, [None])[0]

If SQL query is empty, next will use default value [None] and get first element from that list without raising exceptions.

当梦初醒 2024-12-05 18:20:39

或者,您可以编写一个包装函数,在给定 SQL 的情况下,返回一个标量结果:

def get_scalar_result(conn, sql):
    cursor=conn.cursor()
    cursor.execute(sql)

    return cursor.fetchone()[0]

对于上面的 Python 语法可能不太正确,我深表歉意,但我希望您明白这一点。

Or you could write a wrapper function that, given SQL, returns a scalar result:

def get_scalar_result(conn, sql):
    cursor=conn.cursor()
    cursor.execute(sql)

    return cursor.fetchone()[0]

I apologize for the possibly less than syntactically correct Python above, but I hope you get the idea.

看轻我的陪伴 2024-12-05 18:20:39

如果您没有使用内置的 cursor.fetchone pysqlite

cursor.execute("select value from table order by value desc limit 1")

If you're not using pysqlite which has the built in cursor.fetchone

cursor.execute("select value from table order by value desc limit 1")
对你而言 2024-12-05 18:20:39

序列解包可用于提取标量值来自结果元组。

如果有多行,则通过迭代游标(或cursor.fetchall):

for result, in cursor:
    print(result)

或者如果结果集中只有一行,则使用cursor.fetchone:

result, = cur.fetchone()
print(result)

在这两种情况下result 之后的尾随逗号将元素从单元素元组中解压出来。这与更常见的情况相同,

a, b = (1, 2)

只是元组只有一个元素:

a, = (1,)

Sequence unpacking can be used to extract the scalar value from the result tuple.

By iterating over the cursor (or cursor.fetchall)if there are multiple rows:

for result, in cursor:
    print(result)

Or using cursor.fetchone if there is a single row in the resultset:

result, = cur.fetchone()
print(result)

In both cases the trailing comma after result unpacks the element from the single-element tuple. This is the same as the more commonly seen

a, b = (1, 2)

except the tuples only have one element:

a, = (1,)
逆流 2024-12-05 18:20:39

select count(*) from ... groupy by ... 返回 None 而不是 0
因此 fetchone()[0] 会导致异常。

所以

def get_scalar_from_sql(sqlcur, sqlcmd):
    # select count(*) from .... groupy by ... returns None instead of 0
    sqlcur.execute(sqlcmd)
    scalar = 0
    tuple_or_None = sqlcur.fetchone()
    if not tuple_or_None is None:
        (scalar,) = tuple_or_None
    return scalar

select count(*) from ... groupy by ... returns None instead of 0,
so fetchone()[0] would lead to an exception.

Therefore

def get_scalar_from_sql(sqlcur, sqlcmd):
    # select count(*) from .... groupy by ... returns None instead of 0
    sqlcur.execute(sqlcmd)
    scalar = 0
    tuple_or_None = sqlcur.fetchone()
    if not tuple_or_None is None:
        (scalar,) = tuple_or_None
    return scalar
别挽留 2024-12-05 18:20:39

或者你可以尝试:
cursor.execute("SELECT * FROM table where name='martin'")

or you can try :
cursor.execute("SELECT * FROM table where name='martin'")

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文