: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
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. 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
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. 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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    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
    <http://www.python.org/dev/peps/pep-0249/>`_; 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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :rtype: :class:`datetime.datetime`

.. function:: Binary(string)

    Construct an object holding binary data.

    This function is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

    :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. method:: commit()
    
        Commits the current database transaction.

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. method:: rollback()

        Rolls back the current database transaction.

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. method:: close()

        Closes the database connection.

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. method:: xid(format_id, global_transaction_id, branch_qualifier)

        Create a Transaction IDs (only global_transaction_id is used,
        format_id and branch_qualifier are not used in postgres)
        global_transaction_id may be any string identifier supported by postgres
        returns a tuple (format_id, global_transaction_id, branch_qualifier)

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09

    .. method:: tpc_begin(xid)

        Begin a two-phase transaction with the given Transaction ID xid.
        Actually, this method issues a normal BEGIN in postgres.

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09

    .. method:: tpc_prepare()

        Performs the first phase of a transaction started with tpc_begin(). 
        A ProgrammingError is raised if this method is used outside of a TPC 
        transaction.

        For more information, see `PostgreSQL PREPARE TRANSACTION command
        <http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html>`_

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09

    .. method:: tpc_commit(xid=None)

        Commits a TPC transaction previously prepared with tpc_prepare().
        If Transaction ID xid is not given, currently started TPC ID is used.

        A ProgrammingError is raised if this method is used when no TPC 
        transaction is prepared.

        For more information, see `PostgreSQL COMMIT PREPARED command
        <http://www.postgresql.org/docs/current/static/sql-commit-prepared.html>`_

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09

    .. method:: tpc_rollback(xid=None)

        Rolls back a TPC transaction previously prepared with tpc_prepare().
        If Transaction ID xid is not given, currently started TPC ID is used.

        A ProgrammingError is raised if this method is used when no TPC 
        transaction is prepared.

        For more information, see `PostgreSQL ROLLBACK PREPARED command
        <http://www.postgresql.org/docs/current/static/sql-rollback-prepared.html>`_

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09

    .. method:: tpc_recover()

        Returns a list of all currently available prepared transaction's xid
        (listed in the pg_prepared_xacts 
        `PostgreSQL pg_prepared_xacts system view
        <http://www.postgresql.org/docs/current/static/view-pg-prepared-xacts.html>`_)

        This function is part of the `DBAPI 2.0 specification
        <http://www.python.org/dev/peps/pep-0249/>`_ for optional  
        Two-Phase Commit Extensions.

        .. versionadded:: 1.09
        
    .. 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:: server_version

        String property containing the database version as reported by the server.

        This is an extension to the DBAPI specification; it is a pg8000
        extension.

        .. versionadded:: 1.09

    .. attribute:: autocommit
        
        Boolean property to enable or disable the auto-commit feature.
        Per DBAPI specification, this must be initially off (False), so the 
        application should call :meth:`~pg8000.dbapi.ConnectionWrapper.commit`.
        If this attribute is set to True, no enclosing transaction will be 
        automatically started when executing a database operation. Instead, each 
        statement will be "committed" implicitly immediately when executed.
        This behaviour is required by some PostgreSQL commands like CREATE
        DATABASE or VACUUM that cannot run inside a transaction block.

        .. versionadded:: 1.09

    .. 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
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. 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
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. 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
        <http://www.python.org/dev/peps/pep-0249/>`_.

        :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

        :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

        :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

        :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

        :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
        <http://www.python.org/dev/peps/pep-0249/>`_.

    .. 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
        <http://www.python.org/dev/peps/pep-0249/>`_, 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
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: Error(exceptions.StandardError)

    See :exc:`pg8000.errors.Error`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: InterfaceError(Error)

    See :exc:`pg8000.errors.InterfaceError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: DatabaseError(Error)

    See :exc:`pg8000.errors.DatabaseError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: InternalError(DatabaseError)

    See :exc:`pg8000.errors.InternalError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: OperationalError(DatabaseError)

    See :exc:`pg8000.errors.OperationalError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: ProgrammingError(DatabaseError)

    See :exc:`pg8000.errors.ProgrammingError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: IntegrityError(DatabaseError)

    See :exc:`pg8000.errors.IntegrityError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: DataError(DatabaseError)

    See :exc:`pg8000.errors.DataError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

.. exception:: NotSupportedError(DatabaseError)

    See :exc:`pg8000.errors.NotSupportedError`

    This exception is part of the `DBAPI 2.0 specification
    <http://www.python.org/dev/peps/pep-0249/>`_.

