===========

Usage Guide

===========

.. currentModule:: firebird.driver

Driver structure

================

Source code is currently divided into next submodules:

* :mod:`~firebird.driver.types` - Data types used by driver.

* :mod:`~firebird.driver.interfaces` - Interface wrappers for Firebird new API

* :mod:`~firebird.driver.core` - Main driver source code.

* :mod:`~firebird.driver.fbapi` - Python `ctypes` interface to Firebird client library.

* :mod:`~firebird.driver.config` - Driver configuration.

* :mod:`~firebird.driver.hooks` - Drivers hooks.

All important data, functions, classes and constants are available directly in `firebird.driver`

name space. In normal circumstances is not necessary to import sub-modules directly. However,

you may need them to access some not so frequently needed driver functionality like driver

hooks, or to implement your own callback interfaces.

.. important::

`firebird-driver` is designed to support all Firebird versions starting from version 3.0.

Because each Firebird major version adds new functionality, and Firebird OO API could

be extended even in maintenance releases, the driver isolates volatile functionality

into special class hierarchies.

For example information about database (provided via `~.iAttachment_v3.get_info()` API

call) is isolated into separate `.DatabaseInfoProvider` class hierarchy.

The `.Connection.info` attribute then provides access to instance of appropriate class

- `.DatabaseInfoProvider` or its ancestor - for connected database.

The `.DatabaseInfoProvider` class **always** provides functionality of most recent

Firebird version supported by driver.

This layout has several important consequences:

1. The `.DatabaseInfoProvider` class may change in major driver release if new Firebird

functionality is introduced. This normally represent no problems for client application

as backward compatibility is guaranteed.

2. You should check the class hierarchy for "evolving" classes when you start using the

driver, and whenever you upgrade to new **major** driver version. If there are versioned

ancestor classes (they always have Firebird version number in their name) for canonical

(top level) ones, you should adjust your application to deal with situations when

instance of ancestor class is provided by driver instead top-level one, to prevent

run-time exceptions caused by access to functionality not provided by currently attached

Firebird server.

.. note::

The same apply for low-level API (`~firebird.driver.interfaces`) with difference

that they may change in minor driver releases (because API could be extended in

Firebird maintenance releases).

.. seealso::

`.DatabaseInfoProvider`, `.TransactionInfoProvider`, `.ServerInfoProvider`,

`.ServerDbServices`, `.ServerUserServices` and `.ServerTraceServices`.

.. index:: Configuration

Configuration

=============

The driver uses configuration built on top of `configuration system <firebird.base.config>`

provided by `firebird-base`_ package. In addition to global settings, the configuration

also includes the definition of connection parameters to Firebird servers and databases.

The default configuration connects to embedded server using direct/local connection method.

To access remote servers and databases (or local ones through remote protocols), it's

necessary to adjust default configuration, or `register` them in configuration manager.

You can manipulate the configuration objects directly, or load configuration from files or

strings (in `.ini-style` `configparser` format).

The 'driver_config' object

--------------------------

The global `.driver_config` object holds all configurable driver parameters, and access

configuration parameters for registered Firebird servers and databases.

In initial state, all parameters have default values and there are no registered servers

and databases. You can set individual parameter values directly, or you can set multiple

parameters (including registered servers and databases) at once by loading them from

configuration string, dict or file(s).

.. important::

If you want to use specific Firebird client library, you must set the value of

`.DriverConfig.fb_client_library` configuration option **before** your application

calls any from following functions: `.connect()`, `~firebird.driver.core.create_database()`,

`.connect_server()`, `.load_api()` or `.get_api()`.

.. seealso::

`.DriverConfig` for list of available methods and parameters.

Server and database configuration

---------------------------------

Firebird provides ever-increasing list of parameter options for database and server connections.

To keep the Python API clean and manageable, the `firebird-driver` uses server and database

configuration objects instead function parameters to specify values for almost all such options.

Connection functions then provide a name parameter that can refer to particular server / database

or configuration, and few keyword parameters to specify / override selected options.

.. important::

The configuration objects does not allow specification of next options:

- set database encryption callback (for technical reasons)

- set db_key scope (for security reasons)

- disable garbage collection (for security reasons)

- disable database triggers (for security reasons)

- allow overwrite of existing database with newly created database (for security reasons)

These options could be specified only as keyword arguments in appropriate functions.

.. seealso::

`.ServerConfig` and `.DatabaseConfig` for list of available methods and parameters.

.. index:: Database

pair: Connection; creation

Databases

=========

Access to the database is made available through `.Connection` objects. Firebird-driver

provides two constructors for these:

* `.connect` - Returns `.Connection` to database that already exists.

* `~firebird.driver.core.create_database` - Returns `.Connection` to newly created database.

.. index::

pair: Database; connect

Using connect()

---------------

This constructor has one positional and several keyword parameters.

The value of `database` positional parameter must be one of:

* name of registered database configuration

* database name / alias

.. important:: This value **cannot** be DSN / fully qualified Firebird connection string!

Keyword parameters are intended to override selected configuration options, or to specify

options that are not configurable.

.. note::

If `database` value is not recognized as name of registered database configuration,

the driver uses `~.DriverConfig.db_defaults` and `~.DriverConfig.server_defaults`

configuration objects.

A simple database connection is typically established with code such as this:

.. sourcecode:: python

from firebird.driver import connect

# Attach to 'employee' database/alias using embedded server connection

con = connect('employee', user='sysdba', password='masterkey')

# Attach to 'employee' database/alias using local server connection

from firebird.driver import driver_config

driver_config.server_defaults.host.value = 'localhost'

con = connect('employee', user='sysdba', password='masterkey')

# Set 'user' and 'password' via configuration

driver_config.server_defaults.user.value = 'SYSDBA'

driver_config.server_defaults.password.value = 'masterkey'

con = connect('employee')

However, it's recommended to use specific configuration for servers and databases.

It's possible to register servers and databases directly in code like this:

.. sourcecode:: python

from firebird.driver import connect, driver_config

# Register Firebird server

srv_cfg = """[local]

host = localhost

user = SYSDBA

password = masterkey

"""

driver_config.register_server('local', srv_cfg)

# Register database

db_cfg = """[employee]

server = local

database = employee.fdb

protocol = inet

charset = utf8

"""

driver_config.register_database('employee', db_cfg)

# Attach to 'employee' database

con = connect('employee')

But more convenient approach is using single configuration file::

# file: myapp.cfg

[firebird.driver]

servers = local

databases = employee

[local]

host = localhost

user = SYSDBA

password = masterkey

[employee]

server = local

database = employee.fdb

protocol = inet

charset = utf8

.. sourcecode:: python

from firebird.driver import connect, driver_config

driver_config.read('myapp.cfg')

# Attach to 'employee' database

con = connect('employee')

.. seealso:: `.connect()` for details.

.. index::

pair: Database; create

Using create_database()

-----------------------

This constructor returns connection to newly created database. It works in the same way

as `.connect()`, but utilizes additional database configuration options.

It's possible to specify these options in code like this:

.. sourcecode:: python

from firebird.driver import connect, driver_config

# Register Firebird server

srv_cfg = """[local]

host = localhost

user = SYSDBA

password = masterkey

"""

driver_config.register_server('local', srv_cfg)

# Register database

db_cfg = """[mydb]

server = local

database = mydb.fdb

protocol = inet

charset = utf8

# create options

page_size = 16384

db_charset = utf8

sweep_interval = 80000

reserve_space = no

"""

driver_config.register_database('mydb', db_cfg)

# create 'mydb' database

con = create_database('mydb')

But more convenient approach is using single configuration file::

# file: myapp.cfg

[firebird.driver]

servers = local

databases = mydb

[local]

host = localhost

user = SYSDBA

password = masterkey

[mydb]

server = local

database = mydb.fdb

protocol = inet

charset = utf8

# create options

page_size = 16384

db_charset = utf8

sweep_interval = 80000

reserve_space = no

.. sourcecode:: python

from firebird.driver import create_database, driver_config

driver_config.read('myapp.cfg')

# create 'mydb' database

con = create_database('mydb')

.. seealso:: `~firebird.driver.core.create_database()` for details.

.. index::

pair: Database; delete

Deleting databases

------------------

The Firebird engine also supports dropping (deleting) databases dynamically,

but dropping is a more complicated operation than creating, for several reasons:

an existing database may be in use by users other than the one who requests

the deletion, it may have supporting objects such as temporary sort files, and

it may even have dependent shadow databases. Although the database engine

recognizes a `DROP DATABASE` SQL statement, support for that statement is limited

to the `isql` command-line administration utility. However, the engine supports

the deletion of databases via an API call, which `firebird-driver` exposes as

`~.Connection.drop_database` method in `.Connection` class. So, to drop a database

you need to connect to it first.

**Example:**

.. sourcecode:: python

from firebird.driver import connect, driver_config

driver_config.read('myapp.cfg')

# Attach to 'myapp' database

con = connect('myapp')

con.drop_database()

.. seealso:: `.Connection.drop_database()` for details.

.. index::

pair: Connection; usage

pair: connection; Database

Connection object

-----------------

`.Connection` object represents a direct link to database, and works as

gateway for next operations with it:

* `Executing SQL Statements`_: methods `~.Connection.execute_immediate()` and `~.Connection.cursor()`.

* `Dropping database <Deleting databases>`_: method `~.Connection.drop_database()`.

* `Transanction management`_: methods `~.Connection.begin()`, `~.Connection.commit()`,

`~.Connection.rollback()`, `~.Connection.savepoint()`, `~.Connection.transaction_manager()`,

`~.Connection.is_active()`, and attributes `~.Connection.main_transaction`,

`~.Connection.query_transaction`, `~.Connection.transactions` and `~.Connection.default_tpb`.

* Work with `Database Events`_: method `~.Connection.event_collector`.

* `Getting information about connection`_: methods `~.Connection.is_closed()` and

`~.Connection.ping()` and attributes `~.Connection.dsn`, `~.Connection.charset`

and `~.Connection.sql_dialect`.

* `Getting information about database`_: attribute `~.Connection.info`.

* `Closing the connection`_: method `~.Connection.close()`

.. index::

pair: Connection; closing

Closing the connection

----------------------

There are many local and server resources used by firebird-driver that must be properly

managed, and disposed when they are no longer necessary. All objects that require proper

finalization provide `close()` method that must be called when object is no longer needed.

The `.Connection` (and `.Server`) objects are the most important ones, as other most frequently

used objects like cursors, prepared statements and transactions are typically associated with

connections.

You may call the `close()` method directly, or use the :ref:`with <with>` statement

and context manager support provided by all these objects.

**Example:**

.. sourcecode:: python

from firebird.driver import connect, driver_config

driver_config.read('myapp.cfg')

with connect('employee') as con:

cur = con.cursor()

cur.execute('select 1 from rdb$database')

print(cur.fetchone()[0])

.. note::

Objects that require proper finalization are: `.Connection`, `.TransactionManager`

and `.DistributedTransactionManager`, `.Statement`, `.BlobReader`, `.Cursor` and `.Server`.

Although only `.Connection` and `.Server` objects must be closed directly because

all other objects are associated with them and thus closed when connection is

closed, it's **recommended** to directly close any resource object obtained by

your code when it's no longer needed (either directly by calling `close()` or using

`with` statement).

.. important::

All managed objects have `~object.__del__` method, which ensures that the object in

the active state is properly closed before it is destroyed by the Python memory manager.

However, **the close operation may fail** as the state of your application could be arbitrary

and the sequence in which objects are disposed by memory manager is not deterministic.

The `~object.__del__` methods should be thus considered as safe guard of last resort

that your code should not rely upon. To indicate that your code is not managing

resources properly, the `ResourceWarning` is raises when active object is disposed

by memory manager.

.. note::

Such warnings may not reach your attention if warnings are disabled or filtered

on your system. You should always develop and test your applications with enabled

delivery of resource warnings.

.. seealso:: `.Connection.close()` for details.

.. index::

pair: Connection; information about

Getting information about connection

------------------------------------

Only (most useful) part of information associated with `.Connection` object is directly

available:

* It's possible to check whether Connection object is closed or not with

`~.Connection.is_closed()` method.

* It's possible to check whether connection to the Firebird server is not broken with

`~.Connection.ping()` method.

* The DSN (fully qualified Firebird database connection string) is surfaced as `~.Connection.dsn`

read-only property.

* The character set used by Connection is surfaced as `~.Connection.charset` read-only property.

* The SQL dialect used by Connection is surfaced as `~.Connection.sql_dialect` read-only property.

.. tip::

Additional connection-specific information is currently held as `bytes` in protected

`Connection._dpb` attribute that could be processed using `~firebird.driver.core.DPB` object.

.. index::

pair: Database; information about

Getting information about database

----------------------------------

.. important::

Because the scope and type of database information depends on the version of the Firebird

server and database ODS, this information is made available through a separate class

`.DatabaseInfoProvider`. The `.Connection.info` property provides access to

instance of `.DatabaseInfoProvider` or it's **ancestor** class according to ODS of attached

database and Firebird version.

Although you may query the information directly from server using

`~firebird.driver.core.DatabaseInfoProvider3.get_info()` method (that wraps the Firebird

`.iAttachment.getInfo()` API call), the `.DatabaseInfoProvider` object

provides more convenient methods and properties for obtaining specific information directly.

.. note::

Some information provided by `.DatabaseInfoProvider` properties

(like `~.DatabaseInfoProvider3.cache_hit_ratio`) could not be obtained via

`get_info()` method.

**Example:**

.. sourcecode:: python

from firebird.driver import connect

with connect('employee', user='SYSDBA', password='masterkey') as con:

print(f"Database character set: {con.info.charset}")

print(f"Page size (in bytes): {con.info.page_size}")

print(f"Attachment ID: {con.info.id}")

print(f"SQL dialect used by connected database: {con.info.sql_dialect}")

print(f"Database name (filename or alias): {con.info.name}")

print(f"Database site name: {con.info.site}")

print(f"Implementation (old format): {con.info.implementation!s}")

print(f"Database Provider: {con.info.provider!s}")

print(f"Database Class: {con.info.db_class!s}")

print(f"Date when database was created: {con.info.creation_date}")

print(f"Size of page cache used by connection: {con.info.page_cache_size}")

print(f"Number of pages allocated for database: {con.info.pages_allocated}")

print(f"Number of database pages in active use: {con.info.pages_used}")

print(f"Number of free allocated pages in database: {con.info.pages_free}")

print(f"Sweep interval: {con.info.sweep_interval}")

print(f"Data page space usage (USE_FULL or RESERVE): {con.info.space_reservation!s}")

print(f"Database write mode (SYNC or ASYNC): {con.info.write_mode!s}")

print(f"Database access mode (READ_ONLY or READ_WRITE): {con.info.access_mode!s}")

print(f"Current I/O statistics - Reads from disk to page cache: {con.info.reads}")

print(f"Current I/O statistics - Fetches from page cache: {con.info.fetches}")

print(f"Cache hit ratio = 1 - (reads / fetches): {con.info.cache_hit_ratio}")

print(f"Current I/O statistics - Writes from page cache to disk: {con.info.writes}")

print(f"Current I/O statistics - Writes to page in cache: {con.info.marks}")

print(f"Total amount of memory curretly used by database engine: {con.info.current_memory}")

print(f"Max. total amount of memory so far used by database engine: {con.info.max_memory}")

print(f"ID of Oldest Interesting Transaction: {con.info.oit}")

print(f"ID of Oldest Active Transaction: {con.info.oat}")

print(f"ID of Oldest Snapshot Transaction: {con.info.ost}")

print(f"ID for next transaction: {con.info.next_transaction}")

**Sample output**::

Database character set: NONE

Page size (in bytes): 8192

Attachment ID: 378

SQL dialect used by connected database: 3

Database name (filename or alias): /opt/firebird/examples/empbuild/employee.fdb

Database site name: NewAmarisk

Implementation (old format): Implementation.RDB_VMS

Database Provider: DbProvider.FIREBIRD

Database Class: DbClass.SERVER_ACCESS

Date when database was created: 2020-05-13 10:13:57.005010

Size of page cache used by connection: 2048

Number of pages allocated for database: 346

Number of database pages in active use: 311

Number of free allocated pages in database: 35

Sweep interval: 20000

Data page space usage (USE_FULL or RESERVE): DbSpaceReservation.RESERVE

Database write mode (SYNC or ASYNC): DbWriteMode.SYNC

Database access mode (READ_ONLY or READ_WRITE): DbAccessMode.READ_WRITE

Current I/O statistics - Reads from disk to page cache: 87

Current I/O statistics - Fetches from page cache: 1525

Cache hit ratio = 1 - (reads / fetches): 0.9429508196721311

Current I/O statistics - Writes from page cache to disk: 2

Current I/O statistics - Writes to page in cache: 5

Total amount of memory curretly used by database engine: 21925248

Max. total amount of memory so far used by database engine: 22033760

ID of Oldest Interesting Transaction: 307

ID of Oldest Active Transaction: 308

ID of Oldest Snapshot Transaction: 308

ID for next transaction: 308

.. seealso:: `.DatabaseInfoProvider` for details.

Getting information about Firebird version

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Because functionality and some features depends on actual Firebird version, it could be

important for driver users to check it. This (otherwise) simple task could be confusing

for new Firebird users, because Firebird uses two different version lineages. This abomination

was introduced to Firebird thanks to its InterBase legacy (Firebird 1.0 is a fork of InterBase

6.0), as applications designed to work with InterBase can often work with Firebird without

problems (and vice versa).

`.DatabaseInfoProvider` provides these version strings as two properties:

* `~.DatabaseInfoProvider.server_version` - Legacy InterBase-friendly version string.

* `~.DatabaseInfoProvider.firebird_version` - Firebird’s own version string.

However, this version string contains more information than version number. For example for

Linux Firebird 4.0.0 it’s ‘LI-T4.0.0.1963 Firebird 4.0 Beta 2’. So `.DatabaseInfoProvider`

provides two more properties for your convenience:

* `~.DatabaseInfoProvider.version` - Only Firebird version number. It’s a string with

format: major.minor.subrelease.build

* `~.DatabaseInfoProvider.engine_version` - Engine (major.minor) version as (float) number.

**Example:**

.. sourcecode:: python

from firebird.driver import connect

with connect('employee', user='SYSDBA', password='masterkey') as con:

print(f"server_version: '{con.info.server_version}'")

print(f"firebird_version: '{con.info.firebird_version}'")

print(f"version: '{con.info.version}'")

print(f"engine_version: {con.info.engine_version}")

**Sample output**::

server_version: 'LI-T6.3.0.1963 Firebird 4.0 Beta 2'

firebird_version: 'LI-T4.0.0.1963 Firebird 4.0 Beta 2'

version: '4.0.0.1963'

engine_version: 4.0

Database On-Disk Structure

^^^^^^^^^^^^^^^^^^^^^^^^^^

Particular Firebird features may also depend on specific support in database

(for example number and structure of monitoring tables). These required structures are

present automatically when database is created by particular engine verison that needs

them, but Firebird engine may work with databases created by older versions and thus with

older structure, so it could be necessary to consult also On-Disk Structure (ODS for short)

version. `.DatabaseInfoProvider` provides this number as `~.DatabaseInfoProvider.ods` (float)

property.

**Example:**

.. sourcecode:: python

from firebird.driver import connect

with connect('employee', user='SYSDBA', password='masterkey') as con:

print(f"ods: {con.info.ods}")

print(f"ods_version: {con.info.ods_version}")

print(f"ods_minor_version: {con.info.ods_minor_version}")

**Sample output**::

ods: 13.0

ods_version: 13

ods_minor_version: 0

.. index:: SQL Statement

Executing SQL Statements

========================

Firebird-driver implements two ways for execution of SQL commands against connected database:

* `~.Connection.execute_immediate` - for execution of SQL commands that don't return any result.

* `.Cursor` objects that offer rich interface for execution of SQL commands and fetching their results.

.. index::

pair: Cursor; usage

Cursor object

-------------

Because `.Cursor` objects always operate in context of single `.Connection` (and `.TransactionManager`),

`.Cursor` instances are not created directly, but by constructor method. Python DB API 2.0 assumes

that if database engine supports transactions, it supports only one transaction per connection,

hence it defines constructor method `~.Connection.cursor` (and other transaction-related methods)

as part of `.Connection` interface. However, Firebird supports multiple independent transactions

per connection. To conform to Python DB API, firebird-driver uses concept of internal

`~.Connection.main_transaction` and secondary `~.Connection.transactions`. Cursor constructor is

primarily defined by `.TransactionManager`, and Cursor constructor on `.Connection` is therefore

a shortcut for `main_transaction.cursor()`.

`.Cursor` objects are used for next operations:

* Execution of SQL Statemets: methods `~.Cursor.execute()`, `~.Cursor.executemany()`, `~.Cursor.open()`

and `~.Cursor.callproc()`.

* Creating `.Statement` objects for efficient repeated execution of SQL statements, and to obtain

additional information about SQL statements (like execution `~.Statement.plan`): method `~.Cursor.prepare()`.

* `Fetching results <Fetching data from server>`_: methods `~.Cursor.fetchone()`, `~.Cursor.fetchmany()`,

`~.Cursor.fetchall()`, `~.Cursor.fetch_next()`, `~.Cursor.fetch_prior()`, `~.Cursor.fetch_first()`,

`~.Cursor.fetch_last()`, `~.Cursor.fetch_absolute()` and `~.Cursor.fetch_relative()`.

.. index::

pair: SQL Statement; execution

SQL Execution Basics

--------------------

There are five methods how to execute SQL commands:

1. `.Connection.execute_immediate()` or `.TransactionManager.execute_immediate()` for SQL commands

that don't return any result, and are not executed frequently. This method also **doesn't**

support either `parameterized statements <parameterized-statements>`_ or

`prepared statements <prepared-statements>`_.

.. tip::

This method is efficient for `administrative` and `DDL`_ SQL commands, like `DROP`, `CREATE`

or `ALTER` commands, `SET STATISTICS` etc.

2. `.Cursor.execute()` for SQL commands that return result sets, i.e. sequence of `rows` of the same

structure, and sequence has unknown number of `rows` (including zero). Each row of the sequence

can be read only once, and is returned in the order it is read from the server.

.. tip::

This method is preferred for all `SELECT` and other `DML`_ statements, or any statement that

is executed frequently, either `as is` or in `parameterized` form.

3. `.Cursor.executemany()` for execution of single parameterized SQL command with various set

of parameters.

.. important::

Because `executemany()` is basically a simple loop that calls `execute()` with different

parameters, it's possible to execute any statement acceptable by `execute()`. However,

it's possible to access the result set **only** from last executed command, so this method

should not be used for SQL commands that return results.

4. `.Cursor.open()` for SQL command that return result sets, i.e. sequence of `rows` of the same

structure, and sequence has unknown number of `rows` (including zero). Instead of just fetching

rows sequentially in a forward direction like `execute()`, this method allows flexible navigation

through an open cursor set both backwards and forwards. Rows next to, prior to and relative to

the current cursor row can be targeted.

.. seealso:: `Scrollable cursors <scrollable-cursors>`_ for details.

5. `.Cursor.callproc()` for execution of `Stored procedures` that always return exactly one set

of values.

.. note::

This method of SP invocation is equivalent to `"EXECUTE PROCEDURE ..."` SQL statement.

.. index::

pair: Cursor; fetching data

Fetching data from server

-------------------------

Result of SQL statement execution consists from sequence of zero to unknown number of `rows`,

where each `row` is a set of exactly the same number of values. `.Cursor` object offer number

of different methods for fetching these `rows`, that should satisfy all your specific needs:

* `~.Cursor.fetchone()` - Returns the next row of a query result set, or `None` when no more data

is available.

.. tip::

Cursor supports the :ref:`iterator protocol <python:typeiter>`, yielding tuples of values

like `~.Cursor.fetchone()`.

* `~.Cursor.fetchmany()` - Returns the next set of rows of a query result, returning a sequence

of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.

The number of rows to fetch per call is specified by the parameter. If it is not given, the

cursor’s `~.Cursor.arraysize` determines the number of rows to be fetched. The method does try

to fetch as many rows as indicated by the size parameter. If this is not possible due to

the specified number of rows not being available, fewer rows may be returned.

.. note::

The default value of `~.Cursor.arraysize` is `1`, so without paremeter it's equivalent to

`~.Cursor.fetchone()`, but returns list of `rows`, instead actual `row` directly.

* `~.Cursor.fetchall()` - Returns all (remaining) rows of a query result as list of tuples,

where each tuple is one row of returned values.

.. tip::

This method can potentially return huge amount of data, that may exhaust available memory.

If you need just `iteration` over potentially big result set, use loops with `~.Cursor.fetchone()`

or Cursor's built-in support for :ref:`iterator protocol <python:typeiter>` instead this method.

* Call to `~.Cursor.execute()` returns `self` (Cursor instance) that itself supports the

:ref:`iterator protocol <python:typeiter>`, yielding tuples of values like `~.Cursor.fetchone()`.

.. important::

Firebird-driver makes absolutely no guarantees about the `row` return value of the

`fetch*()` methods except that it is a sequence indexed by field position. Therefore, client

programmers should not rely on the return value being an instance of a particular class or type.

**Examples:**

.. sourcecode:: python

from firebird.driver import connect

with connect('employee', user='SYSDBA', password='masterkey') as con:

cur = con.cursor()

SELECT = "select country, currency from country"

# 1. Using built-in support for iteration protocol to iterate over the rows available

# from the cursor, unpacking the resulting sequences to yield their elements (country, currency):

cur.execute(SELECT)

for (country, currency) in cur:

print(f"{country} uses {currency} as currency.")

# or alternatively you can take an advantage of cur.execute() returning self.

for (country, currency) in cur.execute(SELECT):

print(f"{country} uses {currency} as currency.")

# 2. Equivalently using fetchall():

# This is potentially dangerous if result set is huge, as the whole result set is

# first materialized as list and then used for iteration.

cur.execute(SELECT)

for row in cur.fetchall():

print(f"{row[0]} uses {row[1]} as currency.")

.. important::

Method `.Cursor.executemany()` is not intended for operations that return results,

so it does **NOT** returns `self` like `.Cursor.execute()`, and you can't use calls

to this method as iterator.

.. _scrollable-cursors:

.. index::

pair: Cursor; scrollable

Scrollable cursors

------------------

SQL statements executed by `.Cursor.open()` have scrollable result set that could be

navigated using next methods:

* `~.Cursor.fetch_next()` - Moves the cursor's current position to the next row and

returns it. Returns `None` if the cursor is empty or already positioned at the last row.

* `~.Cursor.fetch_prior()` - Moves the cursor's current position to the prior row and

returns it. Returns `None` if the cursor is empty or already positioned at the first row.

* `~.Cursor.fetch_first()` - Moves the cursor's current position to the first row and

returns it. Returns `None` if the cursor is empty.

* `~.Cursor.fetch_last()` - Moves the cursor's current position to the last row and

returns it. Returns `None` if the cursor is empty.

* `~.Cursor.fetch_absolute()` - Moves the cursor's current position to the specified

<position> and returns the located row. Returns `None` if <position> is beyond the

cursor's boundaries.

* `~.Cursor.fetch_relative()` - Moves the cursor's current position backward or forward

by the specified <offset> and returns the located row. Returns `None` if the calculated

position is beyond the cursor's boundaries.

.. important::

Please note that scrollable cursors:

a) are not supported by all versions of Firebird server.

b) are internally materialized as a temporary record set, thus consuming

memory/disk resources, so this feature should be used only when really necessary.

**Example:**

.. sourcecode:: python

from firebird.driver import connect

def print_row(row):

if row:

print(f"{row[0]}, {row[1]}, {row[2]}")

else:

print('NO DATA')

with connect('employee', user='SYSDBA', password='masterkey') as con:

cur = con.cursor()

cur.open('select row_number() over (order by country), country, currency from country order by country')

# You can iterate over scrollable cursors

for row in cur:

print_row(row)

print('-' * 10)

# or fetch particular rows directly

print_row(cur.fetch_first())

print_row(cur.fetch_last())

print_row(cur.fetch_absolute(10))

print_row(cur.fetch_next())

print_row(cur.fetch_prior())

print_row(cur.fetch_relative(-5))

print_row(cur.fetch_relative(10))

print('-' * 10)

cur.fetch_last()

print_row(cur.fetch_next())

**Sample output**::

1, Australia, ADollar

2, Austria, Euro

3, Belgium, Euro

4, Canada, CdnDlr

5, England, Pound

6, Fiji, FDollar

7, France, Euro

8, Germany, Euro

9, Hong Kong, HKDollar

10, Italy, Euro

11, Japan, Yen

12, Netherlands, Euro

13, Romania, RLeu

14, Russia, Ruble

15, Switzerland, SFranc

16, USA, Dollar

----------

1, Australia, ADollar

16, USA, Dollar

10, Italy, Euro

11, Japan, Yen

10, Italy, Euro

5, England, Pound

15, Switzerland, SFranc

----------

NO DATA

.. index::

pair: SQL Statement; parameterized

.. _parameterized-statements:

Parameterized statements

------------------------

When SQL command you want to execute contains data `values`, you can either:

* Embed them `directly` or via `string formatting` into command string, e.g.:

.. sourcecode:: python

cur.execute("insert into the_table (a,b,c) values ('aardvark', 1, 0.1)")

# or

cur.execute("select * from the_table where col == 'aardvark'")

# or

cur.execute("insert into the_table (a,b,c) values ('%s', %i, %f)" % ('aardvark',1,0.1))

# or

cur.execute(f"select * from the_table where col == '{value}'")

* Use parameter marker (`?`) in command string in the slots where values are expected,

then supply those values as Python list or tuple:

.. sourcecode:: python

cur.execute("insert into the_table (a,b,c) values (?,?,?)", ('aardvark', 1, 0.1))

# or

cur.execute("select * from the_table where col == ?",('aardvark',))

While both methods have the same results, the second one (called `parametrized`) has several

important advantages:

* You don't need to handle conversions from Python data types to strings.

* Firebird-driver will handle all data type conversions (if necessary) from Python data types

to Firebird ones, including `None/NULL` conversion and conversion from `str` to `bytes`

in encoding expected by server.

* You may pass BLOB values as open `file-like` objects, and firebird-driver will handle the

transfer of BLOB value.

Parametrized statemets also have some limitations. Currently:

* `DATE`, `TIME` and `DATETIME` values must be relevant `datetime` objects.

* `NUMERIC` and `DECIMAL` values must be `decimal` objects.

.. index::

pair: SQL Statement; prepared

.. _prepared-statements:

Prepared Statements

-------------------

Execution of any SQL statement has three phases:

* *Preparation*: command is analyzed, validated, execution plan is determined

by optimizer and all necessary data structures (for example for input and output

parameters) are initialized.

* *Execution*: input parameters (if any) are passed to server and previously

prepared statement is actually executed by database engine.

* *Fetching*: result of execution and data (if any) are transferred from server

to client, and allocated resources are then released (by closing the statement).

The preparation phase consumes some amount of server resources (memory and CPU).

Although preparation and release of resources typically takes only small amount

of CPU time, it builds up as number of executed statements grows. Firebird (like

most database engines) allows to spare this time for subsequent execution if

particular statement should be executed repeatedly - by reusing once prepared

statement for repeated execution. This may save significant amount of server

processing time, and result in better overall performance.

Firebird-driver builds on this by encapsulating the Firebird SQL statement data

and related code into separate `.Statement` class, and implementing the `.Cursor`

class around it. The Cursor uses either an internally managed `.Statement` instance

to execute SQL commands provided as `string`, or uses `.Statement` instance

provided by your code as SQL command.

To get the (prepared) `.Statement` instance for later (repeated) execution, use

`~.Cursor.prepare()` method. You can then pass this instance to `~.Cursor.execute()`,

`~.Cursor.executemany()` or `~.Cursor.open()` instead `command string`.

`.Statement` instances are bound to `.Connection` instance, and can't be used

with any other `.Connection`. Beside repeated execution they are also useful

to get information about statement (like its execution `~.Statement.plan` or

`~.Statement.type`) before its execution.

.. note::

The internally managed `.Statement` instance is released when `.Cursor` is closed,

or before any new statement is executed. It means that if your code executes

the same SQL command (passed as string) repeatedly without closing the cursor

between calls, the same `.Statement` instance is (re)used.

.. important::

Implementation of Cursor in firebird-driver somewhat violates the Python DB API 2.0,

which requires that cursor will be unusable after call to `~.Cursor.close()`; and

an Error (or subclass) exception should be raised if any operation is attempted with

the cursor. In firebird-driver, the `.Cursor.close()` call only releases resources

associated with executed statement like the result set, and you can't fetch data or

query information about the SQL statement. However, you can use the cursor instance