[DB-SIG] Re: [PyGreSQL] Version 3.0 PyGreSQL

[Jeremy Hylton]

I am having some trouble figuring out how to use the new PyGreSQL
DB-API interface.  The issues that are puzzling me are transactions,
cursors, and database-Python type conversions.

I've addressed this message to both lists (pygresql and db-sig)
because it's not clear where the people with the answers are.  The
pgdb module is new and the users of the pygresql list don't seem to
have much experience with the DB-API.

I'm a relative novice at all this.  My knowledge of SQL has been
cobbled together from Phil Greenspun's Web pages, the PostgreSQL docs,
and The Practical SQL Handbook (Bowman et al.).  I expect that I'm
puzzled largely because I am a novice.

Issue #1: Transactions

This may be a culture issue.  PostgreSQL executes transactions in
unchained mode -- aka autocommit.  According to Greenspun's SQL for
Web Nerds, Oracle's SQL*Plus works the same way.  I assumed that this
was the way things ought to be :-).

It looks like I was wrong, because the DB-API specifies implicit
transactions that are started automatically and committed or rolled
back on demand.

I think I prefer explicit creation of transactions and autocommit when
not specified.  (Hard to say for sure, since it's all I've ever used.)
Is there a standard way to accomplish this using the DB-API?  Also,
there seem to be methods for commit and rollback, but not begin.  I
assume that the omission is because the DB API does not do autocommit
by default.  However, if there is a way to enable autocommit, there
ought to be a method for creating a transaction.

Issue #2: Cursors

The DB API describes a cursor tersely.  (Dare I say cursorily?)  The
current PyGreSQL implementation does not use the database's CURSOR
facility; it merely.  Does that matter?  Is the DB API definition of
cursor intended to use a database cursor?

Issue #3: Types

The new PyGreSQL module returns floating point numbers for int types
using the fetch methods.  The old, non-DB API version of the module
returned an int.

I can't find any description of how to handle converions between
database types and Python types in the DB API.  Did I miss it, or is
there a standard way to handle the conversions?  The current behavior
(DB int -> PY float) seems broken, but I can't tell if that's just
following the spec.

Jeremy