"Pythonic" way to handle maybe-empty query responses?

[DavidBuchanan314]

This isn't necessarily an apsw-specific question, if anything it's more of a thinking-aloud ramble. Hopefully it's not too annoying.

I often find myself writing queries that look like this:

foo, bar = db.execute("SELECT foo, bar FROM mytable WHERE baz=?", (baz,)).fetchone()

On the happy path, I expect 1 row to be returned. But there might be 0 rows returned - fetchone() will return None, and python will raise TypeError: cannot unpack non-iterable NoneType object.

So then I might write something like this:

try:
    foo, bar = db.execute("SELECT foo, bar FROM mytable WHERE baz=?", (baz,)).fetchone()
except TypeError:
    print("oh no")

or maybe

row =  db.execute("SELECT foo, bar FROM mytable WHERE baz=?", (baz,)).fetchone()
if row is None:
    raise Exception("oh no")
foo, bar = row

Both of these feel a little janky to me, for different reasons. The first one is non-obvious what's going on without a comment to explain (and there are other potential TypeError causes, too), and the second is more explicit but means littering my code with temporary "row" variables.

So my general question is, is there a more graceful and "pythonic" way of doing this that I haven't thought of? I'm curious what other people are doing because I'm sure this general pattern is common.

Maybe I could write a wrapper function like so:

def exec_one(db, query, params) -> tuple:
    cur = db.execute(query, params)
    row = cur.fetchone()
    if row is None:
        raise MyEmptyResponseException("0 rows returned")
    if cur.fetchone() is not None:
        raise Exception("Expected a single row, but there were more")
    return row

(the final check is perhaps unnecessary)

[DavidBuchanan314]

I wonder, is there some way I could subclass apsw.Cursor (to implement fetch_exactly_one()) and then configure an apsw.Connection instance to use it?

[DavidBuchanan314]

Here's my latest thought, a generic "make sure it's not None" helper

class NoneError(TypeError):
	pass

T = TypeVar("T")
def definitely(obj: Optional[T]) -> T:
	if obj is None:
		raise NoneError("Expected something, but found None")
	return obj

Which I can use like so:

try:
    foo, bar = definitely(db.execute("SELECT foo, bar FROM mytable WHERE baz=?", (baz,)).fetchone())
except NoneError:
    print("oh no")

[rogerbinns]

Have you considered using get? It will return None if the query has no rows, else all the rows. If your data means at most one row is returned then great, else you can add LIMIT 1 to the query.

foo, bar = db.execute("SELECT foo, bar FROM mytable WHERE baz=? LIMIT 1", (baz,)).get

This will set foo and bar correctly on a match, else tuple unpack error. get works really great when only one value (non-null) is returned. This will give None on not found (or null at the SQL level).

foo = db.execute("SELECT foo FROM mytable WHERE baz=? LIMIT 1", (baz,)).get

[DavidBuchanan314]

Ah I didn't know about .get, that's definitely an improvement for single-value results (I was previously doing foo, *_ = ...getvalue())

[rogerbinns]

You may also be able to do some shenanigans with apsw.pyobject. For example this makes a function that will create a tuple:

def make_tuple(n):
    return apsw.pyobject((None,) * n)

connection.create_scalar_function("tuple", make_tuple)

So your query can either return a result of N values or tuple(N). But it does start getting hairy and a lot like building an ORM.

I have always wanted some way of fixing queries at a higher level so you don't repeat yourself so that this couldn't have errors:

   for name, typo in con.execute("select name, type from .....")

It is irritating to have to repeat the names at the Python level and at the SQL level and ensure they match.

[rogerbinns]

You can also use match statements with get:

import apsw

con = apsw.Connection("")

con.execute("create table mytable(foo,bar,baz)")
con.executemany("insert into mytable values(?,?,?)",
                (
                    (1,2,3),
                    (4,5,6),
                    (7,8,9),
                ))


match con.execute("select foo, bar from mytable where baz=3").get:
    case None:
        print("no row")
    case [foo, bar]:
        print(f"{foo=} {bar=}")

match con.execute("select foo, bar from mytable where baz=-3").get:
    case None:
        print("no row")
    case [foo, bar]:
        print(f"{foo=} {bar=}")

That outputs:

foo=1 bar=2
no row

Since match statements don't have to be exhaustive you don't have to include the None case if you would do nothing as a result. And you can have branches based on the values too:

match con.execute("select foo, bar from mytable where baz=3").get:
    case [foo, bar] if foo == 1:
        print("foo is 1")
    case [foo, bar]:
        print(f"{foo=} {bar=}")

I suspect match and get give the most Pythonic solution.