[DB-SIG] Insert for Oracledb

aaron watters aaron_watters@classic.msn.com
Mon, 24 Nov 97 12:02:04 UT

The "list of tuples" for dynamic value substitution is
an obvious implicit loop that allows inserts to run in
a more optimized fashion.  I don't think it's always (ever?)
implemented, however.  I'll implement it for Gadfly today.
:)   -- Aaron Watters

From:  owner-db-sig@python.org on behalf of Magnus Lycka
Sent:  Saturday, November 22, 1997 9:30 AM
To:  db
Subject:  Re: [DB-SIG] Insert for Oracledb

At 08:37 1997-11-21 -0600, Victoria White wrote:
> I had hoped that I could use execute(sqlstring, [params...])  as
documented in
>the DBI document  with params presented as one (or more) parameters giving 
>basically a List of Tuples (or arrays of values)  to be "bound" to the input 
>column names given, in some Oracle-specific/binding magic format, in the sql 
>string.  However, I find that  a) only one param is permitted with the
>implementation and
>b) I cannot find the secret way to "bind" the List of Tuples into the sql
>- not even for a single record insert, although I really want it to work
for a 
>whole list of tuples. It is probably extremely simple and just that I don't 
>understand the notation involved in specifying the sqlstring and its
column names 
>to be bound. However, I do appreciate your response and am interested to
>another living soul using this package.
>                        Vicky

If I understand correctly, you want to INSERT several records in one INSERT
statement. As far as I understand you can't do that in SQL (Oracle or any
version I know of) unless you INSERT from a SELECT. I.e. you can write:

INSERT INTO a_table (a_column) VALUES ('a_value');


INSERT INTO a_table (a_column) SELECT the_column FROM the_table WHERE

I don't think there is a way to INSERT several records at once, unless they
come from a SELECT as in the second example below. If you have a list of
records/tuples in your Python app, I think you will have to loop through
them and make an INSERT for each one of them.

I understand that you get the impression that you would be able to do
multiple inserts from the DBI spec though. I honestly don't know what
the list-of-tuples is for. Can anyone enlighten me about that? It would
be nice if it looped through a supplied list if it recieved several tuples
and performed the command for each of them, although it's not really something
you need, and if you have several inserts of updates, you will probably
want to 
check for error conditions after each one anyway (although that might not be
a problem if exceptions are used in a proper way I guess). 


Magnus Lycka, S/W Engineer, M.Sc.E.E; Folktrov. 6C, 907 51 Umea, Sweden
Tel: +46(0)90 198 498, GSM: +46(0)70 582 80 65, Fax: +46(0)70 612 80 65
<mailto:magnus.lycka@tripnet.se>         <http://www1.tripnet.se/~mly/>

DB-SIG  - SIG on Tabular Databases in Python

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

DB-SIG  - SIG on Tabular Databases in Python

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