Win XP: Problem with shell scripting in Python

[Steve Holden]

A.M wrote:
>>I dare hardly suggest this, but might it not be better to use Python's
>>database functionality to perform the task? The language can access both 
>>databases, and you might find it quicker. Then again, if your database 
>>experience is limited, you may not ...
>>
>>regards
>> Steve
>>-- 
>>Steve Holden       +44 150 684 7255  +1 800 494 3119
>>Holden Web LLC/Ltd          http://www.holdenweb.com
>>Love me, love my blog  http://holdenweb.blogspot.com
>>Recent Ramblings     http://del.icio.us/steve.holden
>>
> 
> 
> Hi Steven,
> 
> 
> 
> Based on my experience, the fastest possible way to import raw data into 
> Oracle is SQL*Loader. Similarly, the fastest way to extract raw data from 
> SQL server is BCP.
> 
> 
> 
> My script transfers 40,000,000 records (actually big records) from sql 
> server to oracle in 20 Min. I tried ODBC to do the same work. I turned off 
> all record locking and transactions through query hints. The actual program 
> was a C# program. After 12 hours, I just stopped the program.
> 
I'm not that surprised - it was just an inquiry. Usually the database 
bulk dump and load utilities take advantage of every trick in their 
respective books to provide speed. A native driver might have done 
better than ODBC, but you are probably correct on going that route for 
speed.
> 
> 
> I created DOS batch files to control BCP and SQL*Loader steps. It is faster 
> than any fancy GUI tools. Now I am using Python.
> 
> I am thinking to add comprehensive logging to the ETL (extract transform 
> load) process. All details command line outputs will be stored in database. 
> System administrators can query database and watch how the ETL job is 
> working.
> 
> At this point I have quite challenge with capturing BCP's stdout/stderr 
> output to string variables in Python program.
> 
> I'll post the final outcome here.
> 
OK, good luck.

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Love me, love my blog  http://holdenweb.blogspot.com
Recent Ramblings     http://del.icio.us/steve.holden