[Python-checkins] cpython (merge 3.4 -> default): Closes #15067: Merge port of 2.7 sqlite3 docs.

zach.ware python-checkins at python.org
Tue Apr 1 19:23:05 CEST 2014


http://hg.python.org/cpython/rev/2c7ebb930b64
changeset:   90089:2c7ebb930b64
parent:      90086:2fbba6d68b94
parent:      90088:4a2dabac976d
user:        Zachary Ware <zachary.ware at gmail.com>
date:        Tue Apr 01 12:22:51 2014 -0500
summary:
  Closes #15067: Merge port of 2.7 sqlite3 docs.

files:
  Doc/library/sqlite3.rst |  97 +++++++++++++++-------------
  1 files changed, 51 insertions(+), 46 deletions(-)


diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -13,8 +13,8 @@
 application using SQLite and then port the code to a larger database such as
 PostgreSQL or Oracle.
 
-sqlite3 was written by Gerhard Häring and provides a SQL interface compliant
-with the DB-API 2.0 specification described by :pep:`249`.
+The sqlite3 module was written by Gerhard Häring.  It provides a SQL interface
+compliant with the DB-API 2.0 specification described by :pep:`249`.
 
 To use the module, you must first create a :class:`Connection` object that
 represents the database.  Here the data will be stored in the
@@ -31,23 +31,29 @@
    c = conn.cursor()
 
    # Create table
-   c.execute('''create table stocks
-   (date text, trans text, symbol text,
-    qty real, price real)''')
+   c.execute('''CREATE TABLE stocks
+                (date text, trans text, symbol text, qty real, price real)''')
 
    # Insert a row of data
-   c.execute("""insert into stocks
-             values ('2006-01-05','BUY','RHAT',100,35.14)""")
+   c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
 
    # Save (commit) the changes
    conn.commit()
 
-   # We can also close the cursor if we are done with it
-   c.close()
+   # 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.
+   conn.close()
+
+The data you've saved is persistent and is available in subsequent sessions::
+
+   import sqlite3
+   conn = sqlite3.connect('example.db')
+   c = conn.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.
+is insecure; it makes your program vulnerable to an SQL injection attack
+(see http://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
@@ -56,19 +62,20 @@
 example::
 
    # Never do this -- insecure!
-   symbol = 'IBM'
-   c.execute("select * from stocks where symbol = '%s'" % symbol)
+   symbol = 'RHAT'
+   c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
 
    # Do this instead
-   t = ('IBM',)
-   c.execute('select * from stocks where symbol=?', t)
+   t = ('RHAT',)
+   c.execute('SELECT * FROM stocks WHERE symbol=?', t)
+   print(c.fetchone())
 
-   # Larger example
-   for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
-             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
-             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
-            ]:
-       c.execute('insert into stocks values (?,?,?,?,?)', t)
+   # 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),
+               ]
+   c.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
@@ -77,16 +84,13 @@
 
 This example uses the iterator form::
 
-   >>> c = conn.cursor()
-   >>> c.execute('select * from stocks order by price')
-   >>> for row in c:
-   ...     print(row)
-   ...
+   >>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
+           print(row)
+
    ('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', 'MSOFT', 1000, 72.0)
-   >>>
+   ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
 
 
 .. seealso::
@@ -99,6 +103,9 @@
       The SQLite web page; the documentation describes the syntax and the
       available data types for the supported SQL dialect.
 
+   http://www.w3schools.com/sql/
+      Tutorial, reference and examples for learning SQL syntax.
+
    :pep:`249` - Database API Specification 2.0
       PEP written by Marc-André Lemburg.
 
@@ -517,7 +524,7 @@
 
    .. method:: execute(sql, [parameters])
 
-      Executes an SQL statement. The SQL statement may be parametrized (i. e.
+      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).
@@ -714,19 +721,20 @@
 
 This is how SQLite types are converted to Python types by default:
 
-+-------------+---------------------------------------------+
-| SQLite type | Python type                                 |
-+=============+=============================================+
-| ``NULL``    | :const:`None`                               |
-+-------------+---------------------------------------------+
-| ``INTEGER`` | :class:`int`                                |
-+-------------+---------------------------------------------+
-| ``REAL``    | :class:`float`                              |
-+-------------+---------------------------------------------+
-| ``TEXT``    | depends on text_factory, str by default     |
-+-------------+---------------------------------------------+
-| ``BLOB``    | :class:`bytes`                              |
-+-------------+---------------------------------------------+
++-------------+----------------------------------------------+
+| SQLite type | Python type                                  |
++=============+==============================================+
+| ``NULL``    | :const:`None`                                |
++-------------+----------------------------------------------+
+| ``INTEGER`` | :class:`int`                                 |
++-------------+----------------------------------------------+
+| ``REAL``    | :class:`float`                               |
++-------------+----------------------------------------------+
+| ``TEXT``    | depends on :attr:`~Connection.text_factory`, |
+|             | :class:`str` by default                      |
++-------------+----------------------------------------------+
+| ``BLOB``    | :class:`bytes`                               |
++-------------+----------------------------------------------+
 
 The type system of the :mod:`sqlite3` module is extensible in two ways: you can
 store additional Python types in a SQLite database via object adaptation, and
@@ -742,9 +750,6 @@
 sqlite3 module's supported types for SQLite: one of NoneType, int, float,
 str, bytes.
 
-The :mod:`sqlite3` module uses Python object adaptation, as described in
-:pep:`246` for this.  The protocol to use is :class:`PrepareProtocol`.
-
 There are two ways to enable the :mod:`sqlite3` module to adapt a custom Python
 type to one of the supported ones.
 
@@ -800,8 +805,8 @@
 
 .. note::
 
-   Converter functions **always** get called with a string, no matter under which
-   data type you sent the value to SQLite.
+   Converter functions **always** get called with a :class:`bytes` object, no
+   matter under which data type you sent the value to SQLite.
 
 ::
 

-- 
Repository URL: http://hg.python.org/cpython


More information about the Python-checkins mailing list