[Python-checkins] gh-95432: Add doctest for sqlite3 tutorial (GH-96193)
miss-islington
webhook-mailer at python.org
Tue Aug 23 02:56:44 EDT 2022
https://github.com/python/cpython/commit/145d8f0e57c8e1c819e76f2e6644013e3305e030
commit: 145d8f0e57c8e1c819e76f2e6644013e3305e030
branch: 3.10
author: Miss Islington (bot) <31488909+miss-islington at users.noreply.github.com>
committer: miss-islington <31488909+miss-islington at users.noreply.github.com>
date: 2022-08-22T23:56:39-07:00
summary:
gh-95432: Add doctest for sqlite3 tutorial (GH-96193)
(cherry picked from commit 04c73e5efbfea8ae9da5bd518cee96086017ef4f)
Co-authored-by: Erlend E. Aasland <erlend.aasland at protonmail.com>
files:
M Doc/library/sqlite3.rst
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 1f7ebbe109ff..ce009bef2397 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -8,6 +8,15 @@
**Source code:** :source:`Lib/sqlite3/`
+.. Make sure we always doctest the tutorial with an empty database.
+
+.. testsetup::
+
+ import sqlite3
+ src = sqlite3.connect(":memory:", isolation_level=None)
+ dst = sqlite3.connect("tutorial.db", isolation_level=None)
+ src.backup(dst)
+ del src, dst
.. _sqlite3-intro:
@@ -65,7 +74,9 @@ First, we need to create a new database and open
a database connection to allow :mod:`!sqlite3` to work with it.
Call :func:`sqlite3.connect` to to create a connection to
the database :file:`tutorial.db` in the current working directory,
-implicitly creating it if it does not exist::
+implicitly creating it if it does not exist:
+
+.. testcode::
import sqlite3
con = sqlite3.connect("tutorial.db")
@@ -75,7 +86,9 @@ represents the connection to the on-disk database.
In order to execute SQL statements and fetch results from SQL queries,
we will need to use a database cursor.
-Call :meth:`con.cursor() <Connection.cursor>` to create the :class:`Cursor`::
+Call :meth:`con.cursor() <Connection.cursor>` to create the :class:`Cursor`:
+
+.. testcode::
cur = con.cursor()
@@ -86,7 +99,9 @@ For simplicity, we can just use column names in the table declaration --
thanks to the `flexible typing`_ feature of SQLite,
specifying the data types is optional.
Execute the ``CREATE TABLE`` statement
-by calling :meth:`cur.execute(...) <Cursor.execute>`::
+by calling :meth:`cur.execute(...) <Cursor.execute>`:
+
+.. testcode::
cur.execute("CREATE TABLE movie(title, year, score)")
@@ -99,7 +114,9 @@ which should now contain an entry for the ``movie`` table definition
(see `The Schema Table`_ for details).
Execute that query by calling :meth:`cur.execute(...) <Cursor.execute>`,
assign the result to ``res``,
-and call :meth:`res.fetchone() <Cursor.fetchone>` to fetch the resulting row::
+and call :meth:`res.fetchone() <Cursor.fetchone>` to fetch the resulting row:
+
+.. doctest::
>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
@@ -108,7 +125,9 @@ and call :meth:`res.fetchone() <Cursor.fetchone>` to fetch the resulting row::
We can see that the table has been created,
as the query returns a :class:`tuple` containing the table's name.
If we query ``sqlite_master`` for a non-existent table ``spam``,
-:meth:`!res.fetchone()` will return ``None``::
+:meth:`!res.fetchone()` will return ``None``:
+
+.. doctest::
>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
@@ -116,7 +135,9 @@ If we query ``sqlite_master`` for a non-existent table ``spam``,
Now, add two rows of data supplied as SQL literals
by executing an ``INSERT`` statement,
-once again by calling :meth:`cur.execute(...) <Cursor.execute>`::
+once again by calling :meth:`cur.execute(...) <Cursor.execute>`:
+
+.. testcode::
cur.execute("""
INSERT INTO movie VALUES
@@ -128,7 +149,9 @@ The ``INSERT`` statement implicitly opens a transaction,
which needs to be committed before changes are saved in the database
(see :ref:`sqlite3-controlling-transactions` for details).
Call :meth:`con.commit() <Connection.commit>` on the connection object
-to commit the transaction::
+to commit the transaction:
+
+.. testcode::
con.commit()
@@ -136,7 +159,9 @@ We can verify that the data was inserted correctly
by executing a ``SELECT`` query.
Use the now-familiar :meth:`cur.execute(...) <Cursor.execute>` to
assign the result to ``res``,
-and call :meth:`res.fetchall() <Cursor.fetchall>` to return all resulting rows::
+and call :meth:`res.fetchall() <Cursor.fetchall>` to return all resulting rows:
+
+.. doctest::
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
@@ -146,7 +171,9 @@ The result is a :class:`list` of two :class:`!tuple`\s, one per row,
each containing that row's ``score`` value.
Now, insert three more rows by calling
-:meth:`cur.executemany(...) <Cursor.executemany>`::
+:meth:`cur.executemany(...) <Cursor.executemany>`:
+
+.. testcode::
data = [
("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
@@ -164,14 +191,16 @@ to avoid `SQL injection attacks`_
We can verify that the new rows were inserted
by executing a ``SELECT`` query,
-this time iterating over the results of the query::
+this time iterating over the results of the query:
+
+.. doctest::
>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
... print(row)
- (1971, "And Now for Something Completely Different")
- (1975, "Monty Python and the Holy Grail")
+ (1971, 'And Now for Something Completely Different')
+ (1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
- (1982, "Monty Python Live at the Hollywood Bowl")
+ (1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")
Each row is a two-item :class:`tuple` of ``(year, title)``,
@@ -180,15 +209,17 @@ matching the columns selected in the query.
Finally, verify that the database has been written to disk
by calling :meth:`con.close() <Connection.close>`
to close the existing connection, opening a new one,
-creating a new cursor, then querying the database::
+creating a new cursor, then querying the database:
+
+.. doctest::
>>> con.close()
>>> new_con = sqlite3.connect("tutorial.db")
>>> new_cur = new_con.cursor()
- >>> res = new_cur.execute("SELECT year, title FROM movie ORDER BY score DESC"):
+ >>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
>>> title, year = res.fetchone()
>>> print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')
- 'The highest scoring Monty Python movie is "Monty Python and the Holy Grail", released in 1975'
+ The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975
You've now created an SQLite database using the :mod:`!sqlite3` module,
inserted data and retrieved values from it in multiple ways.
More information about the Python-checkins
mailing list