Python embebbed with Oracle SQL*Plus

[Andy Todd]

olavb at yahoo.com (Olav) wrote in
<eef39a63.0106280338.2b8b6935 at posting.google.com>: 

>"James T. Dennis" <jadestar at idiom.com> wrote in message
>news:<9hebu5$12lo$1 at news.idiom.com>... 
>> In comp.lang.python Olav <olavb at yahoo.com> wrote:
>> 
>> > Some time ago I did some Oracle SQL*Plus on UNIX, 
>> > and I was able to write quite powerful scripts 
>> > embebbed in ksh (Korn Shell).
>>  
>> > Is something similar possible with Python?
>> 
>>      I presume your ksh scripts were passing
>>      SQL code to an sql command (possibly in
>>      the form of "here" documents).
>> 
>>      For course you can build arbitrary commands
>>      in Python and invoke the system to execute
>>      them.  (sys.system()?).
>
>This is a very simple Cygwin/NT example:
>***************
>PLUS33 scott/tiger at prod_sa <<EOF 
>select COU_NAME from M1_countries
>/
>quit
>eof
>********************
>So it is not simply building an SQL-string and calling
>some function.
Looks like it to me. If the above example is the entire contents of your 
ksh script (or even a fragment) you can exactly copy it as a Python script.

>
>It is really like SQL embedded in the script.
>There are also mechanism for passing variables back and forth.
>(I can easily write a script that takes a sql-statement as an arguments
>from the command line, something like osql select "*" from M1_countries)
And so you can with Python (using sys.argv).

>
>Typically what I use it for is regression-testing.  I pass arguments
>on the command line to the script, execute an SQL-statement and run
>the output through sort, grep, diff etc. 
All of which you can do in Python.

>
>I guess the mechanism is to build a temporary file, and call sqlplus
>with this.
>
>I think it might be more difficult with Python because it is actually 
>precompiled and because there is no equivalent of pipes.
I believe that your preconception here is that you need to use SQL*Plus to 
fire SQL statements at an Oracle database. This isn't strictly true. Using 
one of the db-api modules for Python (DCOracle seems to be the favourite 
here) you can substiture Python for SQL*Plus as well as ksh, sed, grep, 
awk, etc and write all of your scripts in one language.

Its basically a case of what you are most comfortable with, no one is 
forcing you to use Python :-)

For what its worth, I make my living as an Oracle programmer and I'm in the 
process of translating my 'toolbox' into Python scripts, if you need more 
details or any help please feel free to mail me on the address above 
(you'll need to remove 'spam.free').

I'd also strongly suggest checking out the Database SIG 
(http://www.python.org/sigs/db-sig/) and the Database topic guide 
(http://www.python.org/topics/database/) for more background and useful 
pointers.

>
>**
>Olav
>

Regards,
Andy
-- 
Content free posts a speciality