.. Automatically generated by code2rst.py
   Edit src/cursor.c not this file!

.. currentmodule:: apsw

.. _cursors:

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
:meth:`Connection.execute` or :meth:`Connection.executemany` will automatically
obtain a cursor behind the scenes.

If you need a cursor you should call :meth:`~Connection.cursor` on your
database::

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

The :ref:`example <example_executing_sql>` 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
:class:`Cursor` maps to a `SQLite statement <https://sqlite.org/c3ref/stmt.html>`_.
APSW maintains a :ref:`cache <statementcache>` so that the mapping is very fast, and the
SQLite objects are reused when possible.

.. note::

  Cursors on the same :ref:`Connection <connections>` 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.

.. seealso::

  * `SQLite transactions <https://sqlite.org/lang_transaction.html>`_
  * `Atomic commit <https://sqlite.org/atomiccommit.html>`_
  * :ref:`Benchmarking`

Cursor class
============

.. class:: Cursor(connection: Connection)

 Use :meth:`Connection.cursor` to make a new cursor.

.. method:: Cursor.__iter__(self: Cursor) -> Cursor

    Cursors are iterators

.. method:: Cursor.__next__(self: Cursor) -> Any

    Cursors are iterators

.. index:: sqlite3_bind_parameter_count

.. attribute:: Cursor.bindings_count
  :type: 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 <https://sqlite.org/c3ref/bind_parameter_count.html>`__

.. index:: sqlite3_bind_parameter_name

.. attribute:: Cursor.bindings_names
  :type: 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 <https://sqlite.org/c3ref/bind_parameter_name.html>`__

.. method:: 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
  :meth:`~Cursor.executemany` and not all of the sequence of bindings
  have been used yet.

  :param 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.

.. attribute:: Cursor.connection
  :type: Connection

  :class:`Connection` this cursor is using

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

    Based on the `DB-API cursor property
    <https://www.python.org/dev/peps/pep-0249/>`__, this returns the
    same as :meth:`get_description` but with 5 Nones appended because
    SQLite does not have the information.

.. index:: sqlite3_column_name, sqlite3_column_decltype, sqlite3_column_database_name, sqlite3_column_table_name, sqlite3_column_origin_name

.. attribute:: Cursor.description_full
  :type: 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 <https://sqlite.org/c3ref/column_name.html>`__
  * `sqlite3_column_decltype <https://sqlite.org/c3ref/column_decltype.html>`__
  * `sqlite3_column_database_name <https://sqlite.org/c3ref/column_database_name.html>`__
  * `sqlite3_column_table_name <https://sqlite.org/c3ref/column_database_name.html>`__
  * `sqlite3_column_origin_name <https://sqlite.org/c3ref/column_database_name.html>`__

.. attribute:: Cursor.exec_trace
  :type: Optional[ExecTracer]

  Called with the cursor, statement and bindings for
  each :meth:`~Cursor.execute` or :meth:`~Cursor.executemany` on this
  cursor.

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

  .. seealso::

    * :ref:`tracing`
    * :ref:`executiontracer`
    * :attr:`Connection.exec_trace`

.. index:: sqlite3_prepare_v3, sqlite3_step, sqlite3_bind_int64, sqlite3_bind_null, sqlite3_bind_text64, sqlite3_bind_double, sqlite3_bind_blob64, sqlite3_bind_zeroblob

.. method:: Cursor.execute(statements: str, bindings: Optional[Bindings] = 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.

    :param statements: One or more SQL statements such as ``select *
      from books`` or ``begin; insert into books ...; select
      last_insert_rowid(); end``.
    :param bindings: If supplied should either be a sequence or a dictionary.  Each item must be one of the :ref:`supported types <types>`
    :param 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
    :param prepare_flags: `flags <https://sqlite.org/c3ref/c_prepare_normalize.html>`__ passed to
      `sqlite_prepare_v3 <https://sqlite.org/c3ref/prepare.html>`__
    :param explain: If 0 or greater then the statement is passed to `sqlite3_stmt_explain <https://sqlite.org/c3ref/stmt_explain.html>`__
       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
    :raises BindingsError: You supplied too many or too few bindings for the statements
    :raises IncompleteExecutionError: There are remaining unexecuted queries from your last execute

    .. seealso::

       * :ref:`Example <example_executing_sql>` showing how to use bindings
       * :ref:`executionmodel`

    Calls:
      * `sqlite3_prepare_v3 <https://sqlite.org/c3ref/prepare.html>`__
      * `sqlite3_step <https://sqlite.org/c3ref/step.html>`__
      * `sqlite3_bind_int64 <https://sqlite.org/c3ref/bind_blob.html>`__
      * `sqlite3_bind_null <https://sqlite.org/c3ref/bind_blob.html>`__
      * `sqlite3_bind_text64 <https://sqlite.org/c3ref/bind_blob.html>`__
      * `sqlite3_bind_double <https://sqlite.org/c3ref/bind_blob.html>`__
      * `sqlite3_bind_blob64 <https://sqlite.org/c3ref/bind_blob.html>`__
      * `sqlite3_bind_zeroblob <https://sqlite.org/c3ref/bind_blob.html>`__

.. method:: 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 :meth:`~Cursor.execute` for more
  information, and the :ref:`example <example_executemany>`.

.. index:: sqlite3_expanded_sql

.. attribute:: Cursor.expanded_sql
  :type: 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 :exc:`MemoryError` if SQLite ran out of memory, or if
  the expanded string would exceed `SQLITE_LIMIT_LENGTH
  <https://www.sqlite.org/c3ref/c_limit_attached.html>`__.

  Calls: `sqlite3_expanded_sql <https://sqlite.org/c3ref/expanded_sql.html>`__

.. method:: Cursor.fetchall() -> list[tuple[SQLiteValue, ...]]

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

.. method:: Cursor.fetchone() -> Optional[Any]

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

.. attribute:: Cursor.get
 :type: Any

 Like :meth:`fetchall` but returns the data with the least amount of structure
 possible.

 .. list-table:: Some examples
    :header-rows: 1
    :widths: auto

    * - 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.

.. method:: Cursor.get_connection() -> Connection

  Returns the :attr:`connection` this cursor is part of

.. index:: sqlite3_column_name, sqlite3_column_decltype

.. method:: Cursor.get_description() -> tuple[tuple[str, str], ...]

   If you are trying to get information about a table or view,
   then `pragma table_info <https://sqlite.org/pragma.html#pragma_table_info>`__
   is better.  If you want to know up front what columns and other
   details a query does then :func:`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 :ref:`query_info example <example_query_details>`.

   Calls:
     * `sqlite3_column_name <https://sqlite.org/c3ref/column_name.html>`__
     * `sqlite3_column_decltype <https://sqlite.org/c3ref/column_decltype.html>`__

.. method:: Cursor.get_exec_trace() -> Optional[ExecTracer]

  Returns the currently installed :attr:`execution tracer
  <Cursor.exec_trace>`

  .. seealso::

    * :ref:`tracing`

.. method:: Cursor.get_row_trace() -> Optional[RowTracer]

  Returns the currently installed (via :meth:`~Cursor.set_row_trace`)
  row tracer.

  .. seealso::

    * :ref:`tracing`

.. attribute:: Cursor.has_vdbe
  :type: bool

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

.. index:: sqlite3_stmt_isexplain

.. attribute:: Cursor.is_explain
  :type: 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 <https://sqlite.org/c3ref/stmt_isexplain.html>`__

.. index:: sqlite3_stmt_readonly

.. attribute:: Cursor.is_readonly
  :type: 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 <https://sqlite.org/c3ref/stmt_readonly.html>`__

.. attribute:: Cursor.row_trace
  :type: Optional[RowTracer]

  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.

  .. seealso::

    * :ref:`tracing`
    * :ref:`rowtracer`
    * :attr:`Connection.row_trace`

.. method:: Cursor.set_exec_trace(callable: Optional[ExecTracer]) -> None

  Sets the :attr:`execution tracer <Cursor.exec_trace>`

.. method:: Cursor.set_row_trace(callable: Optional[RowTracer]) -> None

  Sets the :attr:`row tracer <Cursor.row_trace>`