# [Python-checkins] r51876 - python/branches/release25-maint/Doc/lib/libsqlite3.tex

georg.brandl python-checkins at python.org
Thu Sep 14 07:05:43 CEST 2006

Author: georg.brandl
Date: Thu Sep 14 07:05:42 2006
New Revision: 51876

Modified:
python/branches/release25-maint/Doc/lib/libsqlite3.tex
Log:
Backport rev 51866-51868 from trunk (sqlite3 documentation fixes).

Modified: python/branches/release25-maint/Doc/lib/libsqlite3.tex
==============================================================================
--- python/branches/release25-maint/Doc/lib/libsqlite3.tex	(original)
+++ python/branches/release25-maint/Doc/lib/libsqlite3.tex	Thu Sep 14 07:05:42 2006
@@ -146,8 +146,8 @@
wait for the lock to go away until raising an exception. The default for the
timeout parameter is 5.0 (five seconds).

-For the \var{isolation_level} parameter, please see \member{isolation_level}
-\ref{sqlite3-Connection-IsolationLevel} property of \class{Connection} objects.
+For the \var{isolation_level} parameter, please see the \member{isolation_level}
+property of \class{Connection} objects in section~\ref{sqlite3-Connection-IsolationLevel}.

SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If
you want to use other types, like you have to add support for them yourself.
@@ -197,7 +197,7 @@
\verbatiminput{sqlite3/complete_statement.py}
\end{funcdesc}

-\begin{funcdesc}{}enable_callback_tracebacks{flag}
+\begin{funcdesc}{enable_callback_tracebacks}{flag}
By default you will not get any tracebacks in user-defined functions,
aggregates, converters, authorizer callbacks etc. If you want to debug them,
you can call this function with \var{flag} as True. Afterwards, you will get
@@ -212,13 +212,14 @@
\label{sqlite3-Connection-IsolationLevel}
\begin{memberdesc}{isolation_level}
Get or set the current isolation level. None for autocommit mode or one of
-  "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See  Controlling Transactions
-  \ref{sqlite3-Controlling-Transactions} for a more detailed explanation.
+  "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See Controlling Transactions'',
+  section~\ref{sqlite3-Controlling-Transactions}, for a more detailed explanation.
\end{memberdesc}

\begin{methoddesc}{cursor}{\optional{cursorClass}}
The cursor method accepts a single optional parameter \var{cursorClass}.
-  This is a custom cursor class which must extend \class{sqlite3.Cursor}.
+  If supplied, this must be a custom cursor class that extends
+  \class{sqlite3.Cursor}.
\end{methoddesc}

\begin{methoddesc}{execute}{sql, \optional{parameters}}
@@ -244,7 +245,7 @@
Creates a user-defined function that you can later use from within SQL
statements under the function name \var{name}. \var{num_params} is the number
of parameters the function accepts, and \var{func} is a Python callable that is
-called as SQL function.
+called as the SQL function.

The function can return any of the types supported by SQLite: unicode, str,
int, long, float, buffer and None.
@@ -274,7 +275,7 @@

Creates a collation with the specified \var{name} and \var{callable}. The
callable will be passed two string arguments. It should return -1 if the first
-is ordered lower than the second, 0 if they are ordered equal and 1 and if the
+is ordered lower than the second, 0 if they are ordered equal and 1 if the
first is ordered higher than the second.  Note that this controls sorting
(ORDER BY in SQL) so your comparisons don't affect other SQL operations.

@@ -323,20 +324,21 @@

\begin{memberdesc}{row_factory}
You can change this attribute to a callable that accepts the cursor and
-  the original row as tuple and will return the real result row.  This
-  way, you can implement more advanced ways of returning results, like
-  ones that can also access columns by name.
+  the original row as a tuple and will return the real result row.  This
+  way, you can implement more advanced ways of returning results, such
+  as returning an object that can also access columns by name.

Example:

\verbatiminput{sqlite3/row_factory.py}

-  If the standard tuple types don't suffice for you, and you want name-based
+  If returning a tuple doesn't suffice and you want name-based
-  highly-optimized sqlite3.Row type. It provides both
+  highly-optimized \class{sqlite3.Row} type. \class{Row} provides both
-  approach or even a db_row based solution.
+  no memory overhead. It will probably be better than your own custom
+  dictionary-based approach or even a db_row based solution.
+  % XXX what's a db_row-based solution?
\end{memberdesc}

\begin{memberdesc}{text_factory}
@@ -350,7 +352,7 @@
attribute to \constant{sqlite3.OptimizedUnicode}.

You can also set it to any other callable that accepts a single bytestring
-  parameter and returns the result object.
+  parameter and returns the resulting object.

See the following example code for illustration:

@@ -358,7 +360,7 @@
\end{memberdesc}

\begin{memberdesc}{total_changes}
-  Returns the total number of database rows that have be modified, inserted,
+  Returns the total number of database rows that have been modified, inserted,
or deleted since the database connection was opened.
\end{memberdesc}

@@ -385,9 +387,9 @@

\verbatiminput{sqlite3/execute_2.py}

-    \method{execute} will only execute a single SQL statement. If you try to
+    \method{execute()} will only execute a single SQL statement. If you try to
execute more than one statement with it, it will raise a Warning. Use
-    \method{executescript} if want to execute multiple SQL statements with one
+    \method{executescript()} if you want to execute multiple SQL statements with one
call.
\end{methoddesc}

@@ -395,7 +397,7 @@
\begin{methoddesc}{executemany}{sql, seq_of_parameters}
Executes a SQL command against all parameter sequences or mappings found in the
sequence \var{sql}. The \module{sqlite3} module also allows
-to use an iterator yielding parameters instead of a sequence.
+using an iterator yielding parameters instead of a sequence.

\verbatiminput{sqlite3/executemany_1.py}

@@ -407,7 +409,7 @@
\begin{methoddesc}{executescript}{sql_script}

This is a nonstandard convenience method for executing multiple SQL statements
-at once. It issues a COMMIT statement before, then executes the SQL script it
+at once. It issues a COMMIT statement first, then executes the SQL script it
gets as a parameter.

\var{sql_script} can be a bytestring or a Unicode string.
@@ -464,20 +466,19 @@
\lineii{BLOB}{buffer}
\end{tableii}

-The type system of the \module{sqlite3} module is extensible in both ways: you can store
+The type system of the \module{sqlite3} module is extensible in two ways: you can store
additional Python types in a SQLite database via object adaptation, and you can
let the \module{sqlite3} module convert SQLite types to different Python types via
converters.

-Like described before, SQLite supports only a limited set of types natively. To
+As described before, SQLite supports only a limited set of types natively. To
use other Python types with SQLite, you must \strong{adapt} them to one of the sqlite3
-module's supported types for SQLite. So, one of NoneType, int, long, float,
+module's supported types for SQLite: one of NoneType, int, long, float,
str, unicode, buffer.

-The \module{sqlite3} module uses the Python object adaptation, like described in PEP 246
-for this.  The protocol to use is \class{PrepareProtocol}.
+The \module{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 \module{sqlite3} module to adapt a custom Python type
to one of the supported ones.
@@ -493,8 +494,8 @@
self.x, self.y = x, y
\end{verbatim}

-Now you want to store the point in a single SQLite column. You'll have to
-choose one of the supported types first that you use to represent the point in.
+Now you want to store the point in a single SQLite column.  First you'll have to
+choose one of the supported types first to be used for representing the point.
Let's just use str and separate the coordinates using a semicolon. Then you
need to give your class a method \code{__conform__(self, protocol)} which must
return the converted value. The parameter \var{protocol} will be
@@ -507,13 +508,13 @@
The other possibility is to create a function that converts the type to the
string representation and register the function with \method{register_adapter}.

-
\begin{notice}
The type/class to adapt must be a new-style class, i. e. it must have
\class{object} as one of its bases.
\end{notice}

+
The \module{sqlite3} module has two default adapters for Python's built-in
\class{datetime.date} and \class{datetime.datetime} types.  Now let's suppose
we want to store \class{datetime.datetime} objects not in ISO representation,
@@ -523,16 +524,17 @@

\subsubsection{Converting SQLite values to custom Python types}

-Now that's all nice and dandy that you can send custom Python types to SQLite.
+Writing an adapter lets you send custom Python types to SQLite.
But to make it really useful we need to make the Python to SQLite to Python
-roundtrip work.
+roundtrip work.

Enter converters.

-Let's go back to the Point class. We stored the x and y coordinates separated
-via semicolons as strings in SQLite.
+Let's go back to the \class{Point} class. We stored the x and y
+coordinates separated via semicolons as strings in SQLite.

-Let's first define a converter function that accepts the string as a parameter and constructs a Point object from it.
+First, we'll define a converter function that accepts the string as a
+parameter and constructs a \class{Point} object from it.

\begin{notice}
Converter functions \strong{always} get called with a string, no matter
@@ -558,11 +560,12 @@
\item Explicitly via the column name
\end{itemize}

-Both ways are described at \ref{sqlite3-Module-Contents} in the text explaining
-the constants \constant{PARSE_DECLTYPES} and \constant{PARSE_COlNAMES}.
+Both ways are described in Module Constants'', section~\ref{sqlite3-Module-Contents}, in
+the entries for the constants \constant{PARSE_DECLTYPES} and
+\constant{PARSE_COLNAMES}.

-The following example illustrates both ways.
+The following example illustrates both approaches.

\verbatiminput{sqlite3/converter_point.py}

@@ -571,8 +574,8 @@
There are default adapters for the date and datetime types in the datetime
module. They will be sent as ISO dates/ISO timestamps to SQLite.

-The default converters are registered under the name "date" for datetime.date
-and under the name "timestamp" for datetime.datetime.
+The default converters are registered under the name "date" for \class{datetime.date}
+and under the name "timestamp" for \class{datetime.datetime}.

This way, you can use date/timestamps from Python without any additional
fiddling in most cases. The format of the adapters is also compatible with the
@@ -584,12 +587,12 @@

\subsection{Controlling Transactions \label{sqlite3-Controlling-Transactions}}

-By default, the \module{sqlite3} module opens transactions implicitly before a DML
-statement (INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly
-before a non-DML, non-DQL statement (i. e. anything other than
+By default, the \module{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).

-So if you are within a transaction, and issue a command like \code{CREATE TABLE
+So if you are within a transaction and issue a command like \code{CREATE TABLE
...}, \code{VACUUM}, \code{PRAGMA}, the \module{sqlite3} module will commit implicitly
before executing that command. There are two reasons for doing that. The first
is that some of these commands don't work within transactions. The other reason
@@ -618,17 +621,17 @@

Using the nonstandard \method{execute}, \method{executemany} and
\method{executescript} methods of the \class{Connection} object, your code can
-be written more concisely, because you don't have to create the - often
-superfluous \class{Cursor} objects explicitly. Instead, the \class{Cursor}
+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 for example execute a SELECT statement and iterate
+objects. This way, you can execute a SELECT statement and iterate
over it directly using only a single call on the \class{Connection} object.

\verbatiminput{sqlite3/shortcut_methods.py}

\subsubsection{Accessing columns by name instead of by index}

-One cool feature of the \module{sqlite3} module is the builtin \class{sqlite3.Row} class
+One useful feature of the \module{sqlite3} module is the builtin \class{sqlite3.Row} class
designed to be used as a row factory.

Rows wrapped with this class can be accessed both by index (like tuples) and