Add a cursor.get method

[rogerbinns]

It is really annoying when a query returns either a single row, or just a single value. Code like:

ver = con.execute("pragma user_version").fetchall()[0][0]

There should be a method (eg named get) that:

• if the query returns a single row with a single value, then return that value
• if the query returns a single row with multiple values, then return a tuple of that row
• if the query returns multiple rows, then return all the rows as a list, where each row is just the value for only one column, and a tuple for multiple columns

The code above then becomes:

ver = con.execute("pragma user_version").get()

Some more examples:

"select 3,4" -> (3, 4)
"select 3; select 4"  -> [3, 4]
"select 3,4; select 4,5"  -> [(3, 4), (4, 5)]
"select 3,4; select 5" -> [(3, 4), 5]
"select 3" -> 3

[kmedcalf]

I added a .scalar property to my cursor extension that basically does .fetchone()[0] so you can do

id = db.executex('select id from t where value=?', value).scalar

(executex is like a regular execute except that it "collects up" arguments into either a dictionary or list using *kw or arg and then uses whichever has stuff in it as the bindings argument to an execute).

[rogerbinns]

@kmedcalf good insight not needing it as a function call so saving on typing. My plan was to post to the python-sqlite list after this imminent release cycle to see what suggestions folks have. Your suggestion is the front runner.

[kmedcalf]

.scalar returns either .fetchone()[0] or, if no rows are returned, None. Exceptions are only raised for statement errors. ie, row = self.fetchone() return None if row is None else row[0]

[rogerbinns]

I definitely want fetchall behaviour so I can do something like:

uver, mode = db.execute("pragma user_version; pragma journal_mode").scalar

[kmedcalf]

Yes, I added several properties (to the cursor) that look like the following (I believe my fetchnext is like your fetchone but I re-implemented them to be sure ...

    def fetchnext(self):
        try:
            return super().__next__()
        except StopIteration:
            return None
        except:
            raise

    fetchone = fetchnext

    getone = property(fetchnext)

    @property
    def getall(self):
        return self.fetchall()

    @property
    def scalar(self):
        "Return first column of first row or None"
        row = self.fetchone()
        return row if row is None else row[0]

    @property
    def get(self):
        rows = self.fetchall()
        return rows if len(rows) != 1 else (rows[0][0] if len(rows[0]) == 1 else rows[0])

This seemed to me to encompass all use cases and can be very explicit (.getone / .getall / .scalar) or magical depending on the shape of the result (.get). I suppose if you really wanted you could define a property .scalars that returned a tuple of the first column values of each row ... which would make your case really self-documenting.

[kmedcalf]

Mayhaps something like this?

    @property
    def scalars(self):
        "Return a tuple of the first column value of each row"
        return tuple(row[0] for row in self)

[rogerbinns]

The fetchone implementation just directly calls the internal __next__ method. fetchall calls PySequence_List.

I've updated the original comment to reflect we just want the least amount of tuple/list wrapping.

[kmedcalf]

Like this:

    @property
    def get(self):
        "Fetches rows with minimal wrapping"
        rows = self.fetchall()
        if len(rows) == 1:
            rows = rows[0]
        else:
            for i, row in enumerate(rows):
                if len(row) == 1:
                    rows[i] = row[0]
        if len(rows) == 1:
            rows = rows[0]
        return rows

which gives:

>>> db.execute('select 3, 4').get
(3, 4)
>>> db.execute('select 3; select 4').get
[3, 4]
>>> db.execute('select 3,4; select 4,5').get
[(3, 4), (4, 5)]
>>> db.execute('select 3,4; select 5').get
[(3, 4), 5]
>>> db.execute('select 3').get
3

[rogerbinns]

That is it. However I will be implementing it in C, but the results will be identical.