very large inserts

[Mike C. Fletcher]

Isn't that what the executemany method in DBAPI is intended to allow?

*executemany*(operation,seq_of_parameters)
    Prepare a database operation (query or command) and then execute it
    against all parameter sequences or mappings found in the sequence
    |seq_of_parameters|.

    Modules are free to implement this method using multiple calls to
    the |execute()| method or by using array operations to have the
    database process the sequence as a whole in one call.

That is, the DB may optimise it, or it may have to go through execute, 
but this is where the optimisation comes if it's available.  If I 
understand correctly, you'd create a parameterised insert:

    insert = """INSERT INTO table (col1, col2, col3, col4) values (%1, 
%2, %3, %4)"""

And then call executemany( insert, my_values_list )

Good luck,
Mike


Kempf, Reed wrote:

>Hello,
>
>My name is Reed and I am new to the list as of today.  I have been working
>with python for about 6 months and I have an issue where I am stumped.
>
>My background is in python, pl/sql and oracle database management with a
>touch of mysql database management.
>
>Anyway, I know in mysql you can do a bulk insert or an insert where you can
>insert many records with one insert statement like this:
>
>MYSQL - insert into table (col1, col2, col3, col4)
>        values (1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,7);
>
>In oracle, you would have to do 1 insert at a time unless you are using
>pl/sql in which you can do a bulk insert (as far as I know).
>
>ORACLE - insert into table (col1, col2, col3, col4)
>         values (1,2,3,4);
>         insert into table (col1, col2, col3, col4)
>         values (2,3,4,5); and so forth.......
>
>My question is, can python simulate a mysql bulk insert in python?
>
>I am running a linux distribution 6.2 with kernel 2.4.17 and oracle 8.1.7.4
>patch set.  I am also using python 2.1.  Currently in my python script I
>loop through a python dictionary and build an insert statement which I then
>pass through a connection to update oracle and move onto the next python
>dictionary key.  
>
>This works but I would sincerely like to build one very large insert
>statement and pass it to the oracle connection all at once.  This is an
>issue for me since I am going through sqlnet and across a VPN to update the
>oracle database.  The less cursors I pass through the connection, the
>better.
>
>Thanks in advance,
>
>ReedK
>
>
>  
>

-- 
_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/