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.

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

cursor.fetchone()[0]

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.

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

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.

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

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

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