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

Stuart Bishop zen@shangri-la.dropbear.id.au
Fri, 13 Sep 2002 12:17:26 +1000


On Saturday, August 31, 2002, at 01:16  AM, m.frasca@hyperborea.com 
wrote:

> 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?

You don't without using implementation-dependent code. Just as auto 
generating primary
keys is implementation-dependent, so is retrieving the last used value 
(in Oracle's case,
selecting the current value of the sequence and making sure you have a 
separate connection
per thread).

> 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> ...')?

Its not in the API due to the design of SQL (or at least SQL '92).
Its not going to happen unless SQL grows a 'returns' clause to the 
'select'
statement. A higher level API that doesn't use SQL as its interface 
could do
it, but then you have to worry about if your particular RDBMS is 
supported.

> 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.

It isn't really that hard to have a Python 'if' statement executing the
correct RDBMS specific code. Especially as your DDL already
have to be written against a particular RDBMS implementation.

-- 
Stuart Bishop <zen@shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/