1 files changed, 503 insertions, 0 deletions
diff --git a/Doc/lib/libsqlite3.tex b/Doc/lib/libsqlite3.tex
new file mode 100644
index 0000000..8c80eb6
--- /dev/null
+++ b/Doc/lib/libsqlite3.tex
@@ -0,0 +1,503 @@
+\section{\module{sqlite3} ---
+         DB-API 2.0 interface for SQLite databases}
+
+\declaremodule{builtin}{sqlite3}
+\modulesynopsis{A DB-API 2.0 implementation using SQLite 3.x.}
+\sectionauthor{Gerhard Häring}{gh@ghaering.de}
+\versionadded{2.5}
+
+\subsection{Module functions and constants\label{sqlite3-Module-Contents}}
+
+\begin{datadesc}{PARSE_DECLTYPES}
+This constant is meant to be used with the \var{detect_types} parameter of the
+\function{connect} function.
+
+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 the converters dictionary and use the converter function
+registered for that type there.  Converter names are case-sensitive!
+\end{datadesc}
+
+
+\begin{datadesc}{PARSE_COLNAMES}
+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, 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.
+
+When a database is accessed by multiple connections, and one of the processes
+modifies the database, the SQLite database is locked until that transaction is
+committed. The \var{timeout} parameter specifies how long the connection should
+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.
+
+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 \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 \constant{PARSE_DECLTYPES} and \constant{PARSE_COLNAMES} to turn type
+detection on.
+
+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 \ref{sqlite3-Types} of this manual for details.
+
+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.
+\end{funcdesc}
+
+\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 \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}
+
+\begin{funcdesc}{register_adapter}{type, callable}
+Registers a callable to convert the custom Python type \var{type} into one of
+SQLite's supported types. The callable \var{callable} accepts as single
+parameter the Python value, and must return a value of the following types:
+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  Controlling Transactions
+  \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}.
+\end{methoddesc}
+
+\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 \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 \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 \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
+  way, you can implement more advanced ways of returning results, like
+  ones 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
+  access to columns, you should consider setting \member{row_factory} to the
+  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 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 \constant{sqlite3.OptimizedUnicode}.
+
+  You can also set it to any other callable that accepts a single bytestring
+  parameter and returns the result object.
+
+  See the following example code for illustration:
+
+  \verbatiminput{sqlite3/text_factory.py}
+\end{memberdesc}
+
+\begin{memberdesc}{total_changes}
+  Returns the total number of database rows that have be modified, inserted,
+  or deleted since the database connection was opened.
+\end{memberdesc}
+
+
+
+
+
+\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 \module{sqlite3} module supports two kinds of
+placeholders: question marks (qmark style) and named placeholders (named
+style).
+
+This example shows how to use parameters with qmark style:
+
+    \verbatiminput{sqlite3/execute_1.py}
+
+This example shows how to use the named style:
+
+    \verbatiminput{sqlite3/execute_2.py}
+
+    \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
+    call.
+\end{methoddesc}
+
+
+\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.
+
+\verbatiminput{sqlite3/executemany_1.py}
+
+Here's a shorter example using a generator:
+
+\verbatiminput{sqlite3/executemany_2.py}
+\end{methoddesc}
+
+\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
+gets as a parameter.
+
+\var{sql_script} can be a bytestring or a Unicode string.
+
+Example:
+
+\verbatiminput{sqlite3/executescript.py}
+\end{methoddesc}
+
+\begin{memberdesc}{rowcount}
+  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.
+
+  For \code{SELECT} statements, \member{rowcount} is always None because we cannot
+  determine the number of rows a query produced until all rows were fetched.
+
+  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, 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}
+
+