Each Answer to this Q is separated by one/two green lines.
Is there an elegant way of getting a single result from an SQLite SELECT query when using Python?
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
without any success.
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()
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.execute("select value from table order by value desc limit 1")
Be careful, accepted answer might cause
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() could turn into
None which leads to raising
Better way to get first row or
first_row = next(cursor, [None])
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)
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
fetchone() would lead to an exception.
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"")