diff options
Diffstat (limited to 'Doc/library/sqlite3.rst')
| -rw-r--r-- | Doc/library/sqlite3.rst | 143 |
1 files changed, 112 insertions, 31 deletions
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst index 3e3faca..8ffbce3 100644 --- a/Doc/library/sqlite3.rst +++ b/Doc/library/sqlite3.rst @@ -25,7 +25,7 @@ represents the database. Here the data will be stored in the You can also supply the special name ``:memory:`` to create a database in RAM. Once you have a :class:`Connection`, you can create a :class:`Cursor` object -and call its :meth:`execute` method to perform SQL commands:: +and call its :meth:`~Cursor.execute` method to perform SQL commands:: c = conn.cursor() @@ -50,7 +50,7 @@ is insecure; it makes your program vulnerable to an SQL injection attack. 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:`execute` method. (Other database modules +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! @@ -69,8 +69,8 @@ may use a different placeholder, such as ``%s`` or ``:1``.) For example:: c.execute('insert into stocks values (?,?,?,?,?)', t) To retrieve data after executing a SELECT statement, you can either treat the -cursor as an :term:`iterator`, call the cursor's :meth:`fetchone` method to -retrieve a single matching row, or call :meth:`fetchall` to get a list of 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 matching rows. This example uses the iterator form:: @@ -128,7 +128,7 @@ Module functions and constants returns. It will look for a string formed [mytype] in there, and then decide that 'mytype' is the type of the column. It will try to find an entry of 'mytype' in the converters dictionary and then use the converter function found - there to return the value. The column name found in :attr:`cursor.description` + there to return the value. The column name found in :attr:`Cursor.description` is only the first word of the column name, i. e. if you use something like ``'as "x [datetime]"'`` in your SQL, then we will parse out everything until the first blank for the column name: the column name would simply be "x". @@ -215,11 +215,13 @@ Module functions and constants Connection Objects ------------------ -A :class:`Connection` instance has the following attributes and methods: +.. class:: Connection + + A SQLite database connection has the following attributes and methods: .. attribute:: Connection.isolation_level - Get or set the current isolation level. None for autocommit mode or one of + Get or set the current isolation level. :const:`None` for autocommit mode or one of "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See section :ref:`sqlite3-controlling-transactions` for a more detailed explanation. @@ -234,7 +236,7 @@ A :class:`Connection` instance has the following attributes and methods: .. method:: Connection.commit() This method commits the current transaction. If you don't call this method, - anything you did since the last call to commit() is not visible from from + anything you did since the last call to ``commit()`` is not visible from from other database connections. If you wonder why you don't see the data you've written to the database, please check you didn't forget to call this method. @@ -383,9 +385,9 @@ A :class:`Connection` instance has the following attributes and methods: .. attribute:: Connection.text_factory - Using this attribute you can control what objects are returned for the TEXT data - type. By default, this attribute is set to :class:`str` and the - :mod:`sqlite3` module will return strings for TEXT. If you want to + Using this attribute you can control what objects are returned for the ``TEXT`` + data type. By default, this attribute is set to :class:`str` and the + :mod:`sqlite3` module will return Unicode objects for ``TEXT``. If you want to return bytestrings instead, you can set it to :class:`bytes`. For efficiency reasons, there's also a way to return :class:`str` objects @@ -430,8 +432,9 @@ A :class:`Connection` instance has the following attributes and methods: Cursor Objects -------------- -A :class:`Cursor` instance has the following attributes and methods: +.. class:: Cursor + A SQLite database cursor has the following attributes and methods: .. method:: Cursor.execute(sql, [parameters]) @@ -470,7 +473,7 @@ A :class:`Cursor` instance has the following attributes and methods: .. method:: Cursor.executescript(sql_script) This is a nonstandard convenience method for executing multiple SQL statements - at once. It issues a COMMIT statement first, then executes the SQL script it + at once. It issues a ``COMMIT`` statement first, then executes the SQL script it gets as a parameter. *sql_script* can be an instance of :class:`str` or :class:`bytes`. @@ -483,7 +486,7 @@ A :class:`Cursor` instance has the following attributes and methods: .. method:: Cursor.fetchone() Fetches the next row of a query result set, returning a single sequence, - or ``None`` when no more data is available. + or :const:`None` when no more data is available. .. method:: Cursor.fetchmany([size=cursor.arraysize]) @@ -522,8 +525,8 @@ A :class:`Cursor` instance has the following attributes and methods: into :attr:`rowcount`. As required by the Python DB API Spec, the :attr:`rowcount` attribute "is -1 in - case no executeXX() has been performed on the cursor or the rowcount of the last - operation is not determinable by the interface". + case no ``executeXX()`` has been performed on the cursor or the rowcount of the + last operation is not determinable by the interface". This includes ``SELECT`` statements because we cannot determine the number of rows a query produced until all rows were fetched. @@ -535,6 +538,81 @@ A :class:`Cursor` instance has the following attributes and methods: method. For operations other than ``INSERT`` or when :meth:`executemany` is called, :attr:`lastrowid` is set to :const:`None`. +.. attribute:: Cursor.description + + This read-only attribute provides the column names of the last query. To + remain compatible with the Python DB API, it returns a 7-tuple for each + column where the last six items of each tuple are :const:`None`. + + It is set for ``SELECT`` statements without any matching rows as well. + +.. _sqlite3-row-objects: + +Row Objects +----------- + +.. class:: Row + + A :class:`Row` instance serves as a highly optimized + :attr:`~Connection.row_factory` for :class:`Connection` objects. + It tries to mimic a tuple in most of its features. + + It supports mapping access by column name and index, iteration, + representation, equality testing and :func:`len`. + + If two :class:`Row` objects have exactly the same columns and their + members are equal, they compare equal. + + .. versionchanged:: 2.6 + Added iteration and equality (hashability). + + .. method:: keys + + This method returns a tuple of column names. Immediately after a query, + it is the first member of each tuple in :attr:`Cursor.description`. + + .. versionadded:: 2.6 + +Let's assume we initialize a table as in the example given above:: + + conn = sqlite3.connect(":memory:") + c = conn.cursor() + c.execute('''create table stocks + (date text, trans text, symbol text, + qty real, price real)''') + c.execute("""insert into stocks + values ('2006-01-05','BUY','RHAT',100,35.14)""") + conn.commit() + c.close() + +Now we plug :class:`Row` in:: + + >>> conn.row_factory = sqlite3.Row + >>> c = conn.cursor() + >>> c.execute('select * from stocks') + <sqlite3.Cursor object at 0x7f4e7dd8fa80> + >>> r = c.fetchone() + >>> type(r) + <type 'sqlite3.Row'> + >>> r + (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001) + >>> len(r) + 5 + >>> r[2] + u'RHAT' + >>> r.keys() + ['date', 'trans', 'symbol', 'qty', 'price'] + >>> r['qty'] + 100.0 + >>> for member in r: print member + ... + 2006-01-05 + BUY + RHAT + 100.0 + 35.14 + + .. _sqlite3-types: SQLite and Python types @@ -544,36 +622,38 @@ SQLite and Python types Introduction ^^^^^^^^^^^^ -SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB. +SQLite natively supports the following types: ``NULL``, ``INTEGER``, +``REAL``, ``TEXT``, ``BLOB``. The following Python types can thus be sent to SQLite without any problem: +-------------------------------+-------------+ | Python type | SQLite type | +===============================+=============+ -| ``None`` | NULL | +| :const:`None` | ``NULL`` | +-------------------------------+-------------+ -| :class:`int` | INTEGER | +| :class:`int` | ``INTEGER`` | +-------------------------------+-------------+ -| :class:`float` | REAL | +| :class:`float` | ``REAL`` | +-------------------------------+-------------+ -| :class:`bytes` (UTF8-encoded) | TEXT | +| :class:`bytes` (UTF8-encoded) | ``TEXT`` | +-------------------------------+-------------+ -| :class:`str` | TEXT | +| :class:`str` | ``TEXT`` | +-------------------------------+-------------+ -| :class:`buffer` | BLOB | +| :class:`buffer` | ``BLOB`` | +-------------------------------+-------------+ + This is how SQLite types are converted to Python types by default: +-------------+---------------------------------------------+ | SQLite type | Python type | +=============+=============================================+ -| ``NULL`` | None | +| ``NULL`` | :const:`None` | +-------------+---------------------------------------------+ -| ``INTEGER`` | int | +| ``INTEGER`` | :class`int` | +-------------+---------------------------------------------+ -| ``REAL`` | float | +| ``REAL`` | :class:`float` | +-------------+---------------------------------------------+ | ``TEXT`` | depends on text_factory, str by default | +-------------+---------------------------------------------+ @@ -701,9 +781,10 @@ Controlling Transactions ------------------------ By default, the :mod:`sqlite3` module opens transactions implicitly before a -Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), -and commits transactions implicitly before a non-DML, non-query statement (i. e. -anything other than SELECT/INSERT/UPDATE/DELETE/REPLACE). +Data Modification Language (DML) statement (i.e. +``INSERT``/``UPDATE``/``DELETE``/``REPLACE``), and commits transactions +implicitly before a non-DML, non-query statement (i. e. +anything other than ``SELECT`` or the aforementioned). So if you are within a transaction and issue a command like ``CREATE TABLE ...``, ``VACUUM``, ``PRAGMA``, the :mod:`sqlite3` module will commit implicitly @@ -712,7 +793,7 @@ is that some of these commands don't work within transactions. The other reason is that pysqlite needs to keep track of the transaction state (if a transaction is active or not). -You can control which kind of "BEGIN" statements pysqlite implicitly executes +You can control which kind of ``BEGIN`` statements pysqlite implicitly executes (or none at all) via the *isolation_level* parameter to the :func:`connect` call, or via the :attr:`isolation_level` property of connections. @@ -736,7 +817,7 @@ Using the nonstandard :meth:`execute`, :meth:`executemany` and be written more concisely because you don't have to create the (often superfluous) :class:`Cursor` objects explicitly. Instead, the :class:`Cursor` objects are created implicitly and these shortcut methods return the cursor -objects. This way, you can execute a SELECT statement and iterate over it +objects. This way, you can execute a ``SELECT`` statement and iterate over it directly using only a single call on the :class:`Connection` object. .. literalinclude:: ../includes/sqlite3/shortcut_methods.py |