1.1 Incompatibilities

• No type information in cursor.description

  cursor.description has a tuple with the fields (name, type_code, display_size, internal_size, precision, scale, null_ok) for each column that a query returns. The DB-API spec requires that at least name and type_code are filled, but at the time cursor.description is built, pysqlite cannot determine any types, yet. So, the only field of cursor.description that pysqlite fills is name. All other fields are set to None.

• No type objects

  Consequently, there are also no type objects STRING, BINARY, NUMBER, DATETIME, ROWID at module-level. They would be useless.

1.2 Unsupported Optional Features

• Cursor class

  • nextset method

    This method is not implemented because the database engine does not support opening multiple result sets simultaneously with a single cursor.

1.3 Nominally Supported Optional Features

• Cursor class

  • arraysize attribute

    As required by the spec, the value of this attribute is observed with respect to the fetchmany method. However, changing the value of this attribute does not make any difference in fetch efficiency because the database engine only supports fetching a single row at a time.

  • setinputsizes method

    Although this method is present, it does nothing, as allowed by the spec.

  • setoutputsize method

    Although this method is present, it does nothing, as allowed by the spec.

1.4 Extensions and Caveats

pysqlite offers a large feature set beyond the minimal requirements of the Python DB API. Most of these extensions are documented in the section of this document entitled Native Database Engine Features and Extensions Beyond the Python DB API.

• connect function

  The parameter database refers to the database file for the SQLite database. It's a normal filesystem path and you can use absolute or relative path names.

  The connect function supports the following optional keyword arguments in addition to those required by the spec:

  • timeout - 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).

    Example:

    sqlite.connect(database="mydb", timeout=10.0)

  • isolation_level - pysqlite will by default open transactions with a "BEGIN" statement, when it encounters a DML statement like INSERT/UPDATE/DELETE/REPLACE. Some users don't want pysqlite to implicitly open transactions for them - they want an autocommit mode. Other users want pysqlite to open different kinds of transactions, like with "BEGIN IMMEDIATE". See 5. Controlling Transactions for a more detailed explanation.

    Note that you can also switch to a different isolation level by setting the isolation_level property of connections.

    Example:

    # Turn on autocommit mode
    con = sqlite.connect("mydb", isolation_level=None)
    
    # Set isolation_level to "IMMEDIATE"
    con.isolation_level = "IMMEDIATE"

  • detect_types - 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 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.

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

    • sqlite.PARSE_DECLTYPES - This makes pysqlite 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.

    • sqlite.PARSE_COLNAMES - 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".

      The following example uses the column name timestamp, which is already registered by default in the converters dictionary with an appropriate converter!

      Example:

      from pysqlite2 import dbapi2 as sqlite
      import datetime
      
      con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
      cur = con.cursor()
      cur.execute('select ? as "x [timestamp]"', (datetime.datetime.now(),))
      dt = cur.fetchone()[0]
      print dt, type(dt)

  • check_same_thread - SQLite connections/cursors can only safely be used in the same thread they were created in. pysqlite checks for this each time it would do a call to the SQLite engine. If you are confident that you are ensuring safety otherwise, you can disable that checks by setting check_same_thread to False.

  • factory - By default, pysqlite uses the 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.

    Example:

    from pysqlite2 import dbapi2 as sqlite
    
    class CountCursorsConnection(sqlite.Connection):
        def __init__(self, *args, **kwargs):
            sqlite.Connection.__init__(self, *args, **kwargs)
            self.numcursors = 0
    
        def cursor(self, *args, **kwargs):
            self.numcursors += 1
            return sqlite.Connection.cursor(self, *args, **kwargs)
    
    con = sqlite.connect(":memory:", factory=CountCursorsConnection)
    cur1 = con.cursor()
    cur2 = con.cursor()
    print con.numcursors

  • cached_statements - pysqlite 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 this parameter. The currently implemented default is to cache 100 statements.

  
  

  
  

• register_converter function - register_converter(typename, callable) registers a callable to convert a bytestring from the database into a custom Python type. The converter will be invoked for all database values that are of the type typename. Confer the parameter detect_types of the connect method for how the type detection works.

• register_adapter function - register_adapter(type, callable) registers a callable to convert the custom Python type into one of SQLite's supported types. The 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.

• enable_callback_tracebacks function - enable_callback_tracebacks(flag) Can be used to enable displaying tracebacks of exceptions in user-defined functions, aggregates and other callbacks being printed to stderr. methods should never raise any exception. This feature is off by default.

• Connection class

  • isolation_level attribute (read-write)

    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.

  • cursor method - The cursor method accepts a single optional parameter: a custom cursor class extending pysqlite's Cursor class that you can adapt to your needs. Note that it is required that your custom cursor class extends pysqlite's Cursor class.

  • execute method - Nonstandard - this works as a shortcut for not having to create a cursor object and is implemented like this:

    class Connection:
      def execute(self, *args):
        cur = self.cursor()
        cur.execute(*args)
        return cur

  • executemany method - Nonstandard - The same shortcut as the nonstandard execute method.

  • executesript method - Nonstandard - The same shortcut as the nonstandard execute method.

  • row_factory attribute (read-write)

    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:

    from pysqlite2 import dbapi2 as sqlite
    
    def dict_factory(cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d
    
    con = sqlite.connect(":memory:")
    con.row_factory = dict_factory
    cur = con.cursor()
    cur.execute("select 1 as a")
    print cur.fetchone()["a"]

    If the standard tuple types don't suffice for you, and you want name-based access to columns, you should consider setting 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.

  • text_factory attribute (read-write)

    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:

    from pysqlite2 import dbapi2 as sqlite
    
    con = sqlite.connect(":memory:")
    cur = con.cursor()
    
    # Create the table
    con.execute("create table person(lastname, firstname)")
    
    AUSTRIA = u"\xd6sterreich"
    
    # by default, rows are returned as Unicode
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert row[0] == AUSTRIA
    
    # but we can make pysqlite always return bytestrings ...
    con.text_factory = str
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert type(row[0]) == str
    # the bytestrings will be encoded in UTF-8, unless you stored garbage in the
    # database ...
    assert row[0] == AUSTRIA.encode("utf-8")
    
    # we can also implement a custom text_factory ...
    # here we implement one that will ignore Unicode characters that cannot be
    # decoded from UTF-8
    con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
    cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))
    row = cur.fetchone()
    assert type(row[0]) == unicode
    
    # pysqlite offers a builtin optimized text_factory that will return bytestring
    # objects, if the data is in ASCII only, and otherwise return unicode objects
    con.text_factory = sqlite.OptimizedUnicode
    cur.execute("select ?", (AUSTRIA,))
    row = cur.fetchone()
    assert type(row[0]) == unicode
    
    cur.execute("select ?", ("Germany",))
    row = cur.fetchone()
    assert type(row[0]) == str
    

  • total_changes attribute (read-only)

    Returns the total number of database rows that have be modified, inserted, or deleted since the database connection was opened.

  
  

• Cursor class

  • execute method

    pysqlite uses paramstyle = "qmark". That means if you use parametrized statements, you use the question mark as placeholder.

    This is a basic example showing the use of question marks as placeholders and a parameter tuple:

    from pysqlite2 import dbapi2 as sqlite
    
    con = sqlite.connect("mydb")
    
    cur = con.cursor()
    
    who = "Yeltsin"
    age = 72
    
    cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))
    print cur.fetchone()

    pysqlite also supports paramstyle = "named". That means you can use named placeholders in the format ":name", i. e. a colon followed by the parameter name. As parameters, you then supply a mapping instead of a sequence. In the simplest case, a dictionary instead of a tuple.

    from pysqlite2 import dbapi2 as sqlite
    
    con = sqlite.connect("mydb")
    
    cur = con.cursor()
    
    who = "Yeltsin"
    age = 72
    
    cur.execute("select name_last, age from people where name_last=:who and age=:age",
        {"who":