.. 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 ` 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 `_. APSW maintains a :ref:`cache ` so that the mapping is very fast, and the SQLite objects are reused when possible. .. note:: Cursors on the same :ref:`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. .. seealso:: * `SQLite transactions `_ * `Atomic commit `_ * :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 .. 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 `__, 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 `__ * `sqlite3_column_decltype `__ * `sqlite3_column_database_name `__ * `sqlite3_column_table_name `__ * `sqlite3_column_origin_name `__ .. 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 ` :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 `__ passed to `sqlite_prepare_v3 `__ :param 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 :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 ` showing how to use bindings * :ref:`executionmodel` Calls: * `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.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 `. .. 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 `__. Calls: `sqlite3_expanded_sql `__ .. 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 `__ 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 `. Calls: * `sqlite3_column_name `__ * `sqlite3_column_decltype `__ .. method:: Cursor.get_exec_trace() -> Optional[ExecTracer] Returns the currently installed :attr:`execution tracer ` .. 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 `__ .. 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 `__ .. 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 ` .. method:: Cursor.set_row_trace(callable: Optional[RowTracer]) -> None Sets the :attr:`row tracer `