From 9d08562ed43fee1738a8ef8b07e52598399cc1be Mon Sep 17 00:00:00 2001 From: Zachary Ware Date: Tue, 1 Apr 2014 12:21:56 -0500 Subject: Issue #15067: Port 2.7 sqlite3 docs to 3.4 --- Doc/library/sqlite3.rst | 97 ++++++++++++++++++++++++++----------------------- 1 file changed, 51 insertions(+), 46 deletions(-) diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst index 1ec8398..c2e6080 100644 --- a/Doc/library/sqlite3.rst +++ b/Doc/library/sqlite3.rst @@ -13,8 +13,8 @@ SQLite for internal data storage. It's also possible to prototype an 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 @@ and call its :meth:`~Cursor.execute` method to perform SQL commands:: 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 @@ modules may use a different placeholder, such as ``%s`` or ``:1``.) For 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 @@ matching rows. 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 @@ This example uses the iterator form:: 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 @@ Cursor Objects .. 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 @@ The following Python types can thus be sent to SQLite without any problem: 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 @@ use other Python types with SQLite, you must **adapt** them to one of the 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 @@ and constructs a :class:`Point` object from it. .. 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. :: -- cgit v0.12