path: root/Doc/lib/libsqlite3.tex

\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.}



The module defines the following:

\begin{datadesc}{PARSE_DECLTYPES}
This constant is meant to be used with the detect_types parameter of the connect function.

Setting it makes the 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
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".
\end{datadesc}

\begin{funcdesc}{connect}{database\optional{, timeout, isolation_level, detect_types, check_same_thread, 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 TODO: link property of
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.

\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
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.

Consult the section `4. SQLite and Python types`_ of this manual for details.

The 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 **detect_types** of the
**connect** method 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}


\subsection{Connection Objects \label{sqlite3-Connection-Objects}}

A \class{Connection} instance has the following attributes and methods:

\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.
\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 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
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
parameters given.
\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 pysqlite2.dbapi2.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``.

  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``.

  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{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: 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 Cursors of the \module{sqlite3} module implement 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, pysqlite sums up the number of
  modifications 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}