Cursors (executing SQL)

A cursor encapsulates a SQL query and returning results. You only need an explicit cursor if you want more information or control over execution. Using Connection.execute() or Connection.executemany() will automatically obtain a cursor behind the scenes.

If you need a cursor you should call cursor() on your database:

db = apsw.Connection("databasefilename")
cursor = db.cursor()

The example shows how to execute SQL and how to provide values used in queries (bindings).

Cursors are cheap. Use as many as you need. Behind the scenes a Cursor maps to a SQLite statement. APSW maintains a cache so that the mapping is very fast, and the SQLite objects are reused when possible.

Note

Cursors on the same Connection are not isolated from each other. Anything done on one cursor is immediately visible to all other Cursors on the same connection. This still applies if you start transactions. Connections are isolated from each other with cursors on other connections not seeing changes until they are committed.

See also

Cursor class

class apsw.Cursor(connection: Connection)

Use Connection.cursor() to make a new cursor.

Cursor.__iter__(self: Cursor) → Cursor

Cursors are iterators

Cursor.__next__(self: Cursor) → Any

Cursors are iterators

Cursor.bindings_count: int

How many bindings are in the statement. The ? form results in the largest number. For example you could do SELECT ?123` in which case the count will be 123.

Calls: sqlite3_bind_parameter_count

Cursor.bindings_names: tuple[str | None]

A tuple of the name of each bind parameter, or None for no name. The leading marker (?:@$) is omitted

Note

SQLite parameter numbering starts at 1, while Python indexing starts at 0.

Calls: sqlite3_bind_parameter_name

Cursor.close(force: bool = False) → None

It is very unlikely you will need to call this method. Cursors are automatically garbage collected and when there are none left will allow the connection to be garbage collected if it has no other references.

A cursor is open if there are remaining statements to execute (if your query included multiple statements), or if you called executemany() and not all of the sequence of bindings have been used yet.

Parameters:

force – If False then you will get exceptions if there is remaining work to do be in the Cursor such as more statements to execute, more data from the executemany binding sequence etc. If force is True then all remaining work and state information will be silently discarded.

Cursor.connection: Connection

Connection this cursor is using

Cursor.description: tuple[tuple[str, str, None, None, None, None, None], ...]

Based on the DB-API cursor property, this returns the same as get_description() but with 5 Nones appended because SQLite does not have the information.

Cursor.description_full: tuple[tuple[str, str, str, str, str], ...]

Only present if SQLITE_ENABLE_COLUMN_METADATA was defined at compile time.

Returns all information about the query result columns. In addition to the name and declared type, you also get the database name, table name, and origin name.

Calls:
Cursor.exec_trace: ExecTracer | None

Called with the cursor, statement and bindings for each execute() or executemany() on this cursor.

If callable is None then any existing execution tracer is unregistered.

See also

Cursor.execute(statements: str, bindings: Bindings | None = None, *, can_cache: bool = True, prepare_flags: int = 0, explain: int = -1) → Cursor

Executes the statements using the supplied bindings. Execution returns when the first row is available or all statements have completed.

Parameters:

  • statements – One or more SQL statements such as select * from books or begin; insert into books ...; select last_insert_rowid(); end.

  • bindings – If supplied should either be a sequence or a dictionary. Each item must be one of the supported types

  • can_cache – If False then the statement cache will not be used to find an already prepared query, nor will it be placed in the cache after execution

  • prepare_flags – flags passed to sqlite_prepare_v3

  • explain – If 0 or greater then the statement is passed to sqlite3_stmt_explain where you can force it to not be an explain, or force explain or explain query plan.

Raises:

  • TypeError – The bindings supplied were neither a dict nor a sequence

  • BindingsError – You supplied too many or too few bindings for the statements

  • IncompleteExecutionError – There are remaining unexecuted queries from your last execute

See also

Calls:
Cursor.executemany(statements: str, sequenceofbindings: Iterable[Bindings], *, can_cache: bool = True, prepare_flags: int = 0, explain: int = -1) → Cursor

This method is for when you want to execute the same statements over a sequence of bindings. Conceptually it does this:

for binding in sequenceofbindings:
    cursor.execute(statements, binding)

The return is the cursor itself which acts as an iterator. Your statements can return data. See execute() for more information, and the example.

Cursor.expanded_sql: str

The SQL text with bound parameters expanded. For example:

execute("select ?, ?", (3, "three"))

would return:

select 3, 'three'

Note that while SQLite supports nulls in strings, their implementation of sqlite3_expanded_sql stops at the first null.

You will get MemoryError if SQLite ran out of memory, or if the expanded string would exceed SQLITE_LIMIT_LENGTH.

Calls: sqlite3_expanded_sql

Cursor.fetchall() → list[tuple[SQLiteValue, ...]]

Returns all remaining result rows as a list. This method is defined in DBAPI. See get() which does the same thing, but with the least amount of structure to unpack.

Cursor.fetchone() → Any | None

Returns the next row of data or None if there are no more rows.

Cursor.get: Any

Like fetchall() but returns the data with the least amount of structure possible.

Some examples

Query

Result

select 3

3

select 3,4

(3, 4)

select 3; select 4

[3, 4]

select 3,4; select 4,5

[(3, 4), (4, 5)]

select 3,4; select 5

[(3, 4), 5]

Row tracers are not called when using this method.

Cursor.get_connection() → Connection

Returns the connection this cursor is part of

Cursor.get_description() → tuple[tuple[str, str], ...]

If you are trying to get information about a table or view, then pragma table_info is better. If you want to know up front what columns and other details a query does then apsw.ext.query_info() is useful.

Returns a tuple describing each column in the result row. The return is identical for every row of the results.

The information about each column is a tuple of (column_name, declared_column_type). The type is what was declared in the CREATE TABLE statement - the value returned in the row will be whatever type you put in for that row and column.

See the query_info example.

Calls:
Cursor.get_exec_trace() → ExecTracer | None

Returns the currently installed execution tracer

See also

Cursor.get_row_trace() → RowTracer | None

Returns the currently installed (via set_row_trace()) row tracer.

See also

Cursor.has_vdbe: bool

True if the SQL does anything. Comments have nothing to evaluate, and so are False.

Cursor.is_explain: int

Returns 0 if executing a normal query, 1 if it is an EXPLAIN query, and 2 if an EXPLAIN QUERY PLAN query.

Calls: sqlite3_stmt_isexplain

Cursor.is_readonly: bool

Returns True if the current query does not change the database.

Note that called functions, virtual tables etc could make changes though.

Calls: sqlite3_stmt_readonly

Cursor.row_trace: RowTracer | None

Called with cursor and row being returned. You can change the data that is returned or cause the row to be skipped altogether.

If callable is None then any existing row tracer is unregistered.

See also

Cursor.set_exec_trace(callable: ExecTracer | None) → None

Sets the execution tracer

Cursor.set_row_trace(callable: RowTracer | None) → None

Sets the row tracer