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

[Anthony Tuininga]

There is no DB independent way of doing this that I am aware of. In
Oracle you use the "returning" clause. I am not familiar with DCOracle2
but I suspect something similar can be done.

import cx_Oracle

v_Connection = cx_Oracle.connect("user/pw@tns")
v_Cursor = v_Connection.cursor()
v_Vars = v_Cursor.setinputsizes(p_PrimaryKeyValue = cx_Oracle.NUMBER)
v_Cursor.execute("""
    insert into SomeTable (
      SomeValue
    ) values (
      :p_SomeValue
    ) returning PrimaryKey into :p_PrimaryKeyValue""")
print "PrimaryKeyValue:", v_Vars["p_PrimaryKeyValue"].getvalue()

This method is also not compatible with the DB-API as the DB-API does
not specify a method for handling output variables in anything other
than stored procedures. If you want to conform to the DB-API, you will
have to use a stored procedure and use the cursor method callproc()
instead of the method above.

On Fri, 2002-08-30 at 09:16, 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?
> 
> 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
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
-- 
Anthony Tuininga
anthony@computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com