[Python-checkins] r45987 - python/trunk/Doc/lib/libsqlite3.tex
gerhard.haering
python-checkins at python.org
Sat May 13 01:49:50 CEST 2006
Author: gerhard.haering
Date: Sat May 13 01:49:49 2006
New Revision: 45987
Modified:
python/trunk/Doc/lib/libsqlite3.tex
Log:
Integrated the rest of the pysqlite reference manual into the Python
documentation. Ready to be reviewed and improved upon.
Modified: python/trunk/Doc/lib/libsqlite3.tex
==============================================================================
--- python/trunk/Doc/lib/libsqlite3.tex (original)
+++ python/trunk/Doc/lib/libsqlite3.tex Sat May 13 01:49:49 2006
@@ -3,34 +3,38 @@
\declaremodule{builtin}{sqlite3}
\modulesynopsis{A DB-API 2.0 implementation using SQLite 3.x.}
+\sectionauthor{Gerhard Häring}{gh at ghaering.de}
+\versionadded{2.5}
-
-
-The module defines the following:
+\subsection{Module functions and constants\label{sqlite3-Module-Contents}}
\begin{datadesc}{PARSE_DECLTYPES}
-This constant is meant to be used with the detect_types parameter of the connect function.
+This constant is meant to be used with the \var{detect_types} parameter of the
+\function{connect} function.
-Setting it makes the sqlite3 module parse the declared type for each column it
+Setting it makes the \module{sqlite3} module parse the declared type for each column it
returns. It will parse out the first word of the declared type, i. e. for
"integer primary key", it will parse out "integer". Then for that column, it
-will look into pysqlite's converters dictionary and use the converter function
+will look into the converters dictionary and use the converter function
registered for that type there. Converter names are case-sensitive!
\end{datadesc}
\begin{datadesc}{PARSE_COLNAMES}
-Setting this makes pysqlite parse the column name for each column it 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 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 pysqlite will parse out everything until the first blank for
-the column name: the column name would simply be "x".
+This constant is meant to be used with the \var{detect_types} parameter of the
+\function{connect} function.
+
+Setting this makes the SQLite interface parse the column name for each column
+it 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 \member{cursor.description} is only
+the first word of the column name, i. e. if you use something like
+\code{'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".
\end{datadesc}
-\begin{funcdesc}{connect}{database\optional{, timeout, isolation_level, detect_types, check_same_thread, factory}}
+\begin{funcdesc}{connect}{database\optional{, timeout, isolation_level, detect_types, factory}}
Opens a connection to the SQLite database file \var{database}. You can use
\code{":memory:"} to open a database connection to a database that resides in
RAM instead of on disk.
@@ -41,25 +45,26 @@
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 TODO: link property of
-Connection objects.
+For the \var{isolation_level} parameter, please see \member{isolation_level}
+\ref{sqlite3-Connection-IsolationLevel} property of \class{Connection} objects.
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.
-The \var{detect_types} parameter and the using custom *converters* registered with
-the module-level *register_converter* function allow you to easily do that.
+The \var{detect_types} parameter and the using custom \strong{converters} registered with
+the module-level \function{register_converter} function allow you to easily do that.
\var{detect_types} defaults to 0 (i. e. off, no type detection), you can set it
-to any combination of *PARSE_DECLTYPES* and *PARSE_COLNAMES* to turn type
+to any combination of \constant{PARSE_DECLTYPES} and \constant{PARSE_COLNAMES} to turn type
detection on.
-By default, the sqlite3 module uses its Connection class for the connect call.
-You can, however, subclass the Connection class and make .connect() use your
-class instead by providing your class for the \var{factory} parameter.
+By default, the \module{sqlite3} module uses its \class{Connection} class for the
+connect call. You can, however, subclass the \class{Connection} class and make
+\function{connect} use your class instead by providing your class for the
+\var{factory} parameter.
-Consult the section `4. SQLite and Python types`_ of this manual for details.
+Consult the section \ref{sqlite3-Types} of this manual for details.
-The sqlite3 module internally uses a statement cache to avoid SQL parsing
+The \module{sqlite3} module internally uses a statement cache to avoid SQL parsing
overhead. If you want to explicitly set the number of statements that are
cached for the connection, you can set the \var{cached_statements} parameter.
The currently implemented default is to cache 100 statements.
@@ -68,8 +73,8 @@
\begin{funcdesc}{register_converter}{typename, callable}
Registers a callable to convert a bytestring from the database into a custom
Python type. The callable will be invoked for all database values that are of
-the type \var{typename}. Confer the parameter **detect_types** of the
-**connect** method for how the type detection works. Note that the case of
+the type \var{typename}. Confer the parameter \var{detect_types} of the
+\function{connect} function for how the type detection works. Note that the case of
\var{typename} and the name of the type in your query must match!
\end{funcdesc}
@@ -80,15 +85,26 @@
int, long, float, str (UTF-8 encoded), unicode or buffer.
\end{funcdesc}
+\begin{funcdesc}{complete_statement}{sql}
+Returns \constant{True} if the string \var{sql} one or more complete SQL
+statements terminated by semicolons. It does not verify if the SQL is
+syntactically correct, only if there are no unclosed string literals and if the
+statement is terminated by a semicolon.
+
+This can be used to build a shell for SQLite, like in the following example:
+
+ \verbatiminput{sqlite3/complete_statement.py}
+\end{funcdesc}
\subsection{Connection Objects \label{sqlite3-Connection-Objects}}
A \class{Connection} instance has the following attributes and methods:
+\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 `5. Controlling
- Transactions`_ for a more detailed explanation.
+ 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.
\end{memberdesc}
\begin{methoddesc}{cursor}{\optional{cursorClass}}
@@ -98,22 +114,77 @@
\begin{methoddesc}{execute}{sql, \optional{parameters}}
This is a nonstandard shortcut that creates an intermediate cursor object by
-calling the cursor method, then calls the cursor's execute method with the
+calling the cursor method, then calls the cursor's \method{execute} method with the
parameters given.
\end{methoddesc}
\begin{methoddesc}{executemany}{sql, \optional{parameters}}
This is a nonstandard shortcut that creates an intermediate cursor object by
-calling the cursor method, then calls the cursor's executemany method with the
+calling the cursor method, then calls the cursor's \method{executemany} method with the
parameters given.
\end{methoddesc}
\begin{methoddesc}{executescript}{sql_script}
This is a nonstandard shortcut that creates an intermediate cursor object by
-calling the cursor method, then calls the cursor's executescript method with the
+calling the cursor method, then calls the cursor's \method{executescript} method with the
parameters given.
\end{methoddesc}
+\begin{methoddesc}{create_function}{name, num_params, func}
+
+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.
+
+The function can return any of the types supported by SQLite: unicode, str,
+int, long, float, buffer and None. Exceptions in the function are ignored and
+they are handled as if the function returned None.
+
+Example:
+
+ \verbatiminput{sqlite3/md5func.py}
+\end{methoddesc}
+
+\begin{methoddesc}{create_aggregate}{name, num_params, aggregate_class}
+
+Creates a user-defined aggregate function.
+
+The aggregate class must implement a \code{step} method, which accepts the
+number of parameters \var{num_params}, and a \code{finalize} method which
+will return the final result of the aggregate.
+
+The \code{finalize} method can return any of the types supported by SQLite:
+unicode, str, int, long, float, buffer and None. Any exceptions are ignored.
+
+Example:
+
+ \verbatiminput{sqlite3/mysumaggr.py}
+\end{methoddesc}
+
+\begin{methoddesc}{create_collation}{name, callable}
+
+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
+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.
+
+Note that the callable will get its parameters as Python bytestrings, which
+will normally be encoded in UTF-8.
+
+The following example shows a custom collation that sorts "the wrong way":
+
+ \verbatiminput{sqlite3/collation_reverse.py}
+
+To remove a collation, call \code{create_collation} with None as callable:
+
+\begin{verbatim}
+ con.create_collation("reverse", None)
+\end{verbatim}
+\end{methoddesc}
+
+
\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
@@ -126,21 +197,21 @@
If the standard tuple types don't suffice for you, and you want name-based
access to columns, you should consider setting \member{row_factory} to the
- highly-optimized pysqlite2.dbapi2.Row type. It provides both
+ highly-optimized sqlite3.Row type. It provides both
index-based and case-insensitive name-based access to columns with almost
no memory overhead. Much better than your own custom dictionary-based
approach or even a db_row based solution.
\end{memberdesc}
\begin{memberdesc}{text_factory}
- Using this attribute you can control what objects pysqlite returns for the
- TEXT data type. By default, this attribute is set to ``unicode`` and
- pysqlite will return Unicode objects for TEXT. If you want to return
- bytestrings instead, you can set it to ``str``.
+ Using this attribute you can control what objects are returned for the
+ TEXT data type. By default, this attribute is set to \class{unicode} and
+ the \module{sqlite3} module will return Unicode objects for TEXT. If you want to return
+ bytestrings instead, you can set it to \class{str}.
For efficiency reasons, there's also a way to return Unicode objects only
for non-ASCII data, and bytestrings otherwise. To activate it, set this
- attribute to ``pysqlite2.dbapi2.OptimizedUnicode``.
+ 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.
@@ -159,14 +230,14 @@
-\subsection{Cursor Objects \label{Cursor-Objects}}
+\subsection{Cursor Objects \label{sqlite3-Cursor-Objects}}
A \class{Cursor} instance has the following attributes and methods:
\begin{methoddesc}{execute}{sql, \optional{parameters}}
Executes a SQL statement. The SQL statement may be parametrized (i. e.
-placeholders instead of SQL literals). The sqlite3 module supports two kinds of
+placeholders instead of SQL literals). The \module{sqlite3} module supports two kinds of
placeholders: question marks (qmark style) and named placeholders (named
style).
@@ -211,7 +282,7 @@
\end{methoddesc}
\begin{memberdesc}{rowcount}
- Although the Cursors of the \module{sqlite3} module implement this
+ Although the \class{Cursor} class of the \module{sqlite3} module implements this
attribute, the database engine's own support for the determination of "rows
affected"/"rows selected" is quirky.
@@ -221,11 +292,212 @@
For \code{DELETE} statements, SQLite reports \member{rowcount} as 0 if you make a
\code{DELETE FROM table} without any condition.
- For \method{executemany} statements, pysqlite sums up the number of
- modifications into \member{rowcount}.
+ For \method{executemany} statements, the number of modifications are summed
+ up into \member{rowcount}.
As required by the Python DB API Spec, the \member{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".
\end{memberdesc}
+\subsection{SQLite and Python types\label{sqlite3-Types}}
+
+\subsubsection{Introduction}
+
+SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB.
+
+The following Python types can thus be sent to SQLite without any problem:
+
+\begin{tableii} {c|l}{code}{Python type}{SQLite type}
+\lineii{None}{NULL}
+\lineii{int}{INTEGER}
+\lineii{long}{INTEGER}
+\lineii{float}{REAL}
+\lineii{str (UTF8-encoded)}{TEXT}
+\lineii{unicode}{TEXT}
+\lineii{buffer}{BLOB}
+\end{tableii}
+
+This is how SQLite types are converted to Python types by default:
+
+\begin{tableii} {c|l}{code}{SQLite type}{Python type}
+\lineii{NULL}{None}
+\lineii{INTEGER}{int or long, depending on size}
+\lineii{REAL}{float}
+\lineii{TEXT}{depends on text_factory, unicode by default}
+\lineii{BLOB}{buffer}
+\end{tableii}
+
+The type system of the \module{sqlite3} module is extensible in both 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.
+
+\subsubsection{Using adapters to store additional Python types in SQLite databases}
+
+Like 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,
+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}.
+
+There are two ways to enable the \module{sqlite3} module to adapt a custom Python type
+to one of the supported ones.
+
+\paragraph{Letting your object adapt itself}
+
+This is a good approach if you write the class yourself. Let's suppose you have
+a class like this:
+
+\begin{verbatim}
+class Point(object):
+ def __init__(self, x, y):
+ 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.
+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
+\class{PrepareProtocol}.
+
+\verbatiminput{sqlite3/adapter_point_1.py}
+
+\paragraph{Registering an adapter callable}
+
+The other possibility is to create a function that converts the type to the
+string representation and register the function with \method{register_adapter}.
+
+ \verbatiminput{sqlite3/adapter_point_2.py}
+
+\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 builtin
+\class{datetime.date} and \class{datetime.datetime} types. Now let's suppose we
+want to store \class{datetime.datetime} objects not in ISO representation, but
+as Unix timestamp.
+
+ \verbatiminput{sqlite3/adapter_datetime.py}
+
+\subsubsection{Converting SQLite values to custom Python types}
+
+Now that's all nice and dandy that you can send custom Python types to SQLite.
+But to make it really useful we need to make the Python to SQLite to Python
+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 first define a converter function that accepts the string as a parameter and constructs a Point object from it.
+
+\begin{notice}
+Converter functions \strong{always} get called with a string, no matter
+under which data type you sent the value to SQLite.
+\end{notice}
+
+\begin{notice}
+Converter names are looked up in a case-sensitive manner.
+\end{notice}
+
+
+\begin{verbatim}
+ def convert_point(s):
+ x, y = map(float, s.split(";"))
+ return Point(x, y)
+\end{verbatim}
+
+Now you need to make the \module{sqlite3} module know that what you select from the
+database is actually a point. There are two ways of doing this:
+
+\begin{itemize}
+ \item Implicitly via the declared type
+ \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}.
+
+
+The following example illustrates both ways.
+
+ \verbatiminput{sqlite3/converter_point.py}
+
+\subsubsection{Default adapters and converters}
+
+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.
+
+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
+experimental SQLite date/time functions.
+
+The following example demonstrates this.
+
+ \verbatiminput{sqlite3/pysqlite_datetime.py}
+
+\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
+SELECT/INSERT/UPDATE/DELETE/REPLACE).
+
+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
+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
+(or none at all) via the \var{isolation_level} parameter to the
+\function{connect} call, or via the \member{isolation_level} property of
+connections.
+
+If you want \strong{autocommit mode}, then set \member{isolation_level} to None.
+
+Otherwise leave it at it's default, which will result in a plain "BEGIN"
+statement, or set it to one of SQLite's supported isolation levels: DEFERRED,
+IMMEDIATE or EXCLUSIVE.
+
+As the \module{sqlite3} module needs to keep track of the transaction state, you should
+not use \code{OR ROLLBACK} or \code{ON CONFLICT ROLLBACK} in your SQL. Instead,
+catch the \exception{IntegrityError} and call the \method{rollback} method of
+the connection yourself.
+
+\subsection{Using pysqlite efficiently}
+
+\subsubsection{Using shortcut methods}
+
+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}
+objects are created implicitly and these shortcut methods return the cursor
+objects. This way, you can for example 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
+designed to be used as a row factory.
+
+Rows wrapped with this class can be accessed both by index (like tuples) and
+case-insensitively by name:
+
+ \verbatiminput{sqlite3/rowclass.py}
+
+
More information about the Python-checkins
mailing list