# DBAPI notes

DBAPI is defined in PEP 249. This section describes how APSW complies or differs from it.

## Module Interface

There is no connect method. Use the `Connection` constructor instead.

The Connection object and any cursors can be used in any thread. You cannot use the cursor concurrently in multiple threads for example calling `Cursor.execute()` at the same time. If you attempt to do so then an `exception` will be raised. The Python Global Interpreter Lock (GIL) is released during all SQLite API calls allowing for maximum concurrency.

Three different paramstyles are supported. Note that SQLite starts parameter numbers from one not zero when using qmark/numeric style.

style | use
--- | ---
qmark | `... WHERE name=?`
numeric | `... WHERE name=?4`
named | `... WHERE name=:name` or `... WHERE name=$name` or `... WHERE name=@name`

The DBAPI exceptions are not used. The exceptions used correspond to specific SQLite error codes.

## Connection Objects

There are no commit or rollback methods. You can issue queries as BEGIN, COMMIT, and ROLLBACK to do transactions manually. You should use `with Connection` to get automatic transaction control, which includes nested transactions.

Several methods that are defined in DBAPI to be on the cursor are instead on the Connection object, since this is where SQLite actually stores the information. Doing operations in any other cursor attached to the same Connection object does update their values, and this makes you aware of that.

## Cursor Objects

Use `Cursor.get_description()` instead of description. This information is only obtained on request.

There is no rowcount. Row counts don’t make sense in SQLite any way. SQLite returns results one row at a time, not calculating the next result row until you ask for it. Consequently getting a rowcount would have to calculate all the result rows and would not reduce the amount of effort needed.

callproc is not implemented as SQLite doesn’t support stored procedures.

`execute()` returns the Cursor object and you can use it as an iterator to get the results (if any).

`executemany()` returns the Cursor object and you can use it as an iterator to get the results (if any).

fetchone is not available. Use the cursor as an iterator, or call Python’s `next function` to get the next row.

fetchmany is not available. Use `fetchall()` to get all remaining results.

nextset is not applicable or implemented.

arraysize is not available as fetchmany isn’t.

Neither setinputsizes or setoutputsize are applicable or implemented.

## Type objects

None of the date or time methods are available since SQLite 3 does not have a native date or time type. There are functions for manipulating dates and time which are stored as strings or Julian days (floating point number).

Use any `Buffer` type (eg bytes) for blobs.

Various interesting and useful bits of functionality provides functionality to convert and adapt types to those supported by SQLite.

## Optional DB API Extensions

rownumber is not available.

Exception classes are not available as attributes of Connection but instead are on the `apsw` module. See Exceptions and Errors for more details.

Use `Cursor.connection` to get the associated Connection object from a cursor.

scroll and messages are not available.

The Cursor object supports the iterator protocol and this is the only way of getting information back.

To get the last inserted row id, call `Connection.last_insert_rowid()`. That stores the id from the last insert on any Cursor associated with the the Connection. You can also add select last_insert_rowid() to the end of your execute statements:

    for row in db.execute("""BEGIN;
                                 INSERT ... ;
                                 INSERT ... ;
                                 SELECT last_insert_rowid();
                             COMMIT"""):
       lastrowid=row[0]

There is no errorhandler attribute.