'How to get a single result from a SQL query in python?

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.

pythonsqlsqlitepython-db-api


Solution 1:[1]

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

cursor.fetchone()[0]

Solution 2:[2]

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.

Solution 3:[3]

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")

Solution 4:[4]

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.

Solution 5:[5]

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

Solution 6:[6]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ido Ran
Solution 2 Paul Rooney
Solution 3 user3467349
Solution 4 Alex Kosh
Solution 5 snakecharmerb
Solution 6 Markus

Related Questions