diff options
Diffstat (limited to 'Doc/lib/libsqlite3.tex')
| -rw-r--r-- | Doc/lib/libsqlite3.tex | 648 |
1 files changed, 0 insertions, 648 deletions
diff --git a/Doc/lib/libsqlite3.tex b/Doc/lib/libsqlite3.tex deleted file mode 100644 index a7a0e94..0000000 --- a/Doc/lib/libsqlite3.tex +++ /dev/null @@ -1,648 +0,0 @@ -\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} - -SQLite is a C library that provides a lightweight disk-based database -that doesn't require a separate server process and allows accessing -the database using a nonstandard variant of the SQL query language. -Some applications can use SQLite for internal data storage. It's also -possible to prototype an application using SQLite and then port the -code to a larger database such as PostgreSQL or Oracle. - -pysqlite was written by Gerhard H\"aring and provides a SQL interface -compliant with the DB-API 2.0 specification described by -\pep{249}. - -To use the module, you must first create a \class{Connection} object -that represents the database. Here the data will be stored in the -\file{/tmp/example} file: - -\begin{verbatim} -conn = sqlite3.connect('/tmp/example') -\end{verbatim} - -You can also supply the special name \samp{:memory:} to create -a database in RAM. - -Once you have a \class{Connection}, you can create a \class{Cursor} -object and call its \method{execute()} method to perform SQL commands: - -\begin{verbatim} -c = conn.cursor() - -# Create table -c.execute('''create table stocks -(date text, trans text, symbol text, - qty real, price real)''') - -# Insert a row of data -c.execute("""insert into stocks - values ('2006-01-05','BUY','RHAT',100,35.14)""") - -# Save (commit) the changes -conn.commit() - -# We can also close the cursor if we are done with it -c.close() -\end{verbatim} - -Usually your SQL operations will need to use values from Python -variables. You shouldn't assemble your query using Python's string -operations because doing so is insecure; it makes your program -vulnerable to an SQL injection attack. - -Instead, use the DB-API's parameter substitution. Put \samp{?} 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 \method{execute()} -method. (Other database modules may use a different placeholder, -such as \samp{\%s} or \samp{:1}.) For example: - -\begin{verbatim} -# Never do this -- insecure! -symbol = 'IBM' -c.execute("... where symbol = '%s'" % symbol) - -# Do this instead -t = (symbol,) -c.execute('select * from stocks where symbol=?', t) - -# Larger example -for t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00), - ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), - ('2006-04-06', 'SELL', 'IBM', 500, 53.00), - ): - c.execute('insert into stocks values (?,?,?,?,?)', t) -\end{verbatim} - -To retrieve data after executing a SELECT statement, you can either -treat the cursor as an iterator, call the cursor's \method{fetchone()} -method to retrieve a single matching row, -or call \method{fetchall()} to get a list of the matching rows. - -This example uses the iterator form: - -\begin{verbatim} ->>> c = conn.cursor() ->>> c.execute('select * from stocks order by price') ->>> for row in c: -... print row -... -(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001) -(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0) -(u'2006-04-06', u'SELL', u'IBM', 500, 53.0) -(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0) ->>> -\end{verbatim} - -\begin{seealso} - -\seeurl{http://www.pysqlite.org} -{The pysqlite web page.} - -\seeurl{http://www.sqlite.org} -{The SQLite web page; the documentation describes the syntax and the -available data types for the supported SQL dialect.} - -\seepep{249}{Database API Specification 2.0}{PEP written by -Marc-Andr\'e Lemburg.} - -\end{seealso} - - -\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 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 you must 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} contains one or more complete SQL -statements terminated by semicolons. It does not verify that the SQL is -syntactically correct, only that there are no unclosed string literals and the -statement is terminated by a semicolon. - -This can be used to build a shell for SQLite, as in the following example: - - \verbatiminput{sqlite3/complete_statement.py} -\end{funcdesc} - -\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 -tracebacks from callbacks on \code{sys.stderr}. Use \constant{False} to disable -the feature again. -\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}[Connection]{isolation_level} - Get or set the current isolation level. None for autocommit mode or one of - "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See ``Controlling Transactions'', - section~\ref{sqlite3-Controlling-Transactions}, for a more detailed explanation. -\end{memberdesc} - -\begin{methoddesc}[Connection]{cursor}{\optional{cursorClass}} - The cursor method accepts a single optional parameter \var{cursorClass}. - If supplied, this must be a custom cursor class that extends - \class{sqlite3.Cursor}. -\end{methoddesc} - -\begin{methoddesc}[Connection]{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}[Connection]{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}[Connection]{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}[Connection]{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 the SQL function. - -The function can return any of the types supported by SQLite: unicode, str, -int, long, float, buffer and None. - -Example: - - \verbatiminput{sqlite3/md5func.py} -\end{methoddesc} - -\begin{methoddesc}[Connection]{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. - -Example: - - \verbatiminput{sqlite3/mysumaggr.py} -\end{methoddesc} - -\begin{methoddesc}[Connection]{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 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{methoddesc}[Connection]{interrupt}{} - -You can call this method from a different thread to abort any queries that -might be executing on the connection. The query will then abort and the caller -will get an exception. -\end{methoddesc} - -\begin{methoddesc}[Connection]{set_authorizer}{authorizer_callback} - -This routine registers a callback. The callback is invoked for each attempt to -access a column of a table in the database. The callback should return -\constant{SQLITE_OK} if access is allowed, \constant{SQLITE_DENY} if the entire -SQL statement should be aborted with an error and \constant{SQLITE_IGNORE} if -the column should be treated as a NULL value. These constants are available in -the \module{sqlite3} module. - -The first argument to the callback signifies what kind of operation is to be -authorized. The second and third argument will be arguments or \constant{None} -depending on the first argument. The 4th argument is the name of the database -("main", "temp", etc.) if applicable. The 5th argument is the name of the -inner-most trigger or view that is responsible for the access attempt or -\constant{None} if this access attempt is directly from input SQL code. - -Please consult the SQLite documentation about the possible values for the first -argument and the meaning of the second and third argument depending on the -first one. All necessary constants are available in the \module{sqlite3} -module. -\end{methoddesc} - -\begin{memberdesc}[Connection]{row_factory} - You can change this attribute to a callable that accepts the cursor and - 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 returning a tuple doesn't suffice and you want name-based - access to columns, you should consider setting \member{row_factory} to the - highly-optimized \class{sqlite3.Row} type. \class{Row} provides both - index-based and case-insensitive name-based access to columns with almost - 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}[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{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 resulting object. - - See the following example code for illustration: - - \verbatiminput{sqlite3/text_factory.py} -\end{memberdesc} - -\begin{memberdesc}[Connection]{total_changes} - Returns the total number of database rows that have been 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}[Cursor]{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 you want to execute multiple SQL statements with one - call. -\end{methoddesc} - - -\begin{methoddesc}[Cursor]{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 -using 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}[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 -gets as a parameter. - -\var{sql_script} can be a bytestring or a Unicode string. - -Example: - -\verbatiminput{sqlite3/executescript.py} -\end{methoddesc} - -\begin{memberdesc}[Cursor]{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 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. - -\subsubsection{Using adapters to store additional Python types in SQLite databases} - -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: one of NoneType, int, long, float, -str, unicode, buffer. - -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. - -\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. 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 -\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}. - -\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} - - \verbatiminput{sqlite3/adapter_point_2.py} - -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, -but as a \UNIX{} timestamp. - - \verbatiminput{sqlite3/adapter_datetime.py} - -\subsubsection{Converting SQLite values to custom Python types} - -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. - -Enter converters. - -Let's go back to the \class{Point} class. We stored the x and y -coordinates separated via semicolons as strings in SQLite. - -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 -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 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 approaches. - - \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 \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 -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 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 -...}, \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 its 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 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 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 -case-insensitively by name: - - \verbatiminput{sqlite3/rowclass.py} - - |