# 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

- SQLite transactions

- Atomic commit

- Benchmarking

## 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:

  - sqlite3_column_name

  - sqlite3_column_decltype

  - sqlite3_column_database_name

  - sqlite3_column_table_name

  - sqlite3_column_origin_name

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

  - Tracing

  - Execution Tracer

  - `Connection.exec_trace`

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

  - Example showing how to use bindings

  - Execution model

  Calls:

    - sqlite3_prepare_v3

    - sqlite3_step

    - sqlite3_bind_int64

    - sqlite3_bind_null

    - sqlite3_bind_text64

    - sqlite3_bind_double

    - sqlite3_bind_blob64

    - sqlite3_bind_zeroblob

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:

    - sqlite3_column_name

    - sqlite3_column_decltype

Cursor.get_exec_trace() → ExecTracer | None

  Returns the currently installed `execution tracer`

  See also

  - Tracing

Cursor.get_row_trace() → RowTracer | None

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

  See also

  - Tracing

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

  - Tracing

  - Row Tracer

  - `Connection.row_trace`

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

  Sets the `execution tracer`

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

  Sets the `row tracer`