[PYTHON DB-SIG] "Copperman" API

[Greg Stein]

Using Peter Wolk's terminology... I've updated the Strawman API to produce
the Copperman API :-)

Some changes that have been made:

- more comments as it seems this is getting close to correct (Ironman in
Peter-speak :-)
- dropped execagain() since it wouldn't have really worked quite right and
was a bit redundant, provided that exec() was spec'd correctly.
- converted arraysize() method to the 'arraysize' read/write attribute
- converted describe() to the 'description' read-only attribute (*)
- renamed setbufsize() to setoutputsize()
- added setinputsizes() (**)
- added size parameter to fetchmany()

(*) should this be an attribute or a function (as it was before) ? what do
people feel to be more Python-esque? A read-only attribute feels okay to
me.
(**) this will possibly be REMOVED. tomorrow, an engineer here is going to
run a test to measure the overhead of rebinding input buffers to an Oracle
cursor. This method was added to enable skipping the rebind step, presuming
that it is expensive. We want to measure it, though. If we find that
rebinding has little overhead, then setinputsizes() will be removed and
we'll just rebind all the time.

ISSUES:
- does anybody know if binding input buffers is an "expensive" operation
for some databases?
- need some examples (I'll mail a note a bit later)
- is callproc() sufficient? I have no experience with stored procedures to know

That is all I have for now. I know there are more people out there with
database experience than have mailed to the list. Can we get a few of you
to take a bit of time to post some commentary? :-)  Jim? Joel? You guys
spoke up at the Workshop... I know you guys are out there... :-)

Greg Stein, eShop Inc.
greg_stein@eshop.com


----------------------------
class <modname>:
  def __init__(self, connection_string):
    "Open a connection to a database, returning a Connection instance"
    pass

  def close(self):
    "Close the connection now (rather than whenever __del__ is called)."
    pass

  def commit(self):
    "Commit any pending transaction to the database."
    pass

  def rollback(self):
    "Roll the database back to the start of any pending transaction."
    pass

  def cursor(self):
    """\
Return a new cursor.  An exception may be thrown if the database does
not support a cursor concept."""
    pass

  def callproc(self, params=None):
    """\
Call a stored procedure with the given (optional) parameters.  Return the
result of the stored procedure."""
    pass

  # Implement all Cursor methods (except close), by routing the methods
  # to an internal cursor.  This cursor will be opened on the first
  # exec() on self.  If the database has no concept of cursors, then
  # simply perform the operation.


class Cursor:
  """\
This class represents a database cursor, which is used to manage the context
of a fetch operation.

Instances have a read/write attribute named 'arraysize' that specifies the
number of rows to fetch at a time with fetchmany.  This value is also used
when inserting multiple rows at a time (passing a tuple/list of tuples/lists
as the params value to exec()).  This will default to a single row.

Note that the arraysize is optional and is merely provided for higher
performance database interaction.  Implementations should observe it
with respect to the fetchmany() method, but are free to interact with
the database a single row at a time.

Instances also have a read-only attribute named 'description' that is a
tuple of 7-tuples.  Each 7-tuple contains information describing each
result column: (name, type_code, display_size, internal_size, precision,
scale, null_ok). This attribute will be None for operations that do not
return rows or if the cursor has not had an operation exec'd yet
"""

  def close(self):
    "Close the cursor now (rather than whenever __del__ is called)."
    pass

  def exec(self, operation, params=None):
    """\
Execute (prepare) a database operation (query or command).  Parameters
may be provided (as a tuple/list) and will be bound to variables in the
operation.  Variables are specified in a database-specific notation that
is based on the index in the parameter tuple (position-based rather than
name-based).

The parameters may also be specified as a tuple/list of tuples/lists to
insert multiple rows in a single operation.

A reference to the operation will be retained by the cursor.  If the same
operation object is passed in again, then the cursor can optimize its
behavior.  This is most effective for algorithms where the same operation
is used, but different parameters are bound to it (many times).

For maximum efficiency when reusing an operation, it is best to use the
setinputsizes() method to specify the parameter types and sizes ahead of
time.  It is legal for a parameter to not match the predefined information;
the implementation should compensate, possibly with a loss of efficiency.
"""
    pass

  def fetchone(self):
    "Fetch the next row of a query result, returning a single tuple."
    pass

  def fetchmany(self, size=None):
    """\
Fetch the next set of rows of a query result, returning as a list of
tuples. An empty list is returned when no more rows are available. The
number of rows to fetch is specified by the parameter.  If it is None,
then the cursor's arraysize determines the number of rows to be fetched.

Note there are performance considerations involved with the size parameter.
For optimal performance, it is usually best to use the arraysize attribute.
If the size parameter is used, then it is best for it to retain the same
value from one call to the next.
"""
    pass

  def fetchall(self):
    """\
Fetch all rows of a query result, returning as a list of tuples.  Note
that the cursor's arraysize attribute can affect the performance of this
operation."""
    pass

  def setinputsizes(self, sizes):
    """\
This can be used before a call to exec() to predefine memory areas for
the operation's parameters.  sizes is specified as a tuple -- one item
for each input parameter.  The item should be a Type object that
corresponds to the input that will be used, or it should be an integer
specifying the maximum length of a string parameter.  If the item is
None, then no predefined memory area will be reserved for that column
(this is useful to avoid predefined areas for large inputs).

Note that this is optional and is merely provided for higher performance
database interaction.  Implementations are free to do nothing and users
are free to not use it.
"""
    pass

  def setoutputsize(self, size, col=None):
    """\
Set a column buffer size for fetches of large columns (e.g. LONG).  Column
is specified as an index into the result tuple.  Using a column of None will
set the default size for all large columns in the cursor.

Note that this is optional and is merely provided for higher performance
database interaction.  Implementations are free to do nothing and users
are free to not use it.
"""
    pass


error = "<modname>.error"



=================
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
=================