[Python-checkins] gh-95271: Rework sqlite3 tutorial (#95749)
erlend-aasland
webhook-mailer at python.org
Thu Aug 18 14:36:25 EDT 2022
https://github.com/python/cpython/commit/c87ea10fc91f040822ab3eed2d08b073861360f6
commit: c87ea10fc91f040822ab3eed2d08b073861360f6
branch: main
author: Erlend E. Aasland <erlend.aasland at protonmail.com>
committer: erlend-aasland <erlend.aasland at protonmail.com>
date: 2022-08-18T20:36:20+02:00
summary:
gh-95271: Rework sqlite3 tutorial (#95749)
Co-authored-by: C.A.M. Gerlach <CAM.Gerlach at Gerlach.CAM>
Co-authored-by: Ezio Melotti <ezio.melotti at gmail.com>
files:
M Doc/library/sqlite3.rst
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 67f8b31f11f..32f4188aae5 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -47,85 +47,173 @@ This document includes four main sections:
PEP written by Marc-André Lemburg.
+.. We use the following practises for SQL code:
+ - UPPERCASE for keywords
+ - snake_case for schema
+ - single quotes for string literals
+ - singular for table names
+ - if needed, use double quotes for table and column names
+
.. _sqlite3-tutorial:
Tutorial
--------
-To use the module, start by creating a :class:`Connection` object that
-represents the database. Here the data will be stored in the
-:file:`example.db` file::
+In this tutorial, you will create a database of Monty Python movies
+using basic :mod:`!sqlite3` functionality.
+It assumes a fundamental understanding of database concepts,
+including `cursors`_ and `transactions`_.
+
+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::
import sqlite3
- con = sqlite3.connect('example.db')
+ con = sqlite3.connect("tutorial.db")
-The special path name ``:memory:`` can be provided to create a temporary
-database in RAM.
+The returned :class:`Connection` object ``con``
+represents the connection to the on-disk database.
-Once a :class:`Connection` has been established, create a :class:`Cursor` object
-and call its :meth:`~Cursor.execute` method to perform SQL commands::
+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`::
cur = con.cursor()
- # Create table
- cur.execute('''CREATE TABLE stocks
- (date text, trans text, symbol text, qty real, price real)''')
-
- # Insert a row of data
- cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
+Now that we've got a database connection and a cursor,
+we can create a database table ``movie`` with columns for title,
+release year, and review score.
+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>`::
+
+ cur.execute("CREATE TABLE movie(title, year, score)")
+
+.. Ideally, we'd use sqlite_schema instead of sqlite_master below,
+ but SQLite versions older than 3.33.0 do not recognise that variant.
+
+We can verify that the new table has been created by querying
+the ``sqlite_master`` table built-in to SQLite,
+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::
+
+ >>> res = cur.execute("SELECT name FROM sqlite_master")
+ >>> res.fetchone()
+ ('movie',)
+
+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``::
+
+ >>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
+ >>> res.fetchone() is None
+ True
+
+Now, add two rows of data supplied as SQL literals
+by executing an ``INSERT`` statement,
+once again by calling :meth:`cur.execute(...) <Cursor.execute>`::
+
+ cur.execute("""
+ INSERT INTO movie VALUES
+ ('Monty Python and the Holy Grail', 1975, 8.2),
+ ('And Now for Something Completely Different', 1971, 7.5)
+ """)
+
+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::
- # Save (commit) the changes
con.commit()
- # We can also close the connection if we are done with it.
- # Just be sure any changes have been committed or they will be lost.
- con.close()
-
-The saved data is persistent: it can be reloaded in a subsequent session even
-after restarting the Python interpreter::
-
- import sqlite3
- con = sqlite3.connect('example.db')
- cur = con.cursor()
+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::
-At this point, our database only contains one row::
+ >>> res = cur.execute("SELECT score FROM movie")
+ >>> res.fetchall()
+ [(8.2,), (7.5,)]
- >>> res = cur.execute('SELECT count(rowid) FROM stocks')
- >>> print(res.fetchone())
- (1,)
+The result is a :class:`list` of two :class:`!tuple`\s, one per row,
+each containing that row's ``score`` value.
-The result is a one-item :class:`tuple`:
-one row, with one column.
-Now, let us insert three more rows of data,
-using :meth:`~Cursor.executemany`::
+Now, insert three more rows by calling
+:meth:`cur.executemany(...) <Cursor.executemany>`::
- >>> data = [
- ... ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
- ... ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0),
- ... ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
- ... ]
- >>> cur.executemany('INSERT INTO stocks VALUES(?, ?, ?, ?, ?)', data)
+ data = [
+ ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
+ ("Monty Python's The Meaning of Life", 1983, 7.5),
+ ("Monty Python's Life of Brian", 1979, 8.0),
+ ]
+ cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
+ con.commit() # Remember to commit the transaction after executing INSERT.
-Notice that we used ``?`` placeholders to bind *data* to the query.
+Notice that ``?`` placeholders are used to bind ``data`` to the query.
Always use placeholders instead of :ref:`string formatting <tut-formatting>`
to bind Python values to SQL statements,
-to avoid `SQL injection attacks`_.
-See the :ref:`placeholders how-to <sqlite3-placeholders>` for more details.
+to avoid `SQL injection attacks`_
+(see :ref:`sqlite3-placeholders` for more details).
-Then, retrieve the data by iterating over the result of a ``SELECT`` statement::
+We can verify that the new rows were inserted
+by executing a ``SELECT`` query,
+this time iterating over the results of the query::
- >>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
+ >>> 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")
+ (1979, "Monty Python's Life of Brian")
+ (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)``,
+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::
+
+ >>> 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"):
+ >>> 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'
+
+You've now created an SQLite database using the :mod:`!sqlite3` module,
+inserted data and retrieved values from it in multiple ways.
+
+.. _SQL injection attacks: https://en.wikipedia.org/wiki/SQL_injection
+.. _The Schema Table: https://www.sqlite.org/schematab.html
+.. _cursors: https://en.wikipedia.org/wiki/Cursor_(databases)
+.. _flexible typing: https://www.sqlite.org/flextypegood.html
+.. _sqlite_master: https://www.sqlite.org/schematab.html
+.. _transactions: https://en.wikipedia.org/wiki/Database_transaction
- ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
- ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
- ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
- ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
+.. seealso::
-You've now created an SQLite database using the :mod:`!sqlite3` module.
+ * :ref:`sqlite3-howtos` for further reading:
-.. _SQL injection attacks: https://en.wikipedia.org/wiki/SQL_injection
+ * :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.
.. _sqlite3-reference:
More information about the Python-checkins
mailing list