[Python-checkins] cpython (2.7): Clean-up the SQLite introduction.

[raymond.hettinger]

http://hg.python.org/cpython/rev/d229032dc213
changeset:   76387:d229032dc213
branch:      2.7
user:        Raymond Hettinger <python at rcn.com>
date:        Tue Apr 17 22:48:06 2012 -0400
summary:
  Clean-up the SQLite introduction.

files:
  Doc/library/sqlite3.rst |  52 ++++++++++++++--------------
  1 files changed, 26 insertions(+), 26 deletions(-)


diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -23,7 +23,7 @@
 :file:`/tmp/example` file::
 
    import sqlite3
-   conn = sqlite3.connect('/tmp/example')
+   conn = sqlite3.connect('example.db')
 
 You can also supply the special name ``:memory:`` to create a database in RAM.
 
@@ -33,13 +33,11 @@
    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()
@@ -47,16 +45,17 @@
    # We can also close the cursor if we are done with it
    c.close()
 
-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.
-
 The data you've saved is persistent and is available in subsequent sessions::
 
    import sqlite3
-   conn = sqlite3.connect('/tmp/example')
+   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
+(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
 second argument to the cursor's :meth:`~Cursor.execute` method.  (Other database
@@ -64,19 +63,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 = (symbol,)
-   c.execute('select * from stocks where symbol=?', t)
+   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
@@ -85,16 +85,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
+
    (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
    (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
    (u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
    (u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)
-   >>>
 
 
 .. seealso::
@@ -107,6 +104,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.
 

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