project pg_proboscis 1.0.2

pg_pqueue based GreenTrunk (API) and DB-API 2.0 implementation for PostgreSQL

pg_proboscis is a pg_pqueue based driver for PostgreSQL. It provides a GreenTrunk and a DB-API 2.0 interface to PostgreSQL databases.

contents

  • postgresql.interface.proboscis.tracenull
  • postgresql.interface.proboscis.greentrunk
  • postgresql.interface.proboscis.dbapi2
  • postgresql.interface.proboscis.python
  • pb_python (console script for postgresql.interface.proboscis.python)

examples

Run a console with a connection:

$ pb_python -h localhost -U pgsql -d postgres
Python 2.5.1 (r251:54863, Dec  8 2007, 09:22:18)
[GCC 4.0.1 (Apple Inc. build 5465)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(SavingConsole)
>>> gtx
<postgresql.interface.proboscis.tracenull.GreenTrunk_Connection[pq://pgsql@localhost:5432/postgres] T.0>
>>>

Make a GreenTrunk connection manually:

>>> import postgresql.interface.proboscis.greentrunk as gt
>>> con = gt.connector(user = 'pgsql', host = 'localhost', port = 5432, database = 'postgres')
>>> c=con()

Make a DB-API 2.0 connection manually:

>>> import postgresql.interface.proboscis.dbapi2 as db
>>> c=db.connect(user = 'pgsql', host = 'localhost', port = 5432, database = 'postgres')

Notably, DSN strings are not directly supported. However, if you want connection strings, the postgresql.utility.client.iri module has means for parsing PQ IRIs and the postgresql.utility.client.dsn module has means for parsing DSNs.


chapter connecting.txt

Connecting to PostgreSQL with pg_proboscis

Making a greentrunk connection

pg_proboscis conforms to postgresql.protocol.greentrunk.api, but the specification does not discuss how connections are made. Connecting to the database is a matter for the implementation to handle as how communication should occur is not for the specification to define.

The postgresql.interface.proboscis.greentrunk module provides connect, connector, and connection attributes. They provide the following capabilities:

connector

The connector is a usual mechanism to create a new connection. A connector is actually a "bookmark" object that creates the connection:

import postgresql.interface.proboscis.greentrunk as gt
con = gt.connector(host = 'localhost', user = 'postgres', port = 5432)
gtx = con()

Calling the created connector objects creates the connection to the database. The connector provides a channel for pg_proboscis to share information across connections.

connect

A simple function that returns a new connection. It provides a more convenient mechanism for simple systems. It implies the creation of the connector and just returns a new connection:

import postgresql.interface.proboscis.greentrunk as gt
gtx = gt.connect(host = 'localhost', user = 'postgres', port = 5432)
connection

The class that will be instantiated when a connection is made. connect will return an instance of this class. Normally, if this object is to be subclassed, the connector would also be subclassed and its connection_class would be set to the newly created connector class:

import postgresql.interface.proboscis.greentrunk as gt

class my_connection(gt.connection):
        def aNewHandyMethod(self):
                raise NotImplementedError

class my_connector(gt.connector):
        connection_class = my_connection

Making a connection using PQ IRIs

pg_proboscis doesn't support iri or dsn keyword arguments. So, in order to use these, a parser must be used to transform an IRI or DSN string into a dictionary with the necessary mappings to make the desired connection.

The pg_foundation projects provides an IRI parser. It can be used to construct the necessary dictionary for a connection:

import postgresql.interface.proboscis.greentrunk as gt
import postgresql.utility.client.iri as pg_iri
gtx = gt.connect(**pg_iri.parse('pq://postgres@localhost:5432/dbname'))

Notably, IRI parsing will not fill in defaults. The port and user fields are not filled in if they are not specified within the string.

Keyword Parameters

Connection interfaces only use keyword parameters. DSN or IRI strings are not directly supported--that is a matter for a parser or another interface to handle.

user
The user to connect and authenticate as.
password
The password for the user.
host
The IP host to connect to.
port
The port on the host to connect to. This is required iff host is the method.
database
The name of the database to connect to.
sslmode

Under what condition should the connection be secured using SSL. Can be one of:

"prefer"
If SSL is available, use it, otherwise fall back to an unsecured connection.
"allow"
By default, use an unsecured connection, but if only a secure connection is available, allow it.
"require"
SSL connection must be used.
"disallow"
SSL connection cannot be used.
unix
Connect to the database via a file system socket. The file system path to the socket file.
process

A tuple stating the subprocess to facilitate the connection using the process's standard input and output. This can be used make a connection via ssh. For example:

import postgresql.interface.proboscis.greentrunk as gt
gt.connect(user='postgres', process=(
                'ssh', 'remotehost.tld', 'nc localhost 5432'
        )
)
options
Extra command line options for the server.
tracer

This option provides a means to log PQ transmission. The representation of every message that was sent and received is sent to the function given as the tracer keyword's value. For example:

import postgresql.interface.proboscis.greentrunk as gt
gtx = gt.connect(
        user='postgres',
        host='localhost',
        port=5432,
        tracer=sys.stderr.write
)
other
Any other parameter passed in is used as a setting and value to initiate the connection with. This can be used to set the search_path, or other GUC parameter.

chapter pb_python.txt

The pb_python Console Script

The pb_python script provides a simple way to write scripts against a single target database. Usually these would tend to be scripts that do condition processing that is difficult to do in pure SQL, or simple report construction.

Usage

Usage: pb_python [connection options] [script] [-- script options] [args]

Options:
-d DATABASE, --database=DATABASE
database's name
-h SOCKET_PROVIDER, --host=SOCKET_PROVIDER
database server host
-p PORT, --port=PORT
database server port
-U USER, --username=USER
user name to connect as
-W, --password prompt for password
--unix-socket=SOCKET_PROVIDER
path to filesystem socket
--process=SOCKET_PROVIDER
the subprocess to execute to facilitate the connection
--server-options=OPTIONS
command line options for the remote Postgres backend
--ssl-mode=SSLMODE
SSL rules for connectivity
--require-ssl require an SSL connection
--role=ROLE run operation as the role
--fragment=FRAGMENT
Additional user data for the connection
-s SETTINGS, --setting=SETTINGS
run-time parameters to set upon connecting
--path=PATH Path to prefix search_path with
--pg-service-file=PG_SERVICE_FILE
Postgres service file use to for lookups
--pg-service=PG_SERVICE
Postgres service name to connect to
-I IRI, --iri=IRI
complete resource identifier, pq IRI
-1, --with-transaction
run operation with a transaction block
--python-context=PYTHON_CONTEXT
Python context code to run[file://,module:,<code>(__context__)]
-m PYTHON_MAIN Python module to run as script(__main__)
-c PYTHON_MAIN Python expression to run(__main__)
--python-postmortem=PYTHON_POSTMORTEM
Execute the specified function after a fatal exception
--pdb-pm Postmortem using pdb.pm
--pq-trace=PQ_TRACE
trace PQ protocol transmissions
--version show program's version number and exit
--help show this help message and exit

Environment

pb_python creates a Python environment with an already established connection based on the given arguments. It uses the pkg:jwp_python_command package to aid in harnessing the basic Python command features and then pkg:pg_foundation to fill in the connectivity options. In order to provide global access to these additional object, it assigns them in __builtins__ to the following names:

  • gtx (the connection object)
  • gtc (the connector that created the connection)
  • xact (gtx.xact)
  • settings (gtx.settings)
  • query (gtx.query)
  • cquery (gtx.cquery)
  • proc (gtx.proc)
  • cursor (gtx.cursor)
  • statement (gtx.statement)

All of these are provided for convenience. With a single target being the primary use-case, ambiguity is not an issue. Surely, saving four characters for accessing each of these is not substantial, but it helps keep code concise and tends to be very useful when using pb_python interactively.

Interactive Console Backslash Commands

Inspired by psql:

>>> \?
Backslash Commands:

  \?      Show this help message.
  \E      Edit a file or a temporary script.
  \e      Edit and Execute the file directly in the context.
  \i      Execute a Python script within the interpreter's context.
  \s      Wrap each execution code of in a new SAVEPOINT; Rollback on exception
  \set    Configure environment variables. \set without arguments to show all
  \x      Execute the Python command within this process.

package postgresql.interface.proboscis

Proboscis interface space

module postgresql.interface.proboscis.python

imports
import postgresql.utility.client.config as cl_config
import postgresql.protocol.greentrunk.python as gt_python
import optparse
import os
import postgresql.interface.proboscis.greentrunk as pg
import postgresql.utility.client.option as pg_opt
import sys
functions
def command(args, environ = 
{'os.environ': 'masked'}
):
def main():
data

module postgresql.interface.proboscis.tracenull

GreenTrunk interface for PostgreSQL servers that support the
PQ version 3.0 protocol.
imports
import codecs
import crypt
import errno
import postgresql.protocol.greentrunk.api as gt_api
import os
import postgresql.encodings.aliases as pg_enc_aliases
import postgresql.exceptions as pg_exc
import postgresql.protocol.typical.stdio as pg_io
import postgresql.utility.client.iri as pg_iri
import postgresql.strings as pg_str
import postgresql.types as pg_types
import postgresql.protocol.typical.oidmaps as pg_typio
import postgresql.protocol.pqueue.client3 as pq
import socket
import sys
import name.jwp.terminfo as ti
import types
import pytz as tz
import postgresql.utility.version as version
import warnings
classes
class GreenTrunk_Connector(object):
GreenTrunk_Connector(**config) -> Connector

All arguments to Connector are keywords. At the very least, user,
and socket, may be provided. If socket, unix, or process is not
provided, host and port must be.
methods
def __init__(self):
def __call__(self, *args):
def __getitem__(self, k):
def __repr__(self):
def _typio(self, x):
def create(self, *args):
def get(self, k, otherwise = 
None
):
def socket_connect(self):
create and connect a socket using the configured _socket_data
def socket_create(self):
static methods
def notifier(
msg,
ti = 
name.jwp.terminfo

):
properties
property iri
class data
sslmode = 
'prefer'
class ResultHandle(object):
Object used to provide interfaces for copy and simple commands. Ties to
a given transaction. These objects provide a way for transactions to be
marked as defunct, so that no more time is wasted completing them.
methods
def __init__(self, connection, query, params):
def __call__(self, copyseq, buffersize = 
100
):
def __iter__(self):
def _contract(self):
reduce the buffer's size
def _discover_type(self, xact):
helper method to step until the result handle's type message
is received. This is a copy begin message or a complete message.
def _expand(self):
internal helper function to put more copy data onto the buffer for
reading. This function will only append to the buffer and never
set the offset.
def close(self):
def command(self):
The completion message's command identifier
def count(self):
The completion message's count number
def next(self):
get the next copy line
def read(self, count = 
None
):
Read the specified number of COPY lines from the connection.
def write(self, copylines):
Write a sequence of COPY lines to the connection.
properties
property closed
whether the result handle is the current transaction
class data
_result_types = 
('G', 'H', 'C', 'I')
class GreenTrunk_Connection(connection):
GreenTrunk_Connection(connector) -> GreenTrunk_Connection

GreenTrunk API <-> PQ Protocol <-> PostgreSQL
methods
def __init__(self, connector, *args):
Create a connection based on the given connector.
def ClientStatementCursor(self, *args):
Create a Portal using the given client prepared statement
def ResultHandle(*args):
Execute a query with the given arguments that does not return rows
def _A(self, msg):
[internal] Send notification to any listeners; NOTIFY messages
def _N(self, msg):
[internal] print a notice message using the notifier attribute
def _S(self, msg):
[internal] Receive ShowOption message
def __context__(self):
def __enter__(self):
Connect on entrance.
def __exit__(self, type, value, tb):
Close the connection on exit.
def __nonzero__(self):
connection can issue a query without an existing impediment
def __repr__(self):
def _backend_gc(self):
close portals and statements slated for closure.
NOTE: Assumes no running transaction.
def _clear(self):
Clear container objects of data.
def _complete(self):
[internal] complete the current transaction
def _negotiate(self):
Initialize the connection between the client and the server.
This requires that a socket interface has been set on the
'socket' attribute.
def _pack_timestamptz(self, dt):
def _pop(self):
[internal] remove the transaction and raise the exception if any
def _postgres_error(self, em):
[internal] lookup a PostgreSQL error and instantiate it
def _push(self, xact):
[internal] setup the given transaction to be processed
def _read_into(self):
[internal] protocol message reader. internal use only
def _read_messages(self):
[internal] read more messages into self._read when self._read is empty
def _reset(self):
Reset state and connection information attributes.
def _send_message_data(self):
[internal] send unsent data initialized for delivery
def _standard_read_messages(self):
[internal] read more messages into self._read when self._read is empty
def _standard_write_messages(self, messages):
[internal] protocol message writer
def _step(self):
[internal] make a single transition on the transaction
def _traced_read_messages(self):
[internal] _message_reader used when tracing
def _traced_write_messages(self, messages):
[internal] _message_writer used when tracing
def _typio(self, typoid, recursed = 
False
):
lookup a type's IO routines from a given typoid
def _typioseq_from_pqdesc(self, desc, index):
create a sequence of I/O routines from a pq descriptor
def _unpack_timestamptz(self, data):
def _write_messages(self, messages):
[internal] protocol message writer
def _xp_query(self, *args):
def _xp_query_messages(
self,
string,
args = 
()
,
rformats = 
()
,
argtypes = 
()

):
def close(self):
Close the connection and reinitialize the state and information
def connect(self):
Establish the connection to the server
def cquery(self, *args):
Create a query and store it in a dictionary that associates the
created query with the string that defines the query. Passing the
string back into the method will yield the same query object that was
returned by prior calls. (Not consistent across disconnects)
def cursor(*args):
Create a Portal object that references an already existing cursor
def decode_string(self, strobj):
def encode_string(self, strobj):
def escape_string(self, strobj):
def execute(self, query):
Execute an arbitrary block of SQL
def interrupt(self):
Send a CancelQuery message to the backend
def proc(*args):
Create a StoredProcedure object using the given procedure identity
def query(*args):
Create a query object using the given query string
def quote_string(self, strobj):
def reconnect(self, *args):
close() and connect()
def reset(self):
restore original settings, reset the transaction, drop temporary objects
def statement(*args):
Create a query object using the statement identifier
def synchronize(self):
Explicitly send a Synchronize message to the backend.
Useful for forcing the completion of lazily processed transactions.
[Avoids garbage collection not pushing; _complete, then set _xact.]
static methods
def _update_encoding(connection, key, value):
[internal] subscription method to client_encoding on settings
def _update_timezone(connection, key, value):
[internal] subscription method to TimeZone on settings
properties
property closed
Whether the connection is closed or not
property user
property tracer
Callable object to pass protocol trace strings to. (Normally a write method.)
class data
version = 
None
type = 
None
version_info = 
None
_tracer = 
None
class Portal(cursor):
Class for controlling a Postgres protocol cursor. Provides buffering,
seeking, and iterator interfaces
methods
def __init__(self, connection, portal_id, fetchcount = 
30
):
def __getitem__(self, i):
def __iter__(self):
def _contract(self):
reduce the number of tuples in the buffer by removing past tuples
def _expand(self, count = 
None
, next_xact = 
None
):
internal helper function to put more tuples onto the buffer for
reading. This function will only append to the buffer and never
set the offset.
def _mktuple(self, x):
given a PQ tuple, return a map tuple
def _xp_fetchmore(self, count = 
None
):
def _xp_move(self, whence, position, count = 
None
):
def close(self):
def move(self, location, count = 
None
):
def next(self):
def read(self, quantity = 
4294967295L
):
def scroll(self, quantity):
def seek(self, location, whence = 
None
, count = 
None
):
class data
class ClientStatementCursor(Portal):
Client statement created cursors
methods
def __init__(
self,
query,
params,
output,
output_io,
rformats
):
def __del__(self):
class Cursor(Portal):
Server declared cursor interfaces
methods
def __init__(self, connection, cursor_name):
class StoredProcedure(proc):
Interface to functions kept in the backend
methods
def __init__(self, connection, ident, description = 
()
):
def __call__(self, *args):
def __repr__(self):
class PreparedStatement(query):
methods
def __init__(self, connection, statement_id, title = 
None
):
def __call__(self, *args):
def __invert__(self, *args):
def __iter__(self):
def __lshift__(self, tupleseq, tps = 
40
):
def __repr__(self):
def close(self):
def finish(self):
complete initialization that the prime() method started.
def first(self, *args):
def load(self, tupleseq, tps = 
40
):
def prepare(self):
def prime(self):
Push initialization messages to the server, but don't wait for
the return as there may be things that can be done while waiting
for the return. Use the finish() to complete.
def reprepare(self):
class ClientPreparedStatement(PreparedStatement):
A prepared statement generated by the connection user
methods
def __init__(self, connection, src, *defaults):
def __del__(self):
def prime(self):
Push out initialization messages for query preparation
class ServerSettings(settings):
methods
def __init__(self, connection):
def __call__(self):
def __context__(self):
def __enter__(self):
def __exit__(self, exc, val, tb):
def __getitem__(self, i):
def __setitem__(self, i, v):
def _clear_cache(self):
def _notify(self, msg):
def get(self, k, alt = 
None
):
def getset(self, keys):
def iteritems(self):
def iterkeys(self):
def itervalues(self):
def subscribe(self, key, callback):
def unsubscribe(self, key, callback):
def update(self, d):
properties
property path
structured search_path interface
class HeapTuple(tuple):
Name addressable items tuple; mapping and sequence
class methods
def __new__(subtype, attr, attmap = 
{}
):
methods
def __getitem__(self, i):
def attindex(self, k):
def get(self, i):
def has_key(self, k):
def items(self):
def iteritems(self):
def iterkeys(self):
def itervalues(self):
def keys(self):
def values(self):
class TransactionManager(xact):
methods
def __init__(self, connection):
def __call__(self, gid = 
None
, mode = 
None
, isolation = 
None
):
def __context__(self):
def __enter__(self, isolation = 
None
, mode = 
None
):
def __exit__(self, type, value, tb):
def _commit_string(self, level):
def _execute(self, qstring, adjustment):
def _rollback_string(self, level):
def _start_string(self, level, isolation = 
None
, mode = 
None
):
def abort(self):
def begin(self, isolation = 
None
, mode = 
None
):
def checkpoint(self, isolation = 
None
, mode = 
None
):
def commit(self):
def commit_prepared(self, gid):
def reset(self):
def restart(self, isolation = 
None
, mode = 
None
):
def rollback(self):
def rollback_prepared(self, gid):
def start(self, isolation = 
None
, mode = 
None
):
def wrap(self, callable, *args):
static methods
def _start_block_string(mode, isolation):
properties
property prepared
property failed
property closed
class data
functions
def secure_socket(
connection,
keyfile = 
None
,
certfile = 
None
,
rootcertfile = 
None

):
take a would be connection socket and secure it with SSL
def ID(s, title = 
None
):
generate an id for a client statement or cursor
def row_unpack(seq, typio, decode):
Transform object data into an object using the associated IO routines
def row_pack(seq, typio, encode):
Transform objects into object data using the associated IO routines
def anyarray_unpack_elements(a, unpack):
generator for yielding None if x is None or unpack(x)
def anyarray_unpack(unpack, data):
unpack the array, normalize the lower bounds and return a pg_types.array
def array_typio(
connection,
typoid,
typio,
hasbin_input,
hasbin_output
):
create an array's typio pair
def composite_typio(connection, typoid):
create the typio pair for a given composite type
def iter_row_pack(htiter, typio, encode):
Used to process tuples for bulk loads
def idxiter(tupseq, idx):
def subidxiter(seq, idx):
def attmap_from_pqdesc(desc):
create a dictionary from a pq desc that maps attribute names
to their index
def extract_count(rmsg):
helper function to get the last set of digits in a command completion
as an integer.
def format_message(msg):
def stderr_notifier(
msg,
ti = 
name.jwp.terminfo

):
data

module postgresql.interface.proboscis.dbapi2

DB-API 2.0 conforming interface on proboscis.greentrunk
imports
import datetime
import postgresql.interface.proboscis.greentrunk as gt
import postgresql.strings as pg_str
import postgresql.types as pg_type
import time
classes
class InterfaceError(Error):
class NotSupportedError(Error):
class Connection(object):
DB-API 2.0 connection implementation for GreenTrunk connection objects.
methods
def __init__(self, connection):
def close(self):
def commit(self):
def cursor(self):
def rollback(self):
class data
class Cursor(object):
methods
def __init__(self, C):
def __iter__(self):
def _mkquery(self, query, parameters):
def callproc(self, proname, args):
def close(self):
def execute(self, query, parameters = 
None
):
def executemany(self, query, pseq):
def fetchall(self):
def fetchmany(self, arraysize = 
None
):
def fetchone(self):
def next(self):
def nextset(self):
def setinputsizes(self, sizes):
def setoutputsize(self, sizes, columns = 
None
):
properties
property _portal
class data
description = 
None
arraysize = 
1
rowcount = 
-1
functions
def <lambda>(x):
def <lambda>(x):
def <lambda>(x):
def dbapi_type(typid):
def convert_keyword_parameters(nseq, seq):
Given a sequence of keywords, `nseq`, yield each mapping object in `seq` as a
tuple whose objects are the values of the keys specified in `nseq` in an
order consistent with that in `nseq`
def connect():
data
BINARY = 
str
Binary = 
str
NUMBER = 
int
ROWID = 
int
STRING = 
unicode
Timestamp = 
datetime.datetime
_connectors = 
{}
apilevel = 
'2.0'
paramstyle = 
'pyformat'
threadsafety = 
1

module postgresql.interface.proboscis.greentrunk

GreenTrunk module--only requires the Connector and Connection
functions
def connect():
data