[DB-SIG] http://www.python.org/topics/database/DatabaseAPI-2.0.html

[m.frasca@hyperborea.com]

Hallo everybody,

I'm developing yet another web-based interface to an Oracle database
using Python and DCOracle2.  I would like to write as little
implementation-dependent code as possible.  In this attempt, I miss
something in the API.

my question is about automatically generated primary keys.

when I let the database generate the primary keys (the technique involved
is implementation-dependent), how on earth do I retrieve the keys of my
newly inserted record?

in practice:
>>> import DCOracle2
>>> db = DCOracle2.connect('user/password@service')
>>> crsr = db.cursor()
>>> crsr.execute("insert into persone (nome, cognome, datanascita) values ('Ave', 'Ninchi', '14-Dic-1915')")

here the method returns a value, but I assume that this is only to be
used by the ones how developed the DCOracle2 package.

how do I retrieve the primary key of the 'Ave Ninchi' record?

the cursor.execute method can be used to prepare a SELECT and thus,
it makes sense to ask the cursor to fetchxxx what was prepared by the
executexxx.  this is what I tried, with no luck:

dco2.ProgrammingError: No results available from last execute operation

maybe this possibility is offered somewhere else, but I don't find it in
the documentation of the API.  if it is not there, what about extending
the API by requiring the execute('INSERT ...') to prepare the cursor as
if the user had asked to execute('SELECT <primary keys> ...')?

the other option, as far as I can understand, is not to use any
automatically generated primary keys and do all the work from inside
Python.  Whenever I try to insert any object into a table, I should
check if the primary keys are defined, if that is not the case, generate
them in my Python program and only then put the object into the database.

I don't like this solution (in practice, it forces me to accesses
the database *only* from the Python program) I wonder if there was a
discussion on this regard and if the API2 reflects the results of the
discussion.

thanks for your time,

Mario Frasca