[Python-checkins] bpo-20364: Improve sqlite3 placeholder docs (GH-25003)
berkerpeksag
webhook-mailer at python.org
Wed Apr 14 08:28:59 EDT 2021
https://github.com/python/cpython/commit/3386ca0b36327afeef8d7eff277b2aed1030c08d
commit: 3386ca0b36327afeef8d7eff277b2aed1030c08d
branch: master
author: Erlend Egeberg Aasland <erlend.aasland at innova.no>
committer: berkerpeksag <berker.peksag at gmail.com>
date: 2021-04-14T15:28:55+03:00
summary:
bpo-20364: Improve sqlite3 placeholder docs (GH-25003)
files:
M Doc/includes/sqlite3/execute_1.py
M Doc/library/sqlite3.rst
M Doc/tools/susp-ignored.csv
diff --git a/Doc/includes/sqlite3/execute_1.py b/Doc/includes/sqlite3/execute_1.py
index 3466b1265a5bf..42aad4d5839f0 100644
--- a/Doc/includes/sqlite3/execute_1.py
+++ b/Doc/includes/sqlite3/execute_1.py
@@ -2,17 +2,22 @@
con = sqlite3.connect(":memory:")
cur = con.cursor()
-cur.execute("create table people (name_last, age)")
-
-who = "Yeltsin"
-age = 72
+cur.execute("create table lang (lang_name, lang_age)")
# This is the qmark style:
-cur.execute("insert into people values (?, ?)", (who, age))
+cur.execute("insert into lang values (?, ?)", ("C", 49))
-# And this is the named style:
-cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})
+# The qmark style used with executemany():
+lang_list = [
+ ("Fortran", 64),
+ ("Python", 30),
+ ("Go", 11),
+]
+cur.executemany("insert into lang values (?, ?)", lang_list)
-print(cur.fetchone())
+# And this is the named style:
+cur.execute("select * from lang where lang_name=:name and lang_age=:age",
+ {"name": "C", "age": 49})
+print(cur.fetchall())
con.close()
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index aee58a4bbfc07..6bdf4ed0d81bc 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -55,33 +55,6 @@ The data you've saved is persistent and is available in subsequent sessions::
con = sqlite3.connect('example.db')
cur = con.cursor()
-Usually your SQL operations will need to use values from Python variables. You
-shouldn't assemble your query using Python's string operations because doing so
-is insecure; it makes your program vulnerable to an SQL injection attack
-(see https://xkcd.com/327/ for humorous example of what can go wrong).
-
-Instead, use the DB-API's parameter substitution. Put ``?`` as a placeholder
-wherever you want to use a value, and then provide a tuple of values as the
-second argument to the cursor's :meth:`~Cursor.execute` method. (Other database
-modules may use a different placeholder, such as ``%s`` or ``:1``.) For
-example::
-
- # Never do this -- insecure!
- symbol = 'RHAT'
- cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
-
- # Do this instead
- t = ('RHAT',)
- cur.execute('SELECT * FROM stocks WHERE symbol=?', t)
- print(cur.fetchone())
-
- # Larger example that inserts many records at a time
- purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
- ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
- ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
- ]
- cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
-
To retrieve data after executing a SELECT statement, you can either treat the
cursor as an :term:`iterator`, call the cursor's :meth:`~Cursor.fetchone` method to
retrieve a single matching row, or call :meth:`~Cursor.fetchall` to get a list of the
@@ -98,6 +71,33 @@ This example uses the iterator form::
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
+.. _sqlite3-placeholders:
+
+Usually your SQL operations will need to use values from Python variables. You
+shouldn't assemble your query using Python's string operations because doing so
+is insecure; it makes your program vulnerable to an SQL injection attack
+(see the `xkcd webcomic <https://xkcd.com/327/>`_ for a humorous example of
+what can go wrong)::
+
+ # Never do this -- insecure!
+ symbol = 'RHAT'
+ cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
+
+Instead, use the DB-API's parameter substitution. Put a placeholder wherever
+you want to use a value, and then provide a tuple of values as the second
+argument to the cursor's :meth:`~Cursor.execute` method. An SQL statement may
+use one of two kinds of placeholders: question marks (qmark style) or named
+placeholders (named style). For the qmark style, ``parameters`` must be a
+:term:`sequence <sequence>`. For the named style, it can be either a
+:term:`sequence <sequence>` or :class:`dict` instance. The length of the
+:term:`sequence <sequence>` must match the number of placeholders, or a
+:exc:`ProgrammingError` is raised. If a :class:`dict` is given, it must contain
+keys for all named parameters. Any extra items are ignored. Here's an example
+of both styles:
+
+.. literalinclude:: ../includes/sqlite3/execute_1.py
+
+
.. seealso::
https://www.sqlite.org
@@ -607,14 +607,8 @@ Cursor Objects
.. method:: execute(sql[, parameters])
- Executes an SQL statement. The SQL statement may be parameterized (i. e.
- placeholders instead of SQL literals). The :mod:`sqlite3` module supports two
- kinds of placeholders: question marks (qmark style) and named placeholders
- (named style).
-
- Here's an example of both styles:
-
- .. literalinclude:: ../includes/sqlite3/execute_1.py
+ Executes an SQL statement. Values may be bound to the statement using
+ :ref:`placeholders <sqlite3-placeholders>`.
:meth:`execute` will only execute a single SQL statement. If you try to execute
more than one statement with it, it will raise a :exc:`.Warning`. Use
@@ -624,9 +618,10 @@ Cursor Objects
.. method:: executemany(sql, seq_of_parameters)
- Executes an SQL command against all parameter sequences or mappings found in
- the sequence *seq_of_parameters*. The :mod:`sqlite3` module also allows
- using an :term:`iterator` yielding parameters instead of a sequence.
+ Executes a :ref:`parameterized <sqlite3-placeholders>` SQL command
+ against all parameter sequences or mappings found in the sequence
+ *seq_of_parameters*. The :mod:`sqlite3` module also allows using an
+ :term:`iterator` yielding parameters instead of a sequence.
.. literalinclude:: ../includes/sqlite3/executemany_1.py
diff --git a/Doc/tools/susp-ignored.csv b/Doc/tools/susp-ignored.csv
index 325c5fa35eed7..a3ee3326d255d 100644
--- a/Doc/tools/susp-ignored.csv
+++ b/Doc/tools/susp-ignored.csv
@@ -209,9 +209,9 @@ library/smtplib,,:port,method must support that as well as a regular host:port
library/socket,,::,'5aef:2b::8'
library/socket,,:can,"return (can_id, can_dlc, data[:can_dlc])"
library/socket,,:len,fds.frombytes(cmsg_data[:len(cmsg_data) - (len(cmsg_data) % fds.itemsize)])
-library/sqlite3,,:age,"cur.execute(""select * from people where name_last=:who and age=:age"", {""who"": who, ""age"": age})"
+library/sqlite3,,:name,"cur.execute(""select * from lang where lang_name=:name and lang_age=:age"","
+library/sqlite3,,:age,"cur.execute(""select * from lang where lang_name=:name and lang_age=:age"","
library/sqlite3,,:memory,
-library/sqlite3,,:who,"cur.execute(""select * from people where name_last=:who and age=:age"", {""who"": who, ""age"": age})"
library/sqlite3,,:path,"db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)"
library/ssl,,:My,"Organizational Unit Name (eg, section) []:My Group"
library/ssl,,:My,"Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Organization, Inc."
More information about the Python-checkins
mailing list