very large inserts

[Kempf, Reed]

Thanks for the input and I looked into it and I believe that the executemany
basically simulates a for loop when doing inserts.  It still does the
inserts one by one instead of one massive insert which you can do in mysql.

I do not believe this is possible in dealing with an oracle database is
because of rollback segments.  If you were able to do a massive insert such
as:

insert into table (col1, col2, col3, col4)
       values (1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,7);

You would have to rollback the whole insert statement which would include
all of the rows plus the mysql insert statement is not ANSI complient.  The
last statement problable sums up the problems I am having.

oh well, I was just looking for a cheap and dirty python trick to get around
this.

Thanks

ReedK

-----Original Message-----
From: Mike C. Fletcher [mailto:mcfletch at rogers.com]
Sent: Tuesday, November 19, 2002 1:30 PM
To: Kempf, Reed
Cc: python-list at python.org
Subject: Re: very large inserts


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/




-- 
http://mail.python.org/mailman/listinfo/python-list