13.13.1 Module functions and constants

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 the converters dictionary and use the converter function registered for that type there. Converter names are case-sensitive!

PARSE_COLNAMES

This constant is meant to be used with the detect_types parameter of the 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 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 we will parse out everything until the first blank for the column name: the column name would simply be "x".

connect( database[, timeout, isolation_level, detect_types, factory])

Opens a connection to the SQLite database file database. You can use ":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 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 isolation_level parameter, please see the isolation_level property of Connection objects in section 13.13.2.

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 detect_types parameter and the using custom converters registered with the module-level register_converter function allow you to easily do that.

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 factory parameter.

Consult the section 13.13.4 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 cached_statements parameter. The currently implemented default is to cache 100 statements.

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 typename. Confer the parameter detect_types of the connect function for how the type detection works. Note that the case of typename and the name of the type in your query must match!

register_adapter( type, callable)

Registers a callable to convert the custom Python type type into one of SQLite's supported types. The callable 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.

complete_statement( sql)

Returns True if the string 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:

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."

while True:
    line = raw_input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print cur.fetchall()
        except sqlite3.Error, e:
            print "An error occurred:", e.args[0]
        buffer = ""

con.close()

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 flag as True. Afterwards, you will get tracebacks from callbacks on sys.stderr. Use False to disable the feature again.

See About this document... for information on suggesting changes.