.. _db_interface:

******

Driver

******

`postgresql.driver` provides a PG-API, `postgresql.api`, interface to a

PostgreSQL server using PQ version 3.0 to facilitate communication. It makes

use of the protocol's extended features to provide binary datatype transmission

and protocol level prepared statements for strongly typed parameters.

`postgresql.driver` currently supports PostgreSQL servers as far back as 8.0.

Prior versions are not tested. While any version of PostgreSQL supporting

version 3.0 of the PQ protocol *should* work, many features may not work due to

absent functionality in the remote end.

For DB-API 2.0 users, the driver module is located at

`postgresql.driver.dbapi20`. The DB-API 2.0 interface extends PG-API. All of the

features discussed in this chapter are available on DB-API connections.

.. warning::

PostgreSQL versions 8.1 and earlier do not support standard conforming

strings. In order to avoid subjective escape methods on connections,

`postgresql.driver.pq3` enables the ``standard_conforming_strings`` setting

by default. Greater care must be taken when working versions that do not

support standard strings.

**The majority of issues surrounding the interpolation of properly quoted literals can be easily avoided by using parameterized statements**.

The following identifiers are regularly used as shorthands for significant

interface elements:

``db``

`postgresql.api.Connection`, a database connection. `Connections`_

``ps``

`postgresql.api.Statement`, a prepared statement. `Prepared Statements`_

``c``

`postgresql.api.Cursor`, a cursor; the results of a prepared statement.

`Cursors`_

``C``

`postgresql.api.Connector`, a connector. `Connectors`_

Establishing a Connection

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

There are many ways to establish a `postgresql.api.Connection` to a

PostgreSQL server using `postgresql.driver`. This section discusses those,

connection creation, interfaces.

`postgresql.open`

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

In the root package module, the ``open()`` function is provided for accessing

databases using a locator string and optional connection keywords. The string

taken by `postgresql.open` is a URL whose components make up the client

parameters::

>>> import postgresql

>>> db = postgresql.open("pq://localhost/postgres")

This will connect to the host, ``localhost`` and to the database named

``postgres`` via the ``pq`` protocol. open will inherit client parameters from

the environment, so the user name given to the server will come from

``$PGUSER``, or if that is unset, the result of `getpass.getuser`--the username

of the user running the process. The user's "pgpassfile" will even be

referenced if no password is given::

>>> db = postgresql.open("pq://username:password@localhost/postgres")

In this case, the password *is* given, so ``~/.pgpass`` would never be

referenced. The ``user`` client parameter is also given, ``username``, so

``$PGUSER`` or `getpass.getuser` will not be given to the server.

Settings can also be provided by the query portion of the URL::

>>> db = postgresql.open("pq://user@localhost/postgres?search_path=public&timezone=mst")

The above syntax ultimately passes the query as settings(see the description of

the ``settings`` keyword in `Connection Keywords`). Driver parameters require a

distinction. This distinction is made when the setting's name is wrapped in

square-brackets, '[' and ']':

>>> db = postgresql.open("pq://user@localhost/postgres?[sslmode]=require&[connect_timeout]=5")

``sslmode`` and ``connect_timeout`` are driver parameters. These are never sent

to the server, but if they were not in square-brackets, they would be, and the

driver would never identify them as driver parameters.

The general structure of a PQ-locator is::

protocol://user:password@host:port/database?[driver_setting]=value&server_setting=value

Optionally, connection keyword arguments can be used to override anything given

in the locator::

>>> db = postgresql.open("pq://user:secret@host", password = "thE_real_sekrat")

Or, if the locator is not desired, individual keywords can be used exclusively::

>>> db = postgresql.open(user = 'user', host = 'localhost', port = 6543)

In fact, all arguments to `postgresql.open` are optional as all arguments are

keywords; ``iri`` is merely the first keyword argument taken by

`postgresql.open`. If the environment has all the necessary parameters for a

successful connection, there is no need to pass anything to open::

>>> db = postgresql.open()

For a complete list of keywords that `postgresql.open` can accept, see

`Connection Keywords`_.

For more information about the environment variables, see :ref:`pg_envvars`.

For more information about the ``pgpassfile``, see :ref:`pg_passfile`.

`postgresql.driver.connect`

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

`postgresql.open` is a high-level interface to connection creation. It provides

password resolution services and client parameter inheritance. For some

applications, this is undesirable as such implicit inheritance may lead to

failures due to unanticipated parameters being used. For those applications,

use of `postgresql.open` is not recommended. Rather, `postgresql.driver.connect`

should be used when explicit parameterization is desired by an application:

>>> import postgresql.driver as pg_driver

>>> db = pg_driver.connect(

... user = 'usename',

... password = 'secret',

... host = 'localhost',

... port = 5432

... )

This will create a connection to the server listening on port ``5432``

on the host ``localhost`` as the user ``usename`` with the password ``secret``.

.. note::

`connect` will *not* inherit parameters from the environment as libpq-based drivers do.

See `Connection Keywords`_ for a full list of acceptable keyword parameters and

their meaning.

Connectors

----------

Connectors are the supporting objects used to instantiate a connection. They

exist for the purpose of providing connections with the necessary abstractions

for facilitating the client's communication with the server, *and to act as a

container for the client parameters*. The latter purpose is of primary interest

to this section.

Each connection object is associated with its connector by the ``connector``

attribute on the connection. This provides the user with access to the

parameters used to establish the connection in the first place, and the means to

create another connection to the same server. The attributes on the connector

should *not* be altered. If parameter changes are needed, a new connector should

be created.

The attributes available on a connector are consistent with the names of the

connection parameters described in `Connection Keywords`_, so that list can be

used as a reference to identify the information available on the connector.

Connectors fit into the category of "connection creation interfaces", so

connector instantiation normally takes the same parameters that the

`postgresql.driver.connect` function takes.

.. note::

Connector implementations are specific to the transport, so keyword arguments

like ``host`` and ``port`` aren't supported by the ``Unix`` connector.

The driver, `postgresql.driver.default` provides a set of connectors for making

a connection:

``postgresql.driver.default.host(...)``

Provides a ``getaddrinfo()`` abstraction for establishing a connection.

``postgresql.driver.default.ip4(...)``

Connect to a single IPv4 addressed host.

``postgresql.driver.default.ip6(...)``

Connect to a single IPv6 addressed host.

``postgresql.driver.default.unix(...)``

Connect to a single unix domain socket. Requires the ``unix`` keyword which

must be an absolute path to the unix domain socket to connect to.

``host`` is the usual connector used to establish a connection::

>>> C = postgresql.driver.default.host(

... user = 'auser',

... host = 'foo.com',

... port = 5432)

>>> # create

>>> db = C()

>>> # establish

>>> db.connect()

If a constant internet address is used, ``ip4`` or ``ip6`` can be used::

>>> C = postgresql.driver.default.ip4(user='auser', host='127.0.0.1', port=5432)

>>> db = C()

>>> db.connect()

Additionally, ``db.connect()`` on ``db.__enter__()`` for with-statement support:

>>> with C() as db:

... ...

Connectors are constant. They have no knowledge of PostgreSQL service files,

environment variables or LDAP services, so changes made to those facilities

will *not* be reflected in a connector's configuration. If the latest

information from any of these sources is needed, a new connector needs to be

created as the credentials have changed.

.. note::

``host`` connectors use ``getaddrinfo()``, so if DNS changes are made,

new connections *will* use the latest information.

Connection Keywords

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

The following is a list of keywords accepted by connection creation

interfaces:

``user``

The user to connect as.

``password``

The user's password.

``database``

The name of the database to connect to. (PostgreSQL defaults it to `user`)

``host``

The hostname or IP address to connect to.

``port``

The port on the host to connect to.

``unix``

The unix domain socket to connect to. Exclusive with ``host`` and ``port``.

Expects a string containing the *absolute path* to the unix domain socket to

connect to.

``settings``

A dictionary or key-value pair sequence stating the parameters to give to the

database. These settings are included in the startup packet, and should be

used carefully as when an invalid setting is given, it will cause the

connection to fail.

``connect_timeout``

Amount of time to wait for a connection to be made. (in seconds)

``server_encoding``

Hint given to the driver to properly encode password data and some information

in the startup packet.

This should only be used in cases where connections cannot be made due to

authentication failures that occur while using known-correct credentials.

``sslmode``

``'disable'``

Don't allow SSL connections.

``'allow'``

Try without SSL first, but if that doesn't work, try with.

``'prefer'``

Try SSL first, then without.

``'require'``

Require an SSL connection.

``sslcrtfile``

Certificate file path given to `ssl.wrap_socket`.

``sslkeyfile``

Key file path given to `ssl.wrap_socket`.

``sslrootcrtfile``

Root certificate file path given to `ssl.wrap_socket`

``sslrootcrlfile``

Revocation list file path. [Currently not checked.]

``category``

A `postgresql.api.Category` instance used to further initialize

the database.

Connections

===========

`postgresql.open` and `postgresql.driver.connect` provide the means to

establish a connection. Connections provide a `postgresql.api.Database`

interface to a PostgreSQL server; specifically, a `postgresql.api.Connection`.

Connections are one-time objects. Once, it is closed or lost, it can longer be

used to interact with the database provided by the server. If further use of the

server is desired, a new connection *must* be established.

.. note::

Cannot connect failures, exceptions raised on ``connect()``, are also terminal.

In cases where operations are performed on a closed connection, a

`postgresql.exceptions.ConnectionDoesNotExistError` will be raised.

Database Interface Points

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

After a connection is established::

>>> import postgresql

>>> db = postgresql.open(...)

The methods and properties on the connection object are ready for use:

``Connection.prepare(sql_statement_string)``

Create a `postgresql.api.Statement` object for querying the database.

This provides an "SQL statement template" that can be executed multiple times.

See `Prepared Statements`_ for more information.

``Connection.proc(procedure_id)``

Create a `postgresql.api.StoredProcedure` object referring to a stored

procedure on the database. The returned object will provide a

`collections.Callable` interface to the stored procedure on the server. See

`Stored Procedures`_ for more information.

``Connection.statement_from_id(statement_id)``

Create a `postgresql.api.Statement` object from an existing statement

identifier. This is used in cases where the statement was prepared on the

server. See `Prepared Statements`_ for more information.

``Connection.cursor_from_id(cursor_id)``

Create a `postgresql.api.Cursor` object from an existing cursor identifier.

This is used in cases where the cursor was declared on the server. See

`Cursors`_ for more information.

``Connection.do(language, source)``

Execute a DO statement on the server using the specified language.

*DO statements are available on PostgreSQL 9.0 and greater.*

*Executing this method on servers that do not support DO statements will*

*likely cause a SyntaxError*.

``Connection.execute(sql_statements_string)``

Run a block of SQL on the server. This method returns `None` unless an error

occurs. If errors occur, the processing of the statements will stop and the

error will be raised.

``Connection.xact(isolation = None, mode = None)``

The `postgresql.api.Transaction` constructor for creating transactions.

This method creates a transaction reference. The transaction will not be

started until it's instructed to do so. See `Transactions`_ for more

information.

``Connection.settings``

A property providing a `collections.MutableMapping` interface to the

database's SQL settings. See `Settings`_ for more information.

``Connection.clone()``

Create a new connection object based on the same factors that were used to

create ``db``. The new connection returned will already be connected.

``Connection.msghook(msg)``

By default, the `msghook` attribute does not exist. If set to a callable, any

message that occurs during an operation of the database or an operation of a

database derived object will be given to the callable. See the

`Database Messages`_ section for more information.

``Connection.listen(*channels)``

Start listening for asynchronous notifications in the specified channels.

Sends a batch of ``LISTEN`` statements to the server.

``Connection.unlisten(*channels)``

Stop listening for asynchronous notifications in the specified channels.

Sends a batch of ``UNLISTEN`` statements to the server.

``Connection.listening_channels()``

Return an iterator producing the channel names that are currently being

listened to.

``Connection.notify(*channels, **channel_and_payload)``

NOTIFY the channels with the given payload. Sends a batch of ``NOTIFY``

statements to the server.

Equivalent to issuing "NOTIFY <channel>" or "NOTIFY <channel>, <payload>"

for each item in `channels` and `channel_and_payload`. All NOTIFYs issued

will occur in the same transaction, regardless of auto-commit.

The items in `channels` can either be a string or a tuple. If a string,

no payload is given, but if an item is a `builtins.tuple`, the second item

in the pair will be given as the payload, and the first as the channel.

`channels` offers a means to issue NOTIFYs in guaranteed order::

>>> db.notify('channel1', ('different_channel', 'payload'))

In the above, ``NOTIFY "channel1";`` will be issued first, followed by

``NOTIFY "different_channel", 'payload';``.

The items in `channel_and_payload` are all payloaded NOTIFYs where the

keys are the channels and the values are the payloads. Order is undefined::

>>> db.notify(channel_name = 'payload_data')

`channels` and `channels_and_payload` can be used together. In such cases all

NOTIFY statements generated from `channels_and_payload` will follow those in

`channels`.

``Connection.iternotifies(timeout = None)``

Return an iterator to the NOTIFYs received on the connection. The iterator

will yield notification triples consisting of ``(channel, payload, pid)``.

While iterating, the connection should *not* be used in other threads.

The optional timeout can be used to enable "idle" events in which `None`

objects will be yielded by the iterator.

See :ref:`notifyman` for details.

When a connection is established, certain pieces of information are collected from

the backend. The following are the attributes set on the connection object after

the connection is made:

``Connection.version``

The version string of the *server*; the result of ``SELECT version()``.

``Connection.version_info``

A ``sys.version_info`` form of the ``server_version`` setting. eg.

``(8, 1, 2, 'final', 0)``.

``Connection.security``

`None` if no security. ``'ssl'`` if SSL is enabled.

``Connection.backend_id``

The process-id of the backend process.

``Connection.backend_start``

When backend was started. ``datetime.datetime`` instance.

``Connection.client_address``

The address of the client that the backend is communicating with.

``Connection.client_port``

The port of the client that the backend is communicating with.

``Connection.fileno()``

Method to get the file descriptor number of the connection's socket. This

method will return `None` if the socket object does not have a ``fileno``.

Under normal circumstances, it will return an `int`.

The ``backend_start``, ``client_address``, and ``client_port`` are collected

from pg_stat_activity. If this information is unavailable, the attributes will

be `None`.

Prepared Statements

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

Prepared statements are the primary entry point for initiating an operation on

the database. Prepared statement objects represent a request that will, likely,

be sent to the database at some point in the future. A statement is a single

SQL command.

The ``prepare`` entry point on the connection provides the standard method for

creating a `postgersql.api.Statement` instance bound to the

connection(``db``) from an SQL statement string::

>>> ps = db.prepare("SELECT 1")

>>> ps()

[(1,)]

Statement objects may also be created from a statement identifier using the

``statement_from_id`` method on the connection. When this method is used, the

statement must have already been prepared or an error will be raised.

>>> db.execute("PREPARE a_statement_id AS SELECT 1;")

>>> ps = db.statement_from_id('a_statement_id')

>>> ps()

[(1,)]

When a statement is executed, it binds any given parameters to a *new* cursor

and the entire result-set is returned.

Statements created using ``prepare()`` will leverage garbage collection in order

to automatically close statements that are no longer referenced. However,

statements created from pre-existing identifiers, ``statement_from_id``, must

be explicitly closed if the statement is to be discarded.

Statement objects are one-time objects. Once closed, they can no longer be used.

Statement Interface Points

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

Prepared statements can be executed just like functions:

>>> ps = db.prepare("SELECT 'hello, world!'")

>>> ps()

[('hello, world!',)]

The default execution method, ``__call__``, produces the entire result set. It

is the simplest form of statement execution. Statement objects can be executed in

different ways to accommodate for the larger results or random access(scrollable

cursors).

Prepared statement objects have a few execution methods:

``Statement(*parameters)``

As shown before, statement objects can be invoked like a function to get

the statement's results.

``Statement.rows(*parameters)``

Return a iterator to all the rows produced by the statement. This

method will stream rows on demand, so it is ideal for situations where

each individual row in a large result-set must be processed.

``iter(Statement)``

Convenience interface that executes the ``rows()`` method without arguments.

This enables the following syntax:

>>> for table_name, in db.prepare("SELECT table_name FROM information_schema.tables"):

... print(table_name)

``Statement.column(*parameters)``

Return a iterator to the first column produced by the statement. This

method will stream values on demand, and *should* only be used with statements

that have a single column; otherwise, bandwidth will ultimately be wasted as

the other columns will be dropped.

*This execution method cannot be used with COPY statements.*

``Statement.first(*parameters)``

For simple statements, cursor objects are unnecessary.

Consider the data contained in ``c`` from above, 'hello world!'. To get at this

data directly from the ``__call__(...)`` method, it looks something like::

>>> ps = db.prepare("SELECT 'hello, world!'")

>>> ps()[0][0]

'hello, world!'

To simplify access to simple data, the ``first`` method will simply return

the "first" of the result set::

>>> ps.first()

'hello, world!'

The first value.

When the result set consists of a single column, ``first()`` will return

that column in the first row.

The first row.

When the result set consists of multiple columns, ``first()`` will return

that first row.

The first, and only, row count.

When DML--for instance, an INSERT-statement--is executed, ``first()`` will

return the row count returned by the statement as an integer.

.. note::

DML that returns row data, RETURNING, will *not* return a row count.

The result set created by the statement determines what is actually returned.

Naturally, a statement used with ``first()`` should be crafted with these

rules in mind.

``Statement.chunks(*parameters)``

This access point is designed for situations where rows are being streamed out

quickly. It is a method that returns a ``collections.Iterator`` that produces

*sequences* of rows. This is the most efficient way to get rows from the

database. The rows in the sequences are ``builtins.tuple`` objects.

``Statement.declare(*parameters)``

Create a scrollable cursor with hold. This returns a `postgresql.api.Cursor`

ready for accessing random rows in the result-set. Applications that use the

database to support paging should use this method to manage the view.

``Statement.close()``

Close the statement inhibiting further use.

``Statement.load_rows(collections.Iterable(parameters))``

Given an iterable producing parameters, execute the statement for each

iteration. Always returns `None`.

``Statement.load_chunks(collections.Iterable(collections.Iterable(parameters)))``

Given an iterable of iterables producing parameters, execute the statement

for each parameter produced. However, send the all execution commands with

the corresponding parameters of each chunk before reading any results.

Always returns `None`. This access point is designed to be used in conjunction

with ``Statement.chunks()`` for transferring rows from one connection to another with

great efficiency::

>>> dst.prepare(...).load_chunks(src.prepare(...).chunks())

``Statement.clone()``

Create a new statement object based on the same factors that were used to

create ``ps``.

``Statement.msghook(msg)``

By default, the `msghook` attribute does not exist. If set to a callable, any

message that occurs during an operation of the statement or an operation of a

statement derived object will be given to the callable. See the

`Database Messages`_ section for more information.

In order to provide the appropriate type transformations, the driver must

acquire metadata about the statement's parameters and results. This data is

published via the following properties on the statement object:

``Statement.sql_parameter_types``

A sequence of SQL type names specifying the types of the parameters used in

the statement.

``Statement.sql_column_types``

A sequence of SQL type names specifying the types of the columns produced by

the statement. `None` if the statement does not return row-data.

``Statement.pg_parameter_types``

A sequence of PostgreSQL type Oid's specifying the types of the parameters

used in the statement.

``Statement.pg_column_types``

A sequence of PostgreSQL type Oid's specifying the types of the columns produced by

the statement. `None` if the statement does not return row-data.

``Statement.parameter_types``

A sequence of Python types that the statement expects.

``Statement.column_types``

A sequence of Python types that the statement will produce.

``Statement.column_names``

A sequence of `str` objects specifying the names of the columns produced by

the statement. `None` if the statement does not return row-data.

The indexes of the parameter sequences correspond to the parameter's

identifier, N+1: ``sql_parameter_types[0]`` -> ``'$1'``.

>>> ps = db.prepare("SELECT $1::integer AS intname, $2::varchar AS chardata")

>>> ps.sql_parameter_types

('INTEGER','VARCHAR')

>>> ps.sql_column_types

('INTEGER','VARCHAR')

>>> ps.column_names

('intname','chardata')

>>> ps.column_types

(<class 'int'>, <class 'str'>)

Parameterized Statements

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

Statements can take parameters. Using statement parameters is the recommended

way to interrogate the database when variable information is needed to formulate

a complete request. In order to do this, the statement must be defined using

PostgreSQL's positional parameter notation. ``$1``, ``$2``, ``$3``, etc::

>>> ps = db.prepare("SELECT $1")

>>> ps('hello, world!')[0][0]

'hello, world!'

PostgreSQL determines the type of the parameter based on the context of the

parameter's identifier::

>>> ps = db.prepare(

... "SELECT * FROM information_schema.tables WHERE table_name = $1 LIMIT $2"

... )

>>> ps("tables", 1)

[('postgres', 'information_schema', 'tables', 'VIEW', None, None, None, None, None, 'NO', 'NO', None)]

Parameter ``$1`` in the above statement will take on the type of the

``table_name`` column and ``$2`` will take on the type required by the LIMIT

clause(text and int8).

However, parameters can be forced to a specific type using explicit casts:

>>> ps = db.prepare("SELECT $1::integer")

>>> ps.first(-400)

-400

Parameters are typed. PostgreSQL servers provide the driver with the

type information about a positional parameter, and the serialization routine

will raise an exception if the given object is inappropriate. The Python

types expected by the driver for a given SQL-or-PostgreSQL type are listed

in `Type Support`_.

This usage of types is not always convenient. Notably, the `datetime` module

does not provide a friendly way for a user to express intervals, dates, or

times. There is a likely inclination to forego these parameter type

requirements.

In such cases, explicit casts can be made to work-around the type

requirements::

>>> ps = db.prepare("SELECT $1::text::date")

>>> ps.first('yesterday')

datetime.date(2009, 3, 11)

The parameter, ``$1``, is given to the database as a string, which is then

promptly cast into a date. Of course, without the explicit cast as text, the

outcome would be different::

>>> ps = db.prepare("SELECT $1::date")

>>> ps.first('yesterday')

Traceback:

...

postgresql.exceptions.ParameterError

The function that processes the parameter expects a `datetime.date` object, and

the given `str` object does not provide the necessary interfaces for the

conversion, so the driver raises a `postgresql.exceptions.ParameterError` from

the original conversion exception.

Inserting and DML

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

Loading data into the database is facilitated by prepared statements. In these

examples, a table definition is necessary for a complete illustration::

>>> db.execute(

... """

... CREATE TABLE employee (

... employee_name text,

... employee_salary numeric,

... employee_dob date,

... employee_hire_date date

... );

... """

... )

Create an INSERT statement using ``prepare``::

>>> mkemp = db.prepare("INSERT INTO employee VALUES ($1, $2, $3, $4)")

And add "Mr. Johnson" to the table::

>>> import datetime

>>> r = mkemp(

... "John Johnson",

... "92000",

... datetime.date(1950, 12, 10),

... datetime.date(1998, 4, 23)

... )

>>> print(r[0])

INSERT

>>> print(r[1])

1

The execution of DML will return a tuple. This tuple contains the completed

command name and the associated row count.

Using the call interface is fine for making a single insert, but when multiple

records need to be inserted, it's not the most efficient means to load data. For

multiple records, the ``ps.load_rows([...])`` provides an efficient way to load

large quantities of structured data::

>>> from datetime import date

>>> mkemp.load_rows([

... ("Jack Johnson", "85000", date(1962, 11, 23), date(1990, 3, 5)),

... ("Debra McGuffer", "52000", date(1973, 3, 4), date(2002, 1, 14)),

... ("Barbara Smith", "86000", date(1965, 2, 24), date(2005, 7, 19)),

... ])

While small, the above illustrates the ``ps.load_rows()`` method taking an

iterable of tuples that provides parameters for the each execution of the

statement.

``load_rows`` is also used to support ``COPY ... FROM STDIN`` statements::

>>> copy_emps_in = db.prepare("COPY employee FROM STDIN")

>>> copy_emps_in.load_rows([

... b'Emp Name1\t72000\t1970-2-01\t1980-10-22\n',

... b'Emp Name2\t62000\t1968-9-11\t1985-11-1\n',

... b'Emp Name3\t62000\t1968-9-11\t1985-11-1\n',

... ])

Copy data goes in as bytes and come out as bytes regardless of the type of COPY

taking place. It is the user's obligation to make sure the row-data is in the

appropriate encoding.

COPY Statements

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

`postgresql.driver` transparently supports PostgreSQL's COPY command. To the

user, COPY will act exactly like other statements that produce tuples; COPY

tuples, however, are `bytes` objects. The only distinction in usability is that

the COPY *should* be completed before other actions take place on the

connection--this is important when a COPY is invoked via ``rows()`` or

``chunks()``.

In situations where other actions are invoked during a ``COPY TO STDOUT``, the

entire result set of the COPY will be read. However, no error will be raised so

long as there is enough memory available, so it is *very* desirable to avoid

doing other actions on the connection while a COPY is active.

In situations where other actions are invoked during a ``COPY FROM STDIN``, a

COPY failure error will occur. The driver manages the connection state in such

a way that will purposefully cause the error as the COPY was inappropriately

interrupted. This not usually a problem as ``load_rows(...)`` and

``load_chunks(...)`` methods must complete the COPY command before returning.

Copy data is always transferred using ``bytes`` objects. Even in cases where the

COPY is not in ``BINARY`` mode. Any needed encoding transformations *must* be

made the caller. This is done to avoid any unnecessary overhead by default::

>>> ps = db.prepare("COPY (SELECT i FROM generate_series(0, 99) AS g(i)) TO STDOUT")

>>> r = ps()

>>> len(r)

100

>>> r[0]

b'0\n'

>>> r[-1]

b'99\n'

Of course, invoking a statement that way will read the entire result-set into

memory, which is not usually desirable for COPY. Using the ``chunks(...)``

iterator is the *fastest* way to move data::

>>> ci = ps.chunks()

>>> import sys

>>> for rowset in ps.chunks():

... sys.stdout.buffer.writelines(rowset)

...

<lots of data>

``COPY FROM STDIN`` commands are supported via

`postgresql.api.Statement.load_rows`. Each invocation to

``load_rows`` is a single invocation of COPY. ``load_rows`` takes an iterable of

COPY lines to send to the server::

>>> db.execute("""

... CREATE TABLE sample_copy (

... sc_number int,

... sc_text text

... );

... """)

>>> copyin = db.prepare('COPY sample_copy FROM STDIN')

>>> copyin.load_rows([

... b'123\tone twenty three\n',

... b'350\ttree fitty\n',

... ])

For direct connection-to-connection COPY, use of ``load_chunks(...)`` is

recommended as it will provide the most efficient transfer method::

>>> copyout = src.prepare('COPY atable TO STDOUT')

>>> copyin = dst.prepare('COPY atable FROM STDIN')

>>> copyin.load_chunks(copyout.chunks())

Specifically, each chunk of row data produced by ``chunks()`` will be written in

full by ``load_chunks()`` before getting another chunk to write.

Cursors

=======

When a prepared statement is declared, ``ps.declare(...)``, a

`postgresql.api.Cursor` is created and returned for random access to the rows in

the result set. Direct use of cursors is primarily useful for applications that

need to implement paging. For situations that need to iterate over the result

set, the ``ps.rows(...)`` or ``ps.chunks(...)`` execution methods should be

used.

Cursors can also be created directly from ``cursor_id``'s using the

``cursor_from_id`` method on connection objects::

>>> db.execute('DECLARE the_cursor_id CURSOR WITH HOLD FOR SELECT 1;')

>>> c = db.cursor_from_id('the_cursor_id')

>>> c.read()

[(1,)]

>>> c.close()

.. hint::

If the cursor that needs to be opened is going to be treated as an iterator,

then a FETCH-statement should be prepared instead using ``cursor_from_id``.

Like statements created from an identifier, cursors created from an identifier

must be explicitly closed in order to destroy the object on the server.

Likewise, cursors created from statement invocations will be automatically

released when they are no longer referenced.

.. note::

PG-API cursors are a direct interface to single result-set SQL cursors. This

is in contrast with DB-API cursors, which have interfaces for dealing with

multiple result-sets. There is no execute method on PG-API cursors.

Cursor Interface Points

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

For cursors that return row data, these interfaces are provided for accessing

those results:

``Cursor.read(quantity = None, direction = None)``

This method name is borrowed from `file` objects, and are semantically

similar. However, this being a cursor, rows are returned instead of bytes or

characters. When the number of rows returned is less then the quantity

requested, it means that the cursor has been exhausted in the configured

direction. The ``direction`` argument can be either ``'FORWARD'`` or `True`

to FETCH FORWARD, or ``'BACKWARD'`` or `False` to FETCH BACKWARD.

Like, ``seek()``, the ``direction`` *property* on the cursor object effects

this method.

``Cursor.seek(position[, whence = 0])``

When the cursor is scrollable, this seek interface can be used to move the

position of the cursor. See `Scrollable Cursors`_ for more information.

``next(Cursor)``

This fetches the next row in the cursor object. Cursors support the iterator

protocol. While equivalent to ``cursor.read(1)[0]``, `StopIteration` is raised

if the returned sequence is empty. (``__next__()``)

``Cursor.close()``

For cursors opened using ``cursor_from_id()``, this method must be called in

order to ``CLOSE`` the cursor. For cursors created by invoking a prepared

statement, this is not necessary as the garbage collection interface will take

the appropriate steps.

``Cursor.clone()``

Create a new cursor object based on the same factors that were used to

create ``c``.

``Cursor.msghook(msg)``

By default, the `msghook` attribute does not exist. If set to a callable, any

message that occurs during an operation of the cursor will be given to the

callable. See the `Database Messages`_ section for more information.

Cursors have some additional configuration properties that may be modified

during the use of the cursor:

``Cursor.direction``

A value of `True`, the default, will cause read to fetch forwards, whereas a

value of `False` will cause it to fetch backwards. ``'BACKWARD'`` and

``'FORWARD'`` can be used instead of `False` and `True`.

Cursors normally share metadata with the statements that create them, so it is

usually unnecessary for referencing the cursor's column descriptions directly.

However, when a cursor is opened from an identifier, the cursor interface must

collect the metadata itself. These attributes provide the metadata in absence of

a statement object:

``Cursor.sql_column_types``

A sequence of SQL type names specifying the types of the columns produced by

the cursor. `None` if the cursor does not return row-data.

``Cursor.pg_column_types``

A sequence of PostgreSQL type Oid's specifying the types of the columns produced by

the cursor. `None` if the cursor does not return row-data.

``Cursor.column_types``

A sequence of Python types that the cursor will produce.

``Cursor.column_names``

A sequence of `str` objects specifying the names of the columns produced by

the cursor. `None` if the cursor does not return row-data.

``Cursor.statement``

The statement that was executed that created the cursor. `None` if

unknown--``db.cursor_from_id()``.

Scrollable Cursors

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

Scrollable cursors are supported for applications that need to implement paging.

When statements are invoked via the ``declare(...)`` method, the returned cursor

is scrollable.

.. note::

Scrollable cursors never pre-fetch in order to provide guaranteed positioning.

The cursor interface supports scrolling using the ``seek`` method. Like

``read``, it is semantically similar to a file object's ``seek()``.

``seek`` takes two arguments: ``position`` and ``whence``:

``position``

The position to scroll to. The meaning of this is determined by ``whence``.

``whence``

How to use the position: absolute, relative, or absolute from end:

absolute: ``'ABSOLUTE'`` or ``0`` (default)

seek to the absolute position in the cursor relative to the beginning of the

cursor.

relative: ``'RELATIVE'`` or ``1``

seek to the relative position. Negative ``position``'s will cause a MOVE

backwards, while positive ``position``'s will MOVE forwards.

from end: ``'FROM_END'`` or ``2``

seek to the end of the cursor and then MOVE backwards by the given

``position``.

The ``whence`` keyword argument allows for either numeric and textual

specifications.

Scrolling through employees::

>>> emps_by_age = db.prepare("""

... SELECT