:mod:`pg8000.dbapi` --- DBAPI 2.0 PostgreSQL Interface
======================================================
.. module:: pg8000.dbapi
:synopsis: DBAPI 2.0 compliant PostgreSQL interface using pg8000
DBAPI Properties
----------------
.. attribute:: apilevel
The DBAPI level supported, currently "2.0".
This property is part of the `DBAPI 2.0 specification
`_.
.. attribute:: threadsafety
Integer constant stating the level of thread safety the DBAPI interface
supports. This DBAPI module supports sharing the module, connections, and
cursors, resulting in a threadsafety value of 3.
This property is part of the `DBAPI 2.0 specification
`_.
.. attribute:: paramstyle
String property stating the type of parameter marker formatting expected by
the interface. This value defaults to "format", in which parameters are
marked in this format: "WHERE name=%s".
This property is part of the `DBAPI 2.0 specification
`_.
As an extension to the DBAPI specification, this value is not constant; it
can be changed to any of the following values:
qmark
Question mark style, eg. ``WHERE name=?``
numeric
Numeric positional style, eg. ``WHERE name=:1``
named
Named style, eg. ``WHERE name=:paramname``
format
printf format codes, eg. ``WHERE name=%s``
pyformat
Python format codes, eg. ``WHERE name=%(paramname)s``
.. attribute:: STRING
.. attribute:: BINARY
.. attribute:: NUMBER
.. attribute:: DATETIME
.. attribute:: ROWID
DBAPI Functions
---------------
.. function:: connect(user[, host, unix_sock, port=5432, database, password, socket_timeout=60, ssl=False])
Creates a connection to a PostgreSQL database.
This function is part of the `DBAPI 2.0 specification
`_; however, the arguments of the
function are not defined by the specification. pg8000 guarentees that for
all v1.xx releases, no optional parameters will be removed from the
function definition.
:param user:
The username to connect to the PostgreSQL server with. This
parameter is required.
:keyword host:
The hostname of the PostgreSQL server to connect with. Providing this
parameter is necessary for TCP/IP connections. One of either ``host``
or ``unix_sock`` must be provided.
:keyword unix_sock:
The path to the UNIX socket to access the database through, for
example, ``'/tmp/.s.PGSQL.5432'``. One of either ``host`` or
``unix_sock`` must be provided.
:keyword port:
The TCP/IP port of the PostgreSQL server instance. This parameter
defaults to ``5432``, the registered common port of PostgreSQL TCP/IP
servers.
:keyword database:
The name of the database instance to connect with. This parameter is
optional; if omitted, the PostgreSQL server will assume the database
name is the same as the username.
:keyword password:
The user password to connect to the server with. This parameter is
optional; if omitted and the database server requests password-based
authentication, the connection will fail to open. If this parameter
is provided but not requested by the server, no error will occur.
:keyword socket_timeout:
Socket connect timeout measured in seconds. This parameter defaults to
60 seconds.
:keyword ssl:
Use SSL encryption for TCP/IP sockets if ``True``. Defaults to
``False``.
:rtype:
An instance of :class:`pg8000.dbapi.ConnectionWrapper`.
.. function:: Date(year, month, day)
Constuct an object holding a date value.
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.date`
.. function:: Time(hour, minute, second)
Construct an object holding a time value.
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.time`
.. function:: Timestamp(year, month, day, hour, minute, second)
Construct an object holding a timestamp value.
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.datetime`
.. function:: DateFromTicks(ticks)
Construct an object holding a date value from the given ticks value (number
of seconds since the epoch).
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.date`
.. function:: TimeFromTicks(ticks)
Construct an objet holding a time value from the given ticks value (number
of seconds since the epoch).
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.time`
.. function:: TimestampFromTicks(ticks)
Construct an object holding a timestamp value from the given ticks value
(number of seconds since the epoch).
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`datetime.datetime`
.. function:: Binary(string)
Construct an object holding binary data.
This function is part of the `DBAPI 2.0 specification
`_.
:rtype: :class:`pg8000.types.Bytea`
DBAPI Objects
-------------
.. class:: ConnectionWrapper
A ``ConnectionWrapper`` instance represents a single physical connection
to a PostgreSQL database. To construct an instance of this class, use the
:func:`~pg8000.dbapi.connect` function.
.. method:: cursor()
Creates a :class:`~pg8000.dbapi.CursorWrapper` instance bound to this
connection.
This function is part of the `DBAPI 2.0 specification
`_.
.. method:: commit()
Commits the current database transaction.
This function is part of the `DBAPI 2.0 specification
`_.
.. method:: rollback()
Rolls back the current database transaction.
This function is part of the `DBAPI 2.0 specification
`_.
.. method:: close()
Closes the database connection.
This function is part of the `DBAPI 2.0 specification
`_.
.. attribute:: notifies
A list of server-side notifications received by this database
connection (via the LISTEN/NOTIFY PostgreSQL commands). Each list
element is a two-element tuple containing the PostgreSQL backend PID
that issued the notify, and the notification name.
PostgreSQL will only send notifications to a client between
transactions. The contents of this property are generally only
populated after a commit or rollback of the current transaction.
This list can be modified by a client application to clean out
notifications as they are handled. However, inspecting or modifying
this collection should only be done while holding the
:attr:`notifies_lock` lock in order to guarantee thread-safety.
This attribute is not part of the DBAPI standard; it is a pg8000
extension.
.. versionadded:: 1.07
.. attribute:: notifies_lock
A :class:`threading.Lock` object that should be held to read or modify
the contents of the :attr:`notifies` list.
This attribute is not part of the DBAPI standard; it is a pg8000
extension.
.. versionadded:: 1.07
.. attribute:: Error
Warning
InterfaceError
DatabaseError
InternalError
OperationalError
ProgrammingError
IntegrityError
DataError
NotSupportedError
All of the standard database exception types are accessible via
connection instances.
This is a DBAPI 2.0 extension. Accessing any of these attributes will
generate the warning ``DB-API extension connection.DatabaseError
used``.
.. class:: CursorWrapper
To construct an instance of this class, use the
:func:`pg8000.dbapi.ConnectionWrapper.cursor` method.
.. attribute:: arraysize
This read/write attribute specifies the number of rows to fetch at a
time with :meth:`fetchmany`. It defaults to 1.
.. attribute:: connection
This read-only attribute contains a reference to the connection object
(an instance of :class:`ConnectionWrapper`) on which the cursor was
created.
This attribute is part of a DBAPI 2.0 extension. Accessing this
attribute will generate the following warning: ``DB-API extension
cursor.connection used``.
.. attribute:: rowcount
This read-only attribute contains the number of rows that the last
execute method produced (for query statements like ``SELECT``) or
affected (for modification statements like ``UPDATE``).
During a query statement, accessing this property requires reading the
entire result set into memory. It is preferable to avoid using this
attribute to reduce memory usage.
The value is -1 in case no execute method has been performed on the
cursor, or there was no rowcount associated with the last operation.
This attribute is part of the `DBAPI 2.0 specification
`_.
.. attribute:: description
This read-only attribute is a sequence of 7-item sequences. Each value
contains information describing one result column. The 7 items
returned for each column are (name, type_code, display_size,
internal_size, precision, scale, null_ok). Only the first two values
are provided by the current implementation.
This attribute is part of the `DBAPI 2.0 specification
`_.
.. method:: execute(operation, args=())
Executes a database operation. Parameters may be provided as a
sequence, or as a mapping, depending upon the value of
:data:`pg8000.dbapi.paramstyle`.
This method is part of the `DBAPI 2.0 specification
`_.
:param operation:
The SQL statement to execute.
:param args:
If :data:`paramstyle` is ``qmark``, ``numeric``, or ``format``,
this argument should be an array of parameters to bind into the
statement. If :data:`paramstyle` is ``named``, the argument should
be a dict mapping of parameters. If the :data:`paramstyle` is
``pyformat``, the argument value may be either an array or a
mapping.
.. method:: executemany(operation, parameter_sets)
Prepare a database operation, and then execute it against all parameter
sequences or mappings provided.
This method is part of the `DBAPI 2.0 specification
`_.
:param operation:
The SQL statement to execute
:param parameter_sets:
A sequence of parameters to execute the statement with. The values in
the sequence should be sequences or mappings of parameters, the same as
the args argument of the :meth:`execute` method.
.. method:: fetchone()
Fetch the next row of a query result set.
This method is part of the `DBAPI 2.0 specification
`_.
:returns:
A row as a sequence of field values, or ``None`` if no more rows
are available.
.. method:: fetchmany(size=None)
Fetches the next set of rows of a query result.
This method is part of the `DBAPI 2.0 specification
`_.
:param size:
The number of rows to fetch when called. If not provided, the
:attr:`arraysize` attribute value is used instead.
:returns:
A sequence, each entry of which is a sequence of field values
making up a row. If no more rows are available, an empty sequence
will be returned.
.. method:: fetchall()
Fetches all remaining rows of a query result.
This method is part of the `DBAPI 2.0 specification
`_.
:returns:
A sequence, each entry of which is a sequence of field values
making up a row.
.. method:: copy_from(fileobj, table, sep='\t', null=None)
copy_from(fileobj, query=)
copy_to(fileobj, table, sep='\t', null=None)
copy_to(fileobj, query=)
Performs a PostgreSQL COPY query to stream data in or out of the
PostgreSQL server.
These methods are not part of the standard DBAPI, they are a pg8000
extension. They are designed to be compatible with similar methods
provided by psycopg2.
:param fileobj:
A file-like object that data is read from or written to. For
copy_from, the object have a ``read`` method; for copy_to, the
object must have a ``write`` method.
:param table:
When the table parameter is provided, a COPY query will be constructed
in the form of ``COPY table (TO/FROM) STDOUT``.
:param sep:
Used only when table is provided, this adds a ``DELIMITER AS``
clause to the COPY query.
:param null:
Used only when table is provided, this adds a ``NULL AS`` clause to
the COPY query.
:param query:
A complete COPY query to be used to generate or insert data. This
permits the use of any COPY directives that are supported by the
server.
:raises:
:exc:`~pg8000.errors.CopyQueryOrTableRequiredError` when neither
*table* nor *query* parameters are provided.
.. versionadded:: 1.07
.. method:: close()
Closes the cursor.
This method is part of the `DBAPI 2.0 specification
`_.
.. method:: next()
.. method:: __iter__()
A cursor object is iterable to retrieve the rows from a query.
This is a DBAPI 2.0 extension. Accessing these methods will generate a
warning, ``DB-API extension cursor.next() used`` and ``DB-API extension
cursor.__iter__() used``.
.. method:: setinputsizes(sizes)
.. method:: setoutputsizes(size[,column])
These methods are part of the `DBAPI 2.0 specification
`_, however, they are not
implemented by pg8000.
DBAPI Exceptions
----------------
.. exception:: Warning(exceptions.StandardError)
See :exc:`pg8000.errors.Warning`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: Error(exceptions.StandardError)
See :exc:`pg8000.errors.Error`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: InterfaceError(Error)
See :exc:`pg8000.errors.InterfaceError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: DatabaseError(Error)
See :exc:`pg8000.errors.DatabaseError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: InternalError(DatabaseError)
See :exc:`pg8000.errors.InternalError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: OperationalError(DatabaseError)
See :exc:`pg8000.errors.OperationalError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: ProgrammingError(DatabaseError)
See :exc:`pg8000.errors.ProgrammingError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: IntegrityError(DatabaseError)
See :exc:`pg8000.errors.IntegrityError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: DataError(DatabaseError)
See :exc:`pg8000.errors.DataError`
This exception is part of the `DBAPI 2.0 specification
`_.
.. exception:: NotSupportedError(DatabaseError)
See :exc:`pg8000.errors.NotSupportedError`
This exception is part of the `DBAPI 2.0 specification
`_.