Repository: python/cpython
Pull request: gh-96121: Merge sqlite3.Row examples
- Merge sqlite3.Row examples
- Remove the how-to and incorporate it into the reference

<!--
Thanks for your contribution!
Please read this comment in its entirety. It's quite important.

# Pull Request title

It should be in the following format:

```
gh-NNNNN: Summary of the changes made
```

Where: gh-NNNNN refers to the GitHub issue number.

Most PRs will require an issue number. Trivial changes, like fixing a typo, do not need an issue.

# Backport Pull Request title

If this is a backport PR (PR made against branches other than `main`),
please ensure that the PR title is in the following format:

```
[X.Y] <title from the original PR> (GH-NNNN)
```

Where: [X.Y] is the branch name, e.g. [3.6].

GH-NNNN refers to the PR number from `main`.

-->


<!-- gh-issue-number: gh-96121 -->
* Issue: gh-96121
<!-- /gh-issue-number -->


Problem statement:
Merge sqlite3.Row (reference) example with the Row How-to Guide
In the sqlite3 docs, there are two examples on how to use the `sqlite3.Row` row factory:

1. the sqlite3.Row class reference
2. the _"Accessing columns by name instead of by index"_[^1] how-to guide

Suggesting to improve matters by merging 1. into 2.
That will clean up the reference, and make the how-to more complete.


[^1]: Yes, it is somewhat clumsy named; we should consider improving that also.

Hints from issue discussion:

Commit message: Remove the how-to and incorporate it into the reference

Patch to review:
diff --git a/Doc/includes/sqlite3/rowclass.py b/Doc/includes/sqlite3/rowclass.py
deleted file mode 100644
index fc60287069a854..00000000000000
--- a/Doc/includes/sqlite3/rowclass.py
+++ /dev/null
@@ -1,14 +0,0 @@
-import sqlite3
-
-con = sqlite3.connect(":memory:")
-con.row_factory = sqlite3.Row
-
-cur = con.cursor()
-cur.execute("select 'John' as name, 42 as age")
-for row in cur:
-    assert row[0] == row["name"]
-    assert row["name"] == row["nAmE"]
-    assert row[1] == row["age"]
-    assert row[1] == row["AgE"]
-
-con.close()
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 6413d8859180da..1ebac55692511f 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -207,7 +207,6 @@ inserted data and retrieved values from it in multiple ways.
       * :ref:`sqlite3-placeholders`
       * :ref:`sqlite3-adapters`
       * :ref:`sqlite3-converters`
-      * :ref:`sqlite3-columns-by-name`
       * :ref:`sqlite3-connection-context-manager`
 
    * :ref:`sqlite3-explanation` for in-depth background on transaction control.
@@ -1255,6 +1254,12 @@ Cursor objects
          >>> cur.connection == con
          True
 
+.. The sqlite3.Row example used to be a how-to. It has now been incorporated
+   into the Row reference. We keep the anchor here in order not to break
+   existing links.
+
+.. _sqlite3-columns-by-name:
+
 .. _sqlite3-row-objects:
 
 Row objects
@@ -1262,7 +1267,7 @@ Row objects
 
 .. class:: Row
 
-   A :class:`Row` instance serves as a highly optimized
+   A :class:`!Row` instance serves as a highly optimized
    :attr:`~Connection.row_factory` for :class:`Connection` objects.
    It tries to mimic a :class:`tuple` in most of its features,
    and supports iteration, :func:`repr`, equality testing, :func:`len`,
@@ -1279,45 +1284,21 @@ Row objects
    .. versionchanged:: 3.5
       Added support of slicing.
 
-Let's assume we initialize a table as in the example given above::
-
-   con = sqlite3.connect(":memory:")
-   cur = con.cursor()
-   cur.execute('''create table stocks
-   (date text, trans text, symbol text,
-    qty real, price real)''')
-   cur.execute("""insert into stocks
-               values ('2006-01-05','BUY','RHAT',100,35.14)""")
-   con.commit()
-   cur.close()
-
-Now we plug :class:`Row` in::
-
-   >>> con.row_factory = sqlite3.Row
-   >>> cur = con.cursor()
-   >>> cur.execute('select * from stocks')
-   <sqlite3.Cursor object at 0x7f4e7dd8fa80>
-   >>> r = cur.fetchone()
-   >>> type(r)
-   <class 'sqlite3.Row'>
-   >>> tuple(r)
-   ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
-   >>> len(r)
-   5
-   >>> r[2]
-   'RHAT'
-   >>> r.keys()
-   ['date', 'trans', 'symbol', 'qty', 'price']
-   >>> r['qty']
-   100.0
-   >>> for member in r:
-   ...     print(member)
-   ...
-   2006-01-05
-   BUY
-   RHAT
-   100.0
-   35.14
+   Example::
+
+      >>> con.row_factory = sqlite3.Row  # con is an sqlite3.Connection object.
+      >>> res = con.execute("select 'Tellus' as name, 6378 as radius")
+      >>> row = res.fetchone()
+      >>> row.keys()
+      ['name', 'radius']
+      >>> row[0]
+      'Tellus'
+      >>> row["name"]
+      'Tellus'
+      >>> row["RADIUS"]
+      6378
+      >>> "Tellus" in row
+      True
 
 
 .. _sqlite3-blob-objects:
@@ -1766,20 +1747,6 @@ directly using only a single call on the :class:`Connection` object.
 .. literalinclude:: ../includes/sqlite3/shortcut_methods.py
 
 
-.. _sqlite3-columns-by-name:
-
-Accessing columns by name instead of by index
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-One useful feature of the :mod:`!sqlite3` module is the built-in
-:class:`sqlite3.Row` class designed to be used as a row factory.
-
-Rows wrapped with this class can be accessed both by index (like tuples) and
-case-insensitively by name:
-
-.. literalinclude:: ../includes/sqlite3/rowclass.py
-
-
 .. _sqlite3-connection-context-manager:
 
 Using the connection as a context manager


Code review comments:
diff_hunk: @@ -1255,14 +1254,20 @@ Cursor objects
          >>> cur.connection == con
          True
 
+.. The sqlite3.Row example used to be a how-to. It has now been incorporated
+   into the Row reference. We keep the anchor here in order not to break
+   existing links.
+
+.. _sqlite3-columns-by-name:
+
 .. _sqlite3-row-objects:
 
 Row objects
 ^^^^^^^^^^^
 
 .. class:: Row
 
-   A :class:`Row` instance serves as a highly optimized
+   A :class:`!Row` instance serves as a highly optimized
    :attr:`~Connection.row_factory` for :class:`Connection` objects.
    It tries to mimic a :class:`tuple` in most of its features,
path: Doc/library/sqlite3.rst
text: @user1:
Isn't it more like a `namedtuple` since it supports access by name and position?
```suggestion
   It tries to mimic a :class:`~collections.namedtuple` in most of its features,
```

@author:
I'm afraid that mentioning named tuples will create assumptions that columns can be accessed using the dot operator; which is not true. See also #57508.

@author:
I wonder if we should just drop that "it mimics" sentence, and just describe what it does (it can quack, walk, swim, instead of it mimics a duck)

@user1:
> will create assumptions that columns can be accessed using the dot operator

Good point, I mixed up item access and attribute access.

> just describe what it does

This sounds good to me.

diff_hunk: @@ -1279,45 +1284,21 @@ Row objects
    .. versionchanged:: 3.5
       Added support of slicing.
 
-Let's assume we initialize a table as in the example given above::
-
-   con = sqlite3.connect(":memory:")
-   cur = con.cursor()
-   cur.execute('''create table stocks
-   (date text, trans text, symbol text,
-    qty real, price real)''')
-   cur.execute("""insert into stocks
-               values ('2006-01-05','BUY','RHAT',100,35.14)""")
-   con.commit()
-   cur.close()
-
-Now we plug :class:`Row` in::
-
-   >>> con.row_factory = sqlite3.Row
-   >>> cur = con.cursor()
-   >>> cur.execute('select * from stocks')
-   <sqlite3.Cursor object at 0x7f4e7dd8fa80>
-   >>> r = cur.fetchone()
-   >>> type(r)
-   <class 'sqlite3.Row'>
-   >>> tuple(r)
-   ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
-   >>> len(r)
-   5
-   >>> r[2]
-   'RHAT'
-   >>> r.keys()
-   ['date', 'trans', 'symbol', 'qty', 'price']
-   >>> r['qty']
-   100.0
-   >>> for member in r:
-   ...     print(member)
-   ...
-   2006-01-05
-   BUY
-   RHAT
-   100.0
-   35.14
+   Example::
+
+      >>> con.row_factory = sqlite3.Row  # con is an sqlite3.Connection object.
+      >>> res = con.execute("select 'Tellus' as name, 6378 as radius")
path: Doc/library/sqlite3.rst
text: @user1:
```suggestion
      >>> res = con.execute("select 'Tellus' as name, 6378 as radius")
```
I find this line a bit confusing.  It seems that instead of selecting an actual row from a db it just return the values you provide, even though it requires a connection (to what?).  In addition, people might not be familiar with `'Tellus'`, adding to the confusion.

@author:
Yes, it uses a SELECT query with literals. The connection is needed to execute the query.

We can use another example; I just don't like the current example.

@author:
We can create a test database as well, but that will expand the example with, IMO, unneeded information. We're interested in the row factory only; using a SELECT query that operates with literals quickly gets us a row of data without the need for a database table.

@user1:
> The connection is needed to execute the query.

Is this just a connection to `:memory:`?  Since I'm not querying an existing table, I assume that I don't need to create it and populate it -- how many lines would it take to create a valid `con`?

The goal for me would be having a reproducible example that I can play around with in the interactive interpreter.

Are there other factories?  Maybe the example could be moved back in an howto that sets up a single db and compares different factories if you think an howto specific to row factories is overkill?

@author:
> Is this just a connection to :memory:? 

With the type of query used here, that does not matter. It could be any kind of connection, with or without tables. Since we're SELECTing literals, it does not matter.

A minimal connection is:

```python3
import sqlite3
cx = sqlite3.connect(":memory:")
```

@author:
> Are there other factories? Maybe the example could be moved back in an howto that sets up a single db and compares different factories if you think an howto specific to row factories is overkill?

Not at the moment, but there has been talk about a namedtuple-factory.

I think a general "how to use/write row factories" could be useful. There is a current example in the `row_factory` reference; we could build on that. (For another PR/issue, of course.)

@user1:
> A minimal connection is:

Since the `import sqlite3` can be omitted, this means that a single line (`con = sqlite3.connect(":memory:")`) is enough to turn that into a working example, right?
If that's the case, having a working example outweighs the extra line IMHO.

The `SELECT` with literals is still a bit "weird" if someone is not familiar with it, but the full example will help showing that it's performed on an empty DB and is not assuming the existence of an existing table.  If you want to be more explicit, perhaps a footnote could be added.

@author:
Yes, the SELECT trick may feel weird if you are unfamiliar with it. It may make sense to add a comment or footnote.

@user2:
A comment might probably be most appropriate, IMO, since it explains a potentially confusing but specific implementation detail of the example

@author:
Hm, the SELECT-with-literals trick is very convenient for a lot of examples. Perhaps it would be better to add a "tips and tricks" howto, where we show it, and just reference that howto from the examples that use it?[^1] Obviously, it would not make sense to explain it every time we use it, hence my slight reluctance to add a note about it in this particular example.

[^1]: it could be a footnote too

@author:
I'm not sure how to best resolve this _right now_. The old example also used SELECT-with-literals, so IMO, we're not introducing more confusion than there possibly was before. We can always improve matters with future PRs. I'll create a ticket for this on the tracker, though, so we don't forget about it.

@author:
I created #96165

diff_hunk: @@ -1279,45 +1284,21 @@ Row objects
    .. versionchanged:: 3.5
       Added support of slicing.
 
-Let's assume we initialize a table as in the example given above::
-
-   con = sqlite3.connect(":memory:")
-   cur = con.cursor()
-   cur.execute('''create table stocks
-   (date text, trans text, symbol text,
-    qty real, price real)''')
-   cur.execute("""insert into stocks
-               values ('2006-01-05','BUY','RHAT',100,35.14)""")
-   con.commit()
-   cur.close()
-
-Now we plug :class:`Row` in::
-
-   >>> con.row_factory = sqlite3.Row
-   >>> cur = con.cursor()
-   >>> cur.execute('select * from stocks')
-   <sqlite3.Cursor object at 0x7f4e7dd8fa80>
-   >>> r = cur.fetchone()
-   >>> type(r)
-   <class 'sqlite3.Row'>
-   >>> tuple(r)
-   ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
-   >>> len(r)
-   5
-   >>> r[2]
-   'RHAT'
-   >>> r.keys()
-   ['date', 'trans', 'symbol', 'qty', 'price']
-   >>> r['qty']
-   100.0
-   >>> for member in r:
-   ...     print(member)
-   ...
-   2006-01-05
-   BUY
-   RHAT
-   100.0
-   35.14
+   Example::
+
+      >>> con.row_factory = sqlite3.Row  # con is an sqlite3.Connection object.
+      >>> res = con.execute("select 'Tellus' as name, 6378 as radius")
+      >>> row = res.fetchone()
+      >>> row.keys()
+      ['name', 'radius']
+      >>> row[0]
+      'Tellus'
+      >>> row["name"]
+      'Tellus'
path: Doc/library/sqlite3.rst
text: @user1:
If you want to make it a bit more compact:
```suggestion
      >>> row[0], row["name"]  # supports access by both index and name
      ('Tellus', 'Tellus')
```
I also like to add inline comments that tells what's the point of the example (unless it's obvious).

@author:
Yes, that's neat.