[Python-checkins] cpython (3.4): Issue #15067: Port 2.7 sqlite3 docs to 3.4
zach.ware
python-checkins at python.org
Tue Apr 1 19:23:04 CEST 2014
http://hg.python.org/cpython/rev/4a2dabac976d
changeset: 90088:4a2dabac976d
branch: 3.4
parent: 90084:7b219429c404
user: Zachary Ware <zachary.ware at gmail.com>
date: Tue Apr 01 12:21:56 2014 -0500
summary:
Issue #15067: Port 2.7 sqlite3 docs to 3.4
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